Once again, since the DBA is on vacation today, I have to assume some of his duties. It seems that nobody has access to anything in the development databases. After granting them permissions 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 in order to get a quick query that would build muy many GRANT statements:

SELECT 'GRANT ALL ON ' || quote_ident(schemaname) || '.' || quote_ident(relname) || ' TO john_doe;'
FROM pg_statio_all_sequences
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 privileges 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.

Categories: BlogSQL

Leave a Reply

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