How to Fix PostgreSQL Error: Permission Denied for Relation

The "Permission denied for relation" error occurs when a user attempts to access a table, view, or other database relation without having the necessary privileges. This is a security feature in PostgreSQL that enforces access control and prevents unauthorized data access.

Impact

This error blocks data operations (SELECT, INSERT, UPDATE, DELETE) on specific tables, preventing applications from functioning correctly. It can cause partial application failures where some features work while others fail, making troubleshooting more complex.

Common Causes

1. User lacks appropriate table privileges (SELECT, INSERT, UPDATE, DELETE)
2. Schema permissions not granted
3. Object owned by different user
4. Row-level security policies blocking access
5. Revoked privileges after initial grant
6. Missing permissions on sequences (for INSERT operations)
7. Application using different user than expected

Troubleshooting and Resolution Steps

1. Check current privileges on the relation:

   -- Check table privileges
   \dp table_name
   
   -- Or use SQL
   SELECT grantee, privilege_type
   FROM information_schema.table_privileges
   WHERE table_schema = 'public' AND table_name = 'your_table';
   

2. Grant SELECT privilege:

   GRANT SELECT ON table_name TO username;
   
   -- Grant on all tables in schema
   GRANT SELECT ON ALL TABLES IN SCHEMA public TO username;
   

3. Grant multiple privileges:

   -- Grant read and write access
   GRANT SELECT, INSERT, UPDATE, DELETE ON table_name TO username;
   
   -- Grant all privileges
   GRANT ALL PRIVILEGES ON table_name TO username;
   

4. Grant schema usage permission:

   -- Users need USAGE on schema to access objects
   GRANT USAGE ON SCHEMA public TO username;
   
   -- For custom schemas
   GRANT USAGE ON SCHEMA myschema TO username;
   GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO username;
   

5. Grant privileges on future objects:

   -- Ensure user gets permissions on newly created tables
   ALTER DEFAULT PRIVILEGES IN SCHEMA public
   GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO username;
   
   -- For specific role creating objects
   ALTER DEFAULT PRIVILEGES FOR ROLE admin IN SCHEMA public
   GRANT SELECT ON TABLES TO username;
   

6. Grant sequence permissions (needed for SERIAL/IDENTITY columns):

   -- Grant usage on specific sequence
   GRANT USAGE ON SEQUENCE table_id_seq TO username;
   
   -- Grant on all sequences
   GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO username;
   
   -- Include in default privileges
   ALTER DEFAULT PRIVILEGES IN SCHEMA public
   GRANT USAGE ON SEQUENCES TO username;
   

7. Check and disable row-level security if necessary:

   -- Check if RLS is enabled
   SELECT tablename, rowsecurity
   FROM pg_tables
   WHERE schemaname = 'public' AND tablename = 'your_table';
   
   -- Disable RLS (if appropriate)
   ALTER TABLE your_table DISABLE ROW LEVEL SECURITY;
   
   -- Or create permissive policy
   CREATE POLICY allow_all ON your_table FOR ALL TO username USING (true);
   

8. Transfer object ownership if needed:

   -- Change table owner
   ALTER TABLE table_name OWNER TO new_owner;
   
   -- Change all tables in schema
   REASSIGN OWNED BY old_owner TO new_owner;
   

9. Create a comprehensive role for application access:

   -- Create application role
   CREATE ROLE app_user WITH LOGIN PASSWORD 'secure_password';
   
   -- Grant necessary privileges
   GRANT CONNECT ON DATABASE mydb TO app_user;
   GRANT USAGE ON SCHEMA public TO app_user;
   GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
   GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app_user;
   
   -- Future objects
   ALTER DEFAULT PRIVILEGES IN SCHEMA public
   GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;
   ALTER DEFAULT PRIVILEGES IN SCHEMA public
   GRANT USAGE ON SEQUENCES TO app_user;
   

10. Verify user connection and permissions:

    -- Check current user
    SELECT current_user, session_user;
    
    -- Check effective privileges
    SELECT has_table_privilege('username', 'table_name', 'SELECT');
    SELECT has_table_privilege('username', 'table_name', 'INSERT');
    

Additional Information

• The public schema is accessible by default, but tables within it still require explicit grants
• PUBLIC role represents all users; granting to PUBLIC gives access to everyone
• Use roles (groups) to manage permissions efficiently for multiple users
• Implement principle of least privilege: grant only necessary permissions
• Regular permission audits help identify and fix security issues
• Document your permission model for team reference

Frequently Asked Questions

Q: What's the difference between USAGE and SELECT permissions?
A: USAGE grants access to a schema (like entering a building), while SELECT grants the ability to read data from tables within that schema (like reading specific documents).

Q: How do I grant permissions on all existing and future tables?
A: Use two commands:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO username;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO username;

Q: Can I grant permissions to multiple users at once?
A: Yes, create a role (group), grant permissions to the role, then add users to that role:

CREATE ROLE app_readers;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readers;
GRANT app_readers TO user1, user2, user3;

Q: Why can't I INSERT even though I have INSERT permission?
A: You also need USAGE permission on the sequence associated with SERIAL or IDENTITY columns. Grant with: GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO username;

Q: How do I see all permissions for a specific user?
A: Use:

\du+ username
SELECT * FROM information_schema.role_table_grants WHERE grantee = 'username';

Q: What's the most secure way to grant application access?
A: Create a dedicated role with minimal privileges, grant only to specific tables/columns needed, use row-level security for data isolation, and regularly audit permissions.