Redshift - Group Level Permissions on a Schema

Question

I created two Groups (Confirmed using SELECT groname FROM pg_group)

• Test_Group_A
• Test_Group_AB

Created two External Schemas (Confirmed using SELECT schemaname FROM svv_external_schemas)

• External_Schema_A
• External_Schema_B

Granted permissions to these groups on external schemas, as follows:

GRANT USAGE ON SCHEMA External_Schema_A TO GROUP Test_Group_A;

GRANT USAGE ON SCHEMA External_Schema_A TO GROUP Test_Group_AB;
GRANT USAGE ON SCHEMA External_Schema_B TO GROUP Test_Group_AB;

Using metadata, how do I get the list of

• Schemas that Test_Group_X and Test_Group_XY can access

OR

• Groups that have access to External_Schema_X and External_Schema_Y

Thanks!

Answer 1

The easiest way I would think would be to operate as a member of these groups and test the permissions. The easiest way to "become" another user (while connected as a superuser) is "set session authorization 'user_name';". This will give you the rights and authorization of this user. When done you can "reset session authorization;".

Answer 2

SELECT
    *
FROM
    (
        SELECT
            pg_get_userbyid(b.nspowner)::text AS objowner,
            b.nspname::text AS objname,
            TRIM(SPLIT_PART(array_to_string(b.nspacl, ','), ',', NS.n))::text AS access_control_list_string
        FROM
            (
                SELECT
                    oid,
                    generate_series(1, array_upper(nspacl, 1)) AS n
                FROM
                    pg_catalog.pg_namespace
            )
            ns
            INNER JOIN
                pg_catalog.pg_namespace B
                ON b.oid = ns.oid
                AND ns.n <= array_upper(b.nspacl, 1)
    )
WHERE
    objname = '<external_schema_name>'