Fix: PostgreSQL permission denied for table (or relation, schema, sequence)

The Error

You run a query against a PostgreSQL table and get:

ERROR: permission denied for table my_table

Or one of these variations:

ERROR: permission denied for relation my_table

ERROR: permission denied for schema public

ERROR: permission denied for sequence my_table_id_seq

ERROR: permission denied for view my_view

The query itself might be a simple SELECT, an INSERT, an UPDATE, or even a DELETE. The error means your current PostgreSQL role does not have the required privilege on the object you are trying to access.

Note: In older PostgreSQL versions (before 12), the error says “relation” instead of “table.” They mean the same thing.

Why This Happens

PostgreSQL has a granular permission system. Every database object (table, schema, sequence, view, function) has an owner, and only the owner or a superuser can access it by default. Other roles must be explicitly granted access.

Here are the most common reasons you hit this error:

1. Missing table-level grants. The role you are connected as was never granted SELECT, INSERT, UPDATE, or DELETE on the table.
2. Missing schema usage permission. Even if you have table-level grants, you also need USAGE on the schema that contains the table.
3. New tables not covered by default privileges. You granted permissions on existing tables, but new tables created later do not inherit those grants.
4. Wrong table owner. You created the table with one role but are trying to manage it with another.
5. Row Level Security (RLS) blocking access. RLS policies can silently block rows or raise permission errors.
6. Role inheritance not working as expected. Your role is a member of a group role, but INHERIT is not set.
7. Confusing authentication with authorization. You can connect to the database (authentication via pg_hba.conf passes), but you still lack permission on specific objects (authorization).

How Other Tools Handle This

The “permission denied for table” message is Postgres-specific, but every relational database has the same core grain — a role, a database object, and a verb you are trying to perform on it. The differences in how each engine models grants, schemas, and ownership explain why permission scripts that worked in MySQL break the moment you port them to Postgres.

Postgres GRANT/REVOKE. Postgres separates schema USAGE from table privileges (SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER). You always need both layers — schema usage plus table privilege — and you also need privileges on any sequence backing a SERIAL column. Postgres 14 introduced predefined roles (pg_read_all_data, pg_write_all_data, pg_monitor) that bundle common privilege sets. Postgres 16 added pg_create_subscription and refined privilege handling for foreign data wrappers.

MySQL privilege system. MySQL grants are stored in mysql.user, mysql.db, mysql.tables_priv, and mysql.columns_priv tables. Schemas and databases are the same concept (USE database_name), so there is no schema-usage layer to grant. Privileges are global, database-scoped, table-scoped, or column-scoped — and a single user is identified by 'user'@'host', which means 'app'@'localhost' and 'app'@'%' are different users with separate grant sets. Forgetting to FLUSH PRIVILEGES after directly editing the mysql.* tables is a classic MySQL-only footgun; GRANT and REVOKE already flush automatically.

SQL Server schema-bound permissions. SQL Server grants flow through schemas more aggressively. You can GRANT SELECT ON SCHEMA::dbo TO appuser and every table in dbo inherits the grant, including future tables. Ownership chaining means a stored procedure owned by the same schema owner as its target tables does not require the caller to have direct table permissions. SQL Server roles can be database-scoped (db_datareader, db_datawriter) or server-scoped, and the schema/owner separation lets you change a schema’s owner without rewriting object references.

Oracle synonyms and roles. Oracle treats every user as a schema. A GRANT SELECT ON hr.employees TO app requires app to qualify the table as hr.employees unless you also create a synonym (CREATE SYNONYM employees FOR hr.employees). Roles in Oracle bundle privileges similarly to Postgres group roles, but they must be SET ROLE activated in some contexts. The WITH GRANT OPTION chain works similarly across all four databases, but Oracle additionally enforces “role chaining” rules that block circular grants.

Row-level security across databases. Postgres has RLS through CREATE POLICY and ENABLE ROW LEVEL SECURITY, with bypass for owners unless FORCE ROW LEVEL SECURITY is set. SQL Server has RLS through security predicates introduced in 2016. MySQL has no native row-level security — application-layer filtering or views are the workaround. Oracle has VPD (Virtual Private Database) and Real Application Security, which are more featureful than the others but require Enterprise Edition for full functionality. Porting an RLS-heavy schema between engines almost always means rewriting the policies in the target engine’s idiom.

Default privileges grain. Postgres ALTER DEFAULT PRIVILEGES operates per-role and per-schema — that is the finest grain of any of these engines. SQL Server defaults apply at the schema level. MySQL has no equivalent — you have to re-grant every time someone creates a table, or wrap creation in a procedure that grants as part of the same transaction. If you maintain a Postgres-style “grant to a role and forget” pattern after migrating to MySQL, every new table will silently lock your app out until you re-grant.

