How to query user group privileges in postgresql?

Question

So in postgresql I can do something like:

SELECT has_table_privilege('myuser', 'mytable', 'select')

to see whether myuser has select access on mytable. Is there something similar for user groups? Basically, I'd like to be able to submit a query to see if a group has certain privileges on a specified table.

Thanks!

A group is essentially the same thing as a user. Did you try: `SELECT has_table_privilege('mygroup', 'mytable', 'select')`? — , Jun 23 '14 at 10:17
Yeah, I've tried that. On Redshift, at least, this is not valid — rocket_raccoon, Jun 23 '14 at 17:06

Joachim Isaksson · Answer 1 · 2014-06-22T18:50:44.237

You could make a simple function to query role privileges;

CREATE FUNCTION role_has_table_privilege(g NAME, tn NAME, pt NAME) 
RETURNS boolean AS 
 'SELECT EXISTS (SELECT 1 FROM information_schema.role_table_grants WHERE (grantee, table_name, privilege_type) IN (($1, $2, $3)));' 
LANGUAGE sql;

It basically just selects role table grants from information_schema.role_table_grants and matches with the parameters. Roles include both users and groups.

An SQLfiddle to test with.

Note that in this very simple example has some limitations. For example, the values are case sensitive. That means the privilege type is called INSERT, not insert. Also, if you want to include PUBLIC permissions, you'll have to extend the function slightly.

score 2 · Answer 2 · edited May 23 '17 at 11:44

Joe's view looks like it was derived from How do I view grants on Redshift. Note this other useful query for finding missing permissions where in this example I want to grant read privs to the select_group to objects that don't have it:

select 'grant select on '||namespace||'.'||item||' to group select_group;' from 
(
SELECT 
 use.usename as subject, 
 nsp.nspname as namespace, 
 c.relname as item, 
 c.relkind as type, 
 use2.usename as owner, 
 c.relacl 
FROM 
pg_user use 
cross join pg_class c 
left join pg_namespace nsp on (c.relnamespace = nsp.oid) 
left join pg_user use2 on (c.relowner = use2.usesysid)
WHERE 
c.relowner = use.usesysid  
and  nsp.nspname  NOT IN ('pg_catalog', 'pg_toast', 'information_schema')
and ((array_to_string(relacl, '|') not like '%select_group%' or relacl is null)
    or 
   (array_to_string(relacl, '|') like '%select_group%' and CHARINDEX('r', SPLIT_PART( SPLIT_PART( ARRAY_TO_STRING( RELACL, '|' ), 'select_group', 2 ) , '/', 1 ) ) = 0 )
        )
and c.relkind   <> 'i'
ORDER BY  subject,   namespace,   item 
)

score 1 · Answer 3 · answered Jun 23 '14 at 10:12

Is this question about Redshift or Postgres? If it's Redshift then Joachim Isaksson's answer will not work because Redshift does not support functions.

I use the follow view to see table permissions. It's loosely based on something from Redshift forum but unfortunately I didn't make a note of the source.

/* List of all Grants currently in place. */
CREATE VIEW admin.vw_table_grants
AS
SELECT   relacl
        ,'GRANT ' 
        || SUBSTRING(
           CASE WHEN CHARINDEX('r', SPLIT_PART( SPLIT_PART( ARRAY_TO_STRING( RELACL, '|' ), pu.groname, 2 ) , '/', 1 ) ) > 0 THEN ', SELECT'     ELSE '' END
        || CASE WHEN CHARINDEX('w', SPLIT_PART( SPLIT_PART( ARRAY_TO_STRING( RELACL, '|' ), pu.groname, 2 ) , '/', 1 ) ) > 0 THEN ', UPDATE'     ELSE '' END
        || CASE WHEN CHARINDEX('a', SPLIT_PART( SPLIT_PART( ARRAY_TO_STRING( RELACL, '|' ), pu.groname, 2 ) , '/', 1 ) ) > 0 THEN ', INSERT'     ELSE '' END
        || CASE WHEN CHARINDEX('d', SPLIT_PART( SPLIT_PART( ARRAY_TO_STRING( RELACL, '|' ), pu.groname, 2 ) , '/', 1 ) ) > 0 THEN ', DELETE'     ELSE '' END
        || CASE WHEN CHARINDEX('R', SPLIT_PART( SPLIT_PART( ARRAY_TO_STRING( RELACL, '|' ), pu.groname, 2 ) , '/', 1 ) ) > 0 THEN ', RULE'       ELSE '' END
        || CASE WHEN CHARINDEX('x', SPLIT_PART( SPLIT_PART( ARRAY_TO_STRING( RELACL, '|' ), pu.groname, 2 ) , '/', 1 ) ) > 0 THEN ', REFERENCES' ELSE '' END
        || CASE WHEN CHARINDEX('t', SPLIT_PART( SPLIT_PART( ARRAY_TO_STRING( RELACL, '|' ), pu.groname, 2 ) , '/', 1 ) ) > 0 THEN ', TRIGGER'    ELSE '' END
        || CASE WHEN CHARINDEX('X', SPLIT_PART( SPLIT_PART( ARRAY_TO_STRING( RELACL, '|' ), pu.groname, 2 ) , '/', 1 ) ) > 0 THEN ', EXECUTE'    ELSE '' END
        || CASE WHEN CHARINDEX('U', SPLIT_PART( SPLIT_PART( ARRAY_TO_STRING( RELACL, '|' ), pu.groname, 2 ) , '/', 1 ) ) > 0 THEN ', USAGE'      ELSE '' END
        || CASE WHEN CHARINDEX('C', SPLIT_PART( SPLIT_PART( ARRAY_TO_STRING( RELACL, '|' ), pu.groname, 2 ) , '/', 1 ) ) > 0 THEN ', CREATE'     ELSE '' END
        || CASE WHEN CHARINDEX('T', SPLIT_PART( SPLIT_PART( ARRAY_TO_STRING( RELACL, '|' ), pu.groname, 2 ) , '/', 1 ) ) > 0 THEN ', TEMPORARY'  ELSE '' END
           , 3,10000)
        || ' ON '|| namespace ||'.'|| item ||' TO '|| pu.groname ||' ;' AS grantsql
FROM    (SELECT      use.usename AS subject
                    ,nsp.nspname AS namespace
                    ,cls.relname AS item
                    ,cls.relkind AS type
                    ,use2.usename AS owner
                    ,cls.relacl
        FROM        pg_user     use 
        CROSS JOIN  pg_class    cls
        LEFT JOIN   pg_namespace nsp 
        ON          cls.relnamespace = nsp.oid 
        LEFT JOIN   pg_user      use2 
        ON          cls.relowner = use2.usesysid
        WHERE       cls.relowner = use.usesysid
        AND         nsp.nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema')
        ORDER BY     subject
                    ,namespace
                    ,item ) 
JOIN    pg_group pu ON array_to_string(relacl, '|') LIKE '%'|| pu.groname ||'%' 
WHERE   relacl IS NOT NULL
ORDER BY 2

How to query user group privileges in postgresql?

3 Answers3

Linked