How to Install and Configure PostgreSQL

PostgreSQL is the go-to open-source relational database for serious workloads. This guide walks through a fresh install, creating roles and databases, locking down authentication, and safely opening remote access — everything you need to run a production-ready instance.

Install PostgreSQL

Debian / Ubuntu

The packages in the default Ubuntu/Debian repos are often one or two major versions behind. Use the official PGDG apt repository to get the current release (PostgreSQL 16 at time of writing).

sudo apt install -y curl ca-certificates
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc \
  | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/pgdg.gpg

echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" \
  | sudo tee /etc/apt/sources.list.d/pgdg.list

sudo apt update
sudo apt install -y postgresql-16

Fedora / RHEL / Rocky Linux

On RHEL-family systems, disable the built-in PostgreSQL module first to avoid conflicts with the PGDG repository packages.

sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf -qy module disable postgresql
sudo dnf install -y postgresql16-server postgresql16

Arch Linux

sudo pacman -S postgresql

Initialize and Start the Service

Debian / Ubuntu

The PGDG package initializes the cluster automatically. Just enable and start the service.

sudo systemctl enable --now postgresql

Fedora / RHEL / Rocky Linux

On RPM-based systems you must run the initdb step manually before starting.

sudo /usr/pgsql-16/bin/postgresql-16-setup initdb
sudo systemctl enable --now postgresql-16

Arch Linux

sudo -u postgres initdb -D /var/lib/postgres/data
sudo systemctl enable --now postgresql

Connect as the postgres Superuser

The install creates a system user called postgres and a matching database superuser. Drop into a psql session using peer authentication — no password required at this stage.

sudo -u postgres psql

You should see a prompt like postgres=#. All SQL commands in the next sections run inside this session unless noted otherwise.

Create Roles and Users

PostgreSQL uses the term role for both users and groups. A role with the LOGIN attribute is effectively a user. Always set a strong password for any role that will log in.

Create an application role

CREATE ROLE appuser WITH LOGIN PASSWORD 'StrongPassw0rd!';

Grant superuser or other privileges selectively

Avoid granting SUPERUSER to application accounts. Use fine-grained privileges instead.

-- Allow the role to create databases (useful for dev environments)
ALTER ROLE appuser CREATEDB;

-- Or restrict it entirely and rely on per-database GRANTs (recommended for production)
ALTER ROLE appuser NOCREATEDB NOCREATEROLE;

Create and Manage Databases

Create a database owned by your application role

CREATE DATABASE myappdb OWNER appuser ENCODING 'UTF8' LC_COLLATE 'en_US.UTF-8' LC_CTYPE 'en_US.UTF-8';

Grant schema-level privileges

Connect to the new database and grant usage on the public schema, then privilege specific objects as needed.

\c myappdb

GRANT CONNECT ON DATABASE myappdb TO appuser;
GRANT USAGE ON SCHEMA public TO appuser;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO appuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO appuser;

The ALTER DEFAULT PRIVILEGES line ensures future tables automatically inherit those grants — easy to forget and painful to debug later.

Configure Authentication (pg_hba.conf)

PostgreSQL's pg_hba.conf controls who can connect, from where, and how they must authenticate. It is read top-to-bottom; the first matching rule wins.

Locate the file

# From inside psql:
SHOW hba_file;

# Typical paths:
# Debian/Ubuntu:  /etc/postgresql/16/main/pg_hba.conf
# RHEL/Rocky:     /var/lib/pgsql/16/data/pg_hba.conf
# Arch:           /var/lib/postgres/data/pg_hba.conf

Recommended baseline

Open the file with your editor of choice and set the following. Lines beginning with # are comments.

sudo nano /etc/postgresql/16/main/pg_hba.conf

A secure, minimal configuration looks like this:

# TYPE  DATABASE   USER       ADDRESS        METHOD
local   all        postgres                  peer
local   all        all                       scram-sha-256
host    myappdb    appuser    127.0.0.1/32   scram-sha-256
host    myappdb    appuser    ::1/128        scram-sha-256

• peer — the OS username must match the PostgreSQL role. Safe for the postgres superuser locally.
• scram-sha-256 — the modern password authentication method. Prefer it over the older md5.

Reload after any change to pg_hba.conf — a full restart is not required.

sudo systemctl reload postgresql    # Debian/Ubuntu
sudo systemctl reload postgresql-16 # RHEL/Rocky

Enable Remote Access

By default PostgreSQL only listens on localhost. Enabling remote access requires two changes: one in postgresql.conf and one in pg_hba.conf.

Step 1 — Set the listen address

sudo nano /etc/postgresql/16/main/postgresql.conf

Find and change the listen_addresses line:

listen_addresses = '*'    # or a specific IP, e.g. '192.168.1.10'

Step 2 — Add a remote pg_hba.conf rule

Add this line in pg_hba.conf, replacing the CIDR with your trusted network or a specific client IP.

host    myappdb    appuser    192.168.1.0/24   scram-sha-256

Never use host all all 0.0.0.0/0 trust in production. It accepts any connection without a password.

Step 3 — Open the firewall port (5432)

# Ubuntu / Debian with ufw
sudo ufw allow from 192.168.1.0/24 to any port 5432

# Fedora / RHEL / Rocky with firewalld
sudo firewall-cmd --permanent --add-rich-rule='rule family=ipv4 source address=192.168.1.0/24 port port=5432 protocol=tcp accept'
sudo firewall-cmd --reload

Step 4 — Restart to apply listen_addresses

Unlike pg_hba.conf, changing listen_addresses requires a full restart.

sudo systemctl restart postgresql
# or
sudo systemctl restart postgresql-16

Verify the Setup

# Confirm the service is running
systemctl status postgresql

# Check which addresses PostgreSQL is listening on
sudo ss -tlnp | grep 5432

# Test login as appuser locally
psql -U appuser -d myappdb -h 127.0.0.1 -W

# Test from a remote machine
psql -U appuser -d myappdb -h  -W

A successful login drops you into a myappdb=> prompt. A connection refused error usually means the firewall or listen_addresses is wrong; an authentication failed error points to pg_hba.conf or the password.

Troubleshooting

FATAL: role does not exist

You are connecting as a system user that has no matching PostgreSQL role. Either create the role or explicitly pass -U postgres to your psql command.

FATAL: peer authentication failed

Your OS username does not match the PostgreSQL role you are trying to use. Switch to scram-sha-256 in pg_hba.conf for that entry or connect using sudo -u postgres psql.

Connection refused on port 5432 from a remote host

• Confirm listen_addresses is not still set to localhost in postgresql.conf and that the service was fully restarted.
• Check the firewall on the server: sudo ss -tlnp | grep 5432 should show 0.0.0.0:5432 or the specific IP.
• Ensure the pg_hba.conf rule covers the client's IP address and that you reloaded after editing.

password authentication failed for user

Reset the password inside psql as the postgres superuser with ALTER ROLE appuser PASSWORD 'NewPassword!';, then retry.