Authorization — Database Security | OneNoughtOne

0/131

Database Management SystemsDatabase Security

Database Security

LevelAdvanced

Duration90 mins

TopicDatabase Security

2 / 5

Authorization

From Identity to Permission

Authentication answers the question'Who are you?' But knowing someone's identity tells us nothing about what they should be allowed to do. Authorization is the security mechanism that bridges this gap—it's the system that transforms identity into permission. In enterprise database environments, authorization is deceptively complex. A single database might serve hundreds of applications, thousands of users, and millions of rows of data—each with different sensitivity levels, regulatory requirements, and access patterns. The authorization system must efficiently evaluate permissions for every single query, often making thousands of decisions per second. This isn't just about security—poorly designed authorization creates operational nightmares: support tickets for access requests, audit failures, compliance violations, and the constant pressure to grant 'just a little more access' until everyone has administrative privileges.

What You Will Learn

By the end of this page, you will understand the fundamental models of database authorization (DAC, MAC, RBAC), master SQL GRANT/REVOKE semantics across major database platforms, implement role-based access control architectures, leverage row-level security for fine-grained data protection, and apply the principle of least privilege systematically.

Authorization Fundamentals

Authorization determines what actions an authenticated entity (user, role, or service) may perform on database objects. Unlike authentication, which is typically a one-time verification at connection establishment, authorization decisions occur continuously throughout a session—every SELECT, INSERT, UPDATE, DELETE, and DDL statement is validated against the authorization system. Core Authorization Concepts:

Key Authorization Terminology

•Subject — The entity requesting access (user, application, service account, role).
•Object — The resource being accessed (database, schema, table, column, row, function, sequence).
•Privilege — The specific action permitted (SELECT, INSERT, UPDATE, DELETE, EXECUTE, REFERENCES, USAGE).
•Grant — The act of conferring a privilege from one subject to another.
•Revoke — The act of removing a previously granted privilege.
•Access Control List (ACL) — A data structure mapping subjects to their privileges on an object.

Authorization Models: Database systems implement various authorization models, each with distinct characteristics:

Database Authorization Models
Model	Description	Control	Use Case
Discretionary Access Control (DAC)	Object owners grant permissions at their discretion	Decentralized, owner-controlled	Most SQL databases (default)
Mandatory Access Control (MAC)	System-enforced labels and clearance levels	Centralized, policy-driven	Government, military systems (Oracle Label Security)
Role-Based Access Control (RBAC)	Permissions assigned to roles, roles assigned to users	Centralized, scalable	Enterprise applications (standard practice)
Attribute-Based Access Control (ABAC)	Dynamic decisions based on attributes (time, location, etc.)	Fine-grained, context-aware	Cloud platforms, modern IAM

Discretionary Access Control (DAC) in SQL: The SQL standard defines a DAC model where object owners control access to their objects. When a user creates a table, they become its owner and can grant privileges to others. This model is implemented by all major relational databases: Object owners have full control over their objects Privileges can be granted to specific users or to PUBLIC (all users) Grants can include WITH GRANT OPTION, allowing recipients to grant the same privilege to others The privilege chain can be several levels deep, creating administrative complexity

The DAC Weakness

Pure DAC systems suffer from the 'Trojan Horse' problem. If malicious code executes with an authorized user's privileges, it can access or copy all data that user can access, then grant access to an attacker's account. This is why enterprise environments layer RBAC and MAC on top of DAC.

The SQL Privilege System

SQL defines a comprehensive privilege system through the GRANT and REVOKE statements. Understanding these statements deeply is essential for database security administration. Privilege Types: SQL defines privileges at multiple granularity levels:

SQL Privilege Types by Object Level
Level	Privileges	Description
Database	CREATE, CONNECT, TEMPORARY	Control database-wide operations
Schema	USAGE, CREATE, ALTER, DROP	Control schema access and modification
Table	SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER	Control table data operations
Column	SELECT, INSERT, UPDATE, REFERENCES	Fine-grained column access
Sequence	USAGE, SELECT, UPDATE	Control sequence operations
Function/Procedure	EXECUTE	Control function execution
Type	USAGE	Control use of custom types

grant_revoke_basics.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
-- ================================================
-- GRANT Statement Fundamentals
-- ================================================
 
-- Basic table privilege grant
GRANT SELECT ON employees TO hr_analyst;
 
-- Grant multiple privileges
GRANT SELECT, INSERT, UPDATE ON employees TO hr_manager;
 
-- Grant all privileges (not recommended - violates least privilege)
GRANT ALL PRIVILEGES ON employees TO hr_admin;
 
-- Grant with ability to re-grant (dangerous - creates privilege chains)
GRANT SELECT ON employees TO senior_hr WITH GRANT OPTION;
 
-- Grant to all users (PUBLIC pseudo-role)
GRANT SELECT ON public_announcements TO PUBLIC;
 
-- Column-level grants (fine-grained access)
GRANT SELECT (employee_id, name, department) ON employees TO auditor;
-- auditor cannot see salary, ssn, or other sensitive columns
 
-- Grant on all tables in schema
GRANT SELECT ON ALL TABLES IN SCHEMA reporting TO report_reader;
 
-- Grant on future tables (PostgreSQL)
ALTER DEFAULT PRIVILEGES IN SCHEMA reporting
    GRANT SELECT ON TABLES TO report_reader;
 
-- ================================================
-- REVOKE Statement Fundamentals
-- ================================================
 
-- Basic revoke
REVOKE INSERT ON employees FROM hr_analyst;
 
-- Revoke all privileges
REVOKE ALL PRIVILEGES ON employees FROM former_employee;
 
-- Revoke grant option only (user keeps the privilege)
REVOKE GRANT OPTION FOR SELECT ON employees FROM senior_hr;
 
-- Cascade revoke (revoke from grantees of this grantee)
REVOKE SELECT ON employees FROM senior_hr CASCADE;
 
-- Restrict revoke (fail if others depend on this grant)
REVOKE SELECT ON employees FROM senior_hr RESTRICT;
 
