amazon-web-servicesamazon-redshiftamazon-redshift-spectrumdatabase-metadata

Redshift - Group Level Permissions on a Schema


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

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

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

OR

Thanks!


Solution

  • 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>'