1

I want a query which tells me which user granted a role to another user in my Postgres database. Expected output would be as follows:

SELECT  role, grantor, grantee, grant_date FROM information_schema.<table>;

role              grantor              grantee            grant_date
grp_db_admin      Tom Dwan             Eli Elezra         2019-09-09
........
grp_db_viewer     Viktor Blom          Patrick Antonius   2019-08-02

So far I've found the information_schema.role_table_grants and pg_catalog.pg_roles tables, however these don't allow me to see who it was that granted someone a role group.

Any help would be appreciated.

2

PostgreSQL doesn't register the timestamp when a privilege was granted. The semantics would be unclear: what should the time be when you restore a dump? The time the privilege was originally granted or the time of the restore?

Anyway, you can get the rest of the information from the system catalogs:

SELECT roleid::regrole, member::regrole, grantor::regrole
FROM pg_catalog.pg_auth_members;
|improve this answer|||||
  • Perfect that's what I was looking for! No worries about the timestamp, I'm planning to record this information each day so will just default to NOW(). Thanks for the help! – Laurie Bamber Sep 9 '19 at 14:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.