-- ================================================
-- Viewing Current Privileges
-- ================================================
 
-- PostgreSQL: View table privileges
SELECT 
    grantee,
    table_schema,
    table_name,
    privilege_type,
    is_grantable
FROM information_schema.table_privileges
WHERE table_name = 'employees';
 
-- PostgreSQL: View role memberships
SELECT 
    r.rolname AS role,
    m.rolname AS member,
    g.rolname AS granted_by
FROM pg_auth_members am
JOIN pg_roles r ON r.oid = am.roleid
JOIN pg_roles m ON m.oid = am.member
JOIN pg_roles g ON g.oid = am.grantor;

DENY vs REVOKE

REVOKE removes a previously granted permission (returns to default state). DENY explicitly prohibits access, overriding any GRANT. In SQL Server, DENY takes precedence—if a user has both GRANT and DENY, the DENY wins. Use DENY sparingly for explicit exclusions; prefer REVOKE for routine permission removal.

Role-Based Access Control (RBAC)

Role-Based Access Control is the cornerstone of scalable database authorization. Instead of granting privileges directly to users, permissions are granted to roles, and roles are assigned to users. This indirection provides enormous administrative and security benefits. Why RBAC? Consider an organization with 500 employees accessing a database with 200 tables. Without roles: Each user-table permission pair is a separate grant 500 \times 200 = 100,000 individual grants to manage Employee onboarding requires dozens of individual grants Employee offboarding requires hunting down all grants Auditing who has access to what is nearly impossible With RBAC: Define 10-20 roles matching job functions Grant table permissions to roles (~2,000 grants) Assign users to roles (500 assignments) Onboarding = assign appropriate role(s) Offboarding = remove role assignment(s)

Converting Mermaid diagram...

rbac_implementation.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
-- ================================================
-- Complete RBAC Implementation in PostgreSQL
-- ================================================
 
-- Step 1: Create functional roles (no login capability)
CREATE ROLE hr_read NOLOGIN;
CREATE ROLE hr_write NOLOGIN;
CREATE ROLE finance_read NOLOGIN;
CREATE ROLE finance_write NOLOGIN;
CREATE ROLE audit_read NOLOGIN;
 
-- Step 2: Establish role hierarchy
 
-- hr_write inherits from hr_read (writers can also read)
GRANT hr_read TO hr_write;
 
-- finance_write inherits from finance_read
GRANT finance_read TO finance_write;
 
-- auditors get read access to everything
GRANT hr_read TO audit_read;
GRANT finance_read TO audit_read;
 
-- Step 3: Grant object privileges to roles
GRANT USAGE ON SCHEMA hr TO hr_read, hr_write;
GRANT SELECT ON ALL TABLES IN SCHEMA hr TO hr_read;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA hr TO hr_write;
 
GRANT USAGE ON SCHEMA finance TO finance_read, finance_write;
GRANT SELECT ON ALL TABLES IN SCHEMA finance TO finance_read;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA finance TO finance_write;
 
-- Auditors need read on audit log
GRANT USAGE ON SCHEMA audit TO audit_read;
GRANT SELECT ON audit.audit_log TO audit_read;
 
-- Step 4: Handle future objects (critical for maintainability!)
ALTER DEFAULT PRIVILEGES IN SCHEMA hr
    GRANT SELECT ON TABLES TO hr_read;
ALTER DEFAULT PRIVILEGES IN SCHEMA hr
    GRANT INSERT, UPDATE, DELETE ON TABLES TO hr_write;
 
-- Step 5: Create login roles (actual users)
CREATE ROLE alice LOGIN PASSWORD 'secure_password_1';
CREATE ROLE bob LOGIN PASSWORD 'secure_password_2';
CREATE ROLE carol LOGIN PASSWORD 'secure_password_3';
CREATE ROLE david LOGIN PASSWORD 'secure_password_4';
 
-- Step 6: Assign roles to users
GRANT hr_write TO alice;        -- HR Manager: read + write
GRANT hr_read TO bob;           -- HR Analyst: read only
GRANT finance_write TO carol;   -- Finance Manager: read + write
GRANT audit_read TO david;      -- Auditor: read everything
 
-- Step 7: Set role activation (optional - require explicit SET ROLE)
ALTER ROLE alice SET role = 'hr_write';  -- Default active role
 
-- View role memberships
SELECT 
    r.rolname AS role,
    ARRAY_AGG(m.rolname) AS members
FROM pg_roles r
JOIN pg_auth_members am ON r.oid = am.roleid
JOIN pg_roles m ON m.oid = am.member
GROUP BY r.rolname;

Role Design Principles

Design roles around job functions, not individuals or departments. Create granular roles (reader, writer, admin) and compose them as needed. Avoid creating roles like 'john_smith_access' or 'temporary_project_role'. Good role names are self-documenting: 'order_processor_read', 'inventory_update', 'reporting_analyst'.

Row-Level Security (RLS)

Traditional SQL privileges operate at the object level—you either have access to a table or you don't. But many applications require finer granularity: a sales representative should see only their own customer records, a regional manager should see only their region's data, a tenant in a multi-tenant SaaS should see only their organization's data. Row-Level Security (RLS) provides this capability by automatically filtering rows based on security policies. When a user queries a table, only rows matching their policy predicates are visible—and this filtering is transparent and cannot be bypassed. RLS Use Cases:

Common Row-Level Security Scenarios

•Multi-Tenant Applications — Each tenant sees only their own data, with complete isolation enforced at the database layer.
•Hierarchical Access — Managers see their direct reports' data plus their own; executives see entire department data.
•Geographic Restrictions — Users access only data for their assigned regions, countries, or territories.
•Time-Based Access — Historical data visible only to auditors; draft data visible only to authors.
•Regulatory Compliance — Enforce data residency requirements, GDPR 'right to erasure' visibility.

row_level_security.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
-- ================================================
-- PostgreSQL Row-Level Security Implementation
-- ================================================
 
