Fix PostgreSQL permission denied for schema public or table
Diagnose and repair PostgreSQL permission errors without granting broad roles or changing object ownership. This guide separates database connection, schema USAGE and CREATE, table privileges, sequence access, role inheritance, default privileges, and row-level security so an application receives only the rights it actually needs.
Restore the intended application operation for one PostgreSQL role, prove the effective privileges with a controlled test, preserve least privilege for existing and future objects, and retain an exact record that can be reversed.
- PostgreSQL 14, 15, 16, 17, 18
- psql 14+
- Database owner or delegated grant authority Use the role that owns the affected schema and objects, or a tightly controlled administrator that can SET ROLE to that owner. A normal application login usually cannot repair its own grants.
psql -X -d {{databaseName}} -c "SELECT current_user, session_user, current_database();" - Exact failing identity and statement Record the database, login role, effective role after SET ROLE, schema-qualified object, operation, and exact error. Do not troubleshoot with a superuser connection because it bypasses object privilege checks.
psql -X -d {{databaseName}} -U {{appRole}} -c "SELECT current_user, session_user, current_schemas(true);" - Change record and rollback owner Capture current ACLs and role membership before changing anything. Name who will verify the application and who can revoke the new grants if the result is broader than intended.
OneLiners never runs these steps or stores secrets. Review placeholders, versions, current state, and change-control requirements before using a command.
Full guide
What you will build
- A layered PostgreSQL permission diagnosis that identifies the exact failing identity, database, schema, relation, sequence, role membership, and row-security decision.
- A least-privilege repair for existing and future objects, with positive and negative tests plus exact rollback commands.
- The affected application role can perform only its approved operations on the intended schema objects.
- Schema CREATE, ownership, superuser-like predefined roles, and unrelated table privileges remain absent unless separately approved.
- Future objects created by the deployment owner inherit the reviewed access contract, preventing the incident from recurring after migrations.
Architecture
How the parts fit together
PostgreSQL authorization is a chain rather than one switch. A session connects to one database as a login, may assume an effective role, resolves an object through search_path and schema USAGE, checks the operation against the object's ACL, may access a sequence or function, and finally evaluates row-level security. The repair follows that same order and changes only the failed layer.
- Application opens the target database and establishes session_user/current_user.
- PostgreSQL resolves schema-qualified or search-path object names and checks schema USAGE.
- PostgreSQL checks the requested SELECT, INSERT, UPDATE, DELETE, function, or sequence privilege.
- Triggers, defaults, views, functions, partitions, and sequences may introduce additional object checks.
- If RLS is enabled, applicable USING and WITH CHECK policies constrain rows.
- The operator verifies expected success, expected denial, future-object behavior, and rollback evidence.
Assumptions
- The operator knows the exact database and can reproduce the error as the application's effective role without using a superuser.
- Application schema objects are owned by a stable owner or migration role rather than the runtime login.
- The intended data boundary is documented, including whether row-level security or tenant filters apply.
- The environment uses a supported PostgreSQL release; commands are written for PostgreSQL 14 through 18 and should be checked against the installed major version.
- No command in this guide is executed automatically by OneLiners, and secrets are never stored in placeholders or evidence.
Key concepts
- session_user
- The role that authenticated the connection. It remains the login identity even if SET ROLE changes current_user.
- current_user
- The effective role used for most privilege checks and object ownership during the current command.
- Schema USAGE
- Permission to resolve and access objects within a schema when the object itself also grants the required operation.
- Schema CREATE
- Permission to create new objects in a schema. It is separate from normal read/write access and is a search-path trust decision.
- Object owner
- The role with inherent control over an object, including alteration and drop; ownership is not a normal grantable privilege.
- Default privileges
- ACL rules applied only to future objects created by a specific current role, optionally within a schema.
- Row-level security
- Policies that add row predicates after ordinary object privileges have allowed the SQL operation.
Fill these once. Every matching command and configuration block updates immediately; values stay in this page only.
Security and production boundaries
- Never solve an object permission incident by granting SUPERUSER, ownership, BYPASSRLS, pg_read_all_data, pg_write_all_data, or ALL PRIVILEGES without a separate reviewed requirement.
- A schema on search_path is trusted. Do not let untrusted runtime roles create objects there, because functions, operators, and relations can influence name resolution.
- Use stable no-login group roles for access policy and short-lived or service logins for authentication. Review membership options and SET ROLE behavior.
- RLS and ACLs protect different layers. A correct table GRANT must not be followed by disabling row security just to make data visible.
- Default privileges are determined by the actual object creator. A rule for the wrong role is silent technical debt and does not protect future deployments.
Stop before continuing if
- The effective application role, target database, or schema-qualified object cannot be proven.
- The proposed grant would cross application or tenant boundaries, or the role would gain CREATE, ownership, grant option, TRUNCATE, or RLS bypass without explicit approval.
- The object is owned by an unexpected role or deployments create objects under inconsistent identities.
- A write test could invoke external effects, expose sensitive data, or cannot be reversed safely.
- The apparent permission failure is actually authentication, connection, missing relation, invalid search_path, or a row-security policy incident.
verification
Capture the exact role, database, search path, and failing statement
Reconnect exactly as the affected application role and reproduce one harmless failing operation. Record current_user, session_user, current database, active search path, and whether the object resolves when schema-qualified. An unqualified relation can fail because it is absent from search_path even when the role has privileges; that is a name-resolution problem, not proof that a GRANT is missing.
Why this step matters
Permission errors are evaluated for the identity and database of the current session. Connection pools may SET ROLE, connect to another database, or use a search path different from an interactive shell. Capturing those facts prevents a common mistake: granting privileges to the visible login while the application executes as another effective role. Schema qualification also distinguishes missing search-path resolution from missing schema USAGE.
What to understand
Use -X so local psql startup files cannot silently change role, variables, or search_path during the test.
Use a SELECT with LIMIT 1 or the application's read-only health query before reproducing INSERT, UPDATE, DELETE, or DDL.
Keep the exact SQLSTATE and complete server error; application wrappers often replace the useful PostgreSQL message.
System changes
- None. This step only records session identity and a bounded read attempt.
Syntax explained
-X- Do not read system or user psql startup files, producing a more reproducible session.
-v ON_ERROR_STOP=1- Stop immediately after the first SQL error so later commands do not obscure the failure.
schema.table- Use an explicit schema to separate object lookup from the privilege decision.
Values stay on this page and are never sent or saved.
psql -X -v ON_ERROR_STOP=1 -d {{databaseName}} -U {{appRole}} -c "SELECT current_user, session_user, current_database(), current_setting('search_path');" -c "SELECT 1 FROM {{schemaName}}.{{tableName}} LIMIT 1;"current_user | session_user | current_database | current_setting --------------+--------------+------------------+--------------------- app_runtime | app_runtime | orders | "$user", public ERROR: permission denied for schema app
Checkpoint: The failing execution identity is known
psql -X -d {{databaseName}} -U {{appRole}} -Atc "SELECT current_user||'|'||session_user||'|'||current_database()||'|'||current_setting('search_path');"Continue whenThe output names the same effective role, database, and search path used by the application, and the schema-qualified test reproduces the reported error.
Stop whenThe interactive session cannot match the application's role/database, the statement writes data, or the error changes to relation does not exist, authentication failure, or connection failure.
If this step fails
The test succeeds in psql but the application still reports permission denied.
Likely causeThe application uses another database, effective role, transaction SET ROLE, or connection-pool session state.
Log current_user, session_user, current_database and search_path through the same application connection without logging secrets.
ResolutionRepeat the audit for the actual effective role and database; do not add grants to an identity the application does not use.
Security notes
- Do not paste connection strings containing passwords into shell history or tickets.
Alternatives
- Use a short transaction through the application's existing diagnostic endpoint when direct login as the role is intentionally disabled.
Stop conditions
- Stop if the reproduction would modify production data or if the effective role cannot be proven.
verification
Inventory schema ownership, object ownership, ACLs, and membership
Inspect who owns the schema and table, whether the application role has CONNECT, USAGE, CREATE, and table privileges, and which group roles it can actually use. Ownership matters because only the owner, a role holding a grant option, or a suitable administrator can grant access. Capture the output before making changes; it becomes both evidence and the rollback baseline.
Why this step matters
PostgreSQL checks privileges at several independent layers. CONNECT permits entry to a database; schema USAGE permits resolving objects inside a schema; table privileges permit operations on the relation; sequence privileges may be needed to generate identifiers. Role membership can provide rights indirectly, but membership options and SET ROLE behavior affect whether those rights are active. A complete inventory prevents broad trial-and-error grants.
What to understand
Record schema and object owners because ALTER DEFAULT PRIVILEGES is evaluated for the role that creates future objects, not merely the database owner.
Inspect relkind so a view, partition, sequence, or foreign table is not treated as an ordinary table.
Retain ACL output before and after; NULL ACL does not mean public access and should be interpreted with object defaults.
System changes
- None. The queries read PostgreSQL catalogs and built-in privilege-check functions.
Syntax explained
has_schema_privilege- Tests effective USAGE or CREATE for a role, including privileges obtained through usable membership.
has_table_privilege- Tests a specific operation against one relation rather than guessing from an ACL string.
pg_get_userbyid- Resolves the owner OID to the role that can normally issue the corrective grant.
Values stay on this page and are never sent or saved.
psql -X -d {{databaseName}} -v app_role={{appRole}} -v schema_name={{schemaName}} -v table_name={{tableName}} -c "SELECT current_database(), has_database_privilege(:'app_role', current_database(), 'CONNECT') AS can_connect; SELECT n.nspname, pg_get_userbyid(n.nspowner) AS owner, has_schema_privilege(:'app_role', n.oid, 'USAGE') AS can_use, has_schema_privilege(:'app_role', n.oid, 'CREATE') AS can_create, n.nspacl FROM pg_namespace n WHERE n.nspname=:'schema_name'; SELECT c.relname, c.relkind, pg_get_userbyid(c.relowner) AS owner, has_table_privilege(:'app_role', c.oid, 'SELECT') AS can_select, has_table_privilege(:'app_role', c.oid, 'INSERT') AS can_insert, c.relacl FROM pg_class c JOIN pg_namespace n ON n.oid=c.relnamespace WHERE n.nspname=:'schema_name' AND c.relname=:'table_name';" current_database | can_connect
------------------+------------
orders | t
nspname | owner | can_use | can_create | nspacl
---------+-----------+---------+------------+-------------------------
app | app_owner | f | f | {app_owner=UC/app_owner}
relname | relkind | owner | can_select | can_insert | relacl
---------+---------+-----------+------------+------------+------------------------
invoices| r | app_owner | f | f | {app_owner=arwdDxt/app_owner}Checkpoint: Every failed privilege layer has an owner and an observed value
psql -X -d {{databaseName}} -Atc "SELECT n.nspname||'|'||pg_get_userbyid(n.nspowner)||'|'||has_schema_privilege('{{appRole}}',n.oid,'USAGE') FROM pg_namespace n WHERE n.nspname='{{schemaName}}';"Continue whenThe schema owner, table owner, effective role memberships, and each required privilege are recorded before the fix.
Stop whenThe target object is owned by an unknown deployment role, the administrator cannot act as the owner, or the object name resolves to a different schema than expected.
Security notes
- Catalog output can reveal role and object names; store it with the incident record, not in a public paste.
Alternatives
- In psql, \dn+ {{schemaName}}, \dp {{schemaName}}.{{tableName}}, and \du provide a concise interactive view of the same evidence.
Stop conditions
- Stop if ownership or role membership is ambiguous; changing ACLs before resolving it can grant the wrong tenant or service.
command
Grant schema USAGE, not CREATE, for ordinary application access
If the role must read or write existing objects in the schema, grant USAGE on that schema from the schema owner. USAGE allows object names in the schema to be resolved; it does not grant SELECT, INSERT, UPDATE, or DELETE on the objects. Do not add CREATE unless this role is intentionally allowed to create trusted schema objects.
Why this step matters
The message permission denied for schema usually identifies the first missing layer: USAGE. Granting ALL or CREATE to make the error disappear is broader than required and lets the grantee place objects into a schema that may be on other users' search paths. PostgreSQL documentation treats writable schemas on search_path as a trust boundary. An application that only consumes objects should usually receive USAGE and object-specific rights.
What to understand
Execute as the schema owner or a controlled role that can SET ROLE to it so the recorded grantor and ownership model stay clear.
Grant to a group role when several application identities share an access policy, then grant membership to individual logins.
Schema USAGE can be revoked independently without changing table ACLs, which is useful for containment.
System changes
- Adds one USAGE ACL entry to the named schema; it does not change ownership or table privileges.
Syntax explained
USAGE ON SCHEMA- Allows the role to resolve objects within the schema but not create objects there.
SET ROLE ownerRole- Performs the grant under the role that owns the schema and normally holds the grant option.
TO appRole- Targets the narrowly defined application or access group role.
Values stay on this page and are never sent or saved.
psql -X -v ON_ERROR_STOP=1 -d {{databaseName}} -c "SET ROLE {{ownerRole}}; GRANT USAGE ON SCHEMA {{schemaName}} TO {{appRole}}; RESET ROLE;"SET GRANT RESET
Checkpoint: The role can use the schema but cannot create objects
psql -X -d {{databaseName}} -U {{appRole}} -c "SELECT has_schema_privilege(current_user, '{{schemaName}}', 'USAGE') AS usage, has_schema_privilege(current_user, '{{schemaName}}', 'CREATE') AS create;"Continue whenusage is true and create remains false for a normal runtime role.
Stop whenThe application actually performs migrations, the schema owner is not the reviewed ownerRole, or CREATE is already inherited from a broader role that violates policy.
If this step fails
GRANT reports must be owner of schema.
Likely causeThe connected administrator is neither the owner nor able to SET ROLE to the owner.
Inspect pg_namespace.nspowner and pg_has_role(current_user, ownerRole, 'SET').
ResolutionRun the approved grant as the schema owner or correct the deployment ownership model; do not transfer ownership merely to bypass the error.
Security notes
- Avoid GRANT ALL ON SCHEMA and avoid restoring PUBLIC CREATE on a shared schema.
Alternatives
- Create a dedicated application schema owned by a migration role and grant the runtime role only USAGE.
Stop conditions
- Stop if granting USAGE would expose objects from another application or tenant sharing the schema.
command
Grant only the required operations on existing tables or views
After schema USAGE is present, grant the operation the application actually performs. A read-only service normally needs SELECT. A writer may need INSERT, UPDATE, or DELETE, but not TRUNCATE, REFERENCES, TRIGGER, or ownership. Start with the named relation; expand to all tables in the schema only when the role's policy genuinely covers every current relation.
Why this step matters
Object privileges are separate from schema privileges. A blanket GRANT ALL ON ALL TABLES often repairs the immediate error while silently enabling destructive or administrative operations on unrelated relations. Naming the required operations and object makes the authorization change reviewable. Views and foreign tables are covered by table-style GRANT syntax, but their underlying security behavior may require separate review.
What to understand
Derive the privilege list from observed application statements and tests, not from convenience.
For read-only reporting, grant SELECT on an approved view that exposes only required columns instead of granting base-table access.
Partitioned tables and sequences deserve explicit verification; do not assume one parent grant proves access to every object used by the statement.
System changes
- Adds SELECT, INSERT, and UPDATE ACL entries to the named relation for the application role.
Syntax explained
SELECT- Allows reading rows and referenced columns from the relation.
INSERT- Allows creation of rows, subject to column privileges, constraints, triggers, and row security.
UPDATE- Allows modifying rows and commonly also requires SELECT for predicates or returned values.
ON TABLE schema.table- Limits the change to one reviewed relation rather than the whole schema.
Values stay on this page and are never sent or saved.
psql -X -v ON_ERROR_STOP=1 -d {{databaseName}} -c "SET ROLE {{ownerRole}}; GRANT SELECT, INSERT, UPDATE ON TABLE {{schemaName}}.{{tableName}} TO {{appRole}}; RESET ROLE;"SET GRANT RESET
Checkpoint: The intended operation succeeds and unneeded operations remain denied
psql -X -d {{databaseName}} -U {{appRole}} -c "SELECT has_table_privilege(current_user, '{{schemaName}}.{{tableName}}', 'SELECT') AS sel, has_table_privilege(current_user, '{{schemaName}}.{{tableName}}', 'DELETE') AS del, has_table_privilege(current_user, '{{schemaName}}.{{tableName}}', 'TRUNCATE') AS trunc;"Continue whenThe approved privileges are true while DELETE and TRUNCATE remain false unless separately justified.
Stop whenThe table contains data outside the role's scope, row security is expected but absent, or the requested operation includes destructive privileges without an approved need.
Security notes
- Do not use ownership transfer as a substitute for normal DML privileges; owners can alter, drop, and redefine their objects.
Alternatives
- Grant EXECUTE on a carefully reviewed function or access through a narrow view when direct table privileges are too broad.
Stop conditions
- Stop if the object belongs to another service boundary or the required column/row scope cannot be expressed safely.
command
Grant sequence rights when INSERT uses generated identifiers
If INSERT fails on a sequence after the table grant succeeds, identify the exact sequence used by the default or identity column and grant USAGE. Table privileges do not automatically grant sequence privileges. Avoid granting UPDATE unless the application must call setval, which ordinary runtime code rarely needs.
Why this step matters
PostgreSQL sequences are independent objects with their own ACLs. INSERT into a table can call nextval through a column default and then fail even though INSERT is granted on the table. USAGE covers currval and nextval behavior required by typical generated identifiers; SELECT permits reading sequence state. UPDATE permits setval and should remain absent unless a controlled maintenance workflow explicitly needs it.
What to understand
Use pg_get_serial_sequence or inspect the column default to locate the associated sequence instead of guessing its name.
Test the exact INSERT inside a transaction with ROLLBACK when constraints and triggers have no external effects.
Identity columns still use sequence objects internally and deserve the same privilege check.
System changes
- Adds USAGE and SELECT ACL entries to one named sequence; it does not change its current value.
Syntax explained
USAGE ON SEQUENCE- Permits typical nextval/currval use for generated keys.
SELECT ON SEQUENCE- Permits reading sequence state where the application or driver requires it.
UPDATE- Would permit setval; intentionally omitted from normal runtime access.
Values stay on this page and are never sent or saved.
psql -X -v ON_ERROR_STOP=1 -d {{databaseName}} -c "SET ROLE {{ownerRole}}; GRANT USAGE, SELECT ON SEQUENCE {{schemaName}}.{{sequenceName}} TO {{appRole}}; RESET ROLE;"SET GRANT RESET
Checkpoint: Generated IDs work without sequence mutation authority
psql -X -d {{databaseName}} -U {{appRole}} -c "SELECT has_sequence_privilege(current_user, '{{schemaName}}.{{sequenceName}}', 'USAGE') AS usage, has_sequence_privilege(current_user, '{{schemaName}}.{{sequenceName}}', 'UPDATE') AS can_setval;"Continue whenUSAGE is true and UPDATE is false for a standard application runtime role.
Stop whenThe sequence is shared by another security boundary, the application should not insert rows, or the named sequence is not the default used by the failing column.
Security notes
- Do not grant ALL SEQUENCES IN SCHEMA until the ownership and application boundary of every sequence is reviewed.
Alternatives
- Use an owner-controlled insert function when the application must not receive direct table or sequence access.
Stop conditions
- Stop if resolving the sequence reveals a trigger or function with external effects that cannot be safely tested.
command
Set default privileges for objects created in future deployments
If deployments repeatedly create tables or sequences that the runtime role cannot use, configure default privileges for the role that actually creates those objects. Existing objects are unaffected, so retain the explicit grants from earlier steps. Apply defaults in the intended schema and for the exact creator role; defaults of a group role are not automatically inherited at object-creation time.
Why this step matters
A one-time GRANT repairs current objects only. Future migrations can recreate the outage unless the object creator's defaults encode the access contract. PostgreSQL applies default privileges based on the current creating role, not every role it belongs to. Running the command as the wrong administrator can produce a valid-looking rule that never applies. Schema-scoped defaults add policy only for new objects in that schema.
What to understand
Confirm the migration tool's current_user during a real or staging deployment; database owner and object creator may differ.
Inspect psql \ddp before and after to record the exact default ACL entry and grantor.
Create a temporary test table and sequence as ownerRole in a non-production validation schema, verify grants, and remove the test objects.
System changes
- Adds default ACL records for future tables and sequences created by ownerRole in schemaName.
Syntax explained
FOR ROLE ownerRole- Selects the object-creating role whose future objects receive the defaults.
IN SCHEMA schemaName- Limits the default table and sequence grants to one schema.
ON TABLES / ON SEQUENCES- Defines independent defaults because table grants do not cover sequences.
Values stay on this page and are never sent or saved.
psql -X -v ON_ERROR_STOP=1 -d {{databaseName}} -c "ALTER DEFAULT PRIVILEGES FOR ROLE {{ownerRole}} IN SCHEMA {{schemaName}} GRANT SELECT, INSERT, UPDATE ON TABLES TO {{appRole}}; ALTER DEFAULT PRIVILEGES FOR ROLE {{ownerRole}} IN SCHEMA {{schemaName}} GRANT USAGE, SELECT ON SEQUENCES TO {{appRole}};"ALTER DEFAULT PRIVILEGES ALTER DEFAULT PRIVILEGES
Checkpoint: A future deployment object inherits the intended ACL
psql -X -d {{databaseName}} -c "SELECT defaclrole::regrole AS creator, defaclnamespace::regnamespace AS schema, defaclobjtype, defaclacl FROM pg_default_acl WHERE defaclrole='{{ownerRole}}'::regrole;"Continue whenDefault ACL rows name ownerRole, schemaName, tables and sequences, and grant only the reviewed privileges to appRole.
Stop whenThe migration creator role cannot be proven, multiple teams create objects in the same schema, or the proposed default would cover unrelated future objects.
If this step fails
New tables still produce permission denied after defaults were added.
Likely causeThey are created by a different role, in another schema, or through SET ROLE that changes current_user.
Inspect each new object's relowner and the migration session current_user; compare with pg_default_acl.defaclrole.
ResolutionMove creation under the reviewed owner role or add a separate narrowly scoped default for the actual creator after approval.
Security notes
- Default privileges are durable policy. Review them during role retirement, schema moves, and deployment-tool changes.
Alternatives
- Keep explicit GRANT statements in the same transaction as each migration when object access differs per table.
Stop conditions
- Stop if one uniform privilege set is not appropriate for every future object in the schema.
warning
Do not grant schema CREATE to fix a runtime DML error
A runtime role that only reads and writes application rows normally needs schema USAGE plus object privileges, not schema CREATE. CREATE lets the role place tables, functions, operators, or other objects into the schema. When that schema is trusted through search_path, writable objects can affect how other sessions resolve names. Keep migrations and runtime identities separate.
Why this step matters
Granting CREATE is a tempting response to permission denied for schema public, especially after PostgreSQL 15 changed the default public-schema ownership and CREATE behavior for new databases. That error does not by itself prove that the application should create objects. Separating an owner or migration role from the runtime role reduces the damage from application compromise and makes deployment changes auditable.
What to understand
PostgreSQL 15 and later new databases no longer give PUBLIC CREATE on the public schema by default; upgraded databases can preserve earlier ACLs.
Prefer an application-specific schema owned by a no-login or migration role rather than restoring broad PUBLIC CREATE.
Review search_path so it does not contain schemas writable by untrusted roles.
System changes
- None. This is a policy gate that prevents an unnecessarily broad grant.
Syntax explained
CREATE ON SCHEMA- Allows creation of new objects in the schema; it is not required for normal SELECT or DML on existing objects.
PUBLIC- Represents every current and future role and is rarely an appropriate grantee for application object creation.
app_runtime | usage=t | create=f app_migrate | usage=t | create=t
Checkpoint: Runtime and migration authority are distinct
psql -X -d {{databaseName}} -c "SELECT rolname, has_schema_privilege(rolname, '{{schemaName}}', 'USAGE') AS usage, has_schema_privilege(rolname, '{{schemaName}}', 'CREATE') AS create FROM pg_roles WHERE rolname IN ('{{appRole}}','{{ownerRole}}') ORDER BY rolname;"Continue whenappRole can use the schema but cannot create objects; ownerRole or the approved migration role can create them.
Stop whenThe application legitimately performs tenant DDL and no separate, bounded migration mechanism has been designed.
Security notes
- Never use GRANT CREATE ON SCHEMA public TO PUBLIC as a generic compatibility fix.
Alternatives
- Provide a reviewed migration service or SECURITY DEFINER routine with a fixed search_path for narrowly bounded DDL.
Stop conditions
- Stop if search_path includes an untrusted writable schema or if the runtime role already owns application objects.
verification
Check row-level security when ACLs pass but data access still fails or returns no rows
After has_table_privilege reports true, inspect whether row-level security is enabled and which policies apply to the role and command. RLS is a separate authorization layer. A table owner normally bypasses RLS unless FORCE ROW LEVEL SECURITY is enabled; superuser testing can therefore hide the application's real behavior.
Why this step matters
Successful ACL checks do not guarantee visible or writable rows. With RLS enabled, applicable policies add conditions to SELECT, UPDATE, DELETE, and INSERT checks. A missing policy can produce default-deny behavior, while an unset tenant session variable can make a correct policy reject or hide rows. Testing as owner or superuser is misleading because those identities may bypass the policy.
What to understand
Run the verification as appRole and set only the same trusted session context the application sets.
Review both USING and WITH CHECK expressions for the specific command.
Treat a proposal to add a permissive policy as an authorization design change, not a small permission repair.
System changes
- None. The step reads table flags and policy definitions.
Syntax explained
relrowsecurity- Shows whether row-level security is enabled for the relation.
relforcerowsecurity- Shows whether even the table owner is forced through row-security policies.
pg_policies- Exposes policy roles, commands, USING predicates, and WITH CHECK conditions.
Values stay on this page and are never sent or saved.
psql -X -d {{databaseName}} -c "SELECT c.relrowsecurity, c.relforcerowsecurity FROM pg_class c JOIN pg_namespace n ON n.oid=c.relnamespace WHERE n.nspname='{{schemaName}}' AND c.relname='{{tableName}}'; SELECT schemaname, tablename, policyname, permissive, roles, cmd, qual, with_check FROM pg_policies WHERE schemaname='{{schemaName}}' AND tablename='{{tableName}}';" relrowsecurity | relforcerowsecurity
----------------+---------------------
t | f
schemaname | tablename | policyname | roles | cmd | qual
------------+-----------+------------------+---------------+--------+---------------------------
app | invoices | tenant_isolation | {app_runtime} | SELECT | (tenant_id = current_setting('app.tenant_id')::uuid)Checkpoint: ACL and row-policy behavior are both explained
psql -X -d {{databaseName}} -U {{appRole}} -c "SELECT current_user, has_table_privilege(current_user, '{{schemaName}}.{{tableName}}', 'SELECT'); SELECT count(*) AS visible_rows FROM {{schemaName}}.{{tableName}};"Continue whenThe table privilege is true and the visible-row result matches the documented policy and application tenant context.
Stop whenThe policy depends on unverified session variables, functions owned by unexpected roles, or tenant isolation behavior is not covered by tests.
If this step fails
The role has SELECT but receives zero rows.
Likely causeRLS default deny, a policy that does not include the role, or missing tenant/session context.
Inspect pg_policies and compare current_setting values through the same application session.
ResolutionRepair the trusted session context or review the policy; do not disable RLS to make the query return data.
Security notes
- Disabling RLS can expose every tenant immediately. It is not a troubleshooting shortcut.
Alternatives
- Use a reviewed view or SECURITY DEFINER function with fixed search_path only when direct RLS policy access cannot express the required boundary.
Stop conditions
- Stop before changing any policy unless tenant and authorization tests are available.
verification
Verify the real operation with bounded data and negative tests
Test as the actual application role. Run read checks directly. For a write, use a dedicated fixture row and a transaction that rolls back only after confirming triggers and functions have no external effects. Verify expected success and expected denial of an ungranted operation. Then run the application's own health path to catch pool role and search-path differences.
Why this step matters
A green privilege function is necessary but not sufficient. The actual statement may touch a sequence, view, function, partition, trigger, or RLS policy that adds another authorization decision. Positive and negative tests prove both availability and containment. A transaction reduces database changes, but it cannot undo email, queue, network, or non-transactional sequence effects, so the fixture must be chosen deliberately.
What to understand
Use a production-safe SELECT first and compare the returned shape, not confidential row contents.
Test one ungranted operation such as TRUNCATE through has_table_privilege rather than attempting it.
Verify through the application pool after SQL tests because pool initialization can SET ROLE or alter search_path.
System changes
- None for the shown verification; any optional write fixture must be explicitly reviewed and rolled back.
Syntax explained
BEGIN / ROLLBACK- Bounds transactional database changes but does not reverse external side effects or sequence increments.
has_table_privilege(..., 'TRUNCATE')- Proves a dangerous privilege remains absent without executing the operation.
Values stay on this page and are never sent or saved.
psql -X -v ON_ERROR_STOP=1 -d {{databaseName}} -U {{appRole}} -c "BEGIN; SELECT count(*) FROM {{schemaName}}.{{tableName}}; SELECT has_table_privilege(current_user, '{{schemaName}}.{{tableName}}', 'TRUNCATE') AS truncate_must_be_false; ROLLBACK;"BEGIN
count
-------
42
truncate_must_be_false
------------------------
f
ROLLBACKCheckpoint: Availability and least privilege both pass
psql -X -d {{databaseName}} -U {{appRole}} -Atc "SELECT has_schema_privilege(current_user,'{{schemaName}}','USAGE'),has_table_privilege(current_user,'{{schemaName}}.{{tableName}}','SELECT'),has_table_privilege(current_user,'{{schemaName}}.{{tableName}}','TRUNCATE');"Continue whenThe required schema and object checks are true, the dangerous/unneeded check is false, and the application health path succeeds.
Stop whenThe test invokes unknown triggers, external services, sensitive rows, or a role/search-path different from the application.
Security notes
- Do not validate by temporarily granting superuser, BYPASSRLS, pg_read_all_data, or pg_write_all_data.
Alternatives
- Use a restored production-like database for full write-path validation when a production fixture is not safe.
Stop conditions
- Stop if a successful statement reveals rows outside the role's intended tenant or data boundary.
instruction
Record the final ACL contract and monitor for drift
Save the before/after privilege evidence, grantor, owner, approved operations, default privileges, test results, and rollback commands. Add a deployment check that verifies the runtime role's required rights after migrations without broadening them. Review the contract when schema ownership, migration identity, PostgreSQL major version, or RLS policy changes.
Why this step matters
Permission incidents often recur after migrations because the effective ownership and default ACL contract was implicit. A compact evidence record lets reviewers see exactly what was granted, by whom, and why. Automated checks should assert the minimum required operations and the absence of dangerous privileges; they should not repair drift automatically because an unexpected owner or schema can indicate a deployment or security problem.
What to understand
Record PostgreSQL version because public-schema defaults and available privileges differ across supported majors.
Include the exact ownerRole and migration current_user so future default privileges remain effective.
Alert on owner changes, PUBLIC CREATE, unexpected grant options, or expansion to ALL TABLES/SEQUENCES.
System changes
- None. This step records evidence and defines a future validation check.
Syntax explained
\dn+- Shows schema ownership and access privileges.
\dp- Shows relation and column ACLs in psql.
\ddp- Shows default privileges that apply to future objects.
Values stay on this page and are never sent or saved.
psql -X -d {{databaseName}} -c "\dn+ {{schemaName}}" -c "\dp {{schemaName}}.{{tableName}}" -c "\ddp"List of schemas Name | Owner | Access privileges app | app_owner | app_owner=UC/app_owner+ app_runtime=U/app_owner Access privileges Schema | Name | Type | Access privileges app | invoices | table | app_owner=arwdDxt/app_owner+ app_runtime=arw/app_owner Default access privileges Owner | Schema | Type | Access privileges app_owner | app | table | app_runtime=arw/app_owner
Checkpoint: The permission repair is reproducible and reversible
psql -X -d {{databaseName}} -Atc "SELECT current_setting('server_version'), pg_get_userbyid(n.nspowner), n.nspacl FROM pg_namespace n WHERE n.nspname='{{schemaName}}';"Continue whenThe incident record contains version, ownership, ACL before/after, exact grants, default ACL, positive and negative tests, and rollback commands.
Stop whenThe record cannot identify the grantor or object creator, or observed ACLs differ from the reviewed change.
Security notes
- Keep role and object inventories in restricted operational records when names reveal internal architecture.
Alternatives
- Use an approved schema-migration assertion test that exports only pass/fail and a redacted ACL digest.
Stop conditions
- Do not close the incident while the application still depends on a manual SET ROLE or temporary broad grant.
Finish line
Verification checklist
psql -X -d {{databaseName}} -U {{appRole}} -c "SELECT has_schema_privilege(current_user,'{{schemaName}}','USAGE') AS schema_usage, has_schema_privilege(current_user,'{{schemaName}}','CREATE') AS schema_create, has_table_privilege(current_user,'{{schemaName}}.{{tableName}}','SELECT') AS table_select, has_table_privilege(current_user,'{{schemaName}}.{{tableName}}','TRUNCATE') AS table_truncate, has_sequence_privilege(current_user,'{{schemaName}}.{{sequenceName}}','USAGE') AS sequence_usage;"Required rights are true; schema CREATE, table TRUNCATE, sequence UPDATE, and every other unapproved capability remain false.Run the application's normal read/write health check through its connection pool and record current_user, current_database, search_path, SQLSTATE, and affected-row result without secrets.The original operation succeeds through the actual pool, no other tenant becomes visible, and logs contain no permission error or privilege escalation.Create a temporary table and sequence as {{ownerRole}} in an approved non-production validation schema, inspect privileges as {{appRole}}, then drop the test objects.New objects inherit only the intended table and sequence privileges from the reviewed default ACL.Recovery guidance
Common problems and safe checks
ERROR: permission denied for schema public or another schema.
Likely causeThe effective role lacks schema USAGE, or the application is trying to create an object without an approved migration role.
Check current_user and has_schema_privilege for USAGE and CREATE separately.Schema-qualify the object and inspect search_path.
ResolutionGrant USAGE for normal object access. Grant CREATE only to the reviewed owner or migration role.
ERROR: permission denied for table after schema USAGE is true.
Likely causeThe role lacks the operation-specific table privilege or the query touches a view/partition with another authorization path.
Run has_table_privilege for the exact operation.Inspect the relation kind, owner, view definition, partitions, and invoked functions.
ResolutionGrant only the required operation on the correct object or expose a narrower view/function.
ERROR: permission denied for sequence during INSERT.
Likely causeThe table grant does not cover the sequence used by a serial or identity default.
Use pg_get_serial_sequence or inspect the column default.Run has_sequence_privilege for USAGE and UPDATE separately.
ResolutionGrant USAGE and, only when required, SELECT on the exact sequence; keep UPDATE/setval unavailable to the runtime role.
Existing tables work, but every newly migrated table fails.
Likely causeALTER DEFAULT PRIVILEGES was not set, was set for the wrong object creator, or targeted another schema.
Inspect pg_default_acl and each new object's relowner.Capture current_user inside the migration session.
ResolutionDefine future defaults for the actual creator role and keep explicit grants for already-existing objects.
Privileges show true, but SELECT returns no rows or INSERT violates row security.
Likely causeRLS default deny, an inapplicable policy, or missing trusted tenant/session context.
Inspect relrowsecurity, relforcerowsecurity, and pg_policies.Repeat through the actual application session and compare current_setting values.
ResolutionRepair the trusted context or reviewed policy. Do not disable RLS or test as superuser.
GRANT succeeds but the application still fails after a connection-pool restart.
Likely causeThe pool connects to another database, changes SET ROLE/search_path, or caches an initialization path that differs from the manual test.
Expose redacted current_user, session_user, current_database and search_path through the pool.Compare SQLSTATE and schema-qualified statement.
ResolutionApply the least-privilege contract to the actual effective role and make pool initialization explicit.
Reference
Frequently asked questions
Why does GRANT ALL ON DATABASE not fix permission denied for a table?
Database privileges such as CONNECT and CREATE do not replace schema USAGE or relation privileges. PostgreSQL checks each object layer independently, so the role still needs the required schema and table or sequence rights.
Should an application receive CREATE on the public schema?
Usually no. A runtime application that uses existing objects normally needs USAGE plus object privileges. CREATE belongs to a controlled migration or owner role because writable schemas on search_path are a security boundary.
Why did PostgreSQL 15 change behavior for the public schema?
New PostgreSQL 15 databases no longer grant PUBLIC the CREATE privilege on the public schema by default, and pg_database_owner owns that schema. Upgraded databases can preserve older ACLs, so always inspect the actual database rather than assuming a version-wide state.
Why do future tables ignore ALTER DEFAULT PRIVILEGES?
Defaults apply only to objects created later by the exact target/current creator role. They do not affect existing objects and are not inherited from every group role the creator belongs to.
Can I transfer table ownership to the application role instead?
That is much broader than a normal grant. Owners can alter and drop their objects and possess grant options. Keep ownership with a stable owner or migration role and grant the runtime role only the operations it needs.
Why does a role with SELECT still see no rows?
Row-level security is evaluated after ordinary object privileges. An applicable policy, default deny, or missing tenant context can hide rows even when SELECT is granted.
Recovery
Rollback
Revoke exactly the grants introduced by this change and reverse the matching default privileges as the recorded grantor. Rollback restores the previous ACL state but can immediately break the application, so coordinate it with traffic control or deployment rollback.
- Capture the current ACL and stop or drain the application path that depends on the new permission.
- As {{ownerRole}}, run REVOKE SELECT, INSERT, UPDATE ON TABLE {{schemaName}}.{{tableName}} FROM {{appRole}} and REVOKE USAGE, SELECT ON SEQUENCE {{schemaName}}.{{sequenceName}} FROM {{appRole}} for only the rights added.
- Run REVOKE USAGE ON SCHEMA {{schemaName}} FROM {{appRole}} only if no other approved object access uses that schema.
- Reverse future defaults with ALTER DEFAULT PRIVILEGES FOR ROLE {{ownerRole}} IN SCHEMA {{schemaName}} REVOKE SELECT, INSERT, UPDATE ON TABLES FROM {{appRole}} and the matching sequence REVOKE.
- Re-run the before-state ACL queries and the application's expected-failure test; do not use DROP OWNED because it can remove unrelated privileges and objects.
Evidence