Install PostgreSQL on Ubuntu: Database Setup and Admin Guide

PostgreSQL (often called Postgres) is an open-source relational database system. It supports advanced features like JSON storage, full-text search, and complex queries. This makes it a popular choice for everything from small personal projects to large production applications.

This guide shows you how to install PostgreSQL on Ubuntu, create users and databases, and set up remote access.

<strong>Prerequisite:</strong>&nbsp;You need a user account with sudo access.

Install PostgreSQL on Ubuntu

Run these commands to install PostgreSQL and the contrib package. The contrib package adds extra tools and features that are useful for day-to-day database work:

bashsudo apt updatesudo apt install postgresql postgresql-contrib

The PostgreSQL service starts on its own after install. Verify it by checking the version:

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

If you see a version string in the output, PostgreSQL is installed and working.

PostgreSQL Roles and Authentication

PostgreSQL manages database access using roles. A role can represent a single user or a group of users. Before you create any databases, it helps to understand how authentication works.

The most common authentication methods are:

• Trust — the role can connect without a password if pg_hba.conf allows it.
• Password — the role needs a password. Supports scram-sha-256 and md5 formats.
• Peer — for local connections. PostgreSQL checks that the Linux system user matches the database role.
• Ident — similar to Peer, but works over TCP/IP.

All authentication rules live in /etc/postgresql/12/main/pg_hba.conf. The default method for local connections is Peer.

When you install PostgreSQL, a system user called postgres is created. This is the database superuser. To open the PostgreSQL shell, run:

bashsudo su - postgrespsql

Or, without switching users:

bashsudo -u postgres psql

Type \q to exit.

Create a Role and Database

Only superusers or roles with CREATEROLE privilege can create new roles.

Create a new database role:

bashsudo su - postgres -c "createuser john"

Create a new database:

bashsudo su - postgres -c "createdb johndb"

Open the PostgreSQL shell and grant the role access to the database:

bashsudo -u postgres psql

sqlGRANT ALL PRIVILEGES ON DATABASE johndb TO john;

Allow Remote Connections to PostgreSQL

By default, PostgreSQL only listens on 127.0.0.1. To accept connections from other machines, open the config file:

bashsudo nano /etc/postgresql/12/main/postgresql.conf

Find the listen_addresses setting and change it to:

listen_addresses = '*'

Save the file and restart PostgreSQL:

bashsudo service postgresql restart

Now edit pg_hba.conf to define who can connect and how. Here are some example rules:

# Allow user 'jane' from anywhere using a passwordhost    all     jane    0.0.0.0/0       md5# Allow user 'jane' from a trusted IP without a passwordhost    all     jane    192.168.1.134   trust

Open port 5432 in your firewall, allowing access from your trusted subnet only:

bashsudo ufw allow proto tcp from 192.168.1.0/24 to any port 5432

PostgreSQL is now installed and ready to use on your Ubuntu server. You can manage roles and databases from the command line, and the authentication system gives you full control over who can access what. For more details, check the official PostgreSQL documentation. Questions? Leave a comment below.