Does ALTER SCHEMA NAME affect permission grants to the schema in Redshift

If i update a schema which has been set up with a bunch of permissions to access other schemas with different access rights, will updating the name undo those grants or will they remain in place? Redshift lists the following on their docs as the syntax to run an alter schema, but does not write if the grants will be affected:

ALTER SCHEMA schema_name
{
RENAME TO new_name |
OWNER TO new_owner |
QUOTA { quota [MB | GB | TB] | UNLIMITED }
}

My assumption was that there would be no change to existing grants, but i want to make sure before the change goes into effect.

Answer

You are correct. Changing the name does not change the underlying permissions. You can test this yourself in a new / dev cluster by ensuring that that the privileges return the same before and after you alter a table name. However, changing the owner will impact the owner level privileges.

CREATE SCHEMA dev;

CREATE TABLE dev.test_table AS (SELECT 1 AS t);

GRANT USAGE ON SCHEMA dev TO “username_here”;

GRANT SELECT ON TABLE dev.test_table TO “username_here”;

SELECT u.usename,
   s.schemaname,
   s.tablename,
   has_table_privilege(u.usename, s.schemaname || ‘.’ || s.tablename, ‘select’) AS user_has_table_select,
   has_schema_privilege(u.usename,s.schemaname,‘create’) AS user_has_schema_create,
   has_schema_privilege(u.usename,s.schemaname,‘usage’) AS user_has_schema_usage
FROM pg_user u
     CROSS JOIN
     (SELECT schemaname, tablename FROM pg_tables) s
WHERE s.schemaname in (‘dev’, ‘uat’)

ALTER SCHEMA dev rename TO uat;

SELECT u.usename,
   s.schemaname,
   s.tablename,
   has_table_privilege(u.usename, s.schemaname || ‘.’ || s.tablename, ‘select’) AS user_has_table_select,
   has_schema_privilege(u.usename,s.schemaname,‘create’) AS user_has_schema_create,
   has_schema_privilege(u.usename,s.schemaname,‘usage’) AS user_has_schema_usage
FROM pg_user u
     CROSS JOIN
     (SELECT schemaname, tablename FROM pg_tables) s
WHERE s.schemaname in (‘dev’, ‘uat’)