Recently, since the DBA wasn’t available, I had to assume his duties and grant SELECT privileges to a user (let’s say john_doe) for all of the tables in the public schema. After quite a bit of research (Google of course), I learned about the pg_tables view. After realizing that this view gives you the ability to see all of the tables and their respective schema names, I was able to write a query such as the following to accomplish my task:

SELECT 'GRANT SELECT ON ' || quote_ident(schemaname) || '.' || quote_ident(tablename) || ' TO john_doe;'
FROM pg_tables
WHERE schemaname = 'public';

After running this query, I was given all of the GRANT statements that I needed to run to give the user john_doe read permissions to all of the tables in the public schema. The beauty of this type of query is that it is short and easy to modify in order to get the desired results. Of course, you can use your imagination to think of countless other reasons why knowing about this view is advantageous. šŸ˜‰

Categories: BlogSQL

1 Comment

PostgreSQL – Granting Access To All Sequences | Chris West's Blog · June 11, 2012 at 12:49 PM

[…] in the development databases. After granting them access to all of the tables in the public schema (figured this out last time), I was asked to grant the access to all of the sequences as well. This required a little research […]

Leave a Reply

Your email address will not be published. Required fields are marked *