PostgreSQL Permission Denied: Role Privileges Management

# PostgreSQL Permission Denied: Role Privileges Management

You're getting permission errors when your application or users try to access PostgreSQL:

ERROR:  permission denied for table users
ERROR:  permission denied for schema api
ERROR:  must be owner of relation products
ERROR:  permission denied to create database

Understanding PostgreSQL's privilege system is essential for database administration. Let's diagnose and fix permission issues systematically.

Understanding PostgreSQL Privileges

PostgreSQL has a layered permission system:

1. 1.Role membership - User belongs to a role
2. 2.Schema privileges - Access to schema
3. 3.Object privileges - Access to tables, views, functions
4. 4.Column privileges - Access to specific columns
5. 5.Row-level security - Access to specific rows

Quick Permission Check

```sql -- Current user SELECT current_user, session_user;

-- What roles am I a member of? SELECT r.rolname, r.rolsuper, r.rolcreaterole, r.rolcreatedb, r.rolcanlogin FROM pg_roles r WHERE r.rolname IN ( SELECT role.name FROM pg_auth_members m JOIN pg_roles role ON m.roleid = role.oid JOIN pg_roles member ON m.member = member.oid WHERE member.rolname = current_user ) OR r.rolname = current_user; ```

Diagnosing Permission Issues

Error: "permission denied for table"

```sql -- Check table privileges SELECT grantee, table_schema, table_name, privilege_type FROM information_schema.table_privileges WHERE table_name = 'users' ORDER BY grantee, privilege_type;

-- Check ownership SELECT n.nspname AS schema, c.relname AS table_name, pg_get_userbyid(c.relowner) AS owner, c.relkind AS type FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid WHERE c.relname = 'users';

-- Grant missing privileges GRANT SELECT, INSERT, UPDATE, DELETE ON users TO app_user;

-- Grant all on table GRANT ALL PRIVILEGES ON users TO app_admin; ```

Error: "permission denied for schema"

```sql -- Check schema privileges SELECT nspname AS schema, pg_get_userbyid(nspowner) AS owner, array_agg(privilege_type) AS privileges FROM pg_namespace n JOIN aclexplode(n.nspacl) AS a ON true WHERE nspname = 'api' GROUP BY nspname, nspowner;

-- Grant schema usage GRANT USAGE ON SCHEMA api TO app_user;

-- Grant create in schema GRANT CREATE, USAGE ON SCHEMA api TO app_developer; ```

Error: "must be owner of relation"

This error occurs when trying to modify an object you don't own:

```sql -- Check who owns the table SELECT n.nspname AS schema, c.relname AS table, pg_get_userbyid(c.relowner) AS owner FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid WHERE c.relname = 'products';

-- Option 1: Have the owner make the change SET ROLE table_owner; -- Make changes RESET ROLE;

-- Option 2: Transfer ownership ALTER TABLE products OWNER TO app_admin;

-- Option 3: Grant necessary privileges GRANT ALL PRIVILEGES ON products TO app_admin; ```

Error: "permission denied for column"

Column-level privileges can restrict access:

```sql -- Check column privileges SELECT grantee, table_name, column_name, privilege_type FROM information_schema.column_privileges WHERE table_name = 'users' ORDER BY column_name, privilege_type;

-- Grant column access GRANT SELECT (id, name, email) ON users TO app_readonly;

-- Revoke column access REVOKE SELECT (password_hash, salt) ON users FROM app_readonly; ```

Common Permission Scenarios

Scenario 1: Application User Setup

Create a proper application user with least privileges:

```sql -- Create role CREATE ROLE app_user WITH LOGIN PASSWORD 'secure_password' NOINHERIT;

-- Grant schema usage GRANT USAGE ON SCHEMA public TO app_user; GRANT USAGE ON SCHEMA app_schema TO app_user;

-- Grant table permissions GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app_schema TO app_user;

-- Grant sequence permissions (for SERIAL columns) GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app_schema TO app_user;

-- Set default privileges for future objects ALTER DEFAULT PRIVILEGES IN SCHEMA app_schema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user; ALTER DEFAULT PRIVILEGES IN SCHEMA app_schema GRANT USAGE, SELECT ON SEQUENCES TO app_user; ```

Scenario 2: Read-Only User

```sql -- Create read-only role CREATE ROLE readonly_user WITH LOGIN PASSWORD 'secure_password';

-- Grant schema usage GRANT USAGE ON SCHEMA public TO readonly_user;

-- Grant select on all tables GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

-- Set default for future tables ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user;

-- Prevent modifications (ensure no write permissions) REVOKE INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public FROM readonly_user; ```

