Shipsafershipsafer.ai
Database Security

PostgreSQL Security Hardening Guide: Authentication, Encryption, and Auditing

A comprehensive guide to securing PostgreSQL databases through proper authentication configuration, SSL enforcement, row-level security, auditing, and least-privilege access controls.

August 1, 20258 min readShipSafer Team

PostgreSQL is the most popular open-source relational database in production systems today, and its security posture directly determines how protected your application data is. A misconfigured PostgreSQL instance can expose sensitive records, allow privilege escalation, or leak credentials to attackers. This guide covers every layer of PostgreSQL security: authentication, transport encryption, row-level access control, audit logging, and secrets management.

Understanding pg_hba.conf: The Authentication Gatekeeper

The file pg_hba.conf (host-based authentication) is the single most important configuration file for PostgreSQL security. Every connection attempt is evaluated against rules in this file before PostgreSQL will even check a password. The format is:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256
hostssl all             all             0.0.0.0/0               scram-sha-256
host    all             all             0.0.0.0/0               reject

The critical rule here is the last line: explicitly reject any connection that does not use SSL. Combined with hostssl entries, this ensures all remote connections are encrypted. The peer method for local Unix socket connections is appropriate for the postgres superuser since it maps the OS user to the database user without a password, preventing credential stuffing.

Authentication methods ranked from weakest to strongest:

  • trust — Never use this in production. It allows any connection without a password.
  • password — Sends passwords in cleartext. Do not use.
  • md5 — MD5-hashed password. Vulnerable to offline dictionary attacks. Avoid on modern systems.
  • scram-sha-256 — The current standard. Use this everywhere.
  • cert — Certificate-based authentication. Strongest option for machine-to-machine connections.

To enforce scram-sha-256 as the default hashing method for all new passwords, add this to postgresql.conf:

password_encryption = scram-sha-256

Existing MD5 passwords must be reset to take effect. Iterate through pg_shadow to find accounts still using MD5:

SELECT usename, passwd FROM pg_shadow WHERE passwd NOT LIKE 'SCRAM-SHA-256$%' AND passwd IS NOT NULL;

Enforcing SSL/TLS for All Connections

Enable SSL in postgresql.conf:

ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = 'root.crt'
ssl_min_protocol_version = 'TLSv1.2'
ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL'

The ssl_min_protocol_version prevents clients from negotiating weak TLS 1.0 or 1.1 connections. On the client side, connection strings should specify sslmode=verify-full to validate the server certificate and hostname:

postgresql://appuser:password@db.example.com:5432/mydb?sslmode=verify-full&sslrootcert=/etc/ssl/certs/ca.crt

The difference between sslmode values matters significantly:

  • disable — No SSL at all. Never use in production.
  • require — Encrypts but does not verify the certificate. Vulnerable to MITM.
  • verify-ca — Verifies the certificate authority but not the hostname.
  • verify-full — Verifies both CA and hostname. Use this everywhere.

If you are on AWS RDS, the RDS CA bundle can be downloaded and used as the root certificate. RDS also supports enforcing SSL at the parameter group level by setting rds.force_ssl = 1, which causes PostgreSQL to reject all non-SSL connections regardless of pg_hba.conf.

Row-Level Security: Enforcing Data Isolation in the Database

Row-Level Security (RLS) allows you to enforce that each database user or application context can only see and modify rows that belong to them. This is especially powerful in multi-tenant SaaS applications where a single table stores data for many customers.

-- Enable RLS on the table
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Create a policy: users can only see their own orders
CREATE POLICY orders_isolation_policy ON orders
  USING (user_id = current_setting('app.current_user_id')::uuid);

-- Force RLS even for table owners
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

In your application, set the session variable before querying:

SET LOCAL app.current_user_id = '550e8400-e29b-41d4-a716-446655440000';
SELECT * FROM orders; -- Only returns rows for this user

A common mistake is forgetting FORCE ROW LEVEL SECURITY. Without it, the table owner bypasses all policies. Since your application's database role is often the table owner, this defeats the entire point of RLS. Always add FORCE ROW LEVEL SECURITY unless you have a specific reason not to.

For more complex tenancy, policies can reference a dedicated tenant table:

CREATE POLICY tenant_isolation ON api_keys
  USING (
    organization_id IN (
      SELECT organization_id FROM user_organizations
      WHERE user_id = current_setting('app.current_user_id')::uuid
    )
  );

Audit Logging with pg_audit

The standard PostgreSQL logging captures query errors and slow queries but does not give you a full security audit trail. The pg_audit extension provides detailed session-level and object-level logging suitable for compliance requirements like SOC 2, HIPAA, and PCI-DSS.

Install the extension (on the server, pg_audit.so must be loaded):

# postgresql.conf
shared_preload_libraries = 'pg_audit'
pg_audit.log = 'write, ddl, role, connection'
pg_audit.log_catalog = off
pg_audit.log_relation = on
pg_audit.log_statement_once = off

-- Enable pg_audit extension
CREATE EXTENSION IF NOT EXISTS pgaudit;

