Listing all permissions in PostgreSQL
I've just spent some time trying to debug a permissions issue in PostgreSQL that kept giving me the error "permission denied for <tablename>" when trying to access a view - even though I appeared to have all the required permissions for the table underlying the view.
I found the following useful sql to list all user permissions on every database object, which helped me find the cause of the problem:
SELECT relname as "Relation", relacl as "Access permissions"
FROM pg_class
WHERE relkind IN ('r', 'v', 'S')
AND relname !~ '^pg_'
ORDER BY relname;
In my case, the view was referring to a sequence that I didn't have permissions to read from, and I'd never have found this without the above SQL.