Scenario 3: Admin Role with Superuser Access

```sql -- Create admin role CREATE ROLE app_admin WITH LOGIN PASSWORD 'secure_password' CREATEROLE CREATEDB;

-- Or make superuser (use sparingly) ALTER USER app_admin WITH SUPERUSER;

-- Grant ownership of existing objects DO $$ DECLARE r RECORD; BEGIN FOR r IN SELECT table_schema, table_name FROM information_schema.tables WHERE table_schema NOT IN ('pg_catalog', 'information_schema') LOOP EXECUTE format('ALTER TABLE %I.%I OWNER TO app_admin', r.table_schema, r.table_name); END LOOP; END $$; ```

Scenario 4: Multi-Tenant Access

Use row-level security for tenant isolation:

```sql -- Create tenant policy ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Create policy for tenant access CREATE POLICY tenant_isolation ON orders USING (tenant_id = current_setting('app.current_tenant')::integer);

-- Grant table access GRANT SELECT, INSERT, UPDATE, DELETE ON orders TO tenant_user;

-- Application sets tenant context SET app.current_tenant = '123'; -- Now queries automatically filter by tenant SELECT * FROM orders; -- Only sees tenant 123's orders ```

Managing Role Hierarchy

Create Role Groups

```sql -- Create base roles (no login) CREATE ROLE app_read WITH NOLOGIN; CREATE ROLE app_write WITH NOLOGIN; CREATE ROLE app_admin WITH NOLOGIN;

-- Grant permissions to base roles GRANT USAGE ON SCHEMA app_schema TO app_read; GRANT SELECT ON ALL TABLES IN SCHEMA app_schema TO app_read;

GRANT USAGE ON SCHEMA app_schema TO app_write; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app_schema TO app_write; GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app_schema TO app_write;

-- Create login roles CREATE USER analyst WITH PASSWORD 'password' IN ROLE app_read; CREATE USER developer WITH PASSWORD 'password' IN ROLE app_write; CREATE USER admin_user WITH PASSWORD 'password' IN ROLE app_admin, app_write, app_read;

-- Or grant roles after creation GRANT app_read TO analyst; GRANT app_write TO developer; GRANT app_admin TO admin_user; ```

Role Inheritance

```sql -- Check role inheritance SELECT rolname, rolinherit FROM pg_roles WHERE rolname = 'app_user';

-- Enable inheritance (default) ALTER ROLE app_user INHERIT;

-- Disable inheritance (must SET ROLE) ALTER ROLE app_user NOINHERIT;

-- With NOINHERIT, user must explicitly set role SET ROLE app_admin; ```

Function and Procedure Permissions

Function Execution Permissions

```sql -- Check function privileges SELECT routine_name, routine_type, data_type, security_type FROM information_schema.routines WHERE routine_schema = 'public';

-- Grant function execution GRANT EXECUTE ON FUNCTION calculate_total(integer) TO app_user;

-- Grant all functions in schema GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO app_user;

-- Security definer (runs as function owner) CREATE OR REPLACE FUNCTION sensitive_operation() RETURNS void SECURITY DEFINER -- Runs as function owner, not caller LANGUAGE plpgsql AS $$ BEGIN -- Has elevated privileges INSERT INTO audit_log (action) VALUES ('sensitive_operation'); END; $$;

-- Security invoker (runs as caller - default) CREATE FUNCTION safe_operation() RETURNS void SECURITY INVOKER -- Runs as caller LANGUAGE plpgsql AS $$ BEGIN -- Only has caller's privileges INSERT INTO user_actions (user_id, action) VALUES (current_user_id(), 'safe'); END; $$; ```

Trigger Function Permissions

-- Trigger functions should typically be SECURITY DEFINER
CREATE OR REPLACE FUNCTION audit_trigger()
RETURNS TRIGGER
SECURITY DEFINER
LANGUAGE plpgsql AS $$
BEGIN
    INSERT INTO audit_log (table_name, action, user_id, timestamp)
    VALUES (TG_TABLE_NAME, TG_OP, current_user_id, now());
    RETURN NEW;
END;
$$;

Troubleshooting Permission Issues

Comprehensive Permission Query

