How to Find What Privileges a Group Has in Redshift

Query permissions for a specific table in redshift (Groups and Users)

I finally found something I was able to pick apart for groups and hash together something:

SELECT
    namespace, item, type, groname 
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' ) 
        AND nsp.nspname IN ( 'schema' ) 
        AND relacl IS NOT NULL 
    ORDER BY
        subject,
        NAMESPACE,
        item 
    )
    JOIN pg_group pu ON array_to_string( relacl, '|' ) LIKE'%' || pu.groname || '%'

take apart from How to query user group privileges in postgresql?

How do I view grants on Redshift

Another variation be like:

SELECT * 
FROM 
    (
    SELECT 
        schemaname
        ,objectname
        ,usename
        ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'select') AND has_schema_privilege(usrs.usename, schemaname, 'usage')  AS sel
        ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'insert') AND has_schema_privilege(usrs.usename, schemaname, 'usage')  AS ins
        ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'update') AND has_schema_privilege(usrs.usename, schemaname, 'usage')  AS upd
        ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'delete') AND has_schema_privilege(usrs.usename, schemaname, 'usage')  AS del
        ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'references') AND has_schema_privilege(usrs.usename, schemaname, 'usage')  AS ref
    FROM
        (
        SELECT schemaname, 't' AS obj_type, tablename AS objectname, schemaname + '.' + tablename AS fullobj FROM pg_tables
        WHERE schemaname not in ('pg_internal')
        UNION
        SELECT schemaname, 'v' AS obj_type, viewname AS objectname, schemaname + '.' + viewname AS fullobj FROM pg_views
        WHERE schemaname not in ('pg_internal')
        ) AS objs
        ,(SELECT * FROM pg_user) AS usrs
    ORDER BY fullobj
    )
WHERE (sel = true or ins = true or upd = true or del = true or ref = true)
and schemaname='<opt schema>'
and usename = '<opt username>';

Grant permissions to a group in Redshift for new tables in schema

To set the privileges that will be created for new tables you have to update the default privileges. See: https://docs.aws.amazon.com/redshift/latest/dg/r_ALTER_DEFAULT_PRIVILEGES.html

The default privileges "Defines the default set of access privileges to be applied to objects that are created in the future by the specified user. By default, users can change only their own default access privileges. Only a superuser can specify default privileges for other users." Grant only works on existing tables / objects.

Hope this helps you out.

---

Related Topics