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

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
    Apr 20 at 14:41
  • Well, you need to install the software first. It is not a core extension. Apr 20 at 15:01

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.