PostgreSQL — Security¶

OWNER: boris, yoanna ALSO_USED_BY: ashley, piotr, urszula, maxim LAST_VERIFIED: 2026-03-26 GE_STACK_VERSION: PostgreSQL 16.x

Overview¶

GE targets ISO 27001 and SOC 2 Type II certification. Database security is non-negotiable. Every client project must meet these standards from day one. Retrofitting security is 10x more expensive.

Role-Based Access Control¶

Role Hierarchy (GE standard)¶

-- Superuser: Infrastructure only (Piotr, Boris)
-- NEVER used by application code
CREATE ROLE ge_superuser WITH SUPERUSER LOGIN;

-- Admin role: Cross-tenant operations, migrations
CREATE ROLE admin_role;
GRANT ALL ON ALL TABLES IN SCHEMA public TO admin_role;

-- Application role: Normal CRUD, scoped by RLS
CREATE ROLE app_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_role;

-- Audit role: Read-only access to audit tables
CREATE ROLE audit_role;
GRANT SELECT ON audit_log TO audit_role;

-- Read-only role: Reporting, analytics
CREATE ROLE readonly_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_role;

CHECK: Agent is creating a database role. IF: The role has SUPERUSER privilege. THEN: STOP. Only ge_superuser has SUPERUSER. Application and service roles must NEVER be superuser.

CHECK: Agent is writing application connection code. IF: The connection uses a superuser role. THEN: STOP. Application connects as app_role only.

Principle of Least Privilege¶

-- WRONG: Granting everything
GRANT ALL ON ALL TABLES TO app_role;

-- RIGHT: Grant only what's needed
GRANT SELECT, INSERT ON tasks TO app_role;
GRANT SELECT, INSERT, UPDATE ON agents TO app_role;
-- No DELETE on agents — they are decommissioned, not deleted

ANTI_PATTERN: Using GRANT ALL for application roles. FIX: Grant only the specific operations each role needs. Document which role can do what.

SSL / TLS¶

Connection Encryption (MANDATORY)¶

# postgresql.conf
ssl = on
ssl_cert_file = '/etc/ssl/certs/server.crt'
ssl_key_file = '/etc/ssl/private/server.key'
ssl_min_protocol_version = 'TLSv1.3'

# pg_hba.conf — reject non-SSL connections
hostssl all all 0.0.0.0/0 scram-sha-256
hostnossl all all 0.0.0.0/0 reject

CHECK: Agent is configuring database access. IF: Connection string does not include sslmode=require or stronger. THEN: Add it. GE rejects unencrypted connections.

# Connection string format
postgresql://user:pass@host:5432/db?sslmode=require

Encryption at Rest¶

Managed Encryption (UpCloud)¶

UpCloud managed PostgreSQL encrypts storage at rest using AES-256. No additional configuration needed for managed instances.

Self-Managed (k3s local)¶

For the GE admin-ui running on k3s:

# Verify LUKS encryption on the data volume
sudo cryptsetup status /dev/mapper/data-volume

CHECK: Agent is provisioning a self-managed PostgreSQL instance. IF: The storage volume is not encrypted. THEN: Enable LUKS encryption before initializing the database.

Column-Level Encryption¶

For PII and sensitive fields beyond what RLS provides:

-- Encrypt with pgcrypto
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Encrypt sensitive column
UPDATE clients
SET contact_email = pgp_sym_encrypt(
  contact_email,
  current_setting('app.encryption_key')
);

-- Decrypt
SELECT pgp_sym_decrypt(
  contact_email::bytea,
  current_setting('app.encryption_key')
) as contact_email
FROM clients;

CHECK: Agent is storing PII (email, phone, address, tax numbers). IF: The data is in a client-facing table. THEN: Evaluate whether column-level encryption is needed. Discuss with Ashley (security audit) and Boris (DBA).

Audit Logging¶

Append-Only Audit Log (GE standard)¶

The audit_log table is append-only. RLS prevents UPDATE and DELETE.

-- Audit log table (already exists in admin-ui)
CREATE TABLE audit_log (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  action text NOT NULL,          -- 'create', 'update', 'delete'
  table_name text NOT NULL,
  record_id text NOT NULL,
  actor text NOT NULL,           -- Agent ID or 'system'
  old_data jsonb,
  new_data jsonb,
  created_at timestamptz NOT NULL DEFAULT now()
);

-- Make append-only via RLS
ALTER TABLE audit_log ENABLE ROW LEVEL SECURITY;

