WHERE ARRAY_TO_STRING(nspacl,',') LIKE '%' ||pg.groname|| '%' WHEN charindex ('U',SPLIT_PART(SPLIT_PART(ARRAY_TO_STRING(nspacl,'|'),pg.groname,2),'/',1)) > 0 THEN ' USAGE 'ĮND ||case WHEN charindex('C',SPLIT_PART(SPLIT_PART(ARRAY_TO_STRING(nspacl,'|'),pg.groname,2),'/',1)) > 0 THEN ' CREATE' ELSE '' END as rights, WHERE ARRAY_TO_STRING(nspacl,',') LIKE '%' ||pu.usename|| '%' WHEN charindex ('U',SPLIT_PART(SPLIT_PART(ARRAY_TO_STRING(nspacl,'|'),pu.usename,2),'/',1)) > 0 THEN ' USAGE'ĮND ||case WHEN charindex('C',SPLIT_PART(SPLIT_PART(ARRAY_TO_STRING(nspacl,'|'),pu.usename,2),'/',1)) > 0 THEN ' CREATE' ELSE '' END AS rights, So i have similar to schema privileges queries for table, views, columns, sequences, functions, database and even default privileges.Īlso, there is helpful extension pg_permission where I get logic for provided queries and just upgraded it for my purposes.Ĭombined version (groups, users, PUBLIC) that works for AWS Redshift: SELECT * Where oid_to_rolname() is simple custom function SELECT rolname FROM pg_roles WHERE oid = $1.Īnd, like my task required to have all privileges which all users have. So i've got possible workaround using aclexplode() default function like this: SELECT oid_to_rolname(a.grantee) AS role_name,ĪND oid_to_rolname(a.grantee) IS NOT NULLīut, be careful, last one doesn't include privileges which users have obtained from PUBLIC role. WHERE has_schema_privilege(r.oid, n.oid, p.perm)Ĭould be pretty low in performance at database with a lot of objects and users with which I have come across. Where array_to_string(nspacl,',') like '%'||pg.groname||'%' -and pg.groname=''įor current question can try this one: SELECT r.rolname AS role_name,ĬROSS JOIN (VALUES ('USAGE'), ('CREATE')) AS p(perm) ||case when charindex('C',split_part(split_part(array_to_string(nspacl, '|'),pg.groname,2 ) ,'/',1)) > 0 then ',create ' else '' end Where array_to_string(nspacl,',') like '%'||pu.usename||'%' -and pu.usename=''Ĭase when charindex('U',split_part(split_part(array_to_string(nspacl, '|'),pg.groname,2 ) ,'/',1)) > 0 then ',usage ' else '' end ||case when charindex('C',split_part(split_part(array_to_string(nspacl, '|'),pu.usename,2 ) ,'/',1)) > 0 then ',create ' else '' end This query will give you the grant statements used for users and groups: selectĬase when charindex('U',split_part(split_part(array_to_string(nspacl, '|'),pu.usename,2 ) ,'/',1)) > 0 then ',usage ' else '' end Since it's an array field, you have to do a little fancy coding to parse it. The privileges are stored in the nspacl field of pg_namespace.

WITH | System Information Functions | GRANT (privileges) 'name' = pg_catalog.current_schema() AS 'current' Pg_catalog.has_schema_privilege(current_user, 'name', 'USAGE') AS 'usage', Like the current_schema() the current_user can be replaced with particular role.īONUS with current column WITH 'names'('name') AS ( The only CREATE and USAGE allowed for schemas. In this example current user is not owner of the awesome schema.Īs you could guess, similar request for particular schema: SELECTĬurrent_user, 'awesome', 'CREATE') AS 'create',Ĭurrent_user, 'awesome', 'USAGE') AS 'usage' Īs you know, it's possible to use pg_catalog.current_schema() for current schema. The response will be for example: name | create | usage

Pg_catalog.has_schema_privilege(current_user, 'name', 'USAGE') AS 'usage' Pg_catalog.has_schema_privilege(current_user, 'name', 'CREATE') AS 'create', Using this method we will select the fields we want directly from the pg_user table.List all schemas with their priveleges for current user: WITH 'names'('name') AS (

There is another way to achieve the same result using SQL. Note that this may be different on your computer. We get an output username and user_attribute with a user named postgres who has the attribute 'superuser'. Line 9: Lastly, we order the result in ascending order.

Line 8: We'll fetch all the results from the pg_user table which gives us access to information about database users. Line 7: We commit this transaction using the END keyword. Line 4: We print out the 'superuser' using CAST here because we have to perform a conversion between two data types. Line 3: The usesuper is a boolean type for a 'superuser', so what we are saying here is that if the user is a 'superuser', perform the operations on lines 4 and 6. Line 2: We introduced the CASE expression, which is the same as the if/else statement in most programming languages. Line 1: We select the usename using an alias username.

List user postgres