```sql -- View all privileges for a user SELECT 'TABLE' AS object_type, table_schema AS schema, table_name AS name, privilege_type AS privilege FROM information_schema.table_privileges WHERE grantee = 'app_user'

UNION ALL

SELECT 'SEQUENCE' AS object_type, sequence_schema AS schema, sequence_name AS name, privilege_type AS privilege FROM information_schema.usage_privileges WHERE grantee = 'app_user' AND object_type = 'SEQUENCE'

UNION ALL

SELECT 'SCHEMA' AS object_type, nspname AS schema, nspname AS name, privilege_type FROM pg_namespace n JOIN aclexplode(n.nspacl) a ON true WHERE pg_get_userbyid(a.grantee) = 'app_user'

ORDER BY object_type, schema, name; ```

Debug Permission Denied

```sql -- Function to check if a user has a specific privilege CREATE OR REPLACE FUNCTION has_privilege( user_name text, table_name text, privilege text ) RETURNS boolean AS $$ BEGIN RETURN ( SELECT has_table_privilege(user_name, table_name, privilege) ); END; $$ LANGUAGE plpgsql;

-- Use it SELECT has_privilege('app_user', 'users', 'SELECT'); SELECT has_privilege('app_user', 'users', 'INSERT');

-- Check all privileges for a table SELECT privilege_type, has_table_privilege('app_user', 'users', privilege_type) AS has_privilege FROM unnest(ARRAY['SELECT', 'INSERT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER']) AS privilege_type; ```

Check Effective Privileges

```sql -- Simulate another user to test permissions SET ROLE app_user;

-- Try the operation SELECT * FROM users;

-- Check current privileges SELECT current_user, session_user; SELECT * FROM pg_user WHERE usename = current_user;

-- Reset role RESET ROLE; ```

Fixing Common Permission Issues

Fix Ownership Chain

```sql -- Find objects owned by wrong user SELECT n.nspname AS schema, c.relname AS object, c.relkind AS type, pg_get_userbyid(c.relowner) AS owner FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid WHERE pg_get_userbyid(c.relowner) = 'postgres' AND n.nspname NOT LIKE 'pg_%' ORDER BY n.nspname, c.relname;

-- Fix ownership in bulk DO $$ DECLARE r RECORD; BEGIN FOR r IN SELECT table_schema, table_name FROM information_schema.tables WHERE table_schema = 'app_schema' LOOP EXECUTE format('ALTER TABLE %I.%I OWNER TO app_admin', r.table_schema, r.table_name); END LOOP; END $$; ```

Grant Permissions for All Objects

```sql -- Grant all privileges on all existing objects DO $$ DECLARE r RECORD; BEGIN -- Tables FOR r IN SELECT table_schema, table_name FROM information_schema.tables WHERE table_schema NOT IN ('pg_catalog', 'information_schema') LOOP EXECUTE format('GRANT ALL PRIVILEGES ON TABLE %I.%I TO app_admin', r.table_schema, r.table_name); END LOOP;

-- Sequences FOR r IN SELECT sequence_schema, sequence_name FROM information_schema.sequences WHERE sequence_schema NOT IN ('pg_catalog', 'information_schema') LOOP EXECUTE format('GRANT ALL PRIVILEGES ON SEQUENCE %I.%I TO app_admin', r.sequence_schema, r.sequence_name); END LOOP;

-- Functions FOR r IN SELECT proname, oidvectortypes(proargtypes) AS args FROM pg_proc p JOIN pg_namespace n ON p.pronamespace = n.oid WHERE n.nspname NOT IN ('pg_catalog', 'information_schema') LOOP EXECUTE format('GRANT ALL PRIVILEGES ON FUNCTION %I(%s) TO app_admin', r.proname, r.args); END LOOP; END $$; ```

Verification

After making permission changes:

```sql -- Verify user can connect psql -U app_user -d mydb -c "SELECT current_user;"

-- Verify table access SELECT has_table_privilege('app_user', 'users', 'SELECT') AS can_select, has_table_privilege('app_user', 'users', 'INSERT') AS can_insert, has_table_privilege('app_user', 'users', 'UPDATE') AS can_update, has_table_privilege('app_user', 'users', 'DELETE') AS can_delete;

-- Verify schema access SELECT has_schema_privilege('app_user', 'app_schema', 'USAGE') AS can_use, has_schema_privilege('app_user', 'app_schema', 'CREATE') AS can_create;

-- Test actual operation SET ROLE app_user; SELECT * FROM users LIMIT 1; INSERT INTO users (name) VALUES ('test'); RESET ROLE; ```

Quick Reference