PostgreSQL RLS Policy Permission Auditor

Threat/Impact Level: HIGH | Exploitability/Downtime Risk: HIGH | Time to Fix: 10–30 mins

TL;DR

• What broke: ROW LEVEL SECURITY is enabled on the table but the querying role is missing an explicit GRANT and/or a matching POLICY — Postgres denies all rows by default when RLS is active with no applicable policy.
• How to fix it: Grant SELECT/INSERT/UPDATE/DELETE to the role AND create a CREATE POLICY that matches the role's intended access predicate.
• Shortcut: Use our Client-Side Sandbox above to auto-refactor this — paste your DDL + policy block and get corrected SQL without sending secrets to a third-party server.

ERROR:  permission denied for table sensitive_data
CONTEXT:  SQL function "get_user_records" during inlining

How to Fix It (The Solution)

Basic Fix

Verify the current state first:

-- Check RLS status
SELECT relname, relrowsecurity, relforcerowsecurity
FROM pg_class WHERE relname = 'sensitive_data';

-- Check existing policies
SELECT * FROM pg_policies WHERE tablename = 'sensitive_data';

-- Check grants
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name = 'sensitive_data';

The fix — grant + policy must both exist:

- -- Missing: role has no GRANT and no POLICY
- ALTER TABLE sensitive_data ENABLE ROW LEVEL SECURITY;

+ -- Step 1: Grant object-level privilege
+ GRANT SELECT, INSERT, UPDATE, DELETE
+   ON TABLE sensitive_data
+   TO app_role;
+
+ -- Step 2: Enable RLS
+ ALTER TABLE sensitive_data ENABLE ROW LEVEL SECURITY;
+
+ -- Step 3: Create the policy (tenant-scoped example)
+ CREATE POLICY tenant_isolation_policy
+   ON sensitive_data
+   FOR ALL
+   TO app_role
+   USING (tenant_id = current_setting('app.current_tenant_id')::uuid)
+   WITH CHECK (tenant_id = current_setting('app.current_tenant_id')::uuid);

Enterprise Best Practice — Least-Privilege Role Hierarchy

- -- Anti-pattern: single superuser app credential, BYPASSRLS granted
- GRANT ALL ON ALL TABLES IN SCHEMA public TO app_user;
- ALTER ROLE app_user BYPASSRLS;

+ -- Separate roles by function
+ CREATE ROLE app_readonly NOLOGIN;
+ CREATE ROLE app_readwrite NOLOGIN;
+
+ -- Scoped grants only
+ GRANT SELECT ON TABLE sensitive_data TO app_readonly;
+ GRANT SELECT, INSERT, UPDATE ON TABLE sensitive_data TO app_readwrite;
+
+ -- Enforce RLS — never grant BYPASSRLS to app roles
+ ALTER TABLE sensitive_data ENABLE ROW LEVEL SECURITY;
+ ALTER TABLE sensitive_data FORCE ROW LEVEL SECURITY; -- blocks table owner too
+
+ -- Per-role policies
+ CREATE POLICY read_own_tenant ON sensitive_data
+   FOR SELECT TO app_readonly
+   USING (tenant_id = current_setting('app.current_tenant_id')::uuid);
+
+ CREATE POLICY write_own_tenant ON sensitive_data
+   FOR INSERT TO app_readwrite
+   WITH CHECK (tenant_id = current_setting('app.current_tenant_id')::uuid);
+
+ -- SECURITY INVOKER on functions to preserve RLS context
+ CREATE OR REPLACE FUNCTION get_user_records()
+   RETURNS SETOF sensitive_data
+   LANGUAGE sql
+   SECURITY INVOKER  -- NOT SECURITY DEFINER
+ AS $$
+   SELECT * FROM sensitive_data;
+ $$;

Critical: FORCE ROW LEVEL SECURITY on the table ensures even the table owner (typically your migration role) cannot bypass policies. Without this, a compromised migration pipeline reads all rows.

💡 Tired of pasting proprietary infrastructure configs into public LLMs? Generic AI interfaces log your company's ARNs, connection strings, and private keys. StackEngine enforces client-side zero-trust boundaries—the sandbox above executes 100% locally in your browser. Drop your broken manifest above to redact secrets locally and synthesize deterministic patch drafts with your own API key.

(For CI/CD pipeline automation, install via npm i -g stackengine).

-- Run this as a CI gate query; non-zero result = pipeline failure
SELECT relname
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relrowsecurity = true
  AND n.nspname = 'public'
  AND NOT EXISTS (
    SELECT 1 FROM pg_policy p WHERE p.polrelid = c.oid
  );

checkov -d ./terraform --check CKV_AWS_17,CKV_AWS_129

# deny.rego — block migrations that grant BYPASSRLS
deny[msg] {
  input.statement.type == "AlterRoleStmt"
  input.statement.options[_].defname == "bypassrls"
  input.statement.options[_].arg == true
  msg := sprintf("BYPASSRLS granted to role '%v' — violates least-privilege policy", [input.statement.role])
}

# postgresql.conf
shared_preload_libraries = 'pgaudit'
pgaudit.log = 'ddl, role'
pgaudit.log_catalog = on