Roles, Privileges and Security

Least privilege

The application should not be a superuser, and should not even own the tables if you can avoid it. A common pattern uses three roles:

-- owns the schema; used only for migrations
CREATE ROLE app_owner LOGIN PASSWORD '...';
-- what the running app uses
CREATE ROLE app_rw LOGIN PASSWORD '...';
-- for reports and analytics
CREATE ROLE app_ro LOGIN PASSWORD '...';

GRANT CONNECT ON DATABASE pandadev TO app_rw, app_ro;
GRANT USAGE ON SCHEMA public TO app_rw, app_ro;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_rw;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_rw;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_ro;

Grants on "ALL TABLES" only cover tables that exist now. Make future tables inherit them:

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_rw;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public
  GRANT SELECT ON TABLES TO app_ro;

Now a SQL injection bug in the app cannot DROP TABLE, because app_rw lacks that privilege.

Who can connect: pg_hba.conf

Rules are read top to bottom; the first match wins.

# TYPE   DATABASE  USER     ADDRESS        METHOD
local    all       postgres                peer
host     pandadev  app_rw   127.0.0.1/32   scram-sha-256
hostssl  pandadev  app_rw   10.0.0.0/24    scram-sha-256
host     all       all      0.0.0.0/0      reject

• peer: the OS user must match (lets sudo -u postgres psql work without a password).
• scram-sha-256: modern password hashing. Never use trust or md5.
• hostssl: only encrypted connections.

Reload after edits: sudo systemctl reload postgresql.

Listening address

In postgresql.conf, listen_addresses = 'localhost' is the safe default. If other servers need access, listen on the private network address only and open the port in the firewall for those servers' IPs. Never expose port 5432 to the internet.

Encryption in transit

Enable ssl = on with a certificate, and have clients use ?sslmode=verify-full so they check the server's identity. Managed providers usually require TLS already.

Row-level security

For multi-tenant apps, PostgreSQL can enforce "users see only their own rows" inside the database:

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON invoices
  USING (tenant_id = current_setting('app.tenant_id')::int);

The app sets SET app.tenant_id = 42 per request. Even a buggy query cannot read another tenant's invoices.

Auditing

ALTER SYSTEM SET log_connections = on;
ALTER SYSTEM SET log_statement = 'ddl';   -- log schema changes
SELECT pg_reload_conf();

The pgaudit extension adds detailed audit logs for regulated environments.

Check yourself

• Why should the running app not own its tables?
• What do default privileges solve?
• Which pg_hba method should you use for passwords?