--- title: "09 — Security and Authorization: Roles, Privileges, RLS" uid: security-authorization tags: ["roadmap:postgresql-dba", "security", "ssl", "privileges", "postgresql", "rls", "authentication", "roles"] excerpt: "Access is a four-layer pipeline: can the connection reach the server (network + SSL), is the client who it claims (authentication), may this identity do this thing (roles and privileges), and is this row visible (Row Level Security)." date: 2026-08-13T03:27:51+0000 source: https://www.aveshina.my.id/en/blog/security-authorization --- "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 ```figure ``` ## 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/](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](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](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](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](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](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](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](https://www.postgresql.org/docs/current/runtime-config-resource.html) ```quiz Q: In PostgreSQL, what is the difference between a "user" and a "group"? - Users connect; groups cannot be granted privileges - There is no distinction — both are roles. A role with LOGIN can connect; a role without LOGIN is used only to bundle privileges correct: 1 explain: Postgres unifies users and groups into roles. Login roles connect; group roles exist to bundle privileges that login roles inherit via membership. Q: What does ALTER DEFAULT PRIVILEGES do that plain GRANT does not? - It grants privileges on system catalogs - It sets privileges for objects created in the future, so new tables inherit the grants automatically correct: 1 explain: Plain GRANT applies to existing objects. ALTER DEFAULT PRIVILEGES attaches grants to the schema's future objects, so migrations don't require re-granting. Q: What does a Row Level Security USING clause do? - Encrypts the row at rest - Filters which rows a SELECT returns, based on a per-row policy expression correct: 1 explain: RLS policies add a per-row filter. USING applies to reads; WITH CHECK validates writes. The role still needs SELECT on the table — RLS narrows which rows that SELECT returns. Q: Which authentication method is the modern recommended default for password-based remote connections? - trust - md5 - scram-sha-256 correct: 2 explain: scram-sha-256 salts and challenges the password, defeating offline cracking better than legacy md5. trust (no password) should never be used on a network. Q: A useful defense-in-depth setting to stop a runaway application query from holding locks forever is… - max_connections - statement_timeout correct: 1 explain: statement_timeout aborts any statement running longer than the threshold. idle_in_transaction_session_timeout similarly kills sessions sitting in an open transaction, releasing their locks. ```