Postgres Security¶

Your database is the crown jewel Every authentication bypass, every SSRF, every compromised API key is trying to reach your database. If an attacker gets SQL access, they don't just read your data - they enumerate schemas, extract password hashes, pivot to cloud metadata endpoints, and exfiltrate your entire user base in the time it takes you to notice the alert you didn't configure

Roles and permissions¶

Postgres uses roles (which combine the traditional concepts of "users" and "groups") to manage authentication and authorization. Create granular roles for different application functions instead of connecting everything as the postgres superuser like a script kiddie who learned database administration from a YouTube tutorial

-- Create application roles with least privilege
CREATE ROLE app_readonly;
CREATE ROLE app_readwrite;
CREATE ROLE app_admin;

-- Grant specific permissions to each role
GRANT CONNECT ON DATABASE myapp TO app_readonly;
GRANT USAGE ON SCHEMA app TO app_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_readonly;

GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_readwrite;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA app TO app_readwrite;

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA app TO app_admin;

-- Create login roles that inherit from permission roles
CREATE ROLE api_service WITH LOGIN PASSWORD 'strong_password_here' INHERIT;
GRANT app_readwrite TO api_service;
-- The api_service role now has read+write permissions through inheritance

CREATE ROLE reporting_tool WITH LOGIN PASSWORD 'another_password' INHERIT;
GRANT app_readonly TO reporting_tool;

-- Default privileges for future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA app
    GRANT SELECT ON TABLES TO app_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA app
    GRANT INSERT, UPDATE, DELETE ON TABLES TO app_readwrite;

Row-Level Security (RLS)¶

RLS lets you restrict which rows a user can see or modify based on a policy expression. It's the database equivalent of "this user can only see their own data" enforced at the row level, impossible for the application layer to bypass even if your GraphQL endpoint exposes more than it should

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

-- Create policy: users can only see their own orders
CREATE POLICY user_orders_policy ON app.orders
    FOR ALL
    USING (user_id = current_setting('app.current_user_id')::UUID);
-- current_setting reads a session variable you set from the application

-- Create policy: admins can see everything
CREATE POLICY admin_orders_policy ON app.orders
    FOR ALL
    USING (current_setting('app.user_role') = 'admin');

-- Restrict INSERT to own data
CREATE POLICY user_orders_insert ON app.orders
    FOR INSERT
    WITH CHECK (user_id = current_setting('app.current_user_id')::UUID);

-- Set session variables from your application
-- In Node.js with pg:
-- await client.query("SET app.current_user_id = $1", [userId]);
-- await client.query("SET app.user_role = $1", [role]);

RLS is not a replacement for application-level authorization but a defense-in-depth layer. If your API has a SQL injection vulnerability, RLS can still prevent the attacker from reading other users' data even though they can execute arbitrary queries - it's your last line of defense when everything else fails

SSL/TLS for connections¶

Without encryption, every query and result travels across the network in plaintext. Anyone with access to the network path - compromised router, rogue access point, packet capture on the same subnet - can read your queries, your data, and your password

# Configure postgresql.conf for SSL
ssl = on
ssl_cert_file = '/etc/ssl/certs/server.crt'
ssl_key_file = '/etc/ssl/private/server.key'
ssl_ca_file = '/etc/ssl/certs/ca.crt'

# In pg_hba.conf - require SSL for remote connections
# TYPE  DATABASE  USER       ADDRESS          METHOD
hostssl myapp     app_user   10.0.0.0/8       scram-sha-256
hostssl myapp     app_user   0.0.0.0/0        reject  # Reject non-SSL

// Node.js pg - enforce SSL
const pool = new Pool({
    connectionString: 'postgresql://user:pass@host:5432/db',
    ssl: {
        rejectUnauthorized: true,       // Verify server cert
        ca: fs.readFileSync('./ca.crt'), // CA certificate
        key: fs.readFileSync('./client.key'),  // Client key (mutual TLS)
        cert: fs.readFileSync('./client.crt')  // Client cert
    }
});

pg_hba.conf - the gatekeeper¶

pg_hba.conf (Host-Based Authentication) controls which hosts, users, databases, and authentication methods are allowed to connect. This is your network-level access control and should be locked down before anything else

# TYPE  DATABASE  USER         ADDRESS          METHOD
# Local connections - use Unix sockets
local   all      postgres                      peer
local   all      app_user                      md5

# Remote connections - restrict to application servers only
host    myapp    api_service  10.0.1.0/24      scram-sha-256

# Reject everything else explicitly
host    all      all          0.0.0.0/0        reject
host    all      all          ::/0             reject

The METHOD column determines how passwords are verified: * trust - no password required. Never use this in production * md5 - MD5 hash. Better than trust but consider upgrading to SCRAM * scram-sha-256 - strongest default method. Use this for all production connections * cert - client certificate authentication. For machine-to-machine * peer - OS username must match database username. Unix sockets only

