Install and configure PostgreSQL on Ubuntu
Install PostgreSQL, create a least-privilege role and database, optionally enable restricted TLS network access, validate authentication order, and prove recovery readiness.
Provide a PostgreSQL service with explicit local and remote trust boundaries, a tested application login, and a safe configuration rollback.
- Ubuntu Server 24.04 LTS
- PostgreSQL 16+
- Recovery point Snapshot the host or capture a verified pg_dump/pg_dumpall backup before modifying an existing cluster.
- Access plan Prefer the local Unix socket. For remote clients, identify one private bind address and the smallest client CIDR.
ip -brief address && ip route - Password handling Generate the application password outside shell history and enter it only at an interactive prompt.
- Port check Confirm port 5432 is not already owned by another cluster or service.
sudo ss -lntp 'sport = :5432'
OneLiners never runs these steps or stores secrets. Review placeholders, versions, current state, and change-control requirements before using a command.
Full guide
What you will build
- An Ubuntu-supported PostgreSQL cluster whose package version, cluster name, data directory, active configuration files, service state, listener, storage, and capacity are recorded.
- A login role that owns one application database and has explicit schema privileges, with password authentication tested over loopback instead of being confused with local peer authentication.
- An optional private TLS path consisting of exact listen addresses, a first-match hostssl rule, a source-restricted firewall rule, encrypted client proof, and a first custom-format recovery artifact.
- The intended cluster is online, answers through its local socket, and listens only on loopback plus the selected private address when remote access is required.
- The application role is neither superuser nor role administrator, owns appdb, has reviewed schema capabilities, authenticates with SCRAM, and cannot silently match an unintended pg_hba rule.
- The operator can identify every active configuration file and authentication rule, recover the previous listener and pg_hba policy, revoke remote access, and proceed to an isolated restore rehearsal.
Architecture
How the parts fit together
Ubuntu's postgresql-common tooling can manage multiple versioned clusters on one host, so every operation identifies both major version and cluster name. The postgres operating-system account performs local commissioning through peer authentication. The application receives a distinct login role and owned database. Optional remote access adds an exact private listener, a database/role/CIDR-specific hostssl rule ordered before broader matches, and a matching UFW rule. TLS encryption and pg_hba admission precede PostgreSQL role authorization.
- Inventory packages, all registered clusters, port owners, directories, storage, memory, repositories, extensions, replication, and recoverable state.
- Install the Ubuntu server/client packages, identify the exact major/name pair, and prove local peer-authenticated service health.
- Create a SCRAM-capable login role through a protected prompt, create its database, and explicitly review public-schema behavior.
- Prove local TCP password authentication and role boundaries before any network expansion.
- Discover and archive the active config and hba files, add loopback/private listeners and the narrow first-match hostssl rule, then validate parsing.
- Restart only the intended cluster, apply the narrow firewall rule, prove TLS and identity remotely, and create a custom-format artifact for the recovery guide.
Assumptions
- The host is Ubuntu Server 24.04 LTS and the approved repository supplies PostgreSQL 16 or another application-supported major version.
- Any existing clusters, extensions, tablespaces, replication slots, recovery configuration, encryption material, applications, and backups are inventoried before changes.
- Local Unix-socket access is preferred; remote access is enabled only for a private application tier with a stable source CIDR and exact server address.
- Passwords are generated outside shell history, entered through protected prompts, stored in a secret manager, and rotated through a tested application process.
- The packaged TLS default may be adequate only for an initial encrypted-path test; production clients require an approved CA and server-name verification.
- The first pg_dump artifact proves export capability, not recovery, until an isolated restore, comparison, and application test pass.
Key concepts
- Cluster
- One PostgreSQL server instance with a data directory, port, configuration, and collection of databases; Ubuntu may run several.
- Role
- A database principal that may own objects and optionally login; PostgreSQL represents both users and groups as roles.
- Peer authentication
- A local-socket method mapping the operating-system identity to a database role without a database password.
- SCRAM-SHA-256
- A password mechanism using a salted verifier and challenge exchange instead of plaintext credential transport or storage.
- pg_hba first match
- PostgreSQL uses the first matching connection record and does not fall through when authentication fails.
- hostssl
- A pg_hba record that matches only TCP sessions protected by SSL/TLS.
- Custom-format dump
- A pg_dump archive supporting catalog listing, selective restore, and parallel restore through pg_restore.
Before you copy
Values used in this guide
{{privateAddress}}Exact private address added alongside localhost to listen_addresses.
Example: 10.20.30.10{{clientCidr}}Smallest routed client network admitted by both pg_hba and UFW.
Example: 10.20.30.0/24Security and production boundaries
- Do not set a network password for postgres merely to make administration convenient. Use local peer access and controlled administrative channels.
- A hostssl line requires encryption but does not make the client verify server identity. Production clients need sslmode=verify-full with an approved root certificate.
- Never use trust for remote clients or place a broad `host all all 0.0.0.0/0` record above restrictive policy.
- Protect role secrets, pgpass/service files, TLS private keys, data directories, WAL, dumps, and configuration backups.
- Database ownership is powerful. Separate runtime and migration roles when the application should not create or alter production schema.
- Review IPv4, IPv6, local socket, replication, and administrative records together; one secure remote rule does not define all authentication.
Stop before continuing if
- Stop if an unplanned cluster, port owner, data directory, replication configuration, extension, tablespace, or package origin is discovered.
- Do not change an existing cluster until a verified recovery point and exact active configuration paths are recorded.
- Do not restart when pg_hba parsing, configuration provenance, storage, service identity, or rollback copies are uncertain.
- Do not add UFW access until listener, hostssl rule order, SCRAM role, TLS, and the real client source are proven.
- Do not call the dump recoverable until an isolated restore and application-level comparison have succeeded.
verification
Inspect existing clusters and capacity
Check installed packages, registered PostgreSQL clusters, the listener, storage, and memory before installation.
Why this step matters
Preflight prevents package work from colliding with an existing cluster, data directory, port owner, extension set, or inadequate capacity.
What to understand
Inspect package origins, every pg_lsclusters row, postmaster processes, TCP/5432, configuration/data paths, tablespaces, bytes/inodes, memory, and backups.
Ubuntu can run multiple major/name clusters; an unused default port does not prove the host contains no PostgreSQL state.
For an existing cluster, inventory roles, databases, extensions, replication slots, WAL/recovery policy, applications, and a successfully restored backup.
System changes
- No persistent change; reads package, cluster, socket, storage, and memory state.
Syntax explained
dpkg-query -W- Lists installed PostgreSQL packages and versions.
pg_lsclusters- Shows Ubuntu-managed major/name clusters, ports, owners, status, and data paths.
ss ... :5432- Identifies any process owning the default TCP port.
df / free- Checks storage and memory headroom.
dpkg-query -W 'postgresql*' 2>/dev/null || true; pg_lsclusters 2>/dev/null || true; sudo ss -lntp 'sport = :5432'; df -h /var/lib; free -hNo listener on :5432 Filesystem /var/lib has 38G available Mem: 7.7Gi
Checkpoint: Checkpoint: preflight
Continue whenEvery existing cluster is understood, or the host is clean with sufficient capacity and recovery access.
Stop whenAny unplanned package origin, cluster, port owner, path, extension, replication state, or capacity issue appears.
If this step fails
An unexpected stopped cluster appears.
Likely causeA prior major upgrade or abandoned installation left a registered data directory.
pg_lsclusterssudo du -sh /var/lib/postgresql/*/*
ResolutionInventory its databases and recovery value before package or deletion decisions.
Security notes
- Preflight output contains internal topology and paths; redact it before sharing.
Alternatives
- Use configuration-management inventory when it captures the same authoritative facts.
Stop conditions
- Any unplanned package origin, cluster, port owner, path, extension, replication state, or capacity issue appears.
command
Install PostgreSQL and client tools
Install the Ubuntu-supported server package and record the exact major version before editing versioned configuration paths.
Why this step matters
Signed Ubuntu packages establish a maintainable service, client toolset, security-update path, and explicit major-version baseline.
What to understand
Review apt-cache policy so an unplanned PGDG or third-party origin cannot replace the expected package.
Record psql version and every cluster row because later paths include the installed major and cluster name.
Treat signature, dependency, storage, and package-configuration errors as blockers instead of forcing initialization.
System changes
- Installs PostgreSQL server/client packages and may initialize and start an Ubuntu main cluster.
Syntax explained
apt update- Downloads and verifies current repository metadata.
apt install postgresql- Installs Ubuntu's supported server metapackage.
postgresql-client- Installs psql, pg_dump, pg_restore, and client utilities.
pg_lsclusters- Records initialized cluster identity and state.
sudo apt update && sudo apt install --yes postgresql postgresql-client && psql --version && pg_lsclusterspsql (PostgreSQL) 16.x Ver Cluster Port Status Owner Data directory 16 main 5432 online postgres /var/lib/postgresql/16/main
Checkpoint: Checkpoint: install
Continue whenPackages come from the approved origin and exactly the intended major/name cluster is online.
Stop whenOrigin, major, cluster count, data path, port, or package transaction is unexpected.
If this step fails
Installation creates an unexpected major or port.
Likely causeRepository candidates or an existing cluster changed postgresql-common selection.
apt-cache policy postgresql postgresql-16pg_lsclusters
ResolutionChoose the supported major/cluster plan explicitly; do not delete the unexpected cluster before checking for data.
Security notes
- Do not add repository trust without ownership of its patch and upgrade lifecycle.
Alternatives
- Use official PGDG packages only for an approved required major unavailable in Ubuntu.
Stop conditions
- Origin, major, cluster count, data path, port, or package transaction is unexpected.
verification
Verify the default local cluster
Confirm the cluster is online and that the postgres operating-system account can query the server over the local socket.
Why this step matters
Local peer-authenticated health provides a known-good baseline before creating network credentials or editing versioned configuration.
What to understand
pg_lsclusters must show the exact cluster online; systemd's umbrella unit alone does not prove one cluster.
Run psql as postgres with `--no-psqlrc` so user startup files cannot alter evidence.
Inspect current logs for recovery, corruption, extension, permission, and checkpoint errors in addition to the version query.
System changes
- No persistent change; queries cluster and service state.
Syntax explained
sudo -u postgres- Uses Ubuntu's local postgres operating-system identity.
--no-psqlrc- Prevents personal psql startup files from altering behavior.
SELECT version()- Confirms the responding server release and architecture.
pg_lsclusters && sudo -u postgres psql --no-psqlrc --command='SELECT version();'16 main 5432 online postgres PostgreSQL 16.x on x86_64-pc-linux-gnu
Checkpoint: Checkpoint: service
Continue whenThe intended cluster is online, peer-authenticated query succeeds, and logs have no unexplained error.
Stop whenWrong cluster answers, service is degraded, or logs contain unresolved startup/recovery failures.
If this step fails
The umbrella service is active but the cluster is down.
Likely causepostgresql.service is an orchestrator and one versioned unit failed.
pg_lsclusterssystemctl --failed --no-pager
ResolutionInspect the exact postgresql@VERSION-NAME unit and correct its first concrete error.
Security notes
- Local postgres access is full database administration; restrict OS sudo rights accordingly.
Alternatives
- Query the exact cluster through its explicit port/socket when several clusters coexist.
Stop conditions
- Wrong cluster answers, service is degraded, or logs contain unresolved startup/recovery failures.
command
Create a login role with an interactive password
Use createuser with a prompt so the password is not recorded in shell history, then create a database owned by that role.
Why this step matters
A dedicated login role and owned database isolate the application from postgres administration and unrelated databases.
What to understand
The interactive prompt avoids secrets in arguments/history; confirm password_encryption is scram-sha-256 before setting it.
Database ownership is substantial inside appdb but does not imply cluster superuser, replication, role creation, or bypass-RLS.
Use separate runtime and migration roles when the deployed process should not own schema changes.
System changes
- Creates login role appuser with a password and appdb owned by that role.
Syntax explained
createuser --pwprompt- Creates a login role and prompts twice for its password.
createdb --owner- Creates appdb and assigns its database owner.
sudo -u postgres- Uses local peer-authenticated commissioning.
sudo -u postgres createuser --pwprompt appuser && sudo -u postgres createdb --owner=appuser appdbEnter password for new role: Enter it again: CREATE DATABASE
Checkpoint: Checkpoint: application role
Continue whenappuser can login and owns appdb but lacks superuser, role-admin, replication, and bypass-RLS.
Stop whenPassword handling, role attributes, database locale/encoding, or ownership differs.
If this step fails
The role already exists.
Likely causeA prior deployment or partial run created an identity with unknown dependencies.
sudo -u postgres psql --command='\du+ appuser'
ResolutionInspect and reconcile it; do not drop or overwrite before ownership and consumers are known.
Security notes
- Never reuse the application secret for backup, monitoring, or administrative roles.
Alternatives
- Create separate owner/migration and NOINHERIT runtime roles for stronger separation.
Stop conditions
- Password handling, role attributes, database locale/encoding, or ownership differs.
config
Limit schema creation to the application owner
Remove broad public schema creation and explicitly grant the application role the access it needs.
Why this step matters
Explicit public-schema policy prevents unrelated roles from creating objects that affect name resolution while granting only intended namespace capabilities.
What to understand
Modern releases changed default public-schema ownership and CREATE behavior, so query actual ACLs rather than trusting old advice.
REVOKE is idempotent; grant USAGE/CREATE only when appuser also performs migrations.
Runtime-only accounts should receive object-level data privileges from a separate schema owner.
System changes
- Revokes public CREATE on appdb.public and grants appuser explicit USAGE and CREATE.
Syntax explained
REVOKE CREATE ... PUBLIC- Removes schema creation from the implicit all-roles group.
GRANT USAGE- Allows resolving objects in the schema.
GRANT CREATE- Allows object creation; reserve for owner/migration identity.
\dn+- Displays schema ownership and ACLs.
sudo -u postgres psql --dbname=appdb --command='REVOKE CREATE ON SCHEMA public FROM PUBLIC;' --command='GRANT USAGE, CREATE ON SCHEMA public TO appuser;' --command='\dn+'Name Owner Access privileges public pg_database_owner appuser=UC/postgres
Checkpoint: Checkpoint: schema privileges
Continue whenPUBLIC cannot create and appuser has only the reviewed schema capabilities.
Stop whenUnexpected roles retain CREATE or runtime/migration separation is required.
If this step fails
Migrations fail after revocation.
Likely causeDeployment uses another role/schema and relied on implicit PUBLIC privileges.
sudo -u postgres psql --dbname=appdb --command='\dn+'
ResolutionGrant the exact migration identity on the intended schema; never restore broad PUBLIC CREATE.
Security notes
- Search_path and object ownership can create privilege-escalation paths.
Alternatives
- Create a dedicated application schema owned by a non-login owner role.
Stop conditions
- Unexpected roles retain CREATE or runtime/migration separation is required.
verification
Prove the application login locally
Connect over TCP to exercise password authentication rather than peer authentication, and use an interactive password prompt.
Why this step matters
A loopback TCP connection deliberately exercises password/SCRAM authentication rather than local peer authentication.
What to understand
Explicit host forces TCP; without it psql normally uses the Unix socket and may match peer.
`--password` prompts regardless of cached credentials and avoids a visible argument value.
Confirm current_user/current_database and run one permitted plus one prohibited operation.
System changes
- No persistent change; opens a bounded application session.
Syntax explained
--host=127.0.0.1- Forces loopback TCP instead of Unix socket.
--username=appuser- Submits the application role.
--password- Prompts without exposing the secret in arguments.
--dbname=appdb- Selects the application database.
psql --host=127.0.0.1 --username=appuser --password --dbname=appdb --command='SELECT current_user, current_database();'current_user | current_database appuser | appdb
Checkpoint: Checkpoint: local login
Continue whenThe query reports appuser/appdb, permitted work succeeds, and an administrative negative test fails.
Stop whenPeer authentication is used accidentally, wrong identity appears, or prohibited action succeeds.
If this step fails
Peer authentication failed.
Likely causeThe client omitted host and used the local socket.
printf '%s\n' "$PGHOST"sudo -u postgres psql --command='SELECT * FROM pg_hba_file_rules;'
ResolutionUse explicit loopback TCP and preserve peer for local administration.
Security notes
- Do not store the password in PGPASSWORD persistently or command history.
Alternatives
- Use a protected pgpass/service definition with strict permissions for noninteractive applications.
Stop conditions
- Peer authentication is used accidentally, wrong identity appears, or prohibited action succeeds.
verification
Locate and back up the active configuration
Ask the running server for its actual files instead of assuming a versioned directory, then preserve both before remote-access changes.
Why this step matters
Asking the running cluster for active paths avoids editing a guessed version directory or another Ubuntu cluster and preserves exact rollback files.
What to understand
SHOW config_file and SHOW hba_file are authoritative for the connected cluster; compare them with pg_lsclusters.
Archive both files with metadata and record hashes before remote changes.
Also record data_directory, include directives, and postgresql.auto.conf because effective settings may originate elsewhere.
System changes
- Creates metadata-preserving backup copies of active postgresql.conf and pg_hba.conf.
Syntax explained
SHOW config_file- Returns the active server configuration path.
SHOW hba_file- Returns the active authentication file path.
cp --archive- Preserves content, mode, owner, and timestamps.
pg_lsclusters --no-header- Provides the exact major without a heading row.
sudo -u postgres psql --tuples-only --no-align --command='SHOW config_file; SHOW hba_file;' && PGVER=$(pg_lsclusters --no-header | awk 'NR==1 {print $1}') && sudo cp --archive "/etc/postgresql/$PGVER/main/postgresql.conf" "/etc/postgresql/$PGVER/main/postgresql.conf.before-oneliners" && sudo cp --archive "/etc/postgresql/$PGVER/main/pg_hba.conf" "/etc/postgresql/$PGVER/main/pg_hba.conf.before-oneliners"/etc/postgresql/16/main/postgresql.conf /etc/postgresql/16/main/pg_hba.conf
Checkpoint: Checkpoint: config paths
Continue whenPaths belong to the intended cluster and both rollback copies and hashes exist.
Stop whenTarget cluster/path is unexpected or either rollback copy is missing.
If this step fails
The command backs up a different cluster.
Likely causeNR==1 selected the first of several pg_lsclusters rows.
pg_lsclusterssudo -u postgres psql --command='SHOW data_directory;'
ResolutionSelect the exact major/name explicitly and recreate correct backups.
Security notes
- Configuration backups may disclose authentication topology and should remain root-controlled.
Alternatives
- Resolve active paths entirely through SHOW commands for multi-cluster hosts.
Stop conditions
- Target cluster/path is unexpected or either rollback copy is missing.
config
Bind PostgreSQL to the private service address
Skip this for local-only applications. Retain localhost and add one private address rather than every interface; this setting requires a restart.
Why this step matters
Adding localhost plus one private address preserves deliberate local TCP tests while avoiding wildcard exposure on unrelated interfaces.
What to understand
ALTER SYSTEM writes postgresql.auto.conf for this cluster and must be included in configuration ownership and rollback.
The address must be assigned and stable; listen changes require restart rather than reload.
A listener only selects interfaces; pg_hba, firewall, TLS verification, and SQL privileges remain independent.
System changes
- Writes a cluster-level listen_addresses override activated after restart.
Syntax explained
ALTER SYSTEM SET- Persists an override in postgresql.auto.conf.
localhost- Retains loopback listeners according to host resolution.
{{privateAddress}}- Adds the exact private service interface.
/var/lib/postgresql/16/main/postgresql.auto.confValues stay on this page and are never sent or saved.
sudo -u postgres psql --command="ALTER SYSTEM SET listen_addresses TO 'localhost,{{privateAddress}}';"ALTER SYSTEM
Checkpoint: Checkpoint: listen address
Continue whenThe final file setting contains only localhost and the intended private address without errors.
Stop whenAddress is unavailable, wildcard appears, another source wins, or reset rollback is unclear.
If this step fails
The file setting conflicts.
Likely causeAnother setting source or the wrong connected cluster is involved.
sudo -u postgres psql --command="SELECT * FROM pg_file_settings WHERE name='listen_addresses';"
ResolutionReset or correct the exact override before restart; never add `*` to make it start.
Security notes
- A private interface is not inherently trusted; retain client CIDR and role controls.
Alternatives
- Keep socket/loopback-only when no remote client is required.
Stop conditions
- Address is unavailable, wildcard appears, another source wins, or reset rollback is unclear.
config
Add one ordered TLS client rule
Insert a hostssl rule limited to the application database, role, and client CIDR before broader records. PostgreSQL uses the first matching pg_hba rule, so validate the complete file.
Why this step matters
A database/role/CIDR-specific hostssl record placed first makes remote authentication encrypted and predictable under first-match semantics.
What to understand
Discover the active hba path and insert the specific rule before broader records after preserving the original.
Use pg_hba_file_rules to validate order, address, database, role, method, and parsing before restart.
SCRAM requires a compatible verifier and client; never use trust to diagnose connectivity.
System changes
- Adds a first-match hostssl appdb/appuser/clientCidr SCRAM record.
Syntax explained
hostssl- Matches only TLS-protected TCP sessions.
appdb appuser- Limits database and role.
{{clientCidr}}- Limits the source network.
scram-sha-256- Requires SCRAM password authentication.
/etc/postgresql/16/main/pg_hba.confValues stay on this page and are never sent or saved.
PGHBA=$(sudo -u postgres psql --tuples-only --no-align --command='SHOW hba_file;') && sudo sed -i "1ihostssl appdb appuser {{clientCidr}} scram-sha-256" "$PGHBA" && sudo -u postgres psql --command="SELECT rule_number,type,database,user_name,address,auth_method,error FROM pg_hba_file_rules ORDER BY rule_number;"rule_number | type | database | user_name | address | auth_method | error
1 | hostssl | {appdb} | {appuser} | 10.20.30.0/24 | scram-sha-256 |Checkpoint: Checkpoint: client auth
Continue whenRule 1 is the specific valid hostssl record and no parsed row reports an error.
Stop whenA broader rule precedes it, fields differ, parsing fails, or TLS is unavailable.
If this step fails
pg_hba_file_rules reports an error.
Likely causeMalformed CIDR, field count, quoting, or method.
sudo -u postgres psql --command='SELECT * FROM pg_hba_file_rules;'
ResolutionRestore the saved file or correct the exact line before reload/restart.
Security notes
- Do not expose other databases or roles through a broad record.
Alternatives
- Use certificate authentication only with complete issuance and revocation operations.
Stop conditions
- A broader rule precedes it, fields differ, parsing fails, or TLS is unavailable.
warning
Restart the configured cluster and inspect logs
Restart only after backups exist. Confirm the cluster is online, the private listener is present, and the journal contains no authentication-file errors.
Why this step matters
Restarting only the intended version/name cluster activates listener changes while avoiding accidental interruption of another local cluster.
What to understand
Check sessions and maintenance approval; restart disconnects existing clients.
Use the exact PGVER/name pair, then inspect cluster status, listener, journal, and effective settings.
Prove local socket and loopback TCP again before adding firewall access.
System changes
- Restarts the selected cluster, disconnects sessions, and activates listener/authentication changes.
Syntax explained
pg_ctlcluster VERSION main restart- Restarts one Ubuntu-managed cluster.
pg_lsclusters- Shows status of all local clusters.
ss ... :5432- Shows exact listener addresses.
journalctl -u- Reads the exact cluster service logs.
PGVER=$(pg_lsclusters --no-header | awk 'NR==1 {print $1}') && sudo pg_ctlcluster "$PGVER" main restart && pg_lsclusters && sudo ss -lntp 'sport = :5432' && sudo journalctl -u "postgresql@$PGVER-main" -n 30 --no-pager16 main 5432 online postgres LISTEN 0 244 10.20.30.10:5432 LOG: database system is ready to accept connections
Checkpoint: Checkpoint: restart
Continue whenOnly loopback/private listeners exist, the cluster is online, and local queries succeed.
Stop whenRestart fails, wrong listener appears, logs report errors, or local recovery access fails.
If this step fails
The cluster remains down.
Likely causeUnavailable address, port conflict, invalid config, storage/permission issue, or wrong target.
pg_lsclusterssudo journalctl -u 'postgresql@*-main' --since '10 minutes ago' --no-pager
ResolutionRestore config or reset the override for the exact cluster and keep remote firewall closed.
Security notes
- Never weaken SSL or replace authentication with trust as a startup fix.
Alternatives
- Rehearse the configuration on an isolated restored cluster.
Stop conditions
- Restart fails, wrong listener appears, logs report errors, or local recovery access fails.
warning
Allow only the PostgreSQL client CIDR
Skip this for local-only access. Preview and add a source-restricted firewall rule; never publish PostgreSQL directly to the internet.
Why this step matters
An exact source/destination TCP rule limits reachability to the application tier instead of public or whole-private-network exposure.
What to understand
Verify the source observed after NAT/VPN and account for IPv6 before writing policy.
Preview, apply the narrow rule, inspect order, and test allowed and denied sources.
Keep upstream security groups and network ACLs aligned; host UFW is one layer.
System changes
- Adds a persistent UFW allow rule from clientCidr to privateAddress TCP/5432.
Syntax explained
--dry-run- Shows generated changes without applying them.
from clientCidr- Limits accepted source range.
to privateAddress port 5432- Limits destination and PostgreSQL port.
proto tcp- Prevents an unintended UDP rule.
Values stay on this page and are never sent or saved.
sudo ufw --dry-run allow proto tcp from {{clientCidr}} to {{privateAddress}} port 5432 && sudo ufw allow proto tcp from {{clientCidr}} to {{privateAddress}} port 5432 && sudo ufw status numbered[ 2] 10.20.30.10 5432/tcp ALLOW IN 10.20.30.0/24
Checkpoint: Checkpoint: firewall
Continue whenOne narrow rule matches the private listener; allowed client reaches it and denied source cannot.
Stop whenRule is broad, source/destination is wrong, IPv6 bypass exists, or denied-source test succeeds.
If this step fails
Allowed client times out.
Likely causeRouting, upstream ACL, UFW order, NAT, listener, or address-family mismatch.
sudo ufw status numberedsudo tcpdump -ni any tcp port 5432 -c 20
ResolutionTrace the exact path and correct the smallest boundary; never add Anywhere.
Security notes
- Never expose PostgreSQL directly to the public internet.
Alternatives
- Use private networking or an authenticated tunnel and keep 5432 generally unreachable.
Stop conditions
- Rule is broad, source/destination is wrong, IPv6 bypass exists, or denied-source test succeeds.
verification
Test a TLS remote login
From an allowed client, require TLS, prompt for the password, and confirm the role, database, client address, and SSL state.
Why this step matters
An allowed-client test proves routing, firewall, TLS, HBA matching, SCRAM, database selection, and role identity end to end.
What to understand
Require TLS, prompt for the password, and query inet_client_addr plus pg_stat_ssl for the current backend.
Production acceptance uses sslmode=verify-full and an approved root certificate to authenticate server identity.
Denied-source, wrong-role/database, and non-TLS attempts should fail for expected reasons.
System changes
- No persistent change; opens a bounded encrypted client session.
Syntax explained
sslmode=require- Rejects plaintext but does not verify hostname.
--password- Prompts without exposing the secret.
inet_client_addr()- Shows the source PostgreSQL observes.
pg_stat_ssl- Reports TLS state for the backend.
Values stay on this page and are never sent or saved.
psql "host={{privateAddress}} dbname=appdb user=appuser sslmode=require" --password --command='SELECT current_user, current_database(), inet_client_addr();' --command='SELECT ssl FROM pg_stat_ssl WHERE pid = pg_backend_pid();'current_user | current_database | inet_client_addr appuser | appdb | 10.20.30.25 ssl t
Checkpoint: Checkpoint: remote test
Continue whenExpected role/database/client and TLS appear; denied-source and plaintext tests fail.
Stop whenIdentity, source, SSL state, or negative tests differ.
If this step fails
TLS is active but server identity is unverified.
Likely causesslmode=require encrypts without CA/hostname verification.
psql 'sslmode=verify-full ...'
ResolutionInstall approved trust material and use the correct DNS name before production.
Security notes
- Never accept encryption alone as server authentication.
Alternatives
- Keep remote access disabled until managed TLS is available.
Stop conditions
- Identity, source, SSL state, or negative tests differ.
verification
Create a first logical recovery artifact
Use the dedicated PostgreSQL backup Guide for automation and retention. Here, prove that a custom-format dump can be created and listed.
Why this step matters
A custom-format export catches missing privileges and creates an inspectable artifact, while explicitly withholding recovery claims until isolated restore succeeds.
What to understand
pg_dump covers one database but not global roles/tablespaces or external application state.
Capture stderr, exit status, size, checksum, server/client versions, timing, and pg_restore catalog.
Proceed to the dedicated recovery guide for globals, retention, encryption, isolated restore, comparisons, and cleanup.
System changes
- Creates appdb-baseline.dump in the current directory and reads its archive catalog.
Syntax explained
pg_dump --format=custom- Creates a pg_restore-readable archive.
--file- Writes to the explicit artifact path.
--password- Prompts when authentication requires it.
pg_restore --list- Parses the archive catalog without restoring.
pg_dump --host=127.0.0.1 --username=appuser --password --format=custom --file=appdb-baseline.dump appdb && pg_restore --list appdb-baseline.dump | head; ; Archive created at 2026-07-24 ; dbname: appdb ;
Checkpoint: Checkpoint: backup baseline
Continue whenArtifact is non-empty, checksum/metadata exist, catalog parses, and isolated restore is scheduled.
Stop whenDump exits nonzero, file is empty, catalog fails, or intended database/version is uncertain.
If this step fails
pg_restore cannot list the file.
Likely causeWrong format, truncation, storage failure, or incompatible utility.
file appdb-baseline.dumppg_restore --list appdb-baseline.dump
ResolutionCreate a new captured artifact with the compatible utility and test it through restoration.
Security notes
- The dump contains sensitive data and must be encrypted and access-controlled.
Alternatives
- Use physical or continuous-archive backup when RPO/RTO and scale require it.
Stop conditions
- Dump exits nonzero, file is empty, catalog fails, or intended database/version is uncertain.
Finish line
Verification checklist
pg_lsclusters && sudo ss -lntp 'sport = :5432'The intended cluster is online and listens only on localhost or the selected private address.sudo -u postgres psql --command='\du+ appuser'appuser can log in but is not superuser, replication, bypass-RLS, or role-admin.pg_restore --list appdb-baseline.dump | headThe custom-format archive is readable and identifies appdb objects.Recovery guidance
Common problems and safe checks
The application receives `Peer authentication failed` locally.
Likely causeThe client used a Unix socket and matched a local peer rule instead of the intended loopback TCP SCRAM rule.
sudo -u postgres psql --command='SELECT * FROM pg_hba_file_rules;'psql --host=127.0.0.1 --username=appuser --dbname=appdb --password
ResolutionUse explicit loopback TCP for the password test or define a deliberate local mapping; never replace local peer policy with trust.
A remote client receives `no pg_hba.conf entry`.
Likely causeIts observed address, database, role, address family, or SSL state does not match, or the edited file is not active.
sudo -u postgres psql --tuples-only --no-align --command='SHOW hba_file;'sudo -u postgres psql --command='SELECT * FROM pg_hba_file_rules;'
ResolutionIdentify the real source and active file, correct the smallest rule, validate it, and keep public entries absent.
A new pg_hba rule exists but never matches.
Likely causeAn earlier broader record wins because pg_hba uses first-match ordering.
sudo -u postgres psql --command="SELECT rule_number,type,database,user_name,address,auth_method,error FROM pg_hba_file_rules ORDER BY rule_number;"
ResolutionPlace the specific hostssl application record before broader matches, reload, and prove positive and negative clients.
The cluster fails to restart after changing listen_addresses.
Likely causeThe address is not assigned, port is occupied, configuration is malformed, files have wrong ownership, or the wrong cluster was targeted.
pg_lsclusterssudo journalctl -u 'postgresql@*-main' --since '15 minutes ago' --no-pagersudo ss -lntp 'sport = :5432'
ResolutionRestore active files or reset the ALTER SYSTEM override for the exact cluster, start it locally, and preserve failure evidence.
pg_dump succeeds but pg_restore cannot list or restore the artifact.
Likely causeThe wrong format, truncation, storage failure, version incompatibility, or uncaptured stderr invalidates it.
test -s appdb-baseline.dump && file appdb-baseline.dumppg_restore --list appdb-baseline.dump | head
ResolutionCreate a new custom-format artifact with captured status and checksum, then run the dedicated isolated restore procedure.
Reference
Frequently asked questions
Why does `sudo -u postgres psql` not ask for a password?
It normally uses the local socket and peer authentication, trusting the postgres operating-system identity. This does not create remote password access.
Should listen_addresses be `*`?
Usually no. Keep localhost and add only the exact private service address. Firewall and pg_hba remain required.
Does pg_hba grant table privileges?
No. It decides whether and how a client authenticates. Role memberships and SQL grants decide what the authenticated role may do.
Why use custom-format pg_dump?
It supports catalog inspection, selective restore, and parallel pg_restore, while still requiring restore testing.
Recovery
Rollback
Restore the saved PostgreSQL configuration and local-only listener without deleting the application database.
- Remove the ALTER SYSTEM listen_addresses override with ALTER SYSTEM RESET listen_addresses, or restore the saved postgresql.conf.
- Restore pg_hba.conf.before-oneliners and restart the exact cluster with pg_ctlcluster.
- Delete only the UFW rule added for port 5432 and verify remote connections are refused.
- Retain appdb and appuser unless a verified backup and explicit data-retirement decision exist.
Evidence