0

I am trying to work out how database roles work. Here is my user case...

create role user1 login password 'user1';

create schema authorization user1;

CREATE ROLE DEV_ROLE;

grant connect, temporary on database test to DEV_ROLE;
GRANT ALL ON SCHEMA manager TO DEV_ROLE;

Now how do i view the all the grants assigned to 'DEV_ROLE'? This is possible in Oracle but trying to work out the same here.

Do appreciate your reply. Ta

1 Answer 1

0

There is no way to do that in PostgreSQL; you have to look at all ACLs on all objects.

Perhaps an extension like pg_permissions can be useful.

One other crude method I can think of:

BEGIN;
DROP ROLE dev_role;
ROLLBACK;

The DROP ROLE will give you a list of permissions it can find that are granted to the role.

2
  • dev=# create extension pg_permissions; ERROR: could not open extension control file "/usr/pgsql-12/share/extension/pg_permissions.control": No such file or directory
    – nick
    Commented Apr 20, 2021 at 14:41
  • Well, you need to install the software first. It is not a core extension. Commented Apr 20, 2021 at 15:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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