PostgreSQL – Granting Access To All Tables

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. 😉