Le Nguyen The Dat bio photo

Le Nguyen The Dat

Data Science and Engineering at 90 Seconds

Email Twitter Facebook LinkedIn Github

image


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 :).