PostgreSQL

Synopsis #

PostgreSQL is an open-source relational database with SQL queries, transactions, and advanced data types and functions. It follows database standards and supports extensions.

PostgreSQL is available via OpenBSD’s package system and integrates with rcctl(8) for service management. It supports local Unix socket connections, TCP networking, TLS, fine-grained access control via pg_hba.conf, and flexible authentication methods.

Installation #

Install the PostgreSQL server package:

doas pkg_add postgresql-server

This provides the PostgreSQL daemon, client tools (psql, createdb, etc.), and default configuration files.

Use pkg_info postgresql-server and the installed package README to identify the release version and upgrade requirements.

OpenBSD 8.0 provides PostgreSQL 18. An existing cluster from an earlier major version requires a database migration, using dump/restore or a supported pg_upgrade procedure from the package README. Updating the server package does not convert the data directory; the initialization steps below are for a new cluster.

Initialization #

Before running initdb, set the System V semaphore limits listed in the package README. If initialization fails with could not create semaphores: No space left on device, these limits may be too low; the message refers to semaphores, not disk space. The package recommends these starting values:

doas sysctl kern.seminfo.semmni=256
doas sysctl kern.seminfo.semmns=2048

Save the settings for reboot by adding or updating these entries in /etc/sysctl.conf:

kern.seminfo.semmni=256
kern.seminfo.semmns=2048

Check the package README and increase the limits if more connections or workers need them.

Create the PostgreSQL data directory and initialize the database cluster:

doas su - _postgresql -c 'initdb -D /var/postgresql/data -U postgres -A scram-sha-256 -W'
  • -D /var/postgresql/data specifies the database directory
  • -U postgres creates the initial superuser
  • -A scram-sha-256 -W configures SCRAM password authentication and prompts for the initial database superuser password

Ensure ownership and permissions are correct:

doas chown -R _postgresql:_postgresql /var/postgresql/data

Enabling the Service #

Enable and start the PostgreSQL daemon:

doas rcctl enable postgresql
doas rcctl start postgresql

The daemon runs as _postgresql and listens on a Unix socket by default:

/tmp/.s.PGSQL.5432

Configuration #

The main configuration file is:

/var/postgresql/data/postgresql.conf

Common adjustments:

listen_addresses = 'localhost'
port = 5432
max_connections = 100
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql.log'

Restart the service after changes:

doas rcctl restart postgresql

Access Control #

Client authentication is managed by:

/var/postgresql/data/pg_hba.conf

The initialized authentication file includes local sockets and loopback TCP. Review its actual entries. To enable password-based authentication over TCP:

# TYPE     DATABASE  USER       ADDRESS         METHOD
host       all       all        127.0.0.1/32    scram-sha-256
host       all       all        ::1/128         scram-sha-256

Reload configuration:

doas su - _postgresql -c "pg_ctl reload -D /var/postgresql/data"

Creating Users and Databases #

Switch to the _postgresql user:

doas su - _postgresql

In the _postgresql shell, create a user and database:

createuser -U postgres appuser -P
createdb -U postgres appdb -O appuser

To connect:

psql -U appuser -d appdb

List databases:

\l

List users:

\du

Leave psql with \q, then return to the administrative account:

exit

TLS Support (Optional) #

  1. Generate server key and certificate:
doas mkdir -p /var/postgresql/data/certs
doas openssl req -x509 -newkey rsa:4096 -keyout /var/postgresql/data/certs/server.key \
  -out /var/postgresql/data/certs/server.crt -days 365 -nodes
doas chmod 600 /var/postgresql/data/certs/server.key
doas chown _postgresql:_postgresql /var/postgresql/data/certs/server.key /var/postgresql/data/certs/server.crt
  1. Update postgresql.conf:
ssl = on
ssl_cert_file = 'certs/server.crt'
ssl_key_file = 'certs/server.key'
  1. Restart PostgreSQL:
doas rcctl restart postgresql
  1. sslmode=require requests encryption; use verify-full with a trusted CA and matching server hostname for certificate verification. The following tests encryption only:
psql "host=127.0.0.1 dbname=appdb user=appuser sslmode=require"

Remote Access (Optional) #

To allow TCP connections:

  1. In postgresql.conf:
listen_addresses = '*'
  1. In pg_hba.conf:
hostssl all all 192.0.2.0/24 scram-sha-256
  1. In pf.conf, allow port 5432:
pass in on $int_if proto tcp from 192.0.2.0/24 to port 5432
  1. Restart the service:
doas rcctl restart postgresql

Logging and Diagnostics #

Logs are written to /var/postgresql/data/log/ if logging_collector is enabled.

To inspect logs:

doas tail -f /var/postgresql/data/log/postgresql.log

Inspect server and connection state:

SELECT version();
SELECT * FROM pg_stat_activity;

Check configuration values:

SHOW config_file;
SHOW listen_addresses;
Report a bug