1
2
3
4

# Reload pg_hba.conf
sudo -u postgres psql -c "SELECT pg_reload_conf();"
# or
sudo systemctl reload postgresql

1
2
3
4
5
6
7
8
9

-- postgresql.conf
password_encryption = 'scram-sha-256'

-- Verify existing users use SCRAM (not md5)
SELECT rolname, rolpassword
FROM pg_authid
WHERE rolpassword IS NOT NULL
  AND rolpassword NOT LIKE 'SCRAM-SHA-256$%';
-- Any rows returned are using md5 — reset those passwords

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

# Generate CA key and certificate
openssl genrsa -out ca.key 4096
openssl req -new -x509 -days 3650 -key ca.key -out ca.crt \
  -subj "/CN=PostgreSQL CA/O=MyOrg"

# Generate server key and CSR
openssl genrsa -out server.key 2048
chmod 600 server.key
openssl req -new -key server.key -out server.csr \
  -subj "/CN=db.internal.example.com/O=MyOrg"

# Sign the server certificate
openssl x509 -req -days 365 -in server.csr \
  -CA ca.crt -CAkey ca.key -CAcreateserial \
  -out server.crt

# Generate client certificate for admin user
openssl genrsa -out client-admin.key 2048
openssl req -new -key client-admin.key -out client-admin.csr \
  -subj "/CN=admin"  # CN must match the PostgreSQL username
openssl x509 -req -days 365 -in client-admin.csr \
  -CA ca.crt -CAkey ca.key -CAcreateserial \
  -out client-admin.crt

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

# /etc/postgresql/16/main/postgresql.conf

ssl = on
ssl_cert_file = '/etc/postgresql/certs/server.crt'
ssl_key_file = '/etc/postgresql/certs/server.key'
ssl_ca_file = '/etc/postgresql/certs/ca.crt'

# Minimum TLS version
ssl_min_protocol_version = 'TLSv1.2'

# Preferred cipher suites (PFS only)
ssl_ciphers = 'HIGH:!aNULL:!MD5:!RC4:!3DES'

# Require clients to present certificates for hostssl entries
# (set in pg_hba.conf with 'cert' method)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

# Connect with SSL required
psql "host=db.internal.example.com dbname=myapp_db user=myapp_user sslmode=require"

# Connect with certificate authentication
psql "host=db.internal.example.com dbname=postgres user=admin \
  sslmode=verify-full \
  sslcert=/path/to/client-admin.crt \
  sslkey=/path/to/client-admin.key \
  sslrootcert=/path/to/ca.crt"

# Check SSL status of current connection
SELECT ssl, version, cipher, bits FROM pg_stat_ssl WHERE pid = pg_backend_pid();

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

-- Create a read-only role (base privileges)
CREATE ROLE readonly_role NOLOGIN;
GRANT CONNECT ON DATABASE myapp_db TO readonly_role;
GRANT USAGE ON SCHEMA public TO readonly_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_role;
-- Future tables: grant SELECT automatically
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO readonly_role;

-- Create a read-write application role
CREATE ROLE app_role NOLOGIN;
GRANT CONNECT ON DATABASE myapp_db TO app_role;
GRANT USAGE ON SCHEMA public TO app_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_role;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES TO app_role;

-- Create login users that inherit from the role
CREATE USER myapp_user WITH LOGIN PASSWORD 'strong-password-here' IN ROLE app_role;
CREATE USER myapp_readonly WITH LOGIN PASSWORD 'strong-password-here' IN ROLE readonly_role;
CREATE USER reporting_user WITH LOGIN PASSWORD 'strong-password-here' IN ROLE readonly_role;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- Limit connections per user
ALTER USER myapp_user CONNECTION LIMIT 50;
ALTER USER myapp_readonly CONNECTION LIMIT 10;

-- Limit connections to a database
ALTER DATABASE myapp_db CONNECTION LIMIT 100;

-- Reserve connections for superuser (via max_connections - superuser_reserved_connections)
-- In postgresql.conf:
-- max_connections = 200
-- superuser_reserved_connections = 5

1
2
3
4
5
6
7
8
9

-- Revoke CREATE from public schema (PostgreSQL 15+ does this by default)
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- Revoke CONNECT from databases (force explicit grants)
REVOKE CONNECT ON DATABASE myapp_db FROM PUBLIC;

-- Create an application-specific schema
CREATE SCHEMA app AUTHORIZATION app_role;
SET search_path = app, public;

