PostgreSQL Security & Access Control

Roles vs Users

• Purpose: PostgreSQL uses role-based access control. Both users and groups are roles.

• Characteristics:

  • Users = Roles with login privilege (LOGIN)

  • Groups = Roles without login (used for grouping permissions)

  • Superusers = Roles with SUPERUSER (bypasses all checks)

• Use: Centralized privilege management, simplified permission assignment.

sql

-- Users (roles with LOGIN)
CREATE ROLE alice WITH LOGIN PASSWORD 'secret123';
-- Equivalent to:
CREATE USER alice WITH PASSWORD 'secret123';

-- Groups (roles without login)
CREATE ROLE managers;
CREATE ROLE analysts;

-- Assign user to group
GRANT managers TO alice;

-- Superuser (avoid in production)
CREATE ROLE admin WITH SUPERUSER LOGIN;

-- View roles
\du  -- In psql
SELECT rolname, rolsuper, rolcanlogin FROM pg_roles;

Privileges (GRANT, REVOKE)

• Purpose: Control access to database objects (tables, schemas, functions).

• Characteristics: Fine-grained permissions. Applied to roles. Inherited through group membership.

• Use: Principle of least privilege, separation of duties.

sql

-- Database level privileges
GRANT CONNECT ON DATABASE mydb TO alice;
GRANT CREATE ON DATABASE mydb TO managers;
REVOKE CONNECT ON DATABASE mydb FROM PUBLIC;  -- PUBLIC = all roles

-- Schema level privileges
GRANT USAGE ON SCHEMA public TO alice;
GRANT CREATE ON SCHEMA public TO managers;
-- Revoke default public permissions (SECURITY BEST PRACTICE)
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- Table level privileges (CRUD operations)
GRANT SELECT, INSERT, UPDATE ON users TO alice;
GRANT ALL PRIVILEGES ON orders TO managers;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analysts;

-- Column-level privileges (PostgreSQL specific!)
GRANT SELECT (id, name, email) ON users TO external_app;
GRANT UPDATE (status, last_login) ON users TO support_team;

-- Sequence privileges (for SERIAL columns)
GRANT USAGE, SELECT ON SEQUENCE users_id_seq TO alice;

-- Function privileges
GRANT EXECUTE ON FUNCTION calculate_bonus(integer) TO analysts;

-- Using WITH GRANT OPTION (delegate permission granting)
GRANT SELECT ON orders TO senior_analyst WITH GRANT OPTION;
-- senior_analyst can now grant SELECT on orders to others

-- Revoking privileges
REVOKE INSERT ON users FROM alice;
REVOKE ALL PRIVILEGES ON orders FROM bob;

-- Default privileges (for future objects)
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO analysts;

ALTER DEFAULT PRIVILEGES FOR ROLE app_user
    GRANT INSERT, UPDATE, DELETE ON TABLES TO support;

Row-Level Security (RLS) 

• Purpose: Restrict row access within a table based on policies.

• Characteristics:

  • Enabled per table with ALTER TABLE ... ENABLE ROW LEVEL SECURITY

  • Policies define which rows are visible/usable

  • Uses current user role/context

• Use: Multi-tenancy, data segregation, compliance requirements.

sql

-- Enable RLS on table
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    title TEXT,
    content TEXT,
    owner_username TEXT,
    department TEXT,
    confidential BOOLEAN DEFAULT false
);

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- Create policies
-- Policy 1: Users see their own documents
CREATE POLICY user_own_docs ON documents
    FOR ALL  -- Applies to SELECT, INSERT, UPDATE, DELETE
    USING (owner_username = current_user);

-- Policy 2: Managers see all documents in their department
CREATE POLICY manager_dept_docs ON documents
    FOR SELECT
    TO managers
    USING (department = current_setting('app.current_dept'));

-- Policy 3: Everyone can see non-confidential documents
CREATE POLICY public_non_confidential ON documents
    FOR SELECT
    USING (NOT confidential);

-- Policy with CHECK constraint (for INSERT/UPDATE)
CREATE POLICY user_insert_own_docs ON documents
    FOR INSERT
    WITH CHECK (owner_username = current_user);

-- Multi-tenant example (SaaS application)
CREATE TABLE tenant_data (
    id SERIAL,
    tenant_id INTEGER,
    data JSONB
);

ALTER TABLE tenant_data ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON tenant_data
    FOR ALL
    USING (tenant_id = current_setting('app.current_tenant_id')::integer);

-- In application, set tenant context:
SET app.current_tenant_id = '123';

-- Using roles in policies
CREATE POLICY hr_policy ON employees
    FOR SELECT
    TO hr_role
    USING (true);  -- HR can see all

CREATE POLICY manager_policy ON employees
    FOR SELECT
    TO manager_role
    USING (department_id = current_setting('user.dept_id')::integer);

-- View existing policies
SELECT * FROM pg_policies WHERE tablename = 'documents';

SSL Connections

• Purpose: Encrypt client-server communication.

• Characteristics:

  • Prevents eavesdropping, man-in-the-middle attacks

  • Configured in postgresql.conf and pg_hba.conf

  • Requires SSL certificates

• Use: All production environments, compliance requirements (HIPAA, PCI-DSS).

sql

-- Check if connection is using SSL
SELECT ssl, version(), cipher FROM pg_stat_ssl WHERE pid = pg_backend_pid();

-- Force SSL for specific hosts in pg_hba.conf
# TYPE  DATABASE        USER            ADDRESS                 METHOD
hostssl all             all             0.0.0.0/0               md5
hostssl all             all             ::/0                    md5

