permission denied for table

This error occurs when the database user doesn't have the required permissions for the requested operation.

The permission denied error means your user lacks the required privileges.

Understanding the Error

ERROR: permission denied for table users
ERROR: permission denied for schema public

The current user doesn't have permission for this operation on this object.

Common Causes

1. User Lacks Table Permissions

SQL

-- User doesn't have SELECT privilege
SELECT * FROM users;  -- Error!

2. User Lacks Schema Permissions

SQL

-- User can't access the schema
SELECT * FROM myschema.users;  -- Error!

3. Wrong Owner

User isn't the owner and hasn't been granted permissions.

4. Row Level Security (RLS)

RLS policies blocking access.

How to Fix It

Solution 1: Grant Table Permissions

SQL

-- Grant specific permissions
GRANT SELECT ON users TO myuser;
GRANT INSERT, UPDATE ON users TO myuser;
GRANT ALL ON users TO myuser;

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

Solution 2: Grant Schema Permissions

SQL

-- Allow user to access schema
GRANT USAGE ON SCHEMA myschema TO myuser;

-- Allow user to create objects in schema
GRANT CREATE ON SCHEMA myschema TO myuser;

Solution 3: Grant Future Permissions

SQL

-- Auto-grant permissions for future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO myuser;

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

Solution 4: Change Table Owner

SQL

-- Change owner of table
ALTER TABLE users OWNER TO myuser;

-- Change owner of all tables
DO $$
DECLARE
  tbl RECORD;
BEGIN
  FOR tbl IN SELECT tablename FROM pg_tables WHERE schemaname = 'public'
  LOOP
    EXECUTE format('ALTER TABLE %I OWNER TO myuser', tbl.tablename);
  END LOOP;
END $$;

Solution 5: Grant Sequence Permissions

SQL

-- For SERIAL/IDENTITY columns
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO myuser;

-- For future sequences
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT USAGE, SELECT ON SEQUENCES TO myuser;

Solution 6: Create Role with All Permissions

SQL

-- Create a role with full access
CREATE ROLE app_full_access;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app_full_access;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO app_full_access;
GRANT USAGE, CREATE ON SCHEMA public TO app_full_access;

-- Grant role to user
GRANT app_full_access TO myuser;

Checking Permissions

SQL

-- Check table permissions
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name = 'users';

-- Check current user's privileges
SELECT * FROM information_schema.table_privileges
WHERE grantee = current_user;

-- List roles and their members
\du

-- List table permissions in psql
\dp users

Best Practices

Use roles for permission management
Grant minimum required permissions
Use DEFAULT PRIVILEGES for new objects
Document permission requirements
Audit permissions regularly