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.
