Overview
This guide covers the complete process for remediating Redshift user permissions as part of quarterly user access reviews. When users leave the company or their access needs change, we receive tickets with specific Schema-Permission attributes that need to be removed.
Out of the box, Redshift doesn’t make user permission management easy – especially when dealing with default privileges, object ownership, and the various ways permissions can be granted. This guide provides a systematic approach to handle all the edge cases you’ll encounter.
Step 1: Comprehensive User Audit
Always start by understanding the current state. Run this comprehensive audit to see all permissions:
-- Replace 'john.doe' with the target username
select user_name, schema_name, super_user, has_create, has_insert, has_update, has_delete, has_select, has_references, valuntil from ( select u.usename user_name, u.usesuper super_user, s.schemaname schema_name, has_schema_privilege(u.usename,s.schemaname,'create') has_create, has_table_privilege(u.usename,s.schemaname||'.'||s.tablename,'insert') has_insert, has_table_privilege(u.usename,s.schemaname||'.'||s.tablename,'update') has_update, has_table_privilege(u.usename,s.schemaname||'.'||s.tablename,'delete') has_delete, has_table_privilege(u.usename,s.schemaname||'.'||s.tablename,'select') has_select, has_table_privilege(u.usename,s.schemaname||'.'||s.tablename,'references') has_references, valuntil from pg_user u CROSS join ( SELECT DISTINCT schemaname, tablename FROM pg_tables where schemaname not like 'pg_%' and tablename not like '%newsletter_exp_prior_lookback_temptable%' ) s where (super_user = 1 or has_create = 1 or has_insert = 1 or has_update = 1 or has_delete = 1 or has_select = 1 or has_references = 1 ) and (u.valuntil > NOW() or u.valuntil is NULL) and u.usename = 'john.doe' ) group by user_name, schema_name, super_user, has_create, has_insert, has_update, has_delete, has_select, has_references, valuntil order by user_name, schema_name, has_select, has_create, has_insert, has_update, has_delete;
Step 2: Check for Object Ownership
Critical: If the user owns any tables, views, or functions, they need to be reassigned before permissions can be fully revoked:
select * from ( SELECT n.nspname AS schema_name, c.relname AS rel_name, c.relkind AS rel_kind, pg_get_userbyid(c.relowner) AS owner_name FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace UNION ALL SELECT n.nspname AS schema_name, p.proname, 'p', pg_get_userbyid(p.proowner) FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace ) sub1 where owner_name = 'john.doe';
If this returns results, you’ll need to reassign ownership:
-- Example: Reassign table ownership to a service account
ALTER TABLE schema_name.table_name OWNER TO service_account;
Step 3: Check for Default Privileges
Critical for ETL accounts: Users with default privileges cannot be dropped until these are cleaned up. Check for default privileges:
select pg_get_userbyid(d.defacluser) as user, n.nspname as schema, decode(d.defaclobjtype, 'r', 'tables', 'f', 'functions') as object_type, array_to_string(d.defaclacl, ' + ') as default_privileges from pg_catalog.pg_default_acl d left join pg_catalog.pg_namespace n on n.oid = d.defaclnamespace where array_to_string(defaclacl, ',') like '%john.doe%';
If default privileges exist, you must clean them up using this workaround (due to a PostgreSQL bug):
-- Grant temporary CREATE permission (required due to PostgreSQL bug)
grant create on schema ops to john.doe;
-- Revoke the default privileges
alter default privileges for user john.doe in schema ops revoke all privileges on tables from group ops, group engineering;
-- Remove the temporary CREATE permission
revoke create on schema ops from john.doe;
Step 4: Identify Group Memberships
Find all groups the user belongs to and generate removal statements:
SELECT u.usesysid, g.groname, u.usename, 'ALTER GROUP "' || g.groname || '" DROP USER "' || u.usename || '";' as drop_statement FROM pg_user u LEFT JOIN pg_group g ON u.usesysid = ANY (g.grolist) WHERE u.usename = 'john.doe';
Step 5: Remove Group Memberships
Execute the group removal statements. Based on typical Schema-Permission patterns:
-- Remove user from read-only groups (SELECT permissions)
ALTER GROUP "ops" DROP USER "john.doe";
ALTER GROUP "person" DROP USER "john.doe";
-- Remove user from groups with broader permissions
ALTER GROUP "nonpii" DROP USER "john.doe";
ALTER GROUP "nonpii_readwrite" DROP USER "john.doe";
Step 6: Handle Direct Table Permissions
Some users may have been granted direct permissions on specific tables. This query will find them and generate REVOKE statements:
with users as ( select 'john.doe'::text as username ) select 'REVOKE ALL PRIVILEGES ON TABLE ' || pg_namespace.nspname || '.' || pg_class.relname || ' FROM ' || u.username || ';' as revoke_statement, pg_namespace.nspname as schemaname, pg_class.relname as tablename, array_to_string(pg_class.relacl, ',') as acls from pg_class left join pg_namespace on pg_class.relnamespace = pg_namespace.oid join users u on (array_to_string(pg_class.relacl, ',') like '%' || u.username || '=%') where pg_class.relacl is not null and pg_namespace.nspname not in ('pg_catalog', 'pg_toast', 'information_schema');
Execute the generated REVOKE statements:
-- Example output from above query
REVOKE ALL PRIVILEGES ON TABLE ops.claims_grading FROM john.doe; REVOKE ALL PRIVILEGES ON TABLE person.user_segments FROM john.doe;
Step 7: Handle Schema-Level Permissions
Remove any direct schema-level permissions:
-- Revoke CREATE permissions
REVOKE CREATE ON SCHEMA ops FROM "john.doe";
REVOKE CREATE ON SCHEMA person FROM "john.doe";
-- Revoke USAGE permissions
REVOKE USAGE ON SCHEMA ops FROM "john.doe";
REVOKE USAGE ON SCHEMA person FROM "john.doe";
Step 8: Comprehensive Verification
After remediation, verify all permissions have been removed:
-- Check for any remaining table permissions
SELECT n.nspname AS schema_name, c.relname AS table_name, u.usename AS username, has_table_privilege(u.usename, c.oid, 'SELECT') AS has_select, has_table_privilege(u.usename, c.oid, 'INSERT') AS has_insert, has_table_privilege(u.usename, c.oid, 'UPDATE') AS has_update, has_table_privilege(u.usename, c.oid, 'DELETE') AS has_delete, 'REVOKE ALL ON "' || n.nspname || '"."' || c.relname || '" FROM "' || u.usename || '";' as cleanup_statement, 'SHOW GRANTS ON TABLE "' || n.nspname || '"."' || c.relname || '";' as verification_statement FROM pg_catalog.pg_namespace n JOIN pg_catalog.pg_class c ON n.oid = c.relnamespace CROSS JOIN pg_catalog.pg_user u WHERE u.usename = 'john.doe' AND n.nspname IN ('ops', 'personalization') AND (has_table_privilege(u.usename, c.oid, 'SELECT') = true OR has_table_privilege(u.usename, c.oid, 'INSERT') = true OR has_table_privilege(u.usename, c.oid, 'UPDATE') = true OR has_table_privilege(u.usename, c.oid, 'DELETE') = true) AND c.relkind = 'r' ORDER BY n.nspname ASC;
If this query returns no rows, the remediation was successful.
Step 9: Final Verification
Run these final checks to ensure complete cleanup:
-- Verify no group memberships remain
SELECT u.usesysid, g.groname, u.usename FROM pg_user u LEFT JOIN pg_group g ON u.usesysid = ANY (g.grolist) WHERE u.usename = 'john.doe' AND g.groname IS NOT NULL;
-- Show any remaining grants
SHOW GRANTS FOR "john.doe";
-- Check specific tables if needed
SHOW GRANTS ON TABLE "ops"."claims_grading";
Advanced Troubleshooting
Case 1: User Still Has Access After All Revocations
This usually means permissions were granted to PUBLIC. Check and revoke:
-- Check for PUBLIC grants on specific problematic tables
SHOW GRANTS ON TABLE ops.claims_grading;
-- If PUBLIC has access, revoke it (this is the "nuclear option")
REVOKE ALL ON ops.claims_grading FROM PUBLIC;
Case 2: Cannot Drop User Due to Default Privileges
If you see this error:
ERROR: user "username" cannot be dropped because some objects depend on it DETAIL: owner of default privileges on new relations belonging to user username in schema schema_name
Follow the default privileges cleanup process in Step 3. This is a known PostgreSQL limitation that requires the temporary CREATE permission workaround.
Case 3: Permission Denied When Revoking Default Privileges
If you get “permission denied for schema” when trying to revoke default privileges, you need to temporarily grant CREATE permissions first (see Step 3). This is due to a semi-bug in PostgreSQL.
Case 4: Complex Permission Inheritance
Sometimes users inherit permissions through multiple group memberships or nested groups. In these cases:
- Run the comprehensive audit query multiple times during remediation
- Check for indirect permissions through role inheritance
- Verify that group memberships are removed (some systems cache group information)
Best Practices
- Always follow the order: Object ownership → Default privileges → Group memberships → Direct permissions → Schema permissions → Verification
- Document edge cases: Each remediation teaches you something new. Keep notes on unusual patterns.
- Test in non-production first: For complex users (especially ETL accounts), test the remediation process in a non-production environment.
- Handle default privileges immediately: Don’t wait until user departure to clean up default privileges – they’re the most significant source of complications.
- Use the verification queries: The verification step isn’t optional – it’s the only way to be specific remediation was successful.
- Check PUBLIC permissions last: If a user still has unexpected access, PUBLIC permissions are usually the culprit.
Emergency Procedures
For urgent security situations, you can disable a user account immediately:
-- Disable the account (prevents login but doesn't remove permissions)ALTER USER "john.doe" VALID UNTIL '1900-01-01';
-- Then follow the normal remediation process when time permits
If You Accidentally Revoke Too Much
If you accidentally remove permissions that should remain:
- Check the original ticket carefully – what should actually be removed?
- Re-grant the appropriate group memberships
- Verify using the audit query that permissions match expectations
- Document the mistake to prevent future occurrences
This comprehensive approach ensures that user permissions are properly remediated while handling all the edge cases that make Redshift user management challenging.
Database Blog 