The log setting controls what gets audited:

  • read — SELECT and COPY when the source is a relation.
  • write — INSERT, UPDATE, DELETE, TRUNCATE, COPY when the destination is a relation.
  • function — Function calls and DO blocks.
  • role — Statements related to roles and privileges.
  • ddl — All DDL changes.
  • misc — Other commands like DISCARD, FETCH, CHECKPOINT.
  • all — Log everything.

For object-level auditing (tracking access to specific tables):

-- Create a role to be used by pg_audit for object auditing
CREATE ROLE auditor;
GRANT SELECT ON orders TO auditor;
GRANT INSERT, UPDATE, DELETE ON payments TO auditor;

-- pg_audit will now log all access to these objects
SET pg_audit.role = 'auditor';

Audit logs are written to the PostgreSQL log file. In production, ship these logs to an immutable destination (S3 with Object Lock, Splunk, or a SIEM) immediately. Attackers who gain database access will attempt to delete local logs.

Least-Privilege Roles and Permissions

Application databases should never connect with superuser credentials. Create dedicated roles with only the permissions needed:

-- Create an application role
CREATE ROLE app_user WITH LOGIN PASSWORD 'use-a-strong-generated-password';

-- Grant access only to the specific database
GRANT CONNECT ON DATABASE myapp TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;

-- Grant only what's needed per table
GRANT SELECT, INSERT, UPDATE ON orders TO app_user;
GRANT SELECT ON products TO app_user;
GRANT USAGE, SELECT ON SEQUENCE orders_id_seq TO app_user;

-- Revoke dangerous default privileges
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC;

For read replicas used by reporting tools, create a read-only role:

CREATE ROLE readonly_user WITH LOGIN PASSWORD 'another-strong-password';
GRANT CONNECT ON DATABASE myapp TO readonly_user;
GRANT USAGE ON SCHEMA public TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
-- Grant on future tables too
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user;

Connection limits prevent a single compromised account from exhausting all available connections:

ALTER ROLE app_user CONNECTION LIMIT 50;

You can monitor current connections per role:

SELECT usename, count(*) as connections
FROM pg_stat_activity
GROUP BY usename
ORDER BY connections DESC;

Encryption at Rest

For managed PostgreSQL (AWS RDS, Google Cloud SQL, Azure Database for PostgreSQL), enable storage encryption at the instance level. On AWS RDS, this is the "Enable encryption" checkbox during creation, which uses AWS KMS to encrypt the underlying EBS volumes. This cannot be enabled on an existing unencrypted instance — you must create an encrypted snapshot and restore from it.

# Create encrypted snapshot from unencrypted RDS instance
aws rds create-db-snapshot \
  --db-instance-identifier mydb \
  --db-snapshot-identifier mydb-snapshot

# Copy snapshot with encryption enabled
aws rds copy-db-snapshot \
  --source-db-snapshot-identifier mydb-snapshot \
  --target-db-snapshot-identifier mydb-snapshot-encrypted \
  --kms-key-id arn:aws:kms:us-east-1:123456789:key/your-key-id

# Restore encrypted instance from snapshot
aws rds restore-db-instance-from-db-snapshot \
  --db-instance-identifier mydb-encrypted \
  --db-snapshot-identifier mydb-snapshot-encrypted \
  --storage-encrypted

For column-level encryption of highly sensitive data (PII, payment data), use pgcrypto:

CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Encrypt a value before storing
INSERT INTO user_pii (user_id, ssn_encrypted)
VALUES (
  '550e8400-e29b-41d4-a716-446655440000',
  pgp_sym_encrypt('123-45-6789', current_setting('app.encryption_key'))
);

-- Decrypt when reading
SELECT pgp_sym_decrypt(ssn_encrypted, current_setting('app.encryption_key'))
FROM user_pii
WHERE user_id = '550e8400-e29b-41d4-a716-446655440000';

Store the encryption key in your application's secrets manager (AWS Secrets Manager, HashiCorp Vault), not in the database or connection string. Set it at session start via SET app.encryption_key = ... using a value fetched from the secrets manager.

Connection String Security

Connection strings often appear in environment variables, .env files, and CI/CD system logs. Prevent exposure with these practices:

Never embed passwords in application code. Use environment variables and validate them at startup:

const connectionString = process.env.DATABASE_URL;
if (!connectionString) {
  throw new Error('DATABASE_URL environment variable is required');
}

// Verify SSL mode is set
const url = new URL(connectionString);
const sslmode = url.searchParams.get('sslmode');
if (!sslmode || !['verify-full', 'verify-ca'].includes(sslmode)) {
  throw new Error('DATABASE_URL must specify sslmode=verify-full');
}

Rotate database passwords regularly using a deployment pipeline that:

  1. Creates a new credential in AWS Secrets Manager or Vault.
  2. Updates the database role password.
  3. Restarts application pods to pick up the new credential.
  4. Verifies connectivity before deleting the old credential.

PostgreSQL does not natively support short-lived credentials, but AWS RDS Proxy and HashiCorp Vault's database secrets engine can issue credentials with a TTL, after which they are automatically revoked. This dramatically reduces the blast radius of a leaked connection string.

postgresql
database-security
encryption
auditing
rls
pg_audit

Check Your Security Score — Free

See exactly how your domain scores on DMARC, TLS, HTTP headers, and 25+ other automated security checks in under 60 seconds.