Managing user privileges within a system is always a hard problem in general. In particular, it’s even a harder problem for PostgreSQL Database, especially Amazon Redshift (which only supports a subset of PostgreSQL 8.0.2 features).
Supposed you now have your Data Warehouse running on Amazon Redshift.
By default, you will have all privileges for Schemas / Tables / Views that you’ve created or listed as an owner.
Now, to give read access for a user U1 to a certain table T1 within schema S1, you will then need to execute:
GRANT USAGE ON SCHEMA S1 TO U1;
GRANT SELECT ON TABLE S1.T1 TO U1;Of course, that’s pretty simple and straightforward. However, when you have tens of schemas, thousands of tables to hundreds of users, how would you do it? A similar problem arises when you then need to drop a user with various access types to a good number of tables in your database (hint: you will need to revoke all those privileges first), or you need to transfer ownership of user X to user Y.
(Brief) Answer / Hint: you do it programmatically with:
- Query information_schema.schemata; pg_tables; pg_views: for your schemas, tables and views list.
- Store the list into your program.
- Use customized string manipulation functions to create a composed GRANT Query accordingly. Good luck hacking :).
PS: In case you need to solve this problem ASAP, here is a little command-line interface tool (named: postgresql-user-manager) that I’ve developed while working at Zalora :).