SQL Security: Roles and Privileges

1. Hardware-Mirror: The Permission Cache

In 2026, the database engine does not check your permissions by reading from the disk on every query. That would be too slow. Instead, it uses the Permission Cache.

The ACL Physics

When a role (like sales_rep) is created, its Access Control List (ACL) is stored in the pg_authid and pg_class catalogs.

• The Mirror: Every time a session starts, these ACLs are loaded into the server's process-local memory.
• The Result: A permission check takes less than 1 microsecond. However, this means that if you REVOKE a permission, it might take a few milliseconds for active sessions to "See" the change, as the engine must invalidate the local cache.

Senior Standard: Use nologin roles for logical groups and login roles for human or service identities. This separation ensures that your "Hardcoded" permissions are attached to the group, making it easy to rotate individual users without re-writing your entire security policy.

2. Role Hierarchies: The "Group" Architecture

In a company of 5,000 developers, you don't grant permissions to individuals. That is an administrative suicide. You build a Tree of Roles.

The Professional Hierarchy

1. Grantor Roles: The roles that own the data.
2. Access Roles: read_only, read_write, admin_access.
3. Team Roles: Roles that map to your Slack/Teams groups (e.g., marketing_grp).
4. Login Roles: The actual credentials used by humans or machines.

Architect's Standard: If the analytics service needs write access later, you don't change the user; you simply GRANT data_writer TO marketing_grp. The security configuration lives in the Logic of the Group, not the Persistence of the User.

3. Row-Level Security (RLS): The Invisible Shield

Standard permissions are "Table-Level" (You see everything or nothing). RLS allows you to define permissions per-row. This is the ultimate tool for Multi-Tenancy.

Policy Rewriting Physics

When you enable RLS, the database engine Rewrites your query at the parser stage, before it reaches the Query Planner.

• User Query: SELECT * FROM sales_data;
• Database Rewrite: SELECT * FROM sales_data WHERE owner_id = current_user_id();
• The Fence: The user doesn't see this WHERE clause, and they cannot remove it. Even if they write their own WHERE 1=1, the engine appends the security filter with a logical AND.

The LEAKPROOF Mirror

In high-security environments, you can mark RLS functions as LEAKPROOF. This tells the engine that the function is so secure that it can't be used to "Probe" data via side-channel timing attacks or clever error messages.

4. The Auditing Mirror: Building a Tape-Proof History

Security isn't just about Locking; it's about Proof. If a record is changed, you must know who changed it and what the old value was.

The "Trigger" Physics

The most robust auditing uses Row-Level Triggers that write changes to an audit_log table in a separate, restricted schema.

• The Mirror: The audit table should be Append-Only. Even the api_user should only have INSERT access to the audit table, never DELETE or UPDATE.
• The Metadata: Always capture the session_user, the client_addr (IP address), and the query text.

pgAudit: Enterprise Standards

For high-compliance industries (FinTech, Health), we use the pgaudit extension. It hooks directly into the database logging engine to record every statement, including failed attempts, providing a tamper-proof record that satisfies SOC2 and HIPAA audits.

5. Connection Hardening: pg_hba.conf logic

Security starts at the Door. The pg_hba.conf (Host-Based Authentication) file is your gatekeeper.

The Mirror Configuration

1. Local Trust: Only allow the postgres user to connect via local Unix sockets, never the network.
2. Scram-SHA-256: Only use modern, high-entropy password hashing. Never use md5 or password modes.
3. IP Whitelisting: Explicitly list the IP addresses of your App Servers. Reject everything else (0.0.0.0/0 is a failure of architecture).
4. SSL/TLS Mandatory: Force hostssl connections. Data moving between your App and DB must be encrypted to prevent "Packet Sniffing" within your private network.

6. SQL Injection: The Parser Breach

The #1 security threat to databases is SQL Injection. This isn't a database bug; it's a failure to understand How the Engine Reads.

The Logic Split

When you send a query like SELECT * FROM users WHERE id = '1' OR '1'='1', you are attempting to inject logic into the data stream.

• The Physics of Prepared Statements: By using Parameterized Queries, the database parses the SQL structure before looking at the data.
• The Parser Lock: Once the engine has decided that the query is an index scan on the id column, it "Locks in" that logic. Even if you supply a string that contains a DROP TABLE command, the engine treats it purely as a Literal Identifier. It tries to find a user whose ID is the literal string "DROP TABLE...".
• The Result: The malicious code can never be executed because it arrived after the "Logical Branch" was already decided.

7. The Principle of Least Privilege: Granular Grants

In a high-fidelity system, your application user (e.g., web_app_user) should not own the tables. It should be a guest in the database.

The Granular Grant Mirror

• Table Level: GRANT SELECT, INSERT ON orders TO web_app_user; (The app can read and write, but not delete or alter).
• Column Level: GRANT SELECT (id, status) ON orders TO support_user; (This user can see order statuses but is physically blocked from seeing customer credit card numbers).
• Sequence Level: Remember to GRANT USAGE ON SEQUENCE... or your INSERT commands will fail.
• Function Level: Instead of granting access to a table, you can grant access to a Security Definer Function. The user can run SELECT buy_product(), but they cannot read the products table directly. This creates a "Gated Execution" model.

6. Summary: The Hardening Checklist

1. Least Privilege: The daily app_user should only have SELECT/INSERT/UPDATE on specific tables. It should NEVER have DROP TABLE or TRUNCATE privileges.
2. Schema Isolation: Keep your raw data in a private schema and expose it through Views in the public schema. This adds a layer of abstraction between the user and the storage.
3. Ownership Mirror: Ensure your tables are owned by a schema_owner role, not the postgres superuser.
4. Audit Fidelity: Log every change. In 2026, storage is cheap—Blindness is fatal.
5. Revoke Public: By default, SQL allows any user to connect to the public schema. Execute REVOKE ALL ON SCHEMA public FROM PUBLIC; on day one into your migration scripts to start with a clean slate.

The Audit Integrity Mirror

In a high-security environment, your audit logs are useless if an attacker can delete them. To build a "Hardware-Mirror" level defense, you must ensure the immutability of the trail.

• The Immutability Physics: Store your audit logs in a separate, dedicated database or a Write-Once-Read-Many (WORM) storage device.
• The Checksum Mirror: Generate a cryptographic hash (SHA-256) of each audit log entry, including the unique hash of the previous log entry in the set. This creates a "Hash Chain" (effectively a centralized blockchain). If an attacker manages to modify or delete a single log entry, the entire chain's signature will fail to validate, providing immediate proof of tampering.
• The Senior Recommendation: For multi-region architectures, stream your audit logs to a global logging service (like CloudWatch or Datadog) using a native database collector. This keeps the logs outside the blast radius of the primary database server.

Security is the "Foundation of Trust." By mastering the Role hierarchy and the magic of Row-Level Isolation, you gain the ability to build systems where data integrity is guaranteed by math and physics, not just hoping your developers don't make mistakes. You graduate from "Managing a database" to "Architecting a Fortress."

Phase 21: Security Mastery Action Items

• Run \dp in your terminal to see the Access Privileges of every table in your schema.
• Implement a Role Hierarchy where a manager role inherits from a viewer role.
• Enable ROW LEVEL SECURITY on a customers table and verify that different users can see only their own data.
• Review your pg_hba.conf and ensure all remote connections are forced over SSL.

Read next: SQL Injection: Preventing the Parsing Breach →

Part of the SQL Mastery Course — engineering the defense.