Fix 1: Grant Privileges on a Specific Table

The most direct fix. Connect as the table owner or a superuser and grant the needed privileges:

-- Grant read access
GRANT SELECT ON my_table TO my_user;

-- Grant read and write access
GRANT SELECT, INSERT, UPDATE, DELETE ON my_table TO my_user;

If you also need to use sequences (common with SERIAL or BIGSERIAL columns):

GRANT USAGE, SELECT ON SEQUENCE my_table_id_seq TO my_user;

To verify what grants exist on a table, use \dp in psql:

\dp my_table

This shows the access privileges in a compact format. Look for your role name in the output.

Pro Tip: PostgreSQL privilege abbreviations in \dp output can be confusing. r = SELECT, a = INSERT, w = UPDATE, d = DELETE, U = USAGE. If your role is not listed at all, it has zero privileges on that object.

If you are dealing with a role that does not exist, you need to create it first before granting any privileges.

Fix 2: Grant ALL PRIVILEGES on All Tables in a Schema

When your application role needs access to every table in a schema, granting one table at a time is tedious. Grant on all tables at once:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO my_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO my_user;

Replace public with your schema name if you use a custom schema.

To grant read-only access to all tables:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO my_user;

Note: This only affects tables that exist right now. Tables created after this command will not have these grants. See Fix 3 for the solution.

Fix 3: Fix Default Privileges for Future Tables

This is one of the most commonly missed steps. You grant permissions on all existing tables, everything works, and then a migration creates a new table and your app breaks.

Use ALTER DEFAULT PRIVILEGES to automatically grant permissions on future objects:

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO my_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT USAGE, SELECT ON SEQUENCES TO my_user;

There is a critical detail here: default privileges are role-specific. They only apply to objects created by the role that runs the ALTER DEFAULT PRIVILEGES command. If your migrations run as admin_user but you set default privileges while connected as postgres, the defaults will not apply.

To set default privileges for objects created by a specific role:

ALTER DEFAULT PRIVILEGES FOR ROLE admin_user IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

To check current default privileges:

\ddp

If your tables have a problem where they exist but PostgreSQL claims they do not, that might be a different issue. Check the relation does not exist guide for that scenario.

Fix 4: Fix Schema Search Path and Usage Permissions

Even with correct table-level grants, you will get a permission error if you lack USAGE on the schema. PostgreSQL requires two layers of permission:

1. USAGE on the schema (to “enter” the schema)
2. The specific privilege on the table (to read/write data)

Grant schema usage:

GRANT USAGE ON SCHEMA my_schema TO my_user;

If the user also needs to create new objects in the schema:

GRANT USAGE, CREATE ON SCHEMA my_schema TO my_user;

Check your search path to make sure you are looking in the right schema:

SHOW search_path;

If your table is in a schema that is not on the search path, you either need to qualify the table name (my_schema.my_table) or add the schema to the search path:

ALTER ROLE my_user SET search_path TO my_schema, public;

Common Mistake: In PostgreSQL 15+, the public schema no longer grants CREATE permission to all roles by default. If you upgraded from PostgreSQL 14 or earlier, your application roles may suddenly lose the ability to create tables in the public schema. You need to explicitly run GRANT CREATE ON SCHEMA public TO my_user; after upgrading.

This is a breaking change that catches many teams off guard during upgrades. If you are also hitting connection refused errors after an upgrade, the server configuration may have changed as well.

Fix 5: Fix Ownership Issues

The owner of a table has full control over it. If you need to transfer ownership:

ALTER TABLE my_table OWNER TO my_user;

To change ownership of all tables in a schema at once:

DO $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN SELECT tablename FROM pg_tables WHERE schemaname = 'public'
    LOOP
        EXECUTE 'ALTER TABLE public.' || quote_ident(r.tablename) || ' OWNER TO my_user';
    END LOOP;
END $$;

You can also use the REASSIGN OWNED command to transfer all objects from one role to another:

REASSIGN OWNED BY old_user TO new_user;

Warning: REASSIGN OWNED transfers ownership of all objects owned by old_user in the current database, including schemas, sequences, functions, and types. Run it with caution.

To check who owns a table:

SELECT tableowner FROM pg_tables WHERE tablename = 'my_table';

Or in psql:

\dt my_table

Fix 6: Fix RLS (Row Level Security) Policy Issues

