# Chapter 9: Identity & Access Control ## 9.0 - Access Control (The Bouncers and the VIP List) <img src="assets/arch_access_overview.png" width="250" style="float: left; margin: 0 20px 20px 0;" /> In the initial stages of a system's life, security is often handled on the honor system. We assume that any process connecting to the backend is permitted to read any table, and any admin possesses total authority over the schema. ### What You'll Learn - The two-layer architecture: **Authentication** (`pg_hba.conf`) vs. **Authorization** (catalog ACLs) - How **Roles** and **Membership** inheritance create flexible permission trees - How **Row-Level Security** injects visibility predicates directly into execution plans - How **Security Definers** enable safe privilege escalation without permanent grants ### The Axis of Trust Postgres splits security into two distinct architectural layers: **Authentication** (identity validation) and **Authorization** (resource permissioning). 1. **Authentication (AuthN): The Front Gate (`pg_hba.conf`)** Verifies *who* you are at the start of the connection lifecycle. Whether using SCRAM passwords, GSSAPI, or client certificates, AuthN's sole goal is to map your connection to a specific database **Role**. 2. **Authorization (AuthZ): The Room Keys (Catalogs)** The ongoing verification of *what* you can do. Every query is checked by the planner and executor against the system catalogs to see if your Role OID possesses the necessary privilege bits: - **`pg_authid`**: Master role attributes (see **[[Manuscript/09 - Identity & Access Control/9.1 - Roles & Privileges (The Name Tags)|9.1 Roles & Privileges]]**). - **`pg_class`**: Table-level Access Control Lists (see **[[Manuscript/02 - Physical Storage & MVCC/2.4 - Relation (The Table)|2.4 Relation]]**). - **`pg_namespace`**: Schema-level access lists (see **[[Manuscript/09 - Identity & Access Control/9.1 - Roles & Privileges (The Name Tags)|9.1 Roles & Privileges]]**). ### Identity as a Resource In Postgres, everything is a **Role**. A "User" is simply a Role with the `LOGIN` attribute. A "Group" is simply a Role that possesses other Roles as members. By separating identity from permissions, Postgres allows for an incredibly flexible, inheritance-based security model. You can define a single `readonly_viewer` role, grant it specific permissions on your tables, and then grant that role to dozens of individual human users. When you update the permissions on the group, every member inherits the change instantly. ### The Principle of Least Privilege The goal of this chapter is to guide you through the process of hardening your "Security Posture." We will move from the coarse-grained permissions of the **Superuser** toward a more surgical, policy-driven model: - **Roles & Membership**: Building an inheritance tree of responsibility. - **Default Privileges**: Ensuring that new resources are born secure. - **Row-Level Security**: Injecting visibility rules directly into the query execution plan. - **Security Definers**: Safely delegating authority without elevating permanent status. By the end of this chapter, you will understand that security in Postgres is not a "Lock on the Door," but a pervasive, catalog-driven framework that governs every single byte retrieved by Postgres. --- ## 9.1 - Roles & Privileges (The Name Tags) <img src="assets/arch_access_nametags.png" width="250" style="float: left; margin: 0 20px 20px 0;" /> Postgres uses a unified **Role** system to manage authentication and authorization. Unlike systems that distinguish between users and groups, Postgres treats both as instances of the same entity. A Role is an identity identifier. It can be configured with a password and the ability to log in (acting as a "user"), or it can serve as a container of privileges (acting as a "group"). The behavior is determined by the attributes assigned to the role. > [!NOTE] Every Identity Is a Role > **Concept**: Postgres has no concept of "users" or "groups." There are only **Roles**. > **Payoff**: A role is a single primitive. If you give it `LOGIN` capabilities, it acts as a user. If you grant it to other roles, it acts as a group. By unifying these under a single concept, the privilege checker only has to traverse a single directed acyclic graph of roles (`pg_auth_members`) to resolve access rights, making authorization fast and clean. ### Privilege Inheritance A role membership can make another role's privileges available automatically, through `SET ROLE`, or both. PostgreSQL 18 stores these choices on each membership edge, so `GRANT cafe_waiter TO cafe_manager` is more than a yes/no relationship. **Membership behavior in PostgreSQL 18**: - **`WITH INHERIT TRUE`**: privileges flow through this membership automatically. When omitted on a new grant, it defaults from the member role's `INHERIT` attribute. - **`WITH SET TRUE`** (the default): the member may explicitly switch with `SET ROLE cafe_waiter`. A chain of indirect memberships must have `SET TRUE` at every edge. - **`WITH ADMIN TRUE`**: the member may grant and revoke membership in the granted role. This is independent of using the role's privileges. In PostgreSQL versions before 16, inheritance was primarily a role-level attribute. Modern PostgreSQL makes the per-membership options the precise model. **Role inheritance chain** (membership in `pg_auth_members`): - `postgres` (superuser, grantor) - grants **`cafe_waiter`** → `SELECT` on `orders` - grants membership **`cafe_waiter` → `cafe_manager`** - **`cafe_manager`** (login role) - inherits waiter's table privileges via **`INHERIT`** - can `SET ROLE cafe_waiter` when the membership has **`SET TRUE`** ```sql CREATE ROLE cafe_manager WITH LOGIN PASSWORD 'peanuts' CREATEROLE; CREATE ROLE cafe_waiter; GRANT cafe_waiter TO cafe_manager WITH INHERIT TRUE, SET TRUE, ADMIN FALSE; ``` > [!TIP] > **Delegating Authority**: Use `WITH ADMIN TRUE` (the traditional spelling `WITH ADMIN OPTION` is also accepted) only when the member should be able to grant and revoke that role's membership. It does not by itself confer the role's object privileges. ### Identity Storage: `pg_authid` and `pg_roles` Role metadata is managed through two system catalogs: - **`pg_authid`**: The primary table storing all role information, including hashed passwords. Access is restricted to superusers. - **`pg_roles`**: A sanitized view of `pg_authid` that hides passwords and exposes public attributes. This view is queried by `psql` when running the `\du` command. > [!NOTE] > **The OID Secret**: as covered in [[Manuscript/02 - Physical Storage & MVCC/2.1 - Data Types (Knicks, knacks, bits, and bobs)|Chapter 2.1]], every object in Postgres is internally identified by an **OID** — a 4-byte integer. A role is just a number. A table is just a number. Access control is just a list of `(role_oid, privilege_bits, object_oid)` tuples in `pg_class.relacl`. Each role possesses specific **Attributes**. In this example, `cafe_manager` has `rolcanlogin = true` (enabling connection) and `rolcreaterole = true` (allowing the creation of other roles). The `cafe_waiter` role is not login-capable and serves only as a container for permissions. > [!CAUTION] > **Superuser Privileges**: The `rolsuper` attribute bypasses all permission checks in the engine. A superuser can access any data, modify any configuration, and drop any object. This attribute should be restricted to recovery and administrative tasks, as its compromise results in complete control over the cluster. ### Object Privileges (`GRANT`) A logged-in role still cannot read tables it has not been explicitly granted access to. Every relation has its own access control list (ACL), modified via `GRANT` and `REVOKE`: ```sql GRANT SELECT ON orders TO cafe_waiter; ``` Every query Postgres runs checks `pg_class.relacl` to confirm the executing role has the matching privilege bit. ### Anatomy of an ACL Item The ACL on a table is stored as an array of `aclitem` values that look like this: `cafe_waiter=r/postgres`. Each item has three parts: ```text ┌──────────────────────────────────────────────────────────┐ │ aclitem │ ├─────────────────┬──────────────────┬─────────────────────┤ │ GRANTEE │ PRIVILEGES │ GRANTOR │ │ (Who gets it) │ (Which bits) │ (Who gave it) │ ├─────────────────┼──────────────────┼─────────────────────┤ │ cafe_waiter │ = r │ / postgres │ └─────────────────┴──────────────────┴─────────────────────┘ ``` - **Grantee**: the role receiving the privilege. - **Privileges**: a string of single-letter codes (one per privilege bit). - **Grantor**: the role that performed the `GRANT`. > [!WARNING] > **The PUBLIC Pseudo-Role**: If the grantee field in an ACL is empty (e.g., `=r/postgres`), the privilege is granted to **`PUBLIC`**. This means every existing and future role has access to the object. Ensure sensitive tables are revoked from `PUBLIC` to prevent unauthorized access. ### Privilege Codes Postgres encodes privileges as single letters in the ACL string: | Code | SQL Privilege | Meaning | | :--- | :--- | :--- | | **`r`** | `SELECT` | Read rows from the table. | | **`a`** | `INSERT` | Append new rows. | | **`w`** | `UPDATE` | Modify existing rows. | | **`d`** | `DELETE` | Remove rows. | | **`D`** | `TRUNCATE` | Empty the entire table at once. | | **`x`** | `REFERENCES` | Create a foreign key against this table. | | **`t`** | `TRIGGER` | Define triggers on this table. | | **`X`** | `EXECUTE` | Call a function or procedure. | | **`U`** | `USAGE` | Enter a schema, sequence, or other namespace. | So `{postgres=arwdDxt/postgres}` reads as "the `postgres` role holds every available privilege on this object, granted by itself" — typically the row Postgres writes when the owner first creates the table. > [!IMPORTANT] > **Schema-Level Permissions**: Granting `SELECT` on a table is insufficient if the role lacks `USAGE` on the schema. Postgres checks for schema-level access before evaluating object-level privileges. Both are required for a query to succeed. ### Membership Tracking: `pg_auth_members` The relationships between roles are recorded in the **`pg_auth_members`** catalog. This table tracks the membership graph, identifying which roles belong to which groups and who granted the membership. ```sql SELECT m.rolname AS member, g.rolname AS group_role, admin_option, inherit_option, set_option FROM pg_auth_members am JOIN pg_roles m ON am.member = m.oid JOIN pg_roles g ON am.roleid = g.oid; ``` Mastering role hierarchies enables a scalable, group-based security model. It allows the engine to enforce access control efficiently without requiring redundant individual permissions. --- ## 9.2 - Default Privileges (The Manager's Orders) <img src="assets/arch_access_default_privs.png" width="250" style="float: left; margin: 0 20px 20px 0;" /> The biggest headache in Postgres security is the **New Object Problem**. In **[[Manuscript/02 - Physical Storage & MVCC/2.0 - Storage Foundations (The Building Blocks of Storage)|Chapter 2]]**, we saw that data is stored in relations. A new table is owned by the creating/current role, whose ownership implies control even though those rights are not represented as an ordinary ACL grant. PostgreSQL's built-in default grants no table privileges to `PUBLIC`; superusers and other privileged paths remain exceptions. A previous `GRANT ... ON ALL TABLES IN SCHEMA` affects the objects that existed at that moment, not future tables. If a migration creates a new recipe book tomorrow, other roles will receive a "Permission Denied" error if they try to access it. **The Intuition**: Think of Default Privileges as a **Standing Order** for a personal assistant. The assistant only knows what to do when *their specific boss* (the Creator) produces a new document. They have no instructions for what to do when a different office (a different Role) brings in a table! **The Standing Order**: Default Privileges are **Personal Automation**. They do not pull permissions into the schema automatically; they are triggered only when a specific role performs a specific DDL action. This is managed through the `pg_default_acl` system catalog. > [!TIP] > **The Owner's Trap** > A standing order automates ACLs, while the **owner** retains implicit ownership powers such as altering or dropping the object and granting privileges. Superusers and roles able to assume the owner remain part of the threat model. ### The Standing Order To fix this, you don't grant privileges on the ledger. You give a **Standing Order** to Postgres, known as `ALTER DEFAULT PRIVILEGES`. ```sql ALTER DEFAULT PRIVILEGES FOR ROLE cafe_manager IN SCHEMA public GRANT SELECT ON TABLES TO cafe_waiter; ``` This tells Postgres: *"Listen closely. Every time the `cafe_manager` creates a new ledger in the public room, I want you to immediately cut a 'read' key and hand it to `cafe_waiter`."* > [!NOTE] > **Technical Secret: The Implicit Creator Gotcha** > If you omit `FOR ROLE`, the current role is used. If migrations create objects as a different current role—including after `SET ROLE`—those defaults do not apply. Being explicit makes reviews safer, provided the grantor is authorized to alter that role's defaults. One of the most common security failures in a growing database is the "New Object Gap." When you create a new table, it is owned by the role that created it. By default, no other roles (except superusers) have access to it. If you have a team of developers creating tables, you will constantly be running manual `GRANT` commands to keep your permissions in sync. To solve this, PostgreSQL provides **Default Privileges**. ### The Mechanism: `pg_default_acl` Default privileges allow you to define a set of access rules that will be applied automatically to any **future** objects created within a specific schema. These rules are stored in the `pg_default_acl` system catalog. Unlike standard `GRANT` commands which modify the ACL of an existing object, `ALTER DEFAULT PRIVILEGES` modifies the *blueprint* for new objects. ```sql -- Enforce that any new tables created by 'postgres' in the 'public' schema -- are automatically readable by the 'cafe_admin' group. ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public GRANT SELECT ON TABLES TO cafe_admin; ``` ### The Critical "Gotcha": The Creator Constraint Default privileges in Postgres possess a significant architectural limitation: **They are tied to the creator, not the schema.** If you run the command above for the `postgres` role, it will works perfectly—*as long as the `postgres` role is the one creating the tables*. If another user, `head_chef`, creates a table in the same schema, the default privileges you set for `postgres` **will not apply**. The `head_chef` is a different creator, and they have their own (likely empty) set of default ACLs. > [!WARNING] > **The Inheritance Fallacy**: Even if `head_chef` is a member of the `postgres` role, the default privileges of the group do not automatically apply to objects created by the member. Default privileges must be explicitly defined for every role that is expected to create objects, or you must ensure all creators use `SET ROLE` to become the authorized creator before running DDL. ### Auditing Defaults You can inspect your current default blueprints using the `\ddp` command in `psql` or by querying the catalog directly: ```sql -- View all default ACLs in the cluster SELECT defaclrole::regrole AS creator, defaclnamespace::regnamespace AS schema, defaclobjtype AS type, defaclacl AS acl FROM pg_default_acl; ``` - **`defaclrole`**: The role whose creations are being modified. - **`defaclnamespace`**: The schema filter (Zero means "all schemas"). - **`defaclobjtype`**: `r` for tables, `f` for functions, `S` for sequences. ### Strategy: The "Secure by Default" Pattern For a robust production environment, follow this sequence: 1. **Define a NOLOGIN Admin Role** (`cafe_mgr`) that will own all schemas. 2. **Define a NOLOGIN Read Role** (`cafe_reader`) for reporting. 3. **Establish Defaults** for the Admin Role so it always grants access to the Read Role. 4. **Enforce DDL execution** through the Admin Role only. By mastering default privileges, you ensure that security is an inherent property of your architecture, rather than a manual checklist that can be forgotten during a busy deployment. --- ## 9.3 - Row-Level Security (The VIP List) <img src="assets/arch_bouncer_rhino.png" width="250" style="float: left; margin: 0 20px 20px 0;" /> Table-level permissions are binary: a role either possesses `SELECT` privileges on a table or it does not. In multi-tenant environments where users share a single table but must be restricted to specific rows, a more granular mechanism is required. **Row-Level Security (RLS)** provides this granularity. It attaches declarative visibility and write-validation policies to a table. For roles subject to RLS, PostgreSQL applies the policy even when application SQL forgets its tenant predicate. That is a valuable defense-in-depth boundary—but only if the application role is truly subject to the policy and the tenant context itself cannot be forged. To see why this matters, imagine a `secrets` table that the Manager and the Waiter both have read access to: ```sql CREATE TABLE secrets (id int, owner name, secret text); INSERT INTO secrets VALUES (1, 'cafe_manager', 'The safe code is 555-1234'); INSERT INTO secrets VALUES (2, 'cafe_waiter', 'The Planner burned the toast'); GRANT SELECT ON secrets TO PUBLIC; ``` Without RLS, both roles see both rows. The waiter can read the safe code; the manager learns who burned the toast. Granular per-row access requires a different mechanism. ### The Mechanism: Query Rewriting RLS is implemented as a **Query Rewriting** transformation within the **[[Manuscript/04 - Query Planning & Execution/4.1 - Query Planner (The Blueprint of Execution)|Query Planner]]**. It is not a secondary screening process after data retrieval. When RLS is enabled, the planner retrieves policy expressions from the `pg_policy` catalog and injects them into the query tree. These expressions serve as additional `WHERE` clauses for read operations or validation predicates for write operations. ```sql -- Enabling RLS on a table ALTER TABLE secrets ENABLE ROW LEVEL SECURITY; -- Creating a policy: Users can only see their own records CREATE POLICY read_own ON secrets FOR SELECT USING (owner = current_user); ``` It is useful to reason as though `SELECT * FROM secrets` acquired an additional predicate such as `owner = current_user`. Internally, row-security qualifications are attached during rewrite and constrained during planning; they need not appear as a separate, literal `Filter` node in every plan. ### Who RLS Does—and Does Not—Constrain | Executing identity | Subject to an enabled policy? | Production implication | | :--- | :---: | :--- | | Ordinary role with table privileges | Yes | This should be the application path. With no applicable policy, access is default-deny. | | Superuser | No | Never use a superuser as the tenant-facing application role. | | Role with `BYPASSRLS` | No | Reserve this attribute for tightly controlled administration. | | Table owner | Normally no | Do not make the application role the table owner. | | Table owner after `ALTER TABLE ... FORCE ROW LEVEL SECURITY` | Yes in ordinary queries | Use `FORCE` when the owner must be tested through policy; superusers and `BYPASSRLS` still bypass. | RLS does not authenticate a session variable. If a policy trusts `current_setting('app.current_tenant_id')` and the connected role is free to set that value, the same client can select a different tenant. Establish tenant context through a trusted connection boundary or a carefully hardened function/role design, and test the actual login role, ownership, membership, and pool-reset behavior. ### Visibility vs. Validation (USING vs. WITH CHECK) Postgres distinguishes between existing data and new data being introduced to the system: - **`USING`**: Defines row visibility. If a row does not satisfy this expression, it is excluded from the result set. The engine treats the row as if it does not exist. - **`WITH CHECK`**: Validates new or modified data. If an `INSERT` or `UPDATE` operation produces a row that violates this expression, the operation fails with an error. > [!IMPORTANT] > **Silencing the Errors**: RLS is fundamentally a **Visibility** tool. When a user tries to read a row they don't own, Postgres does not throw an error; it simply returns an empty result set. This prevents "Identity Probing"—a side-channel attack where a hacker could guess the existence of IDs by watching for error messages. ### Performance Implications As a query rewriting mechanism, RLS introduces computational overhead: 1. **Additional qualifications**: Applicable policy expressions become security qualifications. They may be combined with scans and indexes rather than appearing as one dedicated `Filter` node. 2. **Index Suppression**: If a policy uses volatile functions or expressions the planner cannot optimize, it may revert to a **Sequential Scan**, bypassing indexes. 3. **Lookup overhead**: Policies that consult other tables can add planning and execution work. Whether a subquery runs once, is hashed, or is evaluated repeatedly depends on the plan; inspect it rather than assuming per-row execution. > [!TIP] > **Keep Policies Local**: To avoid the performance tax, keep your RLS policies as simple as possible. Use simple column-to-user comparisons (`owner = current_user`) whenever possible to ensure the Planner can still utilize indexes effectively. ### High-Performance Security: The Security Barrier PostgreSQL normally evaluates policy expressions before user-supplied query conditions that could leak protected values. Functions marked `LEAKPROOF` are the documented exception: the optimizer may move them ahead of the row-security check. This ordering constraint can limit some optimizations, which is why policy expressions, operators, and indexes should be tested together. ### Auditing Policies Policy definitions are stored in the `pg_policy` system catalog. You can inspect the visceral, internal representation of these rules: ```sql -- View policies and their internal SQL expressions SELECT polname, polcmd, polroles, polqual FROM pg_policy; ``` - **`polcmd`**: The command type (`r` for SELECT, `a` for INSERT, `w` for UPDATE, `d` for DELETE). - **`polqual`**: The `USING` expression. - **`polwithcheck`**: The `WITH CHECK` expression. RLS moves an important part of row authorization into database metadata. Consistency still depends on using non-bypass roles, safe ownership, correct policies, trusted identity context, and tests for every command type. --- ### 🧪 Manipulation Lab: Build a SaaS Tenant Model To see how RLS applies tenant filtering, we will enable it, construct a policy that reads a session variable, and verify the policy with a non-superuser, non-owner role. This lab demonstrates mechanics, not a complete tenant-authentication design: the production note above explains why an untrusted client must not be allowed to choose arbitrary tenant context. #### The Setup Connect to the database and run the following statements as the superuser: ```sql CREATE TABLE tenant_data ( tenant_id TEXT, data TEXT ); -- 1. Enable RLS ALTER TABLE tenant_data ENABLE ROW LEVEL SECURITY; ALTER TABLE tenant_data FORCE ROW LEVEL SECURITY; -- 2. Define a policy that matches tenant_id to a session parameter CREATE POLICY tenant_isolation_policy ON tenant_data USING (tenant_id = current_setting('app.current_tenant_id', true)); -- Insert data for two separate tenants INSERT INTO tenant_data VALUES ('tenant_alpha', 'Alpha Secret Data'), ('tenant_beta', 'Beta Secret Data'); -- 3. Create a non-superuser application role and grant access CREATE ROLE cafe_app NOLOGIN; GRANT SELECT ON tenant_data TO cafe_app; ``` #### The Task 1. Switch your session identity to the application role: ```sql SET ROLE cafe_app; ``` 2. Query the table without setting a tenant session ID: ```sql SELECT count(*) FROM tenant_data; ``` Output: ``` count ------- 0 ``` Because the session variable `app.current_tenant_id` is empty, no rows match the policy, and Postgres returns an empty result set. 3. Set the tenant ID in the session and query again: ```sql SET app.current_tenant_id = 'tenant_alpha'; SELECT data FROM tenant_data; ``` Output: ``` data ------------------- Alpha Secret Data ``` #### Postgres Appends the Tenant Filter Automatically Notice that Postgres now returns only the record matching `'tenant_alpha'`. The query planner appended the RLS policy filter to the query automatically. ```sql -- Clean up RESET ROLE; DROP TABLE tenant_data; DROP ROLE cafe_app; ``` > [!WARNING] > **The Bypass Matrix Matters**: The `postgres` superuser and any role with `BYPASSRLS` see both records. Without `FORCE ROW LEVEL SECURITY`, the table owner normally bypasses the policy too. A production application role must be non-superuser, lack `BYPASSRLS`, and not own the tenant table; its tenant context must come from a trusted boundary. #### The Database Supplies the Missing Tenant Filter For a correctly constrained application role, RLS supplies the tenant predicate when application SQL omits it. That blocks an important class of accidental cross-tenant queries. It is not a substitute for authenticating the tenant context, controlling bypass roles and ownership, or testing policy behavior under pooling and privileged maintenance paths. --- ## 9.4 - Security Definers (The Manager's Override) <img src="assets/arch_access_security_definer_v2.png" width="250" style="float: left; margin: 0 20px 20px 0;" /> ### The Access Paradox Imagine you have a restricted database user role named `cafe_waiter`. If they run a direct command like `UPDATE orders SET status = 'Served' WHERE id = 123;`, the query fails instantly with a **`Permission Denied`** error. The waiter is forbidden from altering the order ledger. Yet, when the waiter executes a specific database function—`SELECT audit.mark_served(123);`—the order status is successfully updated. The waiter has modified the forbidden table, and the engine accepted the write without raising any security alerts. How does Postgres allow a restricted user to bypass table-level access rules for a specific operation without permanently granting them administrative privileges? > [!IMPORTANT] Predict Whose Privileges the Function Uses > How does a function allow a restricted caller to edit a forbidden table? What mechanism shifts the privilege context during function execution, and does it run as the caller or another role? Pause and formulate a guess. You might expect that database functions simply run with "superuser" bypass authority implicitly, or that they check the caller's privileges but make exceptions for specific lines of code. Neither is true. If all functions bypassed security, database functions would represent a massive, unmanageable security hole, and checking line-by-line exceptions would be slow and complex. Instead, Postgres resolves this using the **`SECURITY DEFINER`** attribute during function creation. By default, database functions are created as **`SECURITY INVOKER`**. This means that when a user calls them, the function executes using the privileges of the caller (the invoker). If the caller lacks permission on the target table, the function crashes. However, if a function is declared as **`SECURITY DEFINER`** (think of it as the **Manager's Override**): 1. **Identity Context Switch**: When a user executes the function, Postgres literally swaps the `CurrentUser` variable in the active session. 2. **Definer Elevation**: The function body executes with the privileges of the role that **created** the function (the Definer), not the user calling it. 3. **Restoration**: Once the function finishes and returns, Postgres restores the identity back to the original caller. This allows the Cafe Owner (a superuser-equivalent Definer) to compile a tightly controlled API function, grant `cafe_waiter` permission to *execute* the function, and let the waiter perform the update under elevated privileges without ever granting them direct access to the underlying table. `SECURITY DEFINER` enables secure API boundaries inside the database, allowing restricted users to perform highly audited actions on private tables. However, this delegation represents a serious **Privilege Escalation** vector. If the function is not designed with strict security rules, it can be hijacked to execute arbitrary code under the definer's identity. ### The Trojan Horse (The `search_path` Vulnerability) The most dangerous vulnerability in `SECURITY DEFINER` functions is a **Search Path Attack**. If the function references a table by an unqualified name, Postgres resolves that name through the caller's `search_path` at execution time — *not* the definer's. An attacker who controls a schema in front of `public` on the search path can substitute a malicious object that the function then reads or writes with elevated privileges. #### A Worked Example Suppose the cafe owner ships an audited helper function for waitstaff to mark an order served: ```sql -- Created by the cafe owner (a superuser-equivalent) CREATE FUNCTION audit.mark_served(order_id BIGINT) RETURNS VOID LANGUAGE plpgsql SECURITY DEFINER AS $ BEGIN -- Note: 'orders' is unqualified UPDATE orders SET status = 'Served' WHERE id = order_id; -- And 'audit_log' is also unqualified INSERT INTO audit_log(actor, action, target_id) VALUES (current_user, 'mark_served', order_id); END; $; GRANT EXECUTE ON FUNCTION audit.mark_served(BIGINT) TO cafe_waiter; ``` The function looks tight: only waitstaff can call it, it uses `SECURITY DEFINER` to update orders the waiter cannot directly modify, and every call is logged. The hole is that `orders` and `audit_log` are unqualified. A motivated waiter (or any role with `CREATE` on a schema that appears earlier in the resolution path) can install a shadow: ```sql -- Run as cafe_waiter SET search_path = my_schema, public; CREATE TABLE my_schema.orders (id BIGINT PRIMARY KEY, status TEXT); CREATE RULE bait AS ON UPDATE TO my_schema.orders DO INSTEAD -- Side effect: reading a sensitive file with definer privileges SELECT pg_read_file('/etc/passwd'); ``` Now when the waiter calls `audit.mark_served(123)`, the function runs as the cafe owner, resolves `orders` to `my_schema.orders` (because the waiter's `search_path` puts `my_schema` first), and triggers the rule — granting the waiter the right to read arbitrary files on the server, courtesy of the definer's elevated identity. #### Hardening the Vault Door Three rules close this attack surface: 1. **Pin `search_path` on the function itself**. The `SET` clause runs *before* the function body and overrides whatever the caller had set: ```sql ALTER FUNCTION audit.mark_served(BIGINT) SET search_path = pg_catalog, public, pg_temp; ``` Putting `pg_catalog` first prevents an attacker from shadowing built-in operators and types as well. 2. **Schema-qualify every object reference inside the body**. `UPDATE public.orders` and `INSERT INTO audit.audit_log` cannot be hijacked by `search_path` games at all. 3. **Revoke `PUBLIC` from the function and from `public`**: ```sql -- Functions are created with EXECUTE for PUBLIC by default REVOKE EXECUTE ON FUNCTION audit.mark_served(BIGINT) FROM PUBLIC; -- The public schema is writable by every login role on PG <= 14 REVOKE ALL ON SCHEMA public FROM PUBLIC; ``` Together these constraints reduce the trust boundary to exactly what the function body says: definer-privileged code reading definer-pinned objects, with no opportunity for the caller to redirect a name lookup. ### Auditing Your Posture Before concluding our exploration of Access Control, perform a final audit of the cluster's security state using these diagnostic axes: 1. **Identity Entropy**: Are you using dedicated Roles for services and humans, or are you over-relying on the `postgres` superuser? 2. **The Least Privilege Check**: Does every role have exactly the `GRANT` keys required for its specific schema (**[[Manuscript/09 - Identity & Access Control/9.1 - Roles & Privileges (The Name Tags)|9.1 Roles & Privileges]]**) and table (**[[Manuscript/02 - Physical Storage & MVCC/2.4 - Relation (The Table)|2.4 Relation]]**)? 3. **The "Secure by Default" Check**: Are **[[Manuscript/09 - Identity & Access Control/9.2 - Default Privileges (The Manager's Orders)|Default Privileges]]** configured so that new data is born within the correct security boundaries? 4. **The Visibility Audit**: Is **[[Manuscript/09 - Identity & Access Control/9.3 - Row-Level Security (The VIP List)|Row-Level Security]]** active on multi-tenant tables, and have you verified that policies correctly exclude rows using `USING` clauses? 5. **The Escalation Audit**: Have you vetted every `SECURITY DEFINER` function for `search_path` safety and unintended side-effects? By mastering the catalogs and the identity graph, you transition from a passive consumer of database security to an architect who builds systems that are **Secure by Construction**. --- ## 9.5 - DDL Audit Logging (The Paper Trail) <img src="assets/arch_ddl_audit_log_v1.png" width="250" style="float: left; margin: 0 20px 20px 0;" /> Every mechanism in this chapter so far has answered the same question: *who is allowed to do what?* Roles define identity. Privileges define capability. RLS defines visibility. Security Definers define delegation. But none of them answer the question that surfaces at 3 AM during an incident: *who actually did what, and when?* DDL statements — `CREATE`, `ALTER`, `DROP`, `GRANT`, `REVOKE` — are the commands that reshape the schema itself. A misplaced `DROP TABLE` or a silent `ALTER COLUMN` can cause cascading application failures, and if no audit record exists, the forensic trail is cold. Postgres provides three mechanisms for capturing DDL activity, each with a different tradeoff between simplicity and queryability. ### The Blunt Instrument: `log_statement` The fastest path to DDL visibility is the `log_statement` server parameter. It accepts four values: `none`, `ddl`, `mod`, and `all`. Setting it to `ddl` causes the server to emit a log line for every DDL statement executed by any session. ```sql -- Enable DDL logging (requires reload, not restart) ALTER SYSTEM SET log_statement = 'ddl'; SELECT pg_reload_conf(); ``` After this reload, every `CREATE TABLE`, `ALTER INDEX`, `DROP FUNCTION`, `GRANT`, and `REVOKE` is written to the server log — the same destination configured by `log_destination` (typically `stderr` or `csvlog`). > [!TIP] > **Enrich the log line.** By default, log entries contain the statement text but limited session context. Add `%u` (user), `%d` (database), and `%r` (remote host) to `log_line_prefix` to make each entry forensically useful: > ``` > log_line_prefix = '%m [%p] %u@%d ' > ``` This approach has a critical limitation: the output is a flat text stream, not a relational table. You cannot `SELECT` from it, join it, or filter it with SQL, and log rotation may discard old entries. That can be enough for basic operational visibility. An audit trail also needs trustworthy identity, retention, access control, and protection against tampering. ### Structured Audit Records: `pgAudit` The `pgaudit` extension emits detailed session and object audit records through PostgreSQL's standard logging facility, using a documented, parseable format. It improves coverage and structure; by itself, it does not prove that every committed transaction has a durable, tamper-resistant record. ``` -- postgresql.conf shared_preload_libraries = 'pgaudit' pgaudit.log = 'DDL' ``` After a server restart and `CREATE EXTENSION pgaudit`, pgAudit can emit records containing the audit type, statement class, command tag, object metadata, statement text, and—when explicitly configured—parameter values. Exact fields depend on the selected settings and command. pgaudit operates in two modes: 1. **Session-level** (`pgaudit.log`): Logs all statements matching the configured classes for every session. Broad and simple. 2. **Object-level** (`pgaudit.role`): Uses membership in a designated audit role to select relation-level `SELECT`, `INSERT`, `UPDATE`, and `DELETE` activity for objects on which that role has privileges. This is useful for focused data-access auditing; it is not an object filter for DDL classes, so DDL coverage still comes from session logging. > [!WARNING] > **pgaudit is not a default extension.** It must be compiled and installed separately, or be available in your distribution's extension catalog. Many managed Postgres services (RDS, Cloud SQL, Supabase) include it; vanilla Docker images typically do not. Check `SELECT * FROM pg_available_extensions WHERE name = 'pgaudit';` before relying on it. Even with pgAudit, the destination remains the server log. Its documentation calls logging best-effort and non-transactional: records do not commit atomically with the transaction, may disappear around failures or unavailable destinations, and can describe statements that later roll back. It also cannot reliably audit superusers. Send the log outward when you need durable search, restricted retention, integrity controls, or alerting. ### The Relational Approach: Event Triggers Postgres provides a native mechanism for capturing DDL events and writing them to an ordinary table: the **Event Trigger**. Unlike regular triggers (which fire on DML against a specific table), event triggers fire on DDL commands across the entire database. An event trigger hooks into one of four firing points: | Event | Fires | Available Context | | :--- | :--- | :--- | | `ddl_command_start` | Before the DDL executes | Command tag only | | `ddl_command_end` | After successful DDL | `pg_event_trigger_ddl_commands()` — full object metadata | | `sql_drop` | After a DROP completes | `pg_event_trigger_dropped_objects()` — cascade details | | `table_rewrite` | When ALTER TABLE rewrites data | `pg_event_trigger_table_rewrite_oid()` | The `ddl_command_end` event fires after successful DDL, and `pg_event_trigger_ddl_commands()` exposes the command tag, object type, schema name, and fully qualified object identity. Combined with `current_user` and `current_query()`, that produces useful database-local evidence. Whether it is enough depends on what your audit must prove and whom it must withstand. > [!NOTE] > **Why not `ddl_command_start`?** The start event fires before the command executes, so `pg_event_trigger_ddl_commands()` is not yet populated. It is useful for *preventing* DDL (by raising an exception), but not for *recording* it. The key operational advantage of event triggers is that the audit data is immediately queryable and joinable inside the database. That is also its trust limitation: a sufficiently privileged actor can alter the trigger, table, function, or history, and a database failure can affect both the audited objects and their evidence. Export important records to separately administered, append-oriented storage when tamper resistance or independent retention matters. ### 🧪 Manipulation Lab: Build a DDL Audit Log This lab constructs a self-contained DDL audit system using an event trigger, a drop trigger, and a relational log table. #### The Setup Connect to the database as the superuser and create the audit infrastructure: ```sql -- 1. Create a dedicated audit schema CREATE SCHEMA IF NOT EXISTS audit; -- 2. Create the log table CREATE TABLE audit.ddl_log ( id BIGSERIAL PRIMARY KEY, logged_at TIMESTAMPTZ NOT NULL DEFAULT now(), actor TEXT NOT NULL DEFAULT current_user, command_tag TEXT NOT NULL, object_type TEXT, schema_name TEXT, object_name TEXT, statement TEXT ); -- 3. Create the event trigger function for CREATE/ALTER CREATE OR REPLACE FUNCTION audit.log_ddl_event() RETURNS event_trigger LANGUAGE plpgsql AS $ DECLARE r RECORD; BEGIN FOR r IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP INSERT INTO audit.ddl_log(command_tag, object_type, schema_name, object_name, statement) VALUES ( r.command_tag, r.object_type, r.schema_name, r.object_identity, current_query() ); END LOOP; END; $; -- 4. Create a separate function for DROP events CREATE OR REPLACE FUNCTION audit.log_drop_event() RETURNS event_trigger LANGUAGE plpgsql AS $ DECLARE r RECORD; BEGIN FOR r IN SELECT * FROM pg_event_trigger_dropped_objects() LOOP INSERT INTO audit.ddl_log(command_tag, object_type, schema_name, object_name, statement) VALUES (tg_tag, r.object_type, r.schema_name, r.object_identity, current_query()); END LOOP; END; $; -- 5. Register both event triggers CREATE EVENT TRIGGER audit_all_ddl ON ddl_command_end EXECUTE FUNCTION audit.log_ddl_event(); CREATE EVENT TRIGGER audit_drops ON sql_drop EXECUTE FUNCTION audit.log_drop_event(); -- Remove DDL generated while installing the lab itself. -- DELETE is DML, so it does not fire these DDL event triggers. DELETE FROM audit.ddl_log; ``` #### The Task Fire a sequence of DDL statements that simulate a typical schema evolution: ```sql CREATE TABLE public.test_audit_target (id INT, label TEXT); ALTER TABLE public.test_audit_target ADD COLUMN created_at TIMESTAMPTZ; CREATE INDEX test_audit_idx ON public.test_audit_target(id); DROP INDEX public.test_audit_idx; DROP TABLE public.test_audit_target; ``` Now query the audit log: ```sql SELECT id, logged_at::TIME(0) AS logged_at, actor, command_tag, object_type, object_name FROM audit.ddl_log ORDER BY id; ``` Illustrative output (drop cascades, generated type names, OIDs, and row order can vary by version and schema state): ``` actor | command_tag | object_type | object_name ---------+-------------+-------------+-------------------------------- postgres | CREATE ... | ... | public.test_audit_target postgres | ALTER ... | ... | public.test_audit_target postgres | DROP ... | ... | public.test_audit_target postgres | DROP ... | ... | dependent objects, if present ``` #### Two Triggers Capture DDL and Cascaded Drops Several things are worth noting: 1. **The exercised DDL is captured.** The commands in this lab appear with the acting role and timestamp. Event triggers do not see every command or cross-database and cluster event, so test them against the events you care about. 2. **Drops can cascade into multiple records.** `sql_drop` exposes the dropped table plus the dependent objects PostgreSQL reports, such as generated types or a TOAST table when present. The exact row count changes with the release and schema shape. 3. **Two triggers, two perspectives.** `ddl_command_end` reports completed DDL commands; `sql_drop` exposes dropped objects and cascades. Together they cover more ground, but not every path into or around the database. ```sql -- Clean up DROP EVENT TRIGGER audit_all_ddl; DROP EVENT TRIGGER audit_drops; DROP TABLE audit.ddl_log; DROP FUNCTION audit.log_ddl_event(); DROP FUNCTION audit.log_drop_event(); DROP SCHEMA audit; ``` #### Schema Changes Become Queryable Evidence Event triggers provide a database-native source of DDL evidence. The log table follows ordinary MVCC, backup, privilege, and replication behavior. You can query it with SQL, enrich it with role metadata, restrict its readers, and forward new entries to an independently controlled system. > [!NOTE] A Searchable Trail Is Not an Untouchable Vault > Event triggers give PostgreSQL a searchable paper trail, not an untouchable vault. A serious audit design must also establish coverage, trusted identity and time, failure behavior, privileged access, independently controlled retention, and regular tests that the evidence survives interference. --- ## 9.6 - Summary: Permission Is Data Before this chapter, “permission denied” may have felt like a wall. Now it looks like a queryable fact. Roles are rows. Privileges are recorded relationships. Row-level policies are predicates. `SECURITY DEFINER` functions deliberately change execution context. Event triggers can turn schema changes into an audit trail. That changes how you debug access. You do not have to guess whether the problem is the user, the schema, the table, the default privilege, the RLS policy, or the function context. You can inspect each layer until the missing permission becomes visible. The achievement is auditability. You can now treat security as part of the database model rather than a separate fog around it. > [!NOTE] Access Control Is Metadata with Consequences > **Concept**: Access control is not magic. It is metadata with consequences. ### Sources & Further Reading - [PostgreSQL 18: Role Membership](https://www.postgresql.org/docs/18/role-membership.html) - [PostgreSQL 18: Privileges](https://www.postgresql.org/docs/18/ddl-priv.html) - [PostgreSQL 18: Row Security Policies](https://www.postgresql.org/docs/18/ddl-rowsecurity.html) - [PostgreSQL 18: Writing `SECURITY DEFINER` Functions Safely](https://www.postgresql.org/docs/18/sql-createfunction.html#SQL-CREATEFUNCTION-SECURITY) - [PostgreSQL 18: Event Triggers](https://www.postgresql.org/docs/18/event-triggers.html) - [pgAudit documentation and caveats](https://github.com/pgaudit/pgaudit/blob/main/README.md). <div style="page-break-after: always;"></div>