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

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, see the PostgreSQL connection refused guide instead.

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.

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.