I would like to seperate access to two existing data schemas (with tables already created) in redshift by creating two new users and granting them access to their relevant schemas.
So user_1 should have access only to schema_1 and user_2 should have access only to schema_2.
By access I mean that the users should be able to SELECT, INSERT, UPDATE, DELETE, DROP from all current tables (no matter who created them) and to CREATE new tables on their schemas.
I have found the below statements to create a new user and to give them specific access types:
Create new user:
CREATE USER user_1 WITH PASSWORD 'password_1';
Grant usage to the given schema:
GRANT USAGE ON SCHEMA "schema_1" TO user_1;
Assign privileges:
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA "schema_1" TO user_1;
Alter Default Privileges to maintain the permissions on new tables
ALTER DEFAULT PRIVILEGES IN SCHEMA "schema_1" GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO user_1;
In the documentation it shows how to add all privileges required except for DROP tables. When testing this it errors with ERROR: must be owner of relation table_to_drop.
I alternatively have tried to grant all privileges as below...
GRANT ALL TABLES IN SCHEMA "schema_1" TO user_1;
...but this doesn't overwrite the requirement of it needing to be the owner of the table that drops is able to drop it.
So question is is it possible to restrict a user to a given schema with acccess to it as mentioned above?
