PostgreSQL security on a VPS starts with controlling the path to your data. A strong database password helps, but it cannot compensate for unnecessary network exposure, overly powerful application accounts, or a client that does not verify the database server's identity.
This guide focuses on database access hardening for a self-hosted web application. It covers listeners, authentication rules, TLS, role permissions, and verification. Use the documentation for your installed PostgreSQL version, and rehearse changes in an isolated environment before applying them to a production database.
Map who actually needs database access
List every legitimate client before tightening access: web application processes, queue workers, migration jobs, monitoring, backup tooling, and administrators. Record the database, role, network source, and owner for each. Include periodic jobs that might not appear during an ordinary daytime traffic check.
A useful access policy describes a specific relationship: the production application connects to its database with its runtime identity from an approved network path. Avoid vague requirements such as allowing every server in an account to reach every database.
| Access layer | Question to answer | Evidence to collect |
|---|---|---|
| Network listener | Which interfaces accept connection attempts? | Running configuration and reachable endpoints |
| Firewall | Which clients can reach the database port? | Allowed and blocked source tests |
| Authentication | Which database and role combinations may sign in? | Matching authentication rules |
| Transport | Does the client verify the intended server? | Driver configuration and certificate failure tests |
| Permissions | What can the authenticated role do? | Required operations and denied-operation checks |
Assign a person to maintain this inventory. An unexplained exception tends to survive long after the deployment that originally needed it.
Keep the listener and firewall scope narrow
If every database client runs on the same VPS, consider whether Unix-domain sockets or loopback TCP meet the requirement. A separate application server needs a deliberate network path, preferably through an appropriately isolated private network or controlled tunnel.
PostgreSQL's connection settings reference explains that listen_addresses controls the interfaces on which PostgreSQL listens. Its default is localhost; * means all available interfaces. Changing this setting requires a server restart, so plan for the operational impact.
Review both provider and host firewalls for the actual database port. Account for IPv4, IPv6, containers, and any network translation. Confirm the source address seen by the database rather than assuming it is the application's advertised public IP.
A private network still needs access control. A compromised neighboring workload should not gain database access simply because it shares a network. For the architectural decision, see our guide to a single VPS versus a separate database server.
Read pg_hba.conf as an ordered policy
The PostgreSQL authentication configuration guide documents the matching rules for pg_hba.conf. PostgreSQL uses the first record matching the connection type, client address, database, and user. Failed authentication does not fall through to a later record. No matching record means access is denied.
Keep rules specific to the intended database, role, and source range. Review included configuration files and earlier entries before assuming that a new restrictive rule controls a connection.
A hostssl entry matches TLS connections when server-side SSL is enabled. A general host entry can match both encrypted and unencrypted TCP connections. Adding one hostssl rule therefore does not prove that every non-TLS path is rejected; inspect the complete ordered policy.
Avoid trust for application access: it permits a matching client to authenticate without supplying a password. Keep a tested administrative recovery path while changing the policy. On a Linux VPS, arrange the required configuration reload and verify new connections afterward.
Use SCRAM with compatible clients and credentials
For password-based authentication, review SCRAM-SHA-256 support across the server, application driver, pooler, and maintenance clients. PostgreSQL's password authentication documentation describes SCRAM and warns that older client libraries may not support it.
The password_encryption setting affects passwords when they are set. Changing the setting does not convert existing stored password verifiers. A migration needs compatible clients, passwords reset using the intended storage format, updated authentication rules, and connection tests.
Do this as a planned credential change. Identify every consumer, prepare its secure configuration update, and test background jobs as well as web requests. Store the credential through your approved secret delivery mechanism; avoid embedding it in shell commands, documentation, or screenshots.
SCRAM protects the authentication exchange, but it does not encrypt every subsequent database query or result. Review transport encryption separately. Keep a rollback plan that preserves access without leaving a broad authentication exception behind.
Require TLS and verify the server identity
For remote connections using libpq, configure a trusted CA certificate and sslmode=verify-full where appropriate. This verifies both the certificate chain and the requested server hostname. PostgreSQL's TLS client documentation explains the protections of each mode.
Encryption alone is not the same as authenticating the server. Do not rely on sslmode=require as a substitute for hostname verification. Use a connection hostname covered by the certificate and distribute the correct trust material to the client.
This illustrative command prompts for the password instead of including it in the connection string:
psql "host=db.example.com dbname=app_db user=app_runtime sslmode=verify-full sslrootcert=/path/to/ca.crt" -WReplace every example value with your approved endpoint, role, database, and CA file. Other drivers may expose different TLS options; verify their documented behavior. If a pooler or proxy sits between application and database, review both connection hops.
Separate application runtime permissions from migrations
Give the application a dedicated login role. Do not use a database superuser for routine web requests. The role attributes documentation explains that a superuser bypasses permission checks apart from the right to log in.
Review elevated attributes such as database creation, role creation, replication, and bypassing row-level security. Grant them only to identities whose documented tasks require them. Keep administrative credentials out of application runtime configuration.
A separate migration identity can perform the schema changes required by deployment while the runtime identity retains narrower privileges. Define who can use that identity and when. Avoid turning a temporary deployment shortcut into a permanent permission grant.
Also review object ownership, role membership, schema access, table and sequence permissions, and grants to PUBLIC. PostgreSQL's privileges documentation describes these distinct controls. A grant on a database does not automatically grant all required table privileges.
Test both existing objects and objects created by the next migration. Check the default privileges associated with the role creating those objects, rather than discovering missing permissions after deployment.
Inspect configuration and the actual connection
From an authorized administrative session, inspect the running configuration without printing credentials:
SHOW listen_addresses;
SHOW port;
SHOW ssl;
SHOW hba_file;
SELECT line_number, error
FROM pg_hba_file_rules
WHERE error IS NOT NULL;The pg_hba_file_rules reference notes that the view reports the current file contents, not necessarily the configuration last loaded by the server. It requires superuser access by default. An empty error result is useful, but does not prove the policy is correct or active.
For a TLS test connection, inspect that connection itself:
SELECT ssl, version, cipher
FROM pg_stat_ssl
WHERE pid = pg_backend_pid();PostgreSQL documents this view in its connection monitoring reference. A TLS connection should report ssl = true. That server-side result does not prove the client verified the certificate or hostname; confirm those properties through client configuration and controlled failure tests.
Test the denied paths before calling the work complete
Use a staging database with representative roles and connection paths. Exercise the application features that actually use data, including queues and scheduled jobs. Then run a small set of controlled negative tests:
- An unapproved source cannot reach or authenticate to the protected endpoint.
- An incorrect password is rejected.
- A remote non-TLS connection is rejected when your policy requires TLS.
- An untrusted certificate or wrong hostname causes the verifying client to refuse the connection.
- The runtime role cannot perform an intentionally forbidden administrative or schema operation.
- Backup and migration workflows succeed only through their approved identities.
Use test objects for permission checks. Record the client, endpoint, configuration version, and result without recording secret values. Repeat the relevant checks after a firewall change, driver upgrade, new application server, or certificate rotation.
Maintain access controls alongside recovery
Retire credentials for removed applications and devices, review exceptions, and assign ownership for certificate renewal. A certificate that expires during a deployment can turn a correct verification policy into an availability incident.
Keep backups protected and test restoration into an isolated environment. Our PostgreSQL backup restore guide covers a related recovery workflow, while the VPS security baseline places database access within the wider server security review.
When evaluating administrative tooling, check its documented responsibilities against these controls. Review the PostgreSQL hosting control panel overview and Core Panel documentation. Core Panel is currently pre-production; evaluate it on an isolated non-production server while checking applicable readiness controls.
Frequently asked questions
Should I expose PostgreSQL port 5432 to the internet?
A typical VPS web application should use a narrowly controlled access path. Start with local access or an isolated private path where suitable. If remote exposure is required, restrict sources and verify authentication, TLS, and permissions.
Does hostssl automatically block every unencrypted connection?
No. It matches TLS connections. Other applicable records may permit a non-TLS connection, so inspect the complete ordered authentication policy and test the denied path.
Does changing password_encryption update existing passwords?
No. It controls how passwords are stored when set. Plan compatible clients and credential updates before enforcing an authentication method that depends on the new format.
Is a successful database connection enough to prove security?
No. It demonstrates one permitted path. Also test rejected sources, incorrect credentials, certificate verification failures, and operations the runtime role should not be allowed to perform.