Row Level Security can cause permission-denied-like behavior that is tricky to diagnose. When RLS is enabled on a table and no policy matches your role, you get zero rows back (for SELECT) or an error (for INSERT, UPDATE, DELETE).

Check if RLS is enabled:

SELECT relname, relrowsecurity, relforcerowsecurity
FROM pg_class
WHERE relname = 'my_table';

If relrowsecurity is true, RLS is active. View existing policies:

SELECT * FROM pg_policies WHERE tablename = 'my_table';

To temporarily bypass RLS for debugging, the table owner can use:

SET row_security = off;

Or grant the BYPASSRLS attribute to a role:

ALTER ROLE my_user BYPASSRLS;

If RLS is enabled but you do not actually need it, the table owner can disable it:

ALTER TABLE my_table DISABLE ROW LEVEL SECURITY;

If you do need RLS, create a policy that allows access for your role:

CREATE POLICY my_policy ON my_table
    FOR ALL
    TO my_user
    USING (true)
    WITH CHECK (true);

That policy allows full access. In production, you would replace true with an actual condition, such as tenant_id = current_setting('app.tenant_id')::int.

Note: The table owner bypasses RLS by default. If you want the owner to also be subject to RLS, use ALTER TABLE my_table FORCE ROW LEVEL SECURITY;.

Fix 7: Fix pg_hba.conf Authentication vs Authorization Confusion

A common source of confusion: pg_hba.conf controls authentication (who can connect), not authorization (what they can do after connecting). If you can connect but get “permission denied for table,” the issue is not in pg_hba.conf.

However, pg_hba.conf can cause indirect permission issues:

1. Connecting as the wrong user. Your pg_hba.conf might map you to a different PostgreSQL role than you expect. Check which role you are connected as:

SELECT current_user, session_user;

2. Peer authentication mapping. On Linux, peer authentication maps your OS username to a PostgreSQL role. If your OS user is deploy but the table owner is myapp, you are connecting as the wrong role.

3. The DATABASE column in pg_hba.conf. This restricts which databases a role can connect to, but it does not affect table-level permissions at all.

To find your pg_hba.conf file:

SHOW hba_file;

If your real problem is that you cannot connect at all, that is a different class of issue — pg_hba.conf matching and network reachability are the first things to check, not table grants.

Fix 8: Fix Role Inheritance and Membership

PostgreSQL supports group roles. You can make one role a member of another to inherit its privileges. But this only works if INHERIT is set.

Check your role’s memberships:

SELECT r.rolname AS role, m.rolname AS member_of,
       r.rolinherit AS inherits
FROM pg_auth_members am
JOIN pg_roles r ON am.roleid = r.oid
JOIN pg_roles m ON am.member = m.oid
WHERE m.rolname = 'my_user';

If inherits is false, the role is a member of the group but does not automatically get its privileges. Fix it:

ALTER ROLE my_user INHERIT;

To add a role to a group:

GRANT app_read_group TO my_user;

If INHERIT is off and you cannot change it, you can manually activate the group role’s privileges in your session:

SET ROLE app_read_group;

This switches your effective role for the current session. To switch back:

RESET ROLE;

A best practice pattern for managing PostgreSQL permissions is to create group roles with specific privileges and then add users to those groups:

-- Create group roles
CREATE ROLE readonly NOLOGIN;
CREATE ROLE readwrite NOLOGIN;

-- Grant privileges to groups
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

GRANT USAGE ON SCHEMA public TO readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO readwrite;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO readwrite;

-- Set default privileges
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO readwrite;

-- Add users to groups
GRANT readonly TO reporting_user;
GRANT readwrite TO app_user;

This pattern avoids the need to grant permissions individually to every user and every table. If you run into deadlocks while running these grants in a migration, serialize the grant statements or run them outside of a transaction — concurrent GRANT and REVOKE against the same object take an ACCESS EXCLUSIVE lock that conflicts with running queries.

Still Not Working?

Superuser vs Regular User

Check if your role is a superuser:

SELECT rolname, rolsuper FROM pg_roles WHERE rolname = current_user;

Superusers bypass all permission checks. If your application used to work with a superuser and broke after switching to a regular user, you need to explicitly grant all required privileges. Never use a superuser role for application connections in production.

pg_dump and pg_restore Permission Issues

When restoring a database dump, you might hit permission errors if:

• The dump contains ALTER OWNER statements for a role that does not exist on the target server.
• The dump includes GRANT statements referencing roles that do not exist.
• You are restoring as a non-superuser.

Fix by restoring with the --no-owner and --no-privileges flags:

pg_restore --no-owner --no-privileges -d my_database my_dump.sql

