Instruction

PostgreSQL 18 Installation on Oracle Linux 9

This instruction installs PostgreSQL 18.3 on Oracle Linux 9 from the PGDG repository, configures SSL/TLS for encrypted client connections, enables the pgcrypto extension for column-level encryption, and applies security hardening with SCRAM-SHA-256 authentication.

This is the first document in a four-part series. Follow-up documents cover pgBackRest backup configuration, streaming replication with two standbys, and pgBackRest with replication.

Assumptions

This instruction assumes:

  • Oracle Linux 9.x (x86_64) is installed with a minimal or server profile
  • Root access or sudo privileges are available
  • The server has internet access to reach download.postgresql.org and yum.postgresql.org
  • Firewalld is the active firewall manager (default on OL9)
  • SELinux is in enforcing mode (default on OL9)
  • No prior PostgreSQL installation exists on the server
  • The default PostgreSQL port (5432) is used

Prerequisites

Automatic setup

The PGDG repository RPM configures the YUM/DNF repository for PostgreSQL packages:

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

This package installs the PGDG repository configuration files under /etc/yum.repos.d/, enabling access to PostgreSQL 18 and all supported versions.

Manual setup

No manual setup is required. The PGDG RPM is the only supported method for configuring the repository.

Additional setup

  1. Disable the built-in PostgreSQL module in Oracle Linux 9

The OL9 AppStream ships older PostgreSQL versions (13, 15, 16) as DNF modules. These conflict with PGDG packages and must be disabled.

sudo dnf -qy module disable postgresql

Expected output:

(no output on success)
  1. Verify the PGDG repository is active
sudo dnf repolist | grep pgdg

Expected output:

pgdg-common                  PostgreSQL common RPMs for RHEL / Rocky / AlmaLi
pgdg18                       PostgreSQL 18 for RHEL / Rocky / AlmaLinux 9 - x

Installation

Step 1: Install PostgreSQL 18 packages

Install the server, client, and contrib packages. The contrib package includes pgcrypto and other useful extensions.

sudo dnf install -y postgresql18-server postgresql18 postgresql18-contrib

Expected output:

...
Installed:
  postgresql18-18.3-1PGDG.rhel9.x86_64
  postgresql18-contrib-18.3-1PGDG.rhel9.x86_64
  postgresql18-libs-18.3-1PGDG.rhel9.x86_64
  postgresql18-server-18.3-1PGDG.rhel9.x86_64
Complete!

Step 2: Initialize the database cluster

The setup script creates the initial database cluster in /var/lib/pgsql/18/data.

sudo /usr/pgsql-18/bin/postgresql-18-setup initdb

Expected output:

Initializing database ... OK

The setup script calls initdb internally. It does not accept authentication method parameters. Authentication is configured in pg_hba.conf in the next steps.

Step 3: Verify SELinux configuration

The PGDG packages install correct SELinux file contexts for the default data directory. Verify that SELinux is enforcing and the PostgreSQL port label is set.

getenforce

Expected output:

Enforcing

Verify the port label for 5432:

sudo semanage port -l | grep postgresql

Expected output:

postgresql_port_t              tcp      5432, 9898

Verify the data directory context:

ls -dZ /var/lib/pgsql/18/data

Expected output:

system_u:object_r:postgresql_db_t:s0 /var/lib/pgsql/18/data

If the data directory context is wrong (e.g., after restoring from backup to a different path), fix it:

sudo semanage fcontext -a -t postgresql_db_t "/var/lib/pgsql/18/data(/.*)?"
sudo restorecon -Rv /var/lib/pgsql/18/data

Step 4: Generate SSL certificates

Generate a self-signed certificate and private key for SSL/TLS connections. Do this before configuring ssl = on in postgresql.conf.

sudo -u postgres openssl req -new -x509 -days 365 -nodes -text \
  -out /var/lib/pgsql/18/data/server.crt \
  -keyout /var/lib/pgsql/18/data/server.key \
  -subj "/CN={HOSTNAME}"

{HOSTNAME} -- the server's fully qualified domain name (e.g., pgdb01.example.com). Use the output of hostname -f if unsure.

Set restrictive permissions on the private key. PostgreSQL refuses to start if the key file is world-readable.

