Screens: Connections and explorer

RLS matrix and simulator

PostgreSQL's RLS (Row-Level Security) makes the same table show different rows to different roles. The Role × Table Permission Matrix dialog shows, in one table, which roles have which privileges on each table of a schema and whether RLS is on. The RLS Policy Simulator dialog runs SQL as another role so you can see which rows a policy actually leaves, and rolls back every change afterwards. Both dialogs work only with PostgreSQL connections (Supabase included).

Where you can use itWebDesktopWithout signing inSigned inLocal projectsAccount projects

What's on the screen

No.NameDescription
1Schema:The schema whose privileges you're viewing. Only schemas of the database that holds the right-clicked schema can be chosen, and the table is read from that database. Changing it reloads the table.
2RLS-enabled tables only switch and search boxKeep only tables with RLS on, or filter by table name. The number of tables and roles left is shown on the right.
3Table columnTable names. A purple shield marks a table with RLS on (hover to see the number of policies); an orange shield marks a table that applies RLS to its owner too (FORCE).
4Role headersOne role per column. A warning-colored shield marks a BYPASSRLS role that ignores RLS; a group icon marks a group role that can't log in.
5Privilege badgesThe privileges the role has on the table: S SELECT, I INSERT, U UPDATE, D DELETE, T TRUNCATE, R REFERENCES, G TRIGGER, or — for none. When a BYPASSRLS role meets a table with RLS on, ⛨ is added in front.
6Legend:What each privilege letter means.

View privileges by role

  1. In the object explorer, right-click a schema of a PostgreSQL connection and choose Explorer context menuRole Matrix.... The Role × Table Permission Matrix window for that schema opens.
  2. To see only tables under RLS, turn on the RLS-enabled tables only switch (2).
  3. Go down each role's column (4) and look for tables where the role has privileges but RLS is off, or tables a BYPASSRLS role can reach.
  • Privileges granted to every role (PUBLIC) are merged into each role's cells.
  • Even with privileges, a table with RLS on shows only the rows its policies allow. Use the simulator to see which rows those are.

Check policies with the RLS simulator

  1. Open the simulator. Where to open it is listed under 'Where to open the simulator' below. When opened from the explorer, the SQL box already holds SELECT * FROM schema.table; for that table.
  2. Choose the role to test in Role (1).
  3. If a policy reads values with current_setting(), enter the keys and values in the Session GUCs (SET LOCAL) box (2). For a Supabase connection, the JWT Claims builder appears instead of the Session GUCs (SET LOCAL) box (see 'With Supabase' below).
  4. Write one statement to check in the SQL box (3). INSERT, UPDATE and DELETE work too.
  5. To see the difference in row counts, turn on the Compare with another role switch (4) and choose a role to compare with (usually a BYPASSRLS role).
  6. Click the Execute button (5). The result (6) shows the rows, the row count and the execution time, plus how many rows RLS hid if you turned on the comparison.

The simulator wraps your SQL like this and always rolls back at the end. The Rollback all changes (forced) indicator can't be turned off.

BEGIN;
  SET LOCAL ROLE "chosen role";
  SELECT set_config('key', 'value', true);   -- for each value you entered
  -- your SQL
ROLLBACK;
Statements the simulator blocksWhy
BEGIN, COMMIT, ROLLBACK, SAVEPOINTThe simulator opens and rolls back the transaction itself
SET without LOCALOnly SET LOCAL is used so that nothing stays in the session
Multiple statementsOnly one statement runs at a time
CREATE INDEX CONCURRENTLYIt can't run inside a transaction

Where to open the simulator

WhereShown when
Right-click a table or view in the explorer › Explorer context menuTest with RLS...A PostgreSQL table with RLS on
The Test with RLS... button in the SQL editor toolbarThe current connection's schema has a table with RLS on. The editor's current SQL is filled in
The Simulate... button on each policy in the RLS Policies table of the table tab and the ERD table detail panelA PostgreSQL table with RLS policies

With Supabase

When a connection is confirmed as Supabase (the Supabase mode item in the connection dialog; supabase.co addresses are detected automatically), both dialogs adapt to Supabase.

  • The matrix puts the anon, authenticated and service_role roles first and explains them in their headers. service_role ignores RLS, so it gets a warning shield.
  • The simulator's role list is split into the Supabase preset and All roles groups.
  • The simulator's value boxes become the JWT Claims builder. The values read by auth.uid(), auth.role() and auth.jwt() in policies are passed as a single request.jwt.claims value.
JWT Claims builderWhat it does
Generate preset listFills the claims with one of anon (no sub), random authenticated or service_role (bypass)
sub, role, email, aud, exp, iss boxesCommonly used claims. role and the Role box above follow each other. The Extend exp by 1h button next to exp sets the expiry to one hour from now
Custom claimsAdds other claims such as app_metadata. A value that parses as JSON goes in as a number, object or array; otherwise it goes in as a string
request.jwt.claims previewThe JSON that will actually be passed

For policies that read values outside the JWT, such as app.tenant_id, use the Session GUCs (advanced) boxes below the builder.

Things to keep in mind

  • The simulator rolls back changes, but anything a transaction can't undo remains. For example, sequence values used by an INSERT are not rolled back.
  • The simulator shows up to 1,000 rows of results and stops a run that takes more than 30 seconds.
  • You must be able to switch to the chosen role. If the connecting account isn't a member of that role, the run fails. If the role list is empty, the connecting account isn't allowed to read the list of roles.
  • The matrix reads privileges from PostgreSQL's information_schema. So privileges between roles unrelated to the connecting account may not appear, and privileges inherited through role membership aren't merged in. Use the simulator for an exact answer.
  • Explorer context menuTest with RLS... in the explorer appears only after the table's RLS status has been read. If it isn't there, refresh that schema in the explorer.