For plain SQL dumps:

psql -d my_database -f my_dump.sql

If the dump references roles that do not exist, create them first or use --role to map ownership:

pg_restore --no-owner --role=my_user -d my_database my_dump.sql

If your dump also has issues with duplicate key constraint violations, you may need to reset sequences after the restore.

Cloud-Managed PostgreSQL (RDS, Cloud SQL, Azure)

Cloud-managed PostgreSQL instances have restrictions that do not exist on self-managed installations:

AWS RDS:

• There is no true superuser. The master user has rds_superuser, which has most but not all superuser privileges.
• You cannot modify pg_hba.conf directly. Use security groups and RDS parameter groups instead.
• To grant privileges on objects created by rds_superuser, you must connect as that role.

Google Cloud SQL:

• The cloudsqlsuperuser role replaces the traditional superuser.
• Some system-level operations are restricted. For example, you cannot run CREATE EXTENSION for extensions that are not on the allow list.
• Use cloudsqlsuperuser to grant permissions, then connect as a regular role for application use.

Azure Database for PostgreSQL:

• The admin role created at server setup is not a full superuser.
• Use the admin role to create application roles and grant permissions.
• Some extensions and settings require the azure_pg_admin role.

In all managed environments, the pattern is the same: connect as the highest-privilege role available, create your application roles, grant them the minimum required permissions, and use those roles for your application connections.

Quick Diagnostic Script

Run this to diagnose permission issues on a specific table:

-- Check table owner
SELECT tableowner FROM pg_tables WHERE tablename = 'my_table';

-- Check current user
SELECT current_user, session_user;

-- Check grants on the table
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name = 'my_table';

-- Check schema permissions
SELECT nspname, nspacl FROM pg_namespace WHERE nspname = 'public';

-- Check if RLS is enabled
SELECT relrowsecurity FROM pg_class WHERE relname = 'my_table';

-- Check role memberships
SELECT r.rolname AS group_role
FROM pg_auth_members am
JOIN pg_roles r ON am.roleid = r.oid
WHERE am.member = (SELECT oid FROM pg_roles WHERE rolname = current_user);

Run each query and compare the output to what you expect. Nine times out of ten, one of these will reveal the mismatch between what your role has and what it needs.

Connection Pooler Role Switching

PgBouncer and Pgpool-II can be configured to authenticate connections as one role and then SET ROLE to another for the duration of the transaction. If your pooler is set up this way and the target role lacks the privileges the user expects, you get “permission denied for table” even though SELECT current_user looks correct from the application side. Check the pooler’s auth_user and pool_mode settings, and confirm whether the pooler issues a SET ROLE after authentication. PgBouncer in transaction pool mode in particular can surprise applications that set per-session GUCs and expect them to persist.

Materialized View Refresh Permissions

REFRESH MATERIALIZED VIEW requires OWNER on the view, not just SELECT. Even a role with full table privileges on the underlying tables and SELECT on the view cannot refresh it. Either change the view owner with ALTER MATERIALIZED VIEW my_view OWNER TO refresh_role, or wrap the refresh in a SECURITY DEFINER function owned by the view owner. REFRESH MATERIALIZED VIEW CONCURRENTLY additionally requires a unique index on the view.

Foreign Data Wrapper and Foreign Server Permissions

If your table is actually a foreign table (postgres_fdw, mysql_fdw, etc.), permission denied can originate from the remote side. The local user needs USAGE on the foreign server (GRANT USAGE ON FOREIGN SERVER my_server TO my_user) and the user mapping must point to a remote user with the corresponding privilege on the remote table. Errors from the remote side are wrapped and re-raised locally, sometimes losing the “remote” hint — check pg_user_mappings and the remote server’s logs if local grants look fine.

Logical Replication Subscriber Permissions

A logical replication subscription applies changes as the role specified in CREATE SUBSCRIPTION (defaults to the subscription owner). If that role lacks INSERT, UPDATE, or DELETE on a replicated table, the apply worker stalls and the replication slot starts retaining WAL. The error appears in the server log, not in the application. In Postgres 16+, you can specify a different run_as_owner setting per subscription; in older versions, ensure the subscription owner has full DML privileges on every replicated table.

Event Triggers and Audit Extensions

If your database uses pg_audit, pg_anonymize, or a custom event trigger, the trigger may revoke or override permissions at execution time. A BEFORE trigger that raises an exception or that uses SECURITY DEFINER to switch roles can produce a “permission denied” message that has nothing to do with GRANT state. Run SELECT * FROM pg_event_trigger to list active triggers and check whether any extension is intercepting your query.