1
2
3
4
5
6
7

-- Migration user: can modify schema but is only used during deployments
CREATE USER migration_user WITH LOGIN PASSWORD 'migration-password';
GRANT ALL ON SCHEMA public TO migration_user;
GRANT ALL ON DATABASE myapp_db TO migration_user;

-- Application user: DML only, no DDL
-- (Already covered by app_role above — no CREATE TABLE privileges)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

-- List all roles and their attributes
\du+

-- Show effective privileges on tables
SELECT grantee, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE table_schema = 'public'
ORDER BY grantee, table_name;

-- Check who can connect to each database
SELECT datname, datacl FROM pg_database;

-- Find users with superuser privileges (should be minimal)
SELECT rolname FROM pg_roles WHERE rolsuper = true;

-- Find users with CREATEROLE (dangerous — can escalate)
SELECT rolname FROM pg_roles WHERE rolcreaterole = true;

1
2
3
4
5

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

-- By default, enabling RLS blocks ALL access (even the table owner)
-- You must create policies to allow access

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

-- Multi-tenant: users can only see their own organization's data
CREATE POLICY org_isolation ON orders
  USING (org_id = current_setting('app.current_org_id')::int);

-- Read-only users can SELECT but not modify
CREATE POLICY readonly_select ON orders
  FOR SELECT
  TO readonly_role
  USING (true);  -- can see all rows

CREATE POLICY app_all ON orders
  FOR ALL
  TO app_role
  USING (org_id = current_setting('app.current_org_id')::int)
  WITH CHECK (org_id = current_setting('app.current_org_id')::int);
-- USING: filter for SELECT/UPDATE/DELETE
-- WITH CHECK: enforce constraint for INSERT/UPDATE

1
2
3
4
5
6

-- Application sets context at the start of each transaction
BEGIN;
SET LOCAL app.current_org_id = '42';
-- Now all queries in this transaction are scoped to org 42
SELECT * FROM orders;  -- only returns rows where org_id = 42
COMMIT;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

import psycopg

async def get_orders(conn, org_id: int):
    async with conn.transaction():
        await conn.execute(
            "SET LOCAL app.current_org_id = %s",
            [str(org_id)]
        )
        rows = await conn.fetch("SELECT * FROM orders")
        return rows

1
2

-- Force RLS even for table owner (use for audit/compliance tables)
ALTER TABLE audit_log FORCE ROW LEVEL SECURITY;

1
2
3
4
5
6
7
8
9

-- Test a policy as a different user without reconnecting
SET ROLE myapp_user;
SET app.current_org_id = '42';
SELECT count(*) FROM orders;  -- should return only org 42's rows

SET app.current_org_id = '99';
SELECT count(*) FROM orders;  -- should return only org 99's rows

RESET ROLE;

1
2
3
4
5
6
7
8

# Install the extension
sudo apt install postgresql-16-pgaudit

# Add to postgresql.conf
shared_preload_libraries = 'pgaudit'

# Restart PostgreSQL (required for shared_preload_libraries changes)
sudo systemctl restart postgresql

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

-- Enable in postgresql.conf or as a per-database setting
-- Session-level audit (all statements by all users)
ALTER SYSTEM SET pgaudit.log = 'write, ddl, role';
SELECT pg_reload_conf();

-- Audit log categories:
-- read     - SELECT, COPY FROM
-- write    - INSERT, UPDATE, DELETE, TRUNCATE, COPY TO
-- function - Function calls
-- role     - GRANT, REVOKE, CREATE/ALTER/DROP ROLE
-- ddl      - CREATE, ALTER, DROP (non-role)
-- misc     - DISCARD, FETCH, CHECKPOINT, VACUUM
-- all      - Everything

1
2
3
4
5
6

-- Audit all activity by privileged users
ALTER ROLE migration_user SET pgaudit.log = 'all';
ALTER ROLE admin SET pgaudit.log = 'all';

-- Only audit writes by application users
ALTER ROLE myapp_user SET pgaudit.log = 'write';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- Enable object audit
ALTER SYSTEM SET pgaudit.log_catalog = 'off';  -- reduce noise
ALTER SYSTEM SET pgaudit.role = 'auditor';

-- Create the auditor role
CREATE ROLE auditor NOLOGIN;

-- Grant SELECT on auditor role to enable read auditing for those objects
GRANT SELECT ON payments TO auditor;
GRANT SELECT ON users TO auditor;
-- Now all SELECTs on these tables will be logged regardless of which user ran them

