Install PostgreSQL on Ubuntu 18.04 with Remote Access Setup

PostgreSQL (also called Postgres) is a free, open-source, object-relational database management system with a strong reputation for reliability, SQL standards compliance, and advanced feature support. It handles complex queries, JSON data, full-text search, and custom data types, making it a natural fit for web applications that outgrow simpler databases.

This guide shows you how to install PostgreSQL on Ubuntu 18.04, connect to the database, manage roles and authentication, create users and databases, and configure the server to accept remote connections.

<strong>Prerequisite:</strong>&nbsp;You need sudo access.

Install PostgreSQL on Ubuntu: Package Setup and Connection Basics

Install PostgreSQL and the contrib package, which adds useful extensions and utility functions not included in the core installation:

bashsudo apt updatesudo apt install postgresql postgresql-contrib

PostgreSQL starts automatically after installation. Verify it is running by connecting and printing the server version:

bashsudo -u postgres psql -c "SELECT version();"

psql is the PostgreSQL interactive terminal. It lets you run SQL queries, manage database objects, and administer the server from the command line.

To open a psql session as the postgres superuser:

bashsudo -u postgres psql

Type \q to exit. The postgres system user is created automatically during installation. It is the PostgreSQL superuser — equivalent to the MySQL root user. It uses peer authentication by default, which is why sudo -u postgres psql works without a password.

PostgreSQL Roles, Authentication Methods, and User Management

PostgreSQL uses roles instead of traditional user accounts. A role can represent a single user or a group of users. Permissions are granted at the role level. Authentication rules for each role are defined in /etc/postgresql/10/main/pg_hba.conf.

Supported authentication methods:

• Trust: Connect without a password if criteria in pg_hba.conf are met
• Password: Authenticate with a password (scram-sha-256, md5, or clear-text)
• Peer: Match the Linux system user to the PostgreSQL role (local connections only)
• Ident: Same as Peer but over TCP/IP connections

Create a role and database. Run these commands as the postgres system user:

bashsudo su - postgres -c "createuser john"sudo su - postgres -c "createdb johndb"

Grant the role full privileges on the database:

bashsudo -u postgres psql

sqlGRANT ALL PRIVILEGES ON DATABASE johndb TO john;

Type \q to exit the psql shell.

Enable Remote Access to PostgreSQL with postgresql.conf

By default, PostgreSQL only listens on 127.0.0.1. To accept connections from remote hosts, edit the configuration file:

bashsudo vim /etc/postgresql/10/main/postgresql.conf

In the CONNECTIONS AND AUTHENTICATION section, change or add:

listen_addresses = '*'

Save the file and restart PostgreSQL:

bashsudo service postgresql restart

Verify PostgreSQL is now listening on all network interfaces:

bashss -nlt | grep 5432

The output should show 0.0.0.0:5432, confirming remote connections are possible.

Configure remote authentication. Edit /etc/postgresql/10/main/pg_hba.conf and add rules using this format:

host  database  user  address  method

For example, to allow user jane to connect from any IP using a password:

host  all  jane  0.0.0.0/0  md5

Changes to pg_hba.conf require a service restart to take effect.

PostgreSQL is now installed and configured on your Ubuntu 18.04 server. Visit the PostgreSQL documentation to learn about indexing, replication, connection pooling, and advanced query tuning. Leave a comment below if you run into any issues.