Amazon Redshift: show permissions

SELECT
    u.usename,
    s.schemaname,
    has_schema_privilege(u.usename,s.schemaname,'create') AS user_has_create_permission,
    has_schema_privilege(u.usename,s.schemaname,'usage') AS user_has_usage_permission
FROM
    pg_user u
CROSS JOIN
    (SELECT DISTINCT schemaname FROM pg_tables) s
WHERE
    u.usename = 'myUserName'
    AND s.schemaname = 'mySchemaName'
;

 usename | schemaname | user_has_select_permission | user_has_usage_permission
---------+------------+----------------------------+---------------------------
(0 rows)

 usename | schemaname | user_has_select_permission | user_has_usage_permission
---------+------------+----------------------------+---------------------------
 your_user  | your_schema  | f                          | f
(1 row)

Grant privileges:

grant select on all tables in schema YOUR_SCHEMA to YOUR_USER;
grant create on all tables in schema YOUR_SCHEMA to YOUR_USER;
grant all on schema YOUR_SCHEMA to group YOUR_USER;

Related

• has_table_privilege
• View GRANTs on Redshift
• GRANT USAGE

See also

• GRANT, \ddp, GRANT USAGE, GRANT ALL PRIVILEGES, GRANT SELECT, View GRANTs on Redshift, has_table_privilege, has_schema_privilege, HAS_DATABASE_PRIVILEGE, SCHEMA, Privileges, GRANT EXECUTE
• Amazon Redshift, Redshift Serverless, aws redshift, Amazon Redshift query editor, AQUA, WLM, Amazon Redshift Spectrum, metrics, VACUUM, Amazon RedShift Management Guide, Datashare, SVV