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.

The Incident (What does the error mean?)

Raw error output:

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

PostgreSQL enforces two independent access control layers: object-level privileges (GRANT) and row-level security policies (RLS). When ALTER TABLE sensitive_data ENABLE ROW LEVEL SECURITY is set, even a role with a valid GRANT sees zero rows — or gets a hard permission denied — if no POLICY applies to it. The two layers do not substitute for each other. Missing either one is a full block.

Immediate consequence: Application queries return errors or empty result sets. If your app treats empty-set as "no data" rather than "access denied," silent data loss bugs emerge in production.

The Attack Vector / Blast Radius

This misconfiguration cuts both ways:

Under-privileged (your current error): Legitimate app roles are locked out. Cascading failures occur if the blocked role is a connection-pooler user (PgBouncer, RDS Proxy) — every tenant's query fails simultaneously.

Over-privileged (the opposite, equally dangerous): If you "fix" this by granting BYPASSRLS to the app role or running queries as a superuser, you've completely nullified RLS. An attacker who compromises that app credential now reads every row across all tenants — a full horizontal privilege escalation in a multi-tenant schema.

Specific blast radius scenarios:

• SECURITY DEFINER functions silently execute under the definer's role, bypassing the caller's RLS context — a common hidden exploit path.
• pg_dump run as a non-superuser with BYPASSRLS exports all tenant data.
• Misconfigured FOR ALL policies with no USING clause default to USING (false) — denying everything silently.

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 configs into ChatGPT? Generic AI tools log your company's ARNs, DB strings, and private keys. StackEngine is a zero-backend, pure browser-native utility. Drop your failing config into the sandbox above. We redact your secrets locally in the browser and auto-generate the refactored code using your own API key.

Prevention in CI/CD

1. Enforce RLS policy existence in migrations (Flyway/Liquibase)

Add a post-migration validation step that fails the pipeline if any RLS-enabled table has zero policies:

-- 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
  );

2. Checkov / KICS static analysis on Terraform

If provisioning RDS or Aurora via Terraform, use Checkov to flag aws_db_instance resources missing parameter groups that enforce SSL + audit logging:

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

3. OPA/Conftest policy for SQL migration files

# 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])
}

4. pgaudit logging in production

Enable pgaudit to log all GRANT and CREATE POLICY DDL changes:

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

Route pgaudit output to your SIEM (Datadog, Splunk, CloudWatch Logs Insights) and alert on BYPASSRLS or SUPERUSER grants outside your approved change window.