Audit logging¶

When someone breaches your application, you need to know exactly what queries were executed, by which user, from which IP, and at what time. Postgres' built-in logging combined with the pgaudit extension gives you forensic-level visibility into every database operation

# postgresql.conf - enable detailed logging
log_destination = 'csvlog'
logging_collector = on
log_directory = '/var/log/postgresql'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1440  # Rotate daily
log_rotation_size = 100MB
log_line_prefix = '%t [%p]: [%r] %u@%d '  # timestamp, pid, remote host, user, database

# Log all queries (expensive - enable selectively)
log_statement = 'mod'   # Log all DDL and DML statements
log_min_duration_statement = 1000  # Log queries taking > 1 second
log_connections = on
log_disconnections = on

-- Install pgaudit for comprehensive audit
CREATE EXTENSION IF NOT EXISTS pgaudit;

-- Configure what to audit
SET pgaudit.log = 'read, write, role, ddl';
SET pgaudit.log_relation = on;
SET pgaudit.log_level = 'notice';
-- Now every SELECT, INSERT, UPDATE, DELETE, role change, and schema change is logged

SQL injection - the deep dive¶

SQL injection is the single most exploited database vulnerability and it works because developers naively concatenate user input into SQL strings without realizing that input IS code. When you write SELECT * FROM users WHERE username = '${input}', the user input becomes part of the SQL syntax and an attacker can inject arbitrary SQL commands by including a single quote followed by their malicious query

-- Vulnerable query pattern:
-- SELECT * FROM users WHERE username = '$input'

-- Attacker sends: ' OR '1'='1
-- Query becomes: SELECT * FROM users WHERE username = '' OR '1'='1'
-- Returns ALL users. Congratulations , you just leaked your entire user table

-- Attacker sends: '; DROP TABLE users; --
-- Query becomes:
-- SELECT * FROM users WHERE username = ''; DROP TABLE users; --'
-- Two queries execute, the second one drops your users table
-- The -- comments out the remaining query

-- Attacker sends: ' UNION SELECT email, password_hash FROM users --
-- Query becomes:
-- SELECT * FROM users WHERE username = '' UNION SELECT email, password_hash FROM users --'
-- The UNION appends the attacker's query to your results
-- Now password hashes are returned alongside legitimate results

// Parameterized queries - the ONLY defense
// pg driver with $1, $2 placeholders
const result = await pool.query(
    'SELECT * FROM users WHERE email = $1 AND is_active = $2',
    [userInput.email, true]
);

// The input is sent as a separate parameter, never parsed as SQL
// Even if userInput.email = "'; DROP TABLE users; --"
// It's treated as a literal string value, not executable code

// NEVER use string interpolation or concatenation for query building
// BAD:
// pool.query(`SELECT * FROM users WHERE email = '${req.body.email}'`);
// GOOD:
// pool.query('SELECT * FROM users WHERE email = $1', [req.body.email]);

Prepared statements for repeated queries¶

Prepared statements parse and plan the query once, then execute it multiple times with different parameters. This is both faster (avoids repeated parsing/planning) and more secure (parameters never contaminate the SQL syntax)

-- Prepare a statement
PREPARE find_user (TEXT) AS
    SELECT id, email, username FROM users WHERE email = $1;

-- Execute with different parameters
EXECUTE find_user('omar@example.com');
EXECUTE find_user('ali@example.com');

-- Deallocate when done
DEALLOCATE find_user;

// Node.js - prepared statements via pg
const findUserQuery = {
    name: 'find-user-by-email',
    text: 'SELECT id, email, username FROM users WHERE email = $1',
    values: [email]
};
const result = await pool.query(findUserQuery);

Encryption at rest¶

Postgres doesn't natively encrypt data files on disk - that's typically handled by filesystem-level encryption (LUKS , dm-crypt) or cloud provider encryption (EBS encryption, GCE persistent disk encryption). For column-level encryption of sensitive data like credit card numbers and PII, use the pgcrypto extension

CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Encrypt specific columns
INSERT INTO app.payment_methods (user_id, encrypted_card_number, card_last_four)
VALUES (
    'user-uuid',
    pgp_sym_encrypt('4111111111111111', 'encryption_key_here'),
    '1111'  -- Store last 4 digits for display without decrypting
);

-- Decrypt when needed (requires key)
SELECT
    pgp_sym_decrypt(encrypted_card_number, 'encryption_key_here') AS card_number,
    card_last_four
FROM app.payment_methods
WHERE user_id = 'user-uuid';

prerequisites¶

db_04_postgres_advanced.md - security builds on everything you've learned so far. If you don't understand transactions, you can't reason about audit logging patterns. If you can't read an EXPLAIN plan, you won't catch the attacker's UNION-based injection via query timing

next → db_06_mongo_intro.md