-- Scenario: Multi-tenant SaaS application
-- Each row has tenant_id; users should only see their tenant's data
 
-- Step 1: Create table with tenant column
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    tenant_id INTEGER NOT NULL,
    customer_name TEXT,
    order_total NUMERIC(10,2),
    created_at TIMESTAMPTZ DEFAULT NOW()
);
 
-- Step 2: Enable RLS on the table
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
 
-- Step 3: Create security policy
-- current_setting() retrieves session variable set by application
 
CREATE POLICY tenant_isolation ON orders
    USING (tenant_id = current_setting('app.current_tenant')::INTEGER);
 
-- Alternatively, for role-based multi-tenancy:
CREATE POLICY tenant_isolation_v2 ON orders
    USING (tenant_id = (
        SELECT tenant_id 
        FROM user_tenants 
        WHERE user_name = current_user
    ));
 
-- Step 4: Force RLS for table owner too (important!)
ALTER TABLE orders FORCE ROW LEVEL SECURITY;
 
-- Step 5: Create separate policies for different operations
 
-- SELECT: Users see only their tenant's orders
CREATE POLICY orders_select ON orders
    FOR SELECT
    USING (tenant_id = current_setting('app.current_tenant')::INTEGER);
 
-- INSERT: Users can only create orders for their tenant
CREATE POLICY orders_insert ON orders
    FOR INSERT
    WITH CHECK (tenant_id = current_setting('app.current_tenant')::INTEGER);
 
-- UPDATE: Users can only update their tenant's orders
CREATE POLICY orders_update ON orders
    FOR UPDATE
    USING (tenant_id = current_setting('app.current_tenant')::INTEGER)
    WITH CHECK (tenant_id = current_setting('app.current_tenant')::INTEGER);
 
-- DELETE: Users can only delete their tenant's orders
CREATE POLICY orders_delete ON orders
    FOR DELETE
    USING (tenant_id = current_setting('app.current_tenant')::INTEGER);
 
-- Step 6: Application sets tenant context
-- (Done at connection establishment or transaction start)
SET app.current_tenant = '42';
 
-- Now all queries automatically filter to tenant 42
SELECT * FROM orders;  -- Only sees tenant 42's orders
INSERT INTO orders (tenant_id, customer_name, order_total) 
    VALUES (99, 'Test', 100.00);  -- FAILS: violates RLS policy
 
-- Step 7: View RLS policies
SELECT 
    schemaname,
    tablename,
    policyname,
    permissive,
    roles,
    cmd,
    qual,  -- USING clause
    with_check  -- WITH CHECK clause
FROM pg_policies
WHERE tablename = 'orders';

RLS Performance Considerations

RLS predicates are evaluated for every row in every query. Complex predicates involving subqueries or joins can severely impact performance. Always ensure predicate columns are indexed, use SCHEMABINDING (SQL Server) for optimizer benefits, and test with production-scale data. Consider materializing frequently-accessed predicate results.

Principle of Least Privilege

The Principle of Least Privilege (PoLP) is a foundational security concept: every user, application, and process should have only the minimum privileges necessary to perform its intended function—nothing more. In database contexts, this means: Application accounts should not have DBA privileges Developers should not have production data access Read operations should not require write privileges Schema modification should be restricted to deployment processes Why Least Privilege Matters:

Benefits

•Limits Breach Impact — Compromised accounts can only access minimal data
•Prevents Accidents — Users can't accidentally delete or modify critical data
•Simplifies Auditing — Clear privilege assignment makes access patterns traceable
•Supports Compliance — Many regulations (HIPAA, SOX, PCI-DSS) require least privilege
•Reduces Attack Surface — Fewer privileges mean fewer potential attack vectors

Implementation Challenges

•Initial Effort — Requires understanding all application access patterns
•Resistance — Developers prefer broad access for convenience
•Operational Overhead — More permission requests, more administration
•Privilege Creep — Permissions accumulate over time without review
•Legacy Systems — Hard to retrofit into existing applications

Implementing Least Privilege:

least_privilege.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
-- ================================================
-- Least Privilege Implementation Strategy
-- ================================================
 
-- ANTI-PATTERN: Overly permissive service account
-- DON'T DO THIS:
CREATE USER webapp WITH PASSWORD 'secret';
GRANT ALL PRIVILEGES ON DATABASE production TO webapp;  -- TERRIBLE!
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO webapp;  -- WORSE!
 
-- ================================================
-- CORRECT: Fine-grained privilege assignment
-- ================================================
 
-- Step 1: Start with NO privileges
CREATE USER webapp WITH PASSWORD 'secure_random_password';
-- User has no access to anything
 
