Another DBA task that I seem to have to carry out is making sure that a specified user or group has all (CREATE & USAGE) permissions to all of the schemas (except pg_catalog for which they only get USAGE) in the database.  For this reason, I end up using the following SELECT statement to generate all of the needed GRANT statements:

SELECT DISTINCT 'GRANT '
    || CASE schemaname WHEN 'pg_catalog' THEN 'USAGE' ELSE 'ALL' END
    || ' ON SCHEMA ' || quote_ident(schemaname) || ' TO john_doe;'
FROM pg_tables;

The above query can be modified in whatever way that you like so that it can create a GRANT statement such as is outlined on the corresponding PostgreSQL 8.1+ documentation page.  Of course, there may be a better way (such as using a table that has only one row for each schema name). If you have another way to grant privileges to a schema, please let me know.

Categories: BlogSQL

Leave a Reply

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