-- postgresql.conf settings
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = 'root.crt'  # For client certificate verification
ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL'  # Custom cipher suites

-- Require client certificates (mutual TLS)
# In pg_hba.conf
hostssl all             all             0.0.0.0/0               cert clientcert=1

-- Connection strings with SSL
psql "host=localhost dbname=mydb user=alice sslmode=require"
psql "host=localhost dbname=mydb user=alice sslmode=verify-full sslrootcert=ca.pem"

-- SSL modes:
-- disable: No SSL
-- allow: Try non-SSL first, then SSL
-- prefer: Try SSL first, then non-SSL (default)
-- require: SSL required, but no cert verification
-- verify-ca: SSL required, verify server cert against CA
-- verify-full: SSL required, verify server cert and hostname

Password Encryption

• Purpose: Secure password storage and transmission.

• Characteristics:

  • Passwords never stored in plain text

  • Multiple encryption methods available

  • Configured in pg_hba.conf authentication methods

• Use: Password-based authentication security.

sql

-- Create user with encrypted password (always encrypted in pg_authid)
CREATE USER alice WITH PASSWORD 'secret123';
-- Password is automatically encrypted with scram-sha-256

-- Change password
ALTER USER alice WITH PASSWORD 'newpassword123';

-- Password encryption methods in pg_hba.conf:
# METHOD       | SECURITY
# ------------|----------
# trust       | NO PASSWORD - INSECURE!
# password    | Plain text - INSECURE!
# md5         | MD5 hash - WEAK
# scram-sha-256| SCRAM-SHA-256 - SECURE (default since PG10)

-- Example pg_hba.conf entries:
# TYPE  DATABASE  USER    ADDRESS      METHOD
host    all       all     127.0.0.1/32 scram-sha-256
host    all       all     ::1/128      scram-sha-256

-- Check password encryption for users
SELECT usename, passwd FROM pg_shadow;
-- passwd starts with:
-- 'md5' = MD5 hash
-- 'SCRAM-SHA-256$' = SCRAM encrypted

-- Force password change
ALTER USER alice VALID UNTIL '2024-12-31';
ALTER USER alice PASSWORD 'temp123' VALID UNTIL '2024-02-01';

-- Password policies (using extensions)
-- Install passwordcheck extension
CREATE EXTENSION passwordcheck;
-- Now passwords are validated on CREATE/ALTER USER
-- (checks length, complexity, etc.)

-- Using pgcrypto for application-level encryption
CREATE EXTENSION pgcrypto;

-- Hash password before storing in application table
INSERT INTO app_users (username, password_hash)
VALUES ('alice', crypt('secret123', gen_salt('bf', 8)));

-- Verify password
SELECT (password_hash = crypt('entered_password', password_hash)) 
FROM app_users 
WHERE username = 'alice';

Advanced Security Features

Audit Logging

sql

-- Enable detailed logging
ALTER SYSTEM SET log_statement = 'all';
ALTER SYSTEM SET log_connections = on;
ALTER SYSTEM SET log_disconnections = on;
ALTER SYSTEM SET log_duration = on;

-- Use pgaudit extension for compliance
CREATE EXTENSION pgaudit;
ALTER SYSTEM SET pgaudit.log = 'read, write, ddl, role';
ALTER SYSTEM SET pgaudit.log_relation = on;

Connection Security

sql

-- Restrict connections
ALTER DATABASE mydb CONNECTION LIMIT 100;
ALTER USER alice CONNECTION LIMIT 10;

-- Idle session timeout
ALTER SYSTEM SET idle_in_transaction_session_timeout = '10min';

-- Source IP restrictions (in pg_hba.conf)
host    mydb     alice     192.168.1.0/24        scram-sha-256
host    mydb     alice     10.0.0.0/8            reject  # Explicit deny

-- SSL certificate authentication
CREATE USER webapp WITH LOGIN;
-- In pg_hba.conf:
hostssl mydb     webapp    0.0.0.0/0             cert
-- Client must present valid certificate

Security Best Practices

sql

-- 1. Remove default PUBLIC privileges
REVOKE ALL ON DATABASE postgres FROM PUBLIC;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- 2. Use separate roles for different functions
CREATE ROLE app_readonly;
CREATE ROLE app_write;
CREATE ROLE app_admin;

-- 3. Implement principle of least privilege
GRANT CONNECT ON DATABASE mydb TO app_readonly;
GRANT USAGE ON SCHEMA public TO app_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;

-- 4. Regular security audits
-- Check for weak passwords
SELECT usename FROM pg_shadow WHERE passwd LIKE 'md5%';

-- Check for excessive privileges
SELECT grantee, privilege_type, table_name 
FROM information_schema.role_table_grants 
WHERE grantee NOT IN ('postgres');

-- 5. Encrypt sensitive columns
CREATE EXTENSION pgcrypto;

CREATE TABLE sensitive_data (
    id SERIAL PRIMARY KEY,
    ssn BYTEA,  -- Encrypted
    card_number BYTEA
);

INSERT INTO sensitive_data (ssn, card_number)
VALUES (
    pgp_sym_encrypt('123-45-6789', 'encryption_key'),
    pgp_sym_encrypt('4111111111111111', 'encryption_key')
);

-- Decrypt when needed (with proper authorization)
SELECT pgp_sym_decrypt(ssn::bytea, 'encryption_key') FROM sensitive_data;