sudo -u postgres chmod 0600 /var/lib/pgsql/18/data/server.key

Verify the certificate:

sudo -u postgres openssl x509 -in /var/lib/pgsql/18/data/server.crt -noout -subject -dates

Expected output:

subject=CN = pgdb01.example.com
notBefore=Feb 28 ... 2026 GMT
notAfter=Feb 28 ... 2027 GMT

Step 5: Configure postgresql.conf

Edit the PostgreSQL configuration to enable SSL, network listening, and SCRAM-SHA-256 authentication.

sudo -u postgres vi /var/lib/pgsql/18/data/postgresql.conf

Set the following parameters:

listen_addresses = '*'
port = 5432
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
password_encryption = scram-sha-256
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_line_prefix = '%m [%p] %q%u@%d '
log_connections = on
log_disconnections = on

listen_addresses = '*' -- binds to all network interfaces. Restrict to specific IPs if the server has multiple interfaces and only some should accept PostgreSQL connections.

Step 6: Configure client authentication (pg_hba.conf)

Replace the default pg_hba.conf with a hardened configuration that uses SCRAM-SHA-256 for all password-based connections and requires SSL for remote connections.

sudo -u postgres vi /var/lib/pgsql/18/data/pg_hba.conf

Replace the contents with:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# Local connections: peer authentication for OS user mapping
local   all             all                                     peer

# Localhost connections: SCRAM-SHA-256 (IPv4 and IPv6)
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256

# Remote connections: SSL required with SCRAM-SHA-256
hostssl all             all             0.0.0.0/0               scram-sha-256
hostssl all             all             ::/0                    scram-sha-256

This configuration:

  • Allows local Unix socket connections via peer authentication (OS user must match database user)
  • Allows localhost TCP connections with SCRAM-SHA-256 passwords
  • Requires SSL for all remote TCP connections with SCRAM-SHA-256 passwords
  • Blocks all unencrypted remote connections (no host entries for remote addresses)

Step 7: Start and enable the PostgreSQL service

sudo systemctl enable --now postgresql-18

Expected output:

Created symlink /etc/systemd/system/multi-user.target.wants/postgresql-18.service → /usr/lib/systemd/system/postgresql-18.service.

Verify the service is running:

sudo systemctl status postgresql-18 --no-pager

Expected output:

● postgresql-18.service - PostgreSQL 18 database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql-18.service; enabled; preset: disabled)
     Active: active (running) since ...

Step 8: Configure firewall

Open port 5432 for PostgreSQL connections through firewalld.

sudo firewall-cmd --permanent --add-service=postgresql
sudo firewall-cmd --reload

Expected output:

success
success

Verify the rule:

sudo firewall-cmd --list-services | grep postgresql

Expected output:

... postgresql ...

To restrict access to specific networks instead of allowing all sources, use a rich rule. Replace {CLIENT_NETWORK} with the allowed CIDR block (e.g., 10.0.1.0/24):

sudo firewall-cmd --permanent --remove-service=postgresql
sudo firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="{CLIENT_NETWORK}" service name="postgresql" accept'
sudo firewall-cmd --reload

Step 9: Create application database and user

Connect as the postgres superuser and create an application database and user.

sudo -u postgres psql

CREATE USER appuser WITH PASSWORD '{APP_PASSWORD}';
CREATE DATABASE appdb OWNER appuser;
GRANT ALL PRIVILEGES ON DATABASE appdb TO appuser;
\q

{APP_PASSWORD} -- a strong password for the application user. Use at least 16 characters with mixed case, numbers, and symbols.

Verify the password is stored with SCRAM-SHA-256:

sudo -u postgres psql -c "SELECT rolname, rolpassword ~ '^SCRAM-SHA-256' AS is_scram FROM pg_authid WHERE rolname = 'appuser';"

Expected output:

 rolname | is_scram
---------+----------
 appuser | t
(1 row)

Step 10: Enable pgcrypto extension

Enable the pgcrypto extension in the application database for column-level encryption.

sudo -u postgres psql -d appdb -c "CREATE EXTENSION IF NOT EXISTS pgcrypto;"

Expected output:

CREATE EXTENSION

Verify the extension is installed:

sudo -u postgres psql -d appdb -c "SELECT extname, extversion FROM pg_extension WHERE extname = 'pgcrypto';"

