Is there a way to copy or clone grants between tables in Redshift?

Question

Due to the append-only nature of Redshift, we often need to consolidate tables to remove duplicate records and surface the most recent version of a record. To prepare for this process, the base table is renamed and a copy of it is made. A script will execute to scan through the insert table and move the most recent unique records into the new base table. However, if the table has been running for awhile prior to this process beginning, there may be grants on the table that are not copied over when the clone is created.

We often do not have the original table DDL, and though we are able to get it via https://github.com/awslabs/amazon-redshift-utils/blob/master/src/AdminViews/v_generate_tbl_ddl.sql this will not generate the table definition with any grants.

Is there a way to copy a table with grants in-place, or generate a script to create the grants based on the old table?

Have tried using https://github.com/awslabs/amazon-redshift-utils/blob/master/src/AdminViews/v_generate_tbl_ddl.sql but this does not generate any grant statements, only the set owner statement.

Have also looked into has_table_privilege but this doesn't seem to be the simplest course of action.

@TheWildHealer Good point, thanks. There are often enough similarities I hoped that there might be a cross-over here, but the tag was probably just leading to frustrated people trying to help. Thank you for the suggestion, I've removed the tag. — tromik, Apr 30 '19 at 16:23

score 0 · Answer 1 · answered May 01 '19 at 14:34

I don't want to mark this as an answer as I'm sure someone has a better approach to this, but I was able to write a script to look at the read permission diffs between two tables and generate grant statements:

SELECT
    'GRANT SELECT ON TABLE ' || admin_table.schemaname || '.' || admin_table.objectname || ' to ' || old_table.usename || ';' AS grant_statement
FROM (
    SELECT *
    FROM (
        SELECT
            schemaname
            ,objectname
            ,usename
            ,has_table_privilege(usrs.usename, fullobj, 'select') AND has_schema_privilege(usrs.usename, schemaname, 'usage')  AS sel
        FROM (
            SELECT schemaname, 't' AS obj_type, tablename AS objectname, schemaname + '.' + tablename AS fullobj FROM pg_tables
            UNION
            SELECT schemaname, 'v' AS obj_type, viewname AS objectname, schemaname + '.' + viewname AS fullobj FROM pg_views
        ) AS objs
        , (SELECT * FROM pg_user) AS usrs
        ORDER BY fullobj
    )
    WHERE (sel = true)
    AND schemaname = '{schema_name}' AND objectname = '{old_table_name}') AS old_table
    FULL OUTER JOIN (
        SELECT *
        FROM (
            SELECT
                schemaname
                ,objectname
                ,usename
                ,has_table_privilege(usrs.usename, fullobj, 'select') AND has_schema_privilege(usrs.usename, schemaname, 'usage')  AS sel
            FROM (
                SELECT schemaname, 't' AS obj_type, tablename AS objectname, schemaname + '.' + tablename AS fullobj FROM pg_tables
                UNION
                SELECT schemaname, 'v' AS obj_type, viewname AS objectname, schemaname + '.' + viewname AS fullobj FROM pg_views
            ) AS objs
            , (SELECT * FROM pg_user) AS usrs
            ORDER BY fullobj
        )
        WHERE (sel = true)
        AND schemaname = '{schema_name}' AND objectname = '{new_table_name}') AS new_table
            ON old_table.schemaname = new_table.schemaname
            AND old_table.sel = new_table.sel
            AND old_table.usename = new_table.usename
    JOIN (
        SELECT *
        FROM (
            SELECT
                schemaname
                ,objectname
                ,usename
                ,has_table_privilege(usrs.usename, fullobj, 'select') AND has_schema_privilege(usrs.usename, schemaname, 'usage')  AS sel
            FROM (
                SELECT schemaname, 't' AS obj_type, tablename AS objectname, schemaname + '.' + tablename AS fullobj FROM pg_tables
                UNION
                SELECT schemaname, 'v' AS obj_type, viewname AS objectname, schemaname + '.' + viewname AS fullobj FROM pg_views
            ) AS objs
            , (SELECT * FROM pg_user) AS usrs
            ORDER BY fullobj
        )
        WHERE (sel = true)
        AND schemaname = '{schema_name}' AND objectname = '{new_table_name}') AS admin_table
            ON admin_table.usename = '{admin_user}'
    WHERE new_table.usename IS NULL
    AND old_table.sel IS true;```

Is there a way to copy or clone grants between tables in Redshift?

1 Answers1