Roles and grants

The Roles view shows a server’s roles and who may do what to each table, view, sequence and function. You can grant and revoke privileges from it, and every change is staged for review before it runs. For anything else about roles, such as creating one or changing its password, write the SQL in a query tab.

Open it

Choose Roles in the activity bar, or run Show roles and grants from the command palette. It opens for the connection in front, with three tabs.

Roles

Every role on the server, with Login, Attributes, Member of, Members, Connections and Valid until. Hide built-in roles, on by default, leaves out the pg_ roles. Attributes such as superuser, createdb and bypassrls show as badges, and your own role is marked you. A note lists the roles that skip row security.

Grants

Who has which privilege on what.

• By role… shows one role’s grants: its attributes and memberships on the server, then, in this database, its privileges on each schema’s tables and views, sequences and functions.
• By object… shows who has access to one object.

Each cell says how the privilege is held: granted, granted + (with the right to grant it on), via <role> (through a membership), superuser or PUBLIC. Default privileges, for objects not created yet, are listed below the grants.

When anyone may create objects in a schema, a note says so and offers Revoke CREATE….

Privileges

Everything one role can act on. Pick a Role, and narrow by Schema and Privilege.

Grant or revoke a privilege

1. In Grants, find the role and object.
2. From the row’s menu, choose Grant… or Revoke….
3. Select the Privileges.
   • For a grant, With grant option lets the role grant them to others.
   • For a revoke, Cascade also revokes what the role granted to others.
4. Choose Stage grant or Stage revoke. The change joins your staged changes.
5. Choose Review SQL, read the GRANT or REVOKE, and apply it.

A revoke counts as a destructive statement, so your Safety rules ask before it runs. On a read-only connection, unlock editing first (Work on production).

You’ll know it worked when the cell in Grants shows the new privilege, or — where you revoked one.

See a table as another role sees it

On a table or a view, View as role reads the rows as one of the roles your user may switch to with SET ROLE. It shows which rows that role’s row security hides, with Show hidden rows to see them, and Stop viewing as to go back. The rows are read in a read-only transaction that is rolled back, so nothing changes.

You can only view as a role your user is a member of. Otherwise Alcuin says You cannot view as reporting: this user is not allowed to SET ROLE to it. (ALC-9003).

What it doesn’t do yet

• Create, rename or drop roles, or change their attributes or passwords.
• Add or remove role memberships.
• Change default privileges.

Run those as SQL in a query tab. Row-security policies are edited on a table’s Structure tab, not here.