1
2
3
4
5
6
7
8
9

# Configure PostgreSQL to log to syslog
# postgresql.conf:
log_destination = 'syslog'
syslog_facility = 'LOCAL0'
syslog_ident = 'postgres'

# Ship syslog to a SIEM (Splunk, Elasticsearch, etc.)
# /etc/rsyslog.d/postgresql.conf:
local0.*    @@siem.internal.example.com:514

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

# /etc/pgbouncer/pgbouncer.ini

[databases]
myapp_db = host=127.0.0.1 port=5432 dbname=myapp_db

[pgbouncer]
listen_port = 5432
listen_addr = 0.0.0.0

# Pool mode: transaction is recommended for most apps
# session = one server connection per client session
# transaction = one server connection per transaction (recommended)
# statement = one server connection per statement (dangerous for multi-statement transactions)
pool_mode = transaction

# Maximum connections to PostgreSQL
server_pool_size = 20
max_client_conn = 1000

# Authentication
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1
auth_user = pgbouncer_auth

# TLS for client connections to PgBouncer
client_tls_sslmode = require
client_tls_cert_file = /etc/pgbouncer/server.crt
client_tls_key_file = /etc/pgbouncer/server.key
client_tls_ca_file = /etc/pgbouncer/ca.crt

# TLS for PgBouncer to PostgreSQL connections
server_tls_sslmode = require
server_tls_ca_file = /etc/pgbouncer/ca.crt

# Logging
log_connections = 1
log_disconnections = 1
log_stats = 1
stats_period = 60

# Limits
query_timeout = 30
client_idle_timeout = 60
server_idle_timeout = 300

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

-- Create auth user with just enough privilege to look up passwords
CREATE USER pgbouncer_auth WITH LOGIN PASSWORD 'strong-password' CONNECTION LIMIT 5;
GRANT SELECT ON pg_shadow TO pgbouncer_auth;
-- Note: pg_shadow requires superuser or specific grant in PostgreSQL 14+
-- Alternative: use pg_authid (no password hashes) or a wrapper function

-- Wrapper function approach (more secure)
CREATE FUNCTION pgbouncer.user_lookup(uname TEXT, OUT uname TEXT, OUT phash TEXT)
RETURNS RECORD LANGUAGE SQL SECURITY DEFINER AS $$
  SELECT usename, passwd FROM pg_shadow WHERE usename = $1;
$$;
REVOKE ALL ON FUNCTION pgbouncer.user_lookup(TEXT) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION pgbouncer.user_lookup(TEXT) TO pgbouncer_auth;

1
2

# In pgbouncer.ini:
auth_query = SELECT uname, phash FROM pgbouncer.user_lookup($1)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

# Connect to PgBouncer admin console
psql -h 127.0.0.1 -p 5432 -U pgbouncer pgbouncer

-- Show pool status
SHOW POOLS;
-- cl_active: clients currently in a transaction
-- cl_waiting: clients waiting for a connection
-- sv_active: server connections in use
-- sv_idle: server connections idle
-- sv_used: server connections used but idle

-- Show statistics
SHOW STATS;

-- Reload config without restart
RELOAD;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

# Enable the database secrets engine
vault secrets enable database

# Configure the PostgreSQL connection
vault write database/config/myapp-postgres \
  plugin_name=postgresql-database-plugin \
  connection_url="postgresql://{{username}}:{{password}}@db.example.com:5432/myapp_db?sslmode=verify-full&sslrootcert=/etc/vault/ca.crt" \
  allowed_roles="app-role,readonly-role,migration-role" \
  username="vault_admin" \
  password="vault-admin-password"

# Create the vault_admin user in PostgreSQL with appropriate privileges
# (it needs permission to CREATE/DROP ROLE)

1
2
3

-- PostgreSQL: create Vault's admin user
CREATE USER vault_admin WITH LOGIN PASSWORD 'vault-admin-password' CREATEROLE;
GRANT CONNECT ON DATABASE myapp_db TO vault_admin;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

# Read-only role: credentials expire after 1 hour
vault write database/roles/readonly-role \
  db_name=myapp-postgres \
  creation_statements="
    CREATE ROLE \"{{name}}\" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}' IN ROLE readonly_role;
  " \
  revocation_statements="DROP ROLE IF EXISTS \"{{name}}\";" \
  default_ttl="1h" \
  max_ttl="24h"

