Revoke in Redshift not removing access to schema

Question

I am trying to remove a user's permissions to multiple schemas to which they had previously been granted access via security groups. However, using the revoke command is not working to do that.

User, KYKE, had been granted access to a number of schemas via a security group.

Removed user from group.... alter group BAMKT drop user KYKE. My DB client will generate the SQL to build each security group and when I do so I can confirm that user, KYKE, is no longer associated with group BAMKT.

However, when I run this sql.. SELECT distinct derived_table1.schemaname, --derived_table1.objectname, derived_table1.usename username, 'USER' usertype , derived_table1.select_flag, derived_table1.insert_flag, derived_table1.update_flag, derived_table1.delete_flag, derived_table1.reference_flag FROM ( SELECT objs.schemaname, objs.objectname, usrs.usename, CASE WHEN has_table_privilege(usrs.usename, objs.fullobj::text, 'select'::text) THEN 1 ELSE 0 END AS select_flag, CASE WHEN has_table_privilege(usrs.usename, objs.fullobj::text, 'insert'::text) THEN 1 ELSE 0 END AS insert_flag, CASE WHEN has_table_privilege(usrs.usename, objs.fullobj::text, 'update'::text) THEN 1 ELSE 0 END AS update_flag, CASE WHEN has_table_privilege(usrs.usename, objs.fullobj::text, 'delete'::text) THEN 1 ELSE 0 END AS delete_flag, CASE WHEN has_table_privilege(usrs.usename, objs.fullobj::text, 'references'::text) THEN 1 ELSE 0 END AS reference_flag FROM ( SELECT pg_tables.schemaname, 't'::character varying AS obj_type, pg_tables.tablename AS objectname, (pg_tables.schemaname::text + '.'::text + pg_tables.tablename::text)::character varying AS fullobj FROM pg_tables UNION SELECT pg_views.schemaname, 'v'::character varying AS obj_type, pg_views.viewname AS objectname, (pg_views.schemaname::text + '.'::text + pg_views.viewname::text)::character varying AS fullobj FROM pg_views) objs, ( SELECT pg_user.usename FROM pg_user where usename = 'KYKE' ) usrs ORDER BY objs.fullobj) derived_table1 WHERE (derived_table1.select_flag + derived_table1.insert_flag + derived_table1.update_flag + derived_table1.delete_flag + derived_table1.reference_flag) > 0 and schemaname not in ('information_schema','pg_catalog', 'pg_automv')

...full code shorted for breviety.

The user KYKE still has access to a number of schemas to which he previously had been granted access via the BAMKT security group.

So then I attempt to remove access to each schema using the following commands: revoke all privileges on all tables in schema BAMKT from KYKE revoke usage on schema BAMKT from KYKE

Commands complete without failure but still user KYKE continues to have access to schema BAMKT

I want to remove all the users permissions so that if the account owns some object and/or is used in a Tableau report, I can recreate permissions without also recreating the account and password.

So what am I doing wrong? Why can't I remove KYKE's access to any schemas?

Answer 1

The same privilege can be granted from multiple sources.

You are revoking that privilege from some but not all sources.

You need to be able to enumerate the privileges held by the user, and show where they come from, so you can revoke them.

Until very recently, this was not possible.

However, recently, a system table was added which shows which privs are held by which users and where they come from.

An alternative is to use Combobulator (Google "Redshift Combobulator"), which is a browser-based PII and regulatory safe Redshift management application, distributed as an AMI, and which has pages showing the priv information you need (and in fact does not use that new system table, but figures out priv info directly from the Postgres system tables).