CREATE POLICY audit_append_only ON audit_log
  FOR INSERT TO app_role
  WITH CHECK (true);

CREATE POLICY audit_read ON audit_log
  FOR SELECT TO audit_role
  USING (true);

REVOKE UPDATE, DELETE ON audit_log FROM app_role;

CHECK: Agent is modifying a table that stores client data, financial data, or access control data. IF: No audit trigger exists for the table. THEN: Add one:

CREATE OR REPLACE FUNCTION audit_trigger()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    INSERT INTO audit_log (action, table_name, record_id, actor, new_data)
    VALUES ('create', TG_TABLE_NAME, NEW.id::text,
            current_setting('app.current_actor', true), to_jsonb(NEW));
  ELSIF TG_OP = 'UPDATE' THEN
    INSERT INTO audit_log (action, table_name, record_id, actor, old_data, new_data)
    VALUES ('update', TG_TABLE_NAME, NEW.id::text,
            current_setting('app.current_actor', true), to_jsonb(OLD), to_jsonb(NEW));
  ELSIF TG_OP = 'DELETE' THEN
    INSERT INTO audit_log (action, table_name, record_id, actor, old_data)
    VALUES ('delete', TG_TABLE_NAME, OLD.id::text,
            current_setting('app.current_actor', true), to_jsonb(OLD));
  END IF;
  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER audit_clients
  AFTER INSERT OR UPDATE OR DELETE ON clients
  FOR EACH ROW EXECUTE FUNCTION audit_trigger();

ISO 27001 Requirements (Database-Specific)¶

Control	Implementation
A.9.1 Access control	Role-based access, principle of least privilege
A.9.4 System access control	SSL/TLS required, strong passwords
A.10.1 Encryption	TLS in transit, AES-256 at rest, pgcrypto for PII
A.12.4 Logging	Append-only audit_log, DDL logging
A.14.1 Security in development	RLS for multi-tenant isolation
A.17.1 Business continuity	Automated backups, PITR, replication
A.18.1 Compliance	GDPR (EU hosting), data retention policies

Password Management¶

Application Credentials¶

# Stored in k8s secrets, injected as environment variables
kubectl create secret generic admin-ui-db \
  --from-literal=DATABASE_URL='postgresql://app:SECURE_PASS@postgres:5432/admin_ui?sslmode=require'

CHECK: Agent is managing database credentials. IF: Credentials are in source code, config files, or environment files. THEN: Move to Kubernetes secrets or Vault. IF: Password is less than 24 characters. THEN: Regenerate. GE minimum is 24 characters, random.

Password Rotation¶

-- Rotate application password
ALTER ROLE app_role PASSWORD 'new_secure_password_here';
-- Then update k8s secret and restart pods

ANTI_PATTERN: Sharing credentials between environments. FIX: Each environment (dev, staging, production) has unique credentials.

Network Security¶

pg_hba.conf (Host-Based Authentication)¶

# Local connections (k3s pod to pod)
host all app_role 10.42.0.0/16 scram-sha-256

# Admin access (restricted IP)
hostssl all admin_role 192.168.1.85/32 scram-sha-256

# Reject everything else
host all all 0.0.0.0/0 reject

CHECK: Agent is configuring database network access. IF: pg_hba.conf allows 0.0.0.0/0 for any role other than reject. THEN: STOP. Restrict to specific CIDR ranges.

Anti-Patterns¶

ANTI_PATTERN: Disabling SSL for "easier development." FIX: Use SSL everywhere, including development. Self-signed certs are fine for dev: sslmode=require.

ANTI_PATTERN: Using trust authentication in pg_hba.conf. FIX: Always use scram-sha-256. Trust mode skips authentication.

ANTI_PATTERN: Application connects as database owner or superuser. FIX: Application uses app_role with only necessary privileges.

ANTI_PATTERN: Storing sensitive data without audit trail. FIX: Every table with PII or financial data gets an audit trigger.

Cross-References¶

READ_ALSO: wiki/docs/stack/postgresql/index.md READ_ALSO: wiki/docs/stack/postgresql/multi-tenant.md READ_ALSO: wiki/docs/stack/postgresql/pitfalls.md READ_ALSO: wiki/docs/stack/postgresql/checklist.md READ_ALSO: wiki/docs/development/pitfalls/infrastructure.md READ_ALSO: wiki/docs/company/compliance/overview.md