Expected output:

 extname  | extversion
----------+------------
 pgcrypto | 1.3
(1 row)

Test symmetric encryption and decryption:

sudo -u postgres psql -d appdb -c "SELECT pgp_sym_decrypt(pgp_sym_encrypt('sensitive data', 'encryption-key'), 'encryption-key') AS decrypted;"

Expected output:

   decrypted
----------------
 sensitive data
(1 row)

Validation

Quick check

sudo -u postgres psql -c "SELECT version();" -c "SHOW ssl;" -c "SHOW password_encryption;"

Expected output:

                                                  version
-----------------------------------------------------------------------------------------------------------
 PostgreSQL 18.3 on x86_64-pc-linux-gnu, compiled by gcc ...
(1 row)

 ssl
-----
 on
(1 row)

 password_encryption
---------------------
 scram-sha-256
(1 row)

Full validation

  1. Verify service status
sudo systemctl is-active postgresql-18

Expected output:

active
  1. Verify listening address and port
sudo ss -tlnp | grep 5432

Expected output:

LISTEN 0      244          0.0.0.0:5432      0.0.0.0:*    users:(("postmaster",pid=...,fd=...))
LISTEN 0      244             [::]:5432         [::]:*    users:(("postmaster",pid=...,fd=...))
  1. Verify SSL is used on a connection
sudo -u postgres psql "host=localhost dbname=appdb sslmode=require" -c "SELECT ssl FROM pg_stat_ssl WHERE pid = pg_backend_pid();"

Expected output:

 ssl
-----
 t
(1 row)
  1. Verify pg_hba.conf has no weak authentication methods
sudo -u postgres psql -c "SELECT type, database, user_name, address, auth_method FROM pg_hba_file_rules WHERE auth_method NOT IN ('peer', 'scram-sha-256');"

Expected output:

 type | database | user_name | address | auth_method
------+----------+-----------+---------+-------------
(0 rows)
  1. Verify data directory location
sudo -u postgres psql -c "SHOW data_directory;"

Expected output:

      data_directory
--------------------------
 /var/lib/pgsql/18/data
(1 row)
  1. Verify pgcrypto extension in application database
sudo -u postgres psql -d appdb -c "SELECT extname FROM pg_extension WHERE extname = 'pgcrypto';"

Expected output:

 extname
----------
 pgcrypto
(1 row)

Troubleshooting

Problem Cause Solution
could not access file "server.key": Permission denied Private key file permissions are too open sudo -u postgres chmod 0600 /var/lib/pgsql/18/data/server.key
FATAL: no pg_hba.conf entry for host Client IP or auth method not in pg_hba.conf Add a hostssl entry for the client network in /var/lib/pgsql/18/data/pg_hba.conf, then sudo systemctl reload postgresql-18
Service fails to start with Address already in use Another process is using port 5432 Run ss -tlnp | grep 5432 to identify the process, stop it, then start PostgreSQL
password authentication failed after setting SCRAM Client library too old for SCRAM-SHA-256 Upgrade the client to PostgreSQL 10+ or change the pg_hba.conf entry to md5 for that client (less secure)
dnf module conflicts when installing PGDG packages OL9 PostgreSQL module not disabled sudo dnf -qy module disable postgresql then retry the install
SELinux denying PostgreSQL access to data directory Wrong SELinux context on data files sudo semanage fcontext -a -t postgresql_db_t "/var/lib/pgsql/18/data(/.*)?" then sudo restorecon -Rv /var/lib/pgsql/18/data
SELinux denying PostgreSQL on a non-default port Port not labeled for PostgreSQL sudo semanage port -a -t postgresql_port_t -p tcp {PORT}
pg_stat_ssl shows ssl = f on localhost Connecting via Unix socket or sslmode=prefer Use psql "host=localhost sslmode=require" to force a TCP+SSL connection
Firewall blocks remote connections Port 5432 not opened in firewalld sudo firewall-cmd --permanent --add-service=postgresql && sudo firewall-cmd --reload
FATAL: password authentication failed for user "postgres" Trying password auth for postgres superuser on local connection Use sudo -u postgres psql (peer auth) for local superuser access. Do not set a password for the postgres OS-level superuser connection.

References