09 — Security and Authorization: Roles, Privileges, RLS

"Give it a strong password" was my Postgres security plan, and it covered one layer out of four. The framing that collapsed the complexity: access is layered, and each layer answers a different question [1]. Can this connection reach the server at all (network + SSL)? Is the client who it claims to be (authentication)? Is that identity allowed to do this thing to this object (authorization via roles and privileges)? And finally, is this row visible to this identity (Row Level Security)? Once I saw the layers as a pipeline, each with its own config surface, the sprawling security checklist became four focused questions.

The layered model

Layer 1: connection and transport

The outermost layer is whether the connection reaches the server at all and whether it's encrypted. pg_hba.conf decides which hosts, databases, users, and addresses may connect, and via which auth method [2]. SSL wraps the transport so traffic between client and server can't be read or tampered with [3]. SSL is configured in postgresql.conf (ssl = on, plus cert/key paths) and enforced per-connection by requiring hostssl lines in pg_hba.conf. For anything crossing a network, SSL is non-optional — without it, credentials and query data travel in the clear.

Layer 2: authentication

Once the connection is allowed, Postgres must verify the client's identity. The auth method in the matching pg_hba.conf line decides how [4]:

• scram-sha-256 — the modern default; password is hashed with a salt challenge. Prefer this over legacy md5.
• peer — for local connections, the OS username is trusted as the database role. Common for admin shells.
• cert — client presents an SSL certificate; strongest for machine-to-machine.
• ldap, gssapi (Kerberos), radius, pam — delegate to an external identity store for centralized auth.

The pattern I follow: scram-sha-256 for password-based remote connections, peer for local admin, cert for service-to-service where I can issue client certs. I never use trust (no password) anywhere except an isolated dev container.

Layer 3: roles and privileges

Postgres does not have separate "users" and "groups" — everything is a role [5]. A role with LOGIN can connect; a role without it is a group role used only to bundle privileges. Roles can inherit from other roles, so I structure access as: a group role per function (app_readonly, app_writer), privileges granted to the group, and login roles made members of the group.

Privileges are managed with GRANT and REVOKE [6]:

CREATE ROLE app_readonly;
GRANT USAGE ON SCHEMA public TO app_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO app_readonly;

The ALTER DEFAULT PRIVILEGES line is the part I used to miss: it sets privileges for future tables created in that schema, so the read-only role automatically gets SELECT on new tables without me re-granting. Without it, every migration needs a re-grant step. The object privilege types — SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, USAGE, CREATE — map to specific operations on tables, sequences, schemas, and functions.

Layer 4: Row Level Security

Object privileges are coarse — a role either has SELECT on a table or it doesn't. Row Level Security (RLS) adds a per-row filter so the same role sees different rows based on a policy [7]:

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

CREATE POLICY owner_isolation ON documents
  FOR SELECT
  USING (owner_id = current_user_id());

With this policy, SELECT * FROM documents returns only rows where owner_id matches the current user, even though the role has SELECT on the whole table. The USING clause filters rows for reads; WITH CHECK validates rows being written. This is the right tool for multi-tenant data where every query must be scoped to the tenant — the database enforces it, so a forgotten WHERE tenant_id = in application code can't leak data. Roles marked BYPASSRLS (typically the superuser) skip the policies.

Resource usage as a security lever

A subtler layer is resource limits. A runaway query or a connection flood is a denial-of-service vector even without malicious intent. Settings like max_connections, statement_timeout, and idle_in_transaction_session_timeout cap how much damage any one connection can do [8]. Limiting per-role resources and connection counts (often via a pooler like PgBouncer in front) prevents one client from exhausting the cluster.

How I use this

The layered model gives me a checklist for every new cluster. I force scram-sha-256 passwords, enable SSL and require hostssl for remote lines, and set up group roles with ALTER DEFAULT PRIVILEGES so access survives migrations. For multi-tenant apps I enable RLS and write USING policies keyed to the session user — the database enforces isolation, not the ORM. And I set statement_timeout and idle_in_transaction_session_timeout on application roles so a stuck query can't hold locks indefinitely. Security isn't one setting; it's four questions answered in order.

References

[1] Percona, "PostgreSQL database security best practices," 2024. [Online]. Available: https://www.percona.com/blog/postgresql-database-security-best-practices/

[2] PostgreSQL Global Development Group, "The pg_hba.conf file," 2024. [Online]. Available: https://www.postgresql.org/docs/current/auth-pg-hba-conf.html

[3] PostgreSQL Global Development Group, "Secure TCP/IP Connections with SSL," 2024. [Online]. Available: https://www.postgresql.org/docs/current/libpq-ssl.html

[4] PostgreSQL Global Development Group, "Authentication Methods," 2024. [Online]. Available: https://www.postgresql.org/docs/current/auth-methods.html

[5] PostgreSQL Global Development Group, "Database Roles," 2024. [Online]. Available: https://www.postgresql.org/docs/current/user-manag.html

[6] PostgreSQL Global Development Group, "GRANT," 2024. [Online]. Available: https://www.postgresql.org/docs/current/sql-grant.html

[7] PostgreSQL Global Development Group, "Row Security Policies," 2024. [Online]. Available: https://www.postgresql.org/docs/current/ddl-rowsecurity.html

[8] PostgreSQL Global Development Group, "Resource Consumption," 2024. [Online]. Available: https://www.postgresql.org/docs/current/runtime-config-resource.html