Readonly permissions for redshift schema

Question

I want to give readonly permission to group in redshift. I used this command

GRANT USAGE ON SCHEMA myschema TO GROUP my_group; 
GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO GROUP my_group; 
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO GROUP my_group; REVOKE CREATE ON SCHEMA myschema FROM GROUP my_group;"

This works but look like if new tables are created then user dont have permissions for those tables

According to AWS

IN SCHEMA schema_name

Optional. If an IN SCHEMA clause appears, the specified default privileges are applied to new objects created in the specified schema_name. In this case, the user or user group that is the target of ALTER DEFAULT PRIVILEGES must have CREATE privilege for the specified schema. Default privileges that are specific to a schema are added to existing global default privileges. By default, default privileges are applied globally to the entire database.

Does that mean that i have to give CREATE permission to that group ?

Answer 1

Let's say that you create the tables with user_x and then you want to give READ permission to group my_group, this is the command that I generally used to achieve that:

ALTER DEFAULT PRIVILEGES FOR USER user_x IN SCHEMA myschema GRANT SELECT ON TABLES TO GROUP my_group;

Answer 2

Here is a Stored Procedure to add Read only user group. This SP has to be run whenever a new schema or user is created (only once)

 CREATE OR REPLACE PROCEDURE 
 public.grant_read_only_access_to_group(group_name varchar)
 LANGUAGE plpgsql
      AS $$
 declare
 s_name RECORD;
 u_s RECORD;
 begin
    
    for s_name in select schema_name from SVV_REDSHIFT_SCHEMAS where schema_name not in ('information_schema','pg_catalog')
    loop    
        execute 'REVOKE CREATE ON SCHEMA ' || quote_ident(s_name.schema_name)|| ' FROM GROUP '|| group_name;
        execute 'GRANT USAGE ON SCHEMA  '|| quote_ident(s_name.schema_name)||' TO GROUP ' || group_name;
        execute 'GRANT SELECT ON ALL TABLES IN SCHEMA '|| quote_ident(s_name.schema_name)||' TO GROUP '|| group_name;
        execute 'ALTER DEFAULT PRIVILEGES IN SCHEMA ' || quote_ident(s_name.schema_name)||' GRANT SELECT ON TABLES TO GROUP ' || group_name; 
    end loop;

        for u_s in select u.usename n , s.schema_name s ,has_schema_privilege(u.usename,s.schema_name, 'create') c 
                    from pg_user u cross join SVV_REDSHIFT_SCHEMAS s 
                    where schema_name not in ('information_schema','pg_catalog') 
                    and usesysid > 1
        loop
        if u_s.c = true then 
            execute 'ALTER DEFAULT PRIVILEGES FOR USER '||u_s.n||' IN SCHEMA ' || quote_ident(u_s.s)||' GRANT SELECT ON TABLES TO GROUP ' || group_name;
        end if;
        end loop;
end;
$$;

Example of how to use SP : create group readonly;

call grant_read_only_access_to_group('readonly');

CREATE USER readonly_user WITH PASSWORD 'pwd';

ALTER GROUP readonly ADD USER readonly_user;