# Application role: credentials expire after 15 minutes, renewable
vault write database/roles/app-role \
  db_name=myapp-postgres \
  creation_statements="
    CREATE ROLE \"{{name}}\" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}' IN ROLE app_role;
    GRANT CONNECT ON DATABASE myapp_db TO \"{{name}}\";
  " \
  revocation_statements="DROP ROLE IF EXISTS \"{{name}}\";" \
  default_ttl="15m" \
  max_ttl="1h"

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

import hvac
import psycopg
from datetime import datetime, timedelta

class VaultPostgresPool:
    def __init__(self, vault_addr: str, vault_token: str, role: str):
        self.vault = hvac.Client(url=vault_addr, token=vault_token)
        self.role = role
        self._creds = None
        self._expiry = None

    def _get_credentials(self):
        """Fetch fresh credentials from Vault, caching until near expiry."""
        now = datetime.utcnow()
        if self._creds and self._expiry and now < self._expiry - timedelta(minutes=1):
            return self._creds

        creds = self.vault.secrets.database.generate_credentials(name=self.role)
        self._creds = creds['data']
        lease_duration = creds['lease_duration']
        self._expiry = now + timedelta(seconds=lease_duration)
        return self._creds

    def connect(self) -> psycopg.Connection:
        creds = self._get_credentials()
        return psycopg.connect(
            host="db.example.com",
            dbname="myapp_db",
            user=creds['username'],
            password=creds['password'],
            sslmode="verify-full",
            sslrootcert="/etc/app/ca.crt"
        )

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

# Pod annotation for Vault Agent injection
annotations:
  vault.hashicorp.com/agent-inject: "true"
  vault.hashicorp.com/role: "app-role"
  vault.hashicorp.com/agent-inject-secret-db-creds: "database/creds/app-role"
  vault.hashicorp.com/agent-inject-template-db-creds: |
    {{- with secret "database/creds/app-role" -}}
    DB_USERNAME={{ .Data.username }}
    DB_PASSWORD={{ .Data.password }}
    {{- end }}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

# Restrict which users can load extensions
# Only superusers can load shared libraries by default
# This prevents unprivileged users from loading malicious .so files

# Log all connections and disconnections (for audit trail)
log_connections = on
log_disconnections = on

# Log statements that take longer than 1 second (catches slow injection attacks)
log_min_duration_statement = 1000

# Log all DDL statements
log_ddl_statements = on  # pg_audit handles this more granularly

# Disable COPY TO/FROM PROGRAM (prevents RCE via SQL injection)
# Note: this requires superuser anyway, but belt-and-suspenders
# Restrict: don't grant SUPERUSER to application users

# Listen only on specific interfaces (not 0.0.0.0)
listen_addresses = '10.0.1.5, 127.0.0.1'

# Disable unnecessary features
# If you don't need logical replication, disable it
wal_level = replica  # not logical (unless needed)

# Prevent non-superusers from creating trusted extensions
# (PostgreSQL 15+)
# In pg_hba.conf: no local trust for non-postgres users handles this

# Statement timeout: prevents runaway queries (potential DoS)
statement_timeout = '30s'  # adjust per workload

# Lock timeout: prevents lock contention attacks
lock_timeout = '10s'

# Idle transaction timeout: prevents holding locks indefinitely
idle_in_transaction_session_timeout = '5min'

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

-- Users with superuser privilege
SELECT rolname FROM pg_roles WHERE rolsuper;

-- Users with no password (potential trust issues)
SELECT rolname FROM pg_roles
WHERE rolcanlogin AND rolpassword IS NULL;

-- Tables without RLS in a multi-tenant database
SELECT tablename FROM pg_tables
WHERE schemaname = 'public'
AND tablename NOT IN (
  SELECT relname FROM pg_class WHERE relrowsecurity
);

-- Roles granted to PUBLIC
SELECT grantee, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE grantee = 'PUBLIC';

-- SSL status of all current connections
SELECT pid, usename, application_name, client_addr, ssl, cipher
FROM pg_stat_ssl
JOIN pg_stat_activity USING (pid)
ORDER BY ssl;

-- Users with CREATEROLE (can escalate privileges)
SELECT rolname FROM pg_roles
WHERE rolcreaterole AND NOT rolsuper;

-- Non-SSL connections (should be empty in a hardened setup)
SELECT pid, usename, client_addr
FROM pg_stat_ssl s
JOIN pg_stat_activity a USING (pid)
WHERE NOT s.ssl
  AND a.client_addr IS NOT NULL;