Studio edition /

Roles, permissions and row security

Create and edit PostgreSQL roles, grant and revoke privileges by role or by object, and manage row-level security policies. Studio edition.

Open it#

In the Studio edition, switch to Advanced and pick Roles and permissions in the sidebar. Section has three tabs: Roles, Permissions and Row security.

Every change shows its SQL and waits for your confirmation, as described in Every change shows its SQL. In read-only mode everything can be seen and nothing changed.

Roles#

The list shows each role with its attributes as labels, the roles it is a member of, and you next to the role Gresbase is connected as. The predefined roles (pg_monitor, pg_read_all_data…) are hidden until you tick Show the predefined roles (pg_*). They can be granted, not changed.

New role asks for a name, an optional password, the attributes and Can use the advanced mode (off by default). Click a role to open its detail:

• Attributes: Can log in, Superuser, Create databases, Create roles, Inherit privileges, Replication, Bypass row security, Connection limit and Password valid until. Save attributes applies them. Making a superuser or giving BYPASSRLS comes with a warning.
• Gresbase Studio: Can use the advanced mode turns the advanced mode on or off for the role. It is saved at once, with no SQL: it is a Gresbase setting, not a PostgreSQL one.
• Member of and Members: the role's memberships in both directions. Add membership joins it to another role, with ADMIN and, from PostgreSQL 16, INHERIT and SET.
• Password: Set password sets a new one. It travels to Gresbase once and PostgreSQL stores it as SCRAM-SHA-256. It is never shown: in the SQL preview it is '********', and it never reaches the logs.
• Rename. Renaming clears an MD5 password, because MD5 is salted with the name; the preview warns you.
• Delete role. If the role owns objects in this database, they are listed and you choose: give them to another role (REASSIGN OWNED) or delete them (DROP OWNED). You confirm by typing the role's name.

Without CREATEROLE or superuser, the screen says so and the roles can be seen, not changed.

Permissions#

Permissions view has three views.

By role. Pick a role to see every object (the database, schemas, tables, views, materialized views, foreign tables, sequences, functions, procedures, and columns with their own privileges) with its privileges as labels:

• granted, filled: given to the role directly. A * marks those with grant option.
• inherited, dashed border: the role has it through a membership or through PUBLIC. The tooltip says where it comes from, for example Inherited from app_readers.
• not granted, empty.

A superuser bypasses every privilege check, and the screen says so.

By object. Pick an object to see which roles (and PUBLIC) hold what on it. For a table, a column selector shows the privileges of each column.

Default privileges. The entries of ALTER DEFAULT PRIVILEGES: what tables, sequences, functions, types or schemas get when a given role creates them later, in one schema or in all. A form adds new ones.

Clicking a privilege doesn't change it at once. It goes to a queue, shown as N pending changes (will be granted, will be revoked). Review and apply shows the GRANT and REVOKE statements and runs them all in one transaction; Discard empties the queue.

Templates writes every statement for a common kind of role, for a role and a schema, to review before it runs:

"Later" means the objects the connected role creates afterwards, through default privileges.

Row security#

The list shows each table with its row-level security state (off, on or forced) and how many policies it has. Pick a table to see what its state means and its policies.

The buttons Enable row security, Disable row security, Force for the owner and Stop forcing change the state. Each one warns about its effect: without row security, every role with SELECT reads every row; with it on and no policies, no role but the owner (and superusers) sees any row.

Each policy shows its command, Permissive or Restrictive, the roles it applies to, and its USING and WITH CHECK conditions. New policy and Edit open a form; the command enables USING, WITH CHECK or both, as PostgreSQL allows them. Editing replaces the policy (DROP POLICY and CREATE POLICY in one transaction), because ALTER POLICY can't change its command or kind. Each condition must be a single expression.

Test as a role counts and shows up to 20 rows that a role can read, in a read-only transaction with SET LOCAL ROLE. You must be a member of that role. If the role skips the policies (a superuser, BYPASSRLS, the owner when not forced, or row security off), the result says why.

Permissions needed#

PostgreSQL checks each statement: when one fails, the transaction rolls back and Gresbase shows the error. See also Security and networking.