Using redshift system views with select pivot

Question

I'd like to pivot the values in the system views svv_*_privileges to show privileges assigned to security principles by object. (For the complete solution will need to union results for all objects and pivot to all privileges)

as an example for the default privileges:

select * from
(select object_type, grantee_name, grantee_type, privilege_type, 1 as is_priv from pg_catalog.svv_default_privileges where grantee_name = 'abc' and grantee_type = 'role')
pivot (max(is_priv) for privilege_type in ('EXECUTE', 'INSERT', 'SELECT', 'UPDATE', 'DELETE', 'RULE', 'REFERENCES', 'TRIGGER', 'DROP') );

This gives error (only valid on leader node?)

[Amazon](500310) Invalid operation: Query unsupported due to an internal error.

Then thought of trying a temp table, pivot then being on a redshift table

select * into temp schema_default_priv from pg_catalog.svv_default_privileges where grantee_name = 'abc' and grantee_type = 'role'

... same error as above :-(

Is there a way I can work with SQL on the system tables to accomplish this in Redshift SQL????

While I can do the pivot in python ... why should I, It's supposedly a sql db!!!

Some more info on the error message would help / why do you mention leader node? My first guess would be that this is due to the SQL provided (non-table data) for is_priv. Redshift has restrictions on data coming from the leader being used on compute nodes. This should have been addressed by your temp table approach. Can you share the SQL you used for this? — Bill Weiner, Aug 03 '22 at 17:09

score 0 · Answer 1 · answered Aug 03 '22 at 20:13

On reread of your question the issue became clear. You are using a leader node only system table and looking to apply compute node data and/or functions. This path of data flow is not supported on Redshift. I do have some question as to what action is requiring compute node action but that isn't the crucial and digging is would take time.

If you need to get leader node data to the compute nodes there are a few ways and none of them are trivial. I find that the best method is to move the needed data is to use a cursor. This previous answer outlines hot to do this

How to join System tables or Information Schema tables with User defined tables in Redshift

Using redshift system views with select pivot

1 Answers1