-- Step 2: Grant only CONNECT (can't even connect without this)
GRANT CONNECT ON DATABASE app_db TO webapp;
 
-- Step 3: Grant USAGE on specific schemas (can't see schemas otherwise)
GRANT USAGE ON SCHEMA app TO webapp;
-- webapp can now see objects in 'app' schema, but can't access them
 
-- Step 4: Grant SELECT on tables needed for read operations
GRANT SELECT ON app.products TO webapp;
GRANT SELECT ON app.categories TO webapp;
GRANT SELECT ON app.inventory TO webapp;
 
-- Step 5: Grant INSERT/UPDATE only where needed
GRANT INSERT ON app.orders TO webapp;
GRANT INSERT ON app.order_items TO webapp;
GRANT UPDATE (status, shipped_date) ON app.orders TO webapp;
-- Note: UPDATE limited to specific columns!
 
-- Step 6: Grant sequence USAGE for inserts with auto-increment
GRANT USAGE ON SEQUENCE app.orders_id_seq TO webapp;
GRANT USAGE ON SEQUENCE app.order_items_id_seq TO webapp;
 
-- Step 7: Grant EXECUTE on specific functions
GRANT EXECUTE ON FUNCTION app.calculate_order_total(INTEGER) TO webapp;
 
-- What webapp CANNOT do:
-- ❌ Access other schemas
-- ❌ Create tables or modify schema
-- ❌ DELETE any rows
-- ❌ UPDATE columns like price, customer_id
-- ❌ Access audit tables
-- ❌ Access user credentials
 
-- ================================================
-- Review: What privileges does webapp actually have?
-- ================================================
 
SELECT 
    table_schema,
    table_name,
    privilege_type
FROM information_schema.table_privileges
WHERE grantee = 'webapp'
ORDER BY table_schema, table_name;

Regular Privilege Reviews

Privileges accumulate over time as new features are added but rarely removed. Implement quarterly privilege reviews: export current grants, compare against documented requirements, revoke unnecessary privileges. Automate with scripts that flag unusual privilege patterns (users with DBA access, service accounts with DELETE privileges, etc.).

Authorization Best Practices

Implementing robust database authorization requires discipline, documentation, and ongoing vigilance. These best practices synthesize decades of enterprise security experience:

Authorization Best Practices Checklist

•Use roles, not direct grants — Never GRANT privileges directly to users. Always create roles that represent job functions and assign users to roles. This simplifies administration and auditing.
•Implement role hierarchy thoughtfully — Create base roles (read-only, read-write) and compose them. Avoid circular dependencies and deep nesting that obscures effective privileges.
•Separate human and service accounts — Human users have different access patterns than applications. Create distinct roles for each; service accounts typically need minimal, fixed privileges.
•Prefer DENY for explicit exclusions — When certain data must never be accessible to a role regardless of other grants, use DENY. But use sparingly—too many DENYs create debugging nightmares.
•Grant on schemas, not tables — Grant privileges at the schema level with DEFAULT PRIVILEGES for future objects. Granting table-by-table creates maintenance burden and gaps.
•Separate read and write schemas — Reporting queries should run against read-only views or replicas. This prevents accidental data modification and improves accountability.
•Implement break-glass procedures — For emergencies requiring elevated access, have documented procedures: MFA, approval workflow, time-limited access, mandatory audit logging.
•Automate privilege provisioning — Use infrastructure-as-code for database privileges. Version-controlled privilege definitions enable review, rollback, and audit.

Common Authorization Mistakes and Corrections
Mistake	Risk	Correction
GRANT ALL PRIVILEGES	Excessive access, no accountability	Grant only specific required privileges
WITH GRANT OPTION	Uncontrolled privilege delegation	Restrict grant option to DBAs only
GRANT TO PUBLIC	Every user gets access, including future users	Grant to specific roles only
Schema owner = application	Application can modify schema, drop tables	Separate schema owner from runtime account
Shared service accounts	No individual accountability	One account per application instance
No privilege expiration	Access persists after need ends	Implement access reviews and automatic expiration

The 'Just Make It Work' Trap

When facing access issues, the temptation to 'just grant more privileges until it works' is strong. This creates cumulative security debt. Instead: diagnose the specific missing privilege, document why it's needed, and grant only that privilege. Short-term convenience creates long-term vulnerability.

Auditing Authorization

Effective authorization isn't just about setting up privileges—it's about continuously verifying that privileges remain appropriate and are being used as expected. Authorization auditing answers critical questions: Who currently has access to what? How did they get that access (grant chain)? Is this access still necessary? Are there anomalous access patterns?

authorization_audit.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
-- ================================================
-- PostgreSQL Authorization Audit Queries
-- ================================================
 
-- 1. All privileges granted on all tables
SELECT 
    grantee,
    table_schema,
    table_name,
    privilege_type,
    is_grantable
FROM information_schema.table_privileges
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY grantee, table_schema, table_name;
 
-- 2. Role memberships (who belongs to which roles)
WITH RECURSIVE role_hierarchy AS (
    -- Direct memberships
    SELECT 
        r.rolname AS role_name,
        m.rolname AS member_name,
        1 AS level,
        ARRAY[r.rolname] AS path
    FROM pg_roles r
    JOIN pg_auth_members am ON r.oid = am.roleid
    JOIN pg_roles m ON m.oid = am.member
    WHERE r.rolcanlogin = false  -- Only group roles
 
    UNION ALL
 
    -- Inherited memberships
    SELECT 
        rh.role_name,
        m.rolname,
        rh.level + 1,
        rh.path || m.rolname
    FROM role_hierarchy rh
    JOIN pg_auth_members am ON am.roleid = (
        SELECT oid FROM pg_roles WHERE rolname = rh.member_name
    )
    JOIN pg_roles m ON m.oid = am.member
    WHERE NOT m.rolname = ANY(rh.path)  -- Prevent cycles
)
SELECT * FROM role_hierarchy ORDER BY role_name, level;
 
-- 3. Users with superuser privileges (high-risk)
SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolbypassrls
FROM pg_roles
WHERE rolsuper OR rolcreaterole OR rolcreatedb OR rolbypassrls;
 
-- 4. Tables accessible via PUBLIC role
SELECT 
    table_schema,
    table_name,
    privilege_type
FROM information_schema.table_privileges
WHERE grantee = 'PUBLIC'
  AND table_schema NOT IN ('pg_catalog', 'information_schema');
 
-- 5. Grant chains (who granted what to whom)
SELECT 
    grantor,
    grantee,
    table_schema || '.' || table_name AS object,
    privilege_type,
    is_grantable
FROM information_schema.table_privileges
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY grantor, grantee;
 
-- 6. Unused roles (no members, no privileges)
SELECT r.rolname
FROM pg_roles r
WHERE NOT r.rolsuper
  AND NOT r.rolcanlogin
  AND NOT EXISTS (
      SELECT 1 FROM pg_auth_members am WHERE am.roleid = r.oid
  );

Automate Privilege Auditing

Schedule daily exports of privilege configurations and compare against baselines. Flag any new grants, role membership changes, or privilege escalations. Integrate with SIEM systems for real-time alerting on suspicious authorization changes.

Database Management SystemsDatabase Security

Database Security

LevelAdvanced

Duration90 mins

TopicDatabase Security

2 / 5

Authorization

From Identity to Permission

Authentication answers the question'Who are you?' But knowing someone's identity tells us nothing about what they should be allowed to do. Authorization is the security mechanism that bridges this gap—it's the system that transforms identity into permission. In enterprise database environments, authorization is deceptively complex. A single database might serve hundreds of applications, thousands of users, and millions of rows of data—each with different sensitivity levels, regulatory requirements, and access patterns. The authorization system must efficiently evaluate permissions for every single query, often making thousands of decisions per second. This isn't just about security—poorly designed authorization creates operational nightmares: support tickets for access requests, audit failures, compliance violations, and the constant pressure to grant 'just a little more access' until everyone has administrative privileges.

What You Will Learn

By the end of this page, you will understand the fundamental models of database authorization (DAC, MAC, RBAC), master SQL GRANT/REVOKE semantics across major database platforms, implement role-based access control architectures, leverage row-level security for fine-grained data protection, and apply the principle of least privilege systematically.

Authorization Fundamentals

Authorization determines what actions an authenticated entity (user, role, or service) may perform on database objects. Unlike authentication, which is typically a one-time verification at connection establishment, authorization decisions occur continuously throughout a session—every SELECT, INSERT, UPDATE, DELETE, and DDL statement is validated against the authorization system. Core Authorization Concepts:

Key Authorization Terminology

•Subject — The entity requesting access (user, application, service account, role).
•Object — The resource being accessed (database, schema, table, column, row, function, sequence).
•Privilege — The specific action permitted (SELECT, INSERT, UPDATE, DELETE, EXECUTE, REFERENCES, USAGE).
•Grant — The act of conferring a privilege from one subject to another.
•Revoke — The act of removing a previously granted privilege.
•Access Control List (ACL) — A data structure mapping subjects to their privileges on an object.

Authorization Models: Database systems implement various authorization models, each with distinct characteristics:

Database Authorization Models
Model	Description	Control	Use Case
Discretionary Access Control (DAC)	Object owners grant permissions at their discretion	Decentralized, owner-controlled	Most SQL databases (default)
Mandatory Access Control (MAC)	System-enforced labels and clearance levels	Centralized, policy-driven	Government, military systems (Oracle Label Security)
Role-Based Access Control (RBAC)	Permissions assigned to roles, roles assigned to users	Centralized, scalable	Enterprise applications (standard practice)
Attribute-Based Access Control (ABAC)	Dynamic decisions based on attributes (time, location, etc.)	Fine-grained, context-aware	Cloud platforms, modern IAM

Discretionary Access Control (DAC) in SQL: The SQL standard defines a DAC model where object owners control access to their objects. When a user creates a table, they become its owner and can grant privileges to others. This model is implemented by all major relational databases: Object owners have full control over their objects Privileges can be granted to specific users or to PUBLIC (all users) Grants can include WITH GRANT OPTION, allowing recipients to grant the same privilege to others The privilege chain can be several levels deep, creating administrative complexity

The DAC Weakness

Pure DAC systems suffer from the 'Trojan Horse' problem. If malicious code executes with an authorized user's privileges, it can access or copy all data that user can access, then grant access to an attacker's account. This is why enterprise environments layer RBAC and MAC on top of DAC.

The SQL Privilege System

SQL defines a comprehensive privilege system through the GRANT and REVOKE statements. Understanding these statements deeply is essential for database security administration. Privilege Types: SQL defines privileges at multiple granularity levels:

SQL Privilege Types by Object Level
Level	Privileges	Description
Database	CREATE, CONNECT, TEMPORARY	Control database-wide operations
Schema	USAGE, CREATE, ALTER, DROP	Control schema access and modification
Table	SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER	Control table data operations
Column	SELECT, INSERT, UPDATE, REFERENCES	Fine-grained column access
Sequence	USAGE, SELECT, UPDATE	Control sequence operations
Function/Procedure	EXECUTE	Control function execution
Type	USAGE	Control use of custom types

grant_revoke_basics.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
-- ================================================
-- GRANT Statement Fundamentals
-- ================================================
 
-- Basic table privilege grant
GRANT SELECT ON employees TO hr_analyst;
 
-- Grant multiple privileges
GRANT SELECT, INSERT, UPDATE ON employees TO hr_manager;
 
-- Grant all privileges (not recommended - violates least privilege)
GRANT ALL PRIVILEGES ON employees TO hr_admin;
 
-- Grant with ability to re-grant (dangerous - creates privilege chains)
GRANT SELECT ON employees TO senior_hr WITH GRANT OPTION;
 
-- Grant to all users (PUBLIC pseudo-role)
GRANT SELECT ON public_announcements TO PUBLIC;
 
-- Column-level grants (fine-grained access)
GRANT SELECT (employee_id, name, department) ON employees TO auditor;
-- auditor cannot see salary, ssn, or other sensitive columns
 
-- Grant on all tables in schema
GRANT SELECT ON ALL TABLES IN SCHEMA reporting TO report_reader;
 
-- Grant on future tables (PostgreSQL)
ALTER DEFAULT PRIVILEGES IN SCHEMA reporting
    GRANT SELECT ON TABLES TO report_reader;
 
-- ================================================
-- REVOKE Statement Fundamentals
-- ================================================
 
-- Basic revoke
REVOKE INSERT ON employees FROM hr_analyst;
 
-- Revoke all privileges
REVOKE ALL PRIVILEGES ON employees FROM former_employee;
 
-- Revoke grant option only (user keeps the privilege)
REVOKE GRANT OPTION FOR SELECT ON employees FROM senior_hr;
 
-- Cascade revoke (revoke from grantees of this grantee)
REVOKE SELECT ON employees FROM senior_hr CASCADE;
 
-- Restrict revoke (fail if others depend on this grant)
REVOKE SELECT ON employees FROM senior_hr RESTRICT;
 
-- ================================================
-- Viewing Current Privileges
-- ================================================
 
-- PostgreSQL: View table privileges
SELECT 
    grantee,
    table_schema,
    table_name,
    privilege_type,
    is_grantable
FROM information_schema.table_privileges
WHERE table_name = 'employees';
 
-- PostgreSQL: View role memberships
SELECT 
    r.rolname AS role,
    m.rolname AS member,
    g.rolname AS granted_by
FROM pg_auth_members am
JOIN pg_roles r ON r.oid = am.roleid
JOIN pg_roles m ON m.oid = am.member
JOIN pg_roles g ON g.oid = am.grantor;

DENY vs REVOKE

REVOKE removes a previously granted permission (returns to default state). DENY explicitly prohibits access, overriding any GRANT. In SQL Server, DENY takes precedence—if a user has both GRANT and DENY, the DENY wins. Use DENY sparingly for explicit exclusions; prefer REVOKE for routine permission removal.

Role-Based Access Control (RBAC)

Role-Based Access Control is the cornerstone of scalable database authorization. Instead of granting privileges directly to users, permissions are granted to roles, and roles are assigned to users. This indirection provides enormous administrative and security benefits. Why RBAC? Consider an organization with 500 employees accessing a database with 200 tables. Without roles: Each user-table permission pair is a separate grant 500 \times 200 = 100,000 individual grants to manage Employee onboarding requires dozens of individual grants Employee offboarding requires hunting down all grants Auditing who has access to what is nearly impossible With RBAC: Define 10-20 roles matching job functions Grant table permissions to roles (~2,000 grants) Assign users to roles (500 assignments) Onboarding = assign appropriate role(s) Offboarding = remove role assignment(s)

Converting Mermaid diagram...

rbac_implementation.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
-- ================================================
-- Complete RBAC Implementation in PostgreSQL
-- ================================================
 
-- Step 1: Create functional roles (no login capability)
CREATE ROLE hr_read NOLOGIN;
CREATE ROLE hr_write NOLOGIN;
CREATE ROLE finance_read NOLOGIN;
CREATE ROLE finance_write NOLOGIN;
CREATE ROLE audit_read NOLOGIN;
 
-- Step 2: Establish role hierarchy
 
-- hr_write inherits from hr_read (writers can also read)
GRANT hr_read TO hr_write;
 
-- finance_write inherits from finance_read
GRANT finance_read TO finance_write;
 
-- auditors get read access to everything
GRANT hr_read TO audit_read;
GRANT finance_read TO audit_read;
 
-- Step 3: Grant object privileges to roles
GRANT USAGE ON SCHEMA hr TO hr_read, hr_write;
GRANT SELECT ON ALL TABLES IN SCHEMA hr TO hr_read;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA hr TO hr_write;
 
GRANT USAGE ON SCHEMA finance TO finance_read, finance_write;
GRANT SELECT ON ALL TABLES IN SCHEMA finance TO finance_read;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA finance TO finance_write;
 
-- Auditors need read on audit log
GRANT USAGE ON SCHEMA audit TO audit_read;
GRANT SELECT ON audit.audit_log TO audit_read;
 
-- Step 4: Handle future objects (critical for maintainability!)
ALTER DEFAULT PRIVILEGES IN SCHEMA hr
    GRANT SELECT ON TABLES TO hr_read;
ALTER DEFAULT PRIVILEGES IN SCHEMA hr
    GRANT INSERT, UPDATE, DELETE ON TABLES TO hr_write;
 
-- Step 5: Create login roles (actual users)
CREATE ROLE alice LOGIN PASSWORD 'secure_password_1';
CREATE ROLE bob LOGIN PASSWORD 'secure_password_2';
CREATE ROLE carol LOGIN PASSWORD 'secure_password_3';
CREATE ROLE david LOGIN PASSWORD 'secure_password_4';
 
-- Step 6: Assign roles to users
GRANT hr_write TO alice;        -- HR Manager: read + write
GRANT hr_read TO bob;           -- HR Analyst: read only
GRANT finance_write TO carol;   -- Finance Manager: read + write
GRANT audit_read TO david;      -- Auditor: read everything
 
-- Step 7: Set role activation (optional - require explicit SET ROLE)
ALTER ROLE alice SET role = 'hr_write';  -- Default active role
 
-- View role memberships
SELECT 
    r.rolname AS role,
    ARRAY_AGG(m.rolname) AS members
FROM pg_roles r
JOIN pg_auth_members am ON r.oid = am.roleid
JOIN pg_roles m ON m.oid = am.member
GROUP BY r.rolname;

Role Design Principles

Design roles around job functions, not individuals or departments. Create granular roles (reader, writer, admin) and compose them as needed. Avoid creating roles like 'john_smith_access' or 'temporary_project_role'. Good role names are self-documenting: 'order_processor_read', 'inventory_update', 'reporting_analyst'.

Row-Level Security (RLS)

Traditional SQL privileges operate at the object level—you either have access to a table or you don't. But many applications require finer granularity: a sales representative should see only their own customer records, a regional manager should see only their region's data, a tenant in a multi-tenant SaaS should see only their organization's data. Row-Level Security (RLS) provides this capability by automatically filtering rows based on security policies. When a user queries a table, only rows matching their policy predicates are visible—and this filtering is transparent and cannot be bypassed. RLS Use Cases:

Common Row-Level Security Scenarios

•Multi-Tenant Applications — Each tenant sees only their own data, with complete isolation enforced at the database layer.
•Hierarchical Access — Managers see their direct reports' data plus their own; executives see entire department data.
•Geographic Restrictions — Users access only data for their assigned regions, countries, or territories.
•Time-Based Access — Historical data visible only to auditors; draft data visible only to authors.
•Regulatory Compliance — Enforce data residency requirements, GDPR 'right to erasure' visibility.

row_level_security.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
-- ================================================
-- PostgreSQL Row-Level Security Implementation
-- ================================================
 
-- Scenario: Multi-tenant SaaS application
-- Each row has tenant_id; users should only see their tenant's data
 
-- Step 1: Create table with tenant column
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    tenant_id INTEGER NOT NULL,
    customer_name TEXT,
    order_total NUMERIC(10,2),
    created_at TIMESTAMPTZ DEFAULT NOW()
);
 
-- Step 2: Enable RLS on the table
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
 
-- Step 3: Create security policy
-- current_setting() retrieves session variable set by application
 
CREATE POLICY tenant_isolation ON orders
    USING (tenant_id = current_setting('app.current_tenant')::INTEGER);
 
-- Alternatively, for role-based multi-tenancy:
CREATE POLICY tenant_isolation_v2 ON orders
    USING (tenant_id = (
        SELECT tenant_id 
        FROM user_tenants 
        WHERE user_name = current_user
    ));
 
-- Step 4: Force RLS for table owner too (important!)
ALTER TABLE orders FORCE ROW LEVEL SECURITY;
 
-- Step 5: Create separate policies for different operations
 
-- SELECT: Users see only their tenant's orders
CREATE POLICY orders_select ON orders
    FOR SELECT
    USING (tenant_id = current_setting('app.current_tenant')::INTEGER);
 
-- INSERT: Users can only create orders for their tenant
CREATE POLICY orders_insert ON orders
    FOR INSERT
    WITH CHECK (tenant_id = current_setting('app.current_tenant')::INTEGER);
 
-- UPDATE: Users can only update their tenant's orders
CREATE POLICY orders_update ON orders
    FOR UPDATE
    USING (tenant_id = current_setting('app.current_tenant')::INTEGER)
    WITH CHECK (tenant_id = current_setting('app.current_tenant')::INTEGER);
 
-- DELETE: Users can only delete their tenant's orders
CREATE POLICY orders_delete ON orders
    FOR DELETE
    USING (tenant_id = current_setting('app.current_tenant')::INTEGER);
 
-- Step 6: Application sets tenant context
-- (Done at connection establishment or transaction start)
SET app.current_tenant = '42';
 
-- Now all queries automatically filter to tenant 42
SELECT * FROM orders;  -- Only sees tenant 42's orders
INSERT INTO orders (tenant_id, customer_name, order_total) 
    VALUES (99, 'Test', 100.00);  -- FAILS: violates RLS policy
 
-- Step 7: View RLS policies
SELECT 
    schemaname,
    tablename,
    policyname,
    permissive,
    roles,
    cmd,
    qual,  -- USING clause
    with_check  -- WITH CHECK clause
FROM pg_policies
WHERE tablename = 'orders';

RLS Performance Considerations

RLS predicates are evaluated for every row in every query. Complex predicates involving subqueries or joins can severely impact performance. Always ensure predicate columns are indexed, use SCHEMABINDING (SQL Server) for optimizer benefits, and test with production-scale data. Consider materializing frequently-accessed predicate results.

Principle of Least Privilege

The Principle of Least Privilege (PoLP) is a foundational security concept: every user, application, and process should have only the minimum privileges necessary to perform its intended function—nothing more. In database contexts, this means: Application accounts should not have DBA privileges Developers should not have production data access Read operations should not require write privileges Schema modification should be restricted to deployment processes Why Least Privilege Matters:

Benefits

•Limits Breach Impact — Compromised accounts can only access minimal data
•Prevents Accidents — Users can't accidentally delete or modify critical data
•Simplifies Auditing — Clear privilege assignment makes access patterns traceable
•Supports Compliance — Many regulations (HIPAA, SOX, PCI-DSS) require least privilege
•Reduces Attack Surface — Fewer privileges mean fewer potential attack vectors

Implementation Challenges

•Initial Effort — Requires understanding all application access patterns
•Resistance — Developers prefer broad access for convenience
•Operational Overhead — More permission requests, more administration
•Privilege Creep — Permissions accumulate over time without review
•Legacy Systems — Hard to retrofit into existing applications

Implementing Least Privilege:

least_privilege.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
-- ================================================
-- Least Privilege Implementation Strategy
-- ================================================
 
-- ANTI-PATTERN: Overly permissive service account
-- DON'T DO THIS:
CREATE USER webapp WITH PASSWORD 'secret';
GRANT ALL PRIVILEGES ON DATABASE production TO webapp;  -- TERRIBLE!
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO webapp;  -- WORSE!
 
-- ================================================
-- CORRECT: Fine-grained privilege assignment
-- ================================================
 
-- Step 1: Start with NO privileges
CREATE USER webapp WITH PASSWORD 'secure_random_password';
-- User has no access to anything
 
-- Step 2: Grant only CONNECT (can't even connect without this)
GRANT CONNECT ON DATABASE app_db TO webapp;
 
-- Step 3: Grant USAGE on specific schemas (can't see schemas otherwise)
GRANT USAGE ON SCHEMA app TO webapp;
-- webapp can now see objects in 'app' schema, but can't access them
 
-- Step 4: Grant SELECT on tables needed for read operations
GRANT SELECT ON app.products TO webapp;
GRANT SELECT ON app.categories TO webapp;
GRANT SELECT ON app.inventory TO webapp;
 
-- Step 5: Grant INSERT/UPDATE only where needed
GRANT INSERT ON app.orders TO webapp;
GRANT INSERT ON app.order_items TO webapp;
GRANT UPDATE (status, shipped_date) ON app.orders TO webapp;
-- Note: UPDATE limited to specific columns!
 
-- Step 6: Grant sequence USAGE for inserts with auto-increment
GRANT USAGE ON SEQUENCE app.orders_id_seq TO webapp;
GRANT USAGE ON SEQUENCE app.order_items_id_seq TO webapp;
 
-- Step 7: Grant EXECUTE on specific functions
GRANT EXECUTE ON FUNCTION app.calculate_order_total(INTEGER) TO webapp;
 
-- What webapp CANNOT do:
-- ❌ Access other schemas
-- ❌ Create tables or modify schema
-- ❌ DELETE any rows
-- ❌ UPDATE columns like price, customer_id
-- ❌ Access audit tables
-- ❌ Access user credentials
 
-- ================================================
-- Review: What privileges does webapp actually have?
-- ================================================
 
SELECT 
    table_schema,
    table_name,
    privilege_type
FROM information_schema.table_privileges
WHERE grantee = 'webapp'
ORDER BY table_schema, table_name;

Regular Privilege Reviews

Privileges accumulate over time as new features are added but rarely removed. Implement quarterly privilege reviews: export current grants, compare against documented requirements, revoke unnecessary privileges. Automate with scripts that flag unusual privilege patterns (users with DBA access, service accounts with DELETE privileges, etc.).

Authorization Best Practices

Implementing robust database authorization requires discipline, documentation, and ongoing vigilance. These best practices synthesize decades of enterprise security experience:

Authorization Best Practices Checklist

•Use roles, not direct grants — Never GRANT privileges directly to users. Always create roles that represent job functions and assign users to roles. This simplifies administration and auditing.
•Implement role hierarchy thoughtfully — Create base roles (read-only, read-write) and compose them. Avoid circular dependencies and deep nesting that obscures effective privileges.
•Separate human and service accounts — Human users have different access patterns than applications. Create distinct roles for each; service accounts typically need minimal, fixed privileges.
•Prefer DENY for explicit exclusions — When certain data must never be accessible to a role regardless of other grants, use DENY. But use sparingly—too many DENYs create debugging nightmares.
•Grant on schemas, not tables — Grant privileges at the schema level with DEFAULT PRIVILEGES for future objects. Granting table-by-table creates maintenance burden and gaps.
•Separate read and write schemas — Reporting queries should run against read-only views or replicas. This prevents accidental data modification and improves accountability.
•Implement break-glass procedures — For emergencies requiring elevated access, have documented procedures: MFA, approval workflow, time-limited access, mandatory audit logging.
•Automate privilege provisioning — Use infrastructure-as-code for database privileges. Version-controlled privilege definitions enable review, rollback, and audit.

Common Authorization Mistakes and Corrections
Mistake	Risk	Correction
GRANT ALL PRIVILEGES	Excessive access, no accountability	Grant only specific required privileges
WITH GRANT OPTION	Uncontrolled privilege delegation	Restrict grant option to DBAs only
GRANT TO PUBLIC	Every user gets access, including future users	Grant to specific roles only
Schema owner = application	Application can modify schema, drop tables	Separate schema owner from runtime account
Shared service accounts	No individual accountability	One account per application instance
No privilege expiration	Access persists after need ends	Implement access reviews and automatic expiration

The 'Just Make It Work' Trap

When facing access issues, the temptation to 'just grant more privileges until it works' is strong. This creates cumulative security debt. Instead: diagnose the specific missing privilege, document why it's needed, and grant only that privilege. Short-term convenience creates long-term vulnerability.

Auditing Authorization

Effective authorization isn't just about setting up privileges—it's about continuously verifying that privileges remain appropriate and are being used as expected. Authorization auditing answers critical questions: Who currently has access to what? How did they get that access (grant chain)? Is this access still necessary? Are there anomalous access patterns?

authorization_audit.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
-- ================================================
-- PostgreSQL Authorization Audit Queries
-- ================================================
 
-- 1. All privileges granted on all tables
SELECT 
    grantee,
    table_schema,
    table_name,
    privilege_type,
    is_grantable
FROM information_schema.table_privileges
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY grantee, table_schema, table_name;
 
-- 2. Role memberships (who belongs to which roles)
WITH RECURSIVE role_hierarchy AS (
    -- Direct memberships
    SELECT 
        r.rolname AS role_name,
        m.rolname AS member_name,
        1 AS level,
        ARRAY[r.rolname] AS path
    FROM pg_roles r
    JOIN pg_auth_members am ON r.oid = am.roleid
    JOIN pg_roles m ON m.oid = am.member
    WHERE r.rolcanlogin = false  -- Only group roles
 
    UNION ALL
 
    -- Inherited memberships
    SELECT 
        rh.role_name,
        m.rolname,
        rh.level + 1,
        rh.path || m.rolname
    FROM role_hierarchy rh
    JOIN pg_auth_members am ON am.roleid = (
        SELECT oid FROM pg_roles WHERE rolname = rh.member_name
    )
    JOIN pg_roles m ON m.oid = am.member
    WHERE NOT m.rolname = ANY(rh.path)  -- Prevent cycles
)
SELECT * FROM role_hierarchy ORDER BY role_name, level;
 
-- 3. Users with superuser privileges (high-risk)
SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolbypassrls
FROM pg_roles
WHERE rolsuper OR rolcreaterole OR rolcreatedb OR rolbypassrls;
 
-- 4. Tables accessible via PUBLIC role
SELECT 
    table_schema,
    table_name,
    privilege_type
FROM information_schema.table_privileges
WHERE grantee = 'PUBLIC'
  AND table_schema NOT IN ('pg_catalog', 'information_schema');
 
-- 5. Grant chains (who granted what to whom)
SELECT 
    grantor,
    grantee,
    table_schema || '.' || table_name AS object,
    privilege_type,
    is_grantable
FROM information_schema.table_privileges
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY grantor, grantee;
 
-- 6. Unused roles (no members, no privileges)
SELECT r.rolname
FROM pg_roles r
WHERE NOT r.rolsuper
  AND NOT r.rolcanlogin
  AND NOT EXISTS (
      SELECT 1 FROM pg_auth_members am WHERE am.roleid = r.oid
  );

Automate Privilege Auditing

Schedule daily exports of privilege configurations and compare against baselines. Flag any new grants, role membership changes, or privilege escalations. Integrate with SIEM systems for real-time alerting on suspicious authorization changes.

Authorization

From Identity to Permission

Authorization Fundamentals

The SQL Privilege System

Role-Based Access Control (RBAC)

Row-Level Security (RLS)

Principle of Least Privilege

Authorization Best Practices

Auditing Authorization

There’s more to understand.

Authorization

From Identity to Permission

Authorization Fundamentals

The SQL Privilege System

Role-Based Access Control (RBAC)

Row-Level Security (RLS)

Principle of Least Privilege

Authorization Best Practices

Auditing Authorization

There’s more to understand.