Row-Level Security

PostgreSQL can decide which rows a user sees (row-level security, RLS). myDBR can hand the logged in user over to PostgreSQL, so the database itself limits the rows of a report. The report does not need a where clause for the user, and a report that is changed or copied cannot show more than the user is allowed to see.

The feature is off by default. It is available on PostgreSQL only.

Settings

The settings are on the Database tab of Preferences > Environment settings (shown only for PostgreSQL connections).

Setting	Meaning
Row-level security	Off: nothing changes, reports run with the connection account. Identity: each report run tells the database who the user is. Role: identity, and the report also runs as the database role of the user.
Role prefix	The role of a user is the prefix plus the user name. Default mydbr_, so the user anna runs as mydbr_anna.
Fallback role	The role for users that have no role of their own. If it is empty, such a user gets an error and the report does not run.
User setting	The name of the setting that holds the user, mydbr.<name>. Default user, so the user is current_setting('mydbr.user', true).

Below the fields the page warns about setups that do not work as expected, for example when the connection account is a superuser or has the BYPASSRLS attribute, when no table has row-level security enabled, when tables with RLS belong to the connection account and do not use FORCE ROW LEVEL SECURITY, and when roles with the prefix cannot be switched to by the connection account.

How it works

For every report run myDBR sets, inside the transaction of the report:

• Identity: set_config('mydbr.user', '<user name>', true). The setting lasts until the end of the transaction.
• Role: the setting above and SET LOCAL ROLE <prefix><user name>. The role also lasts until the end of the transaction, so the next report starts again from the connection account.

All the cursors of the report are read in the same transaction, so the policies apply to every result set. Only the run of a report is affected. myDBR's own queries, the SQL Editor, synchronization and the other tools are not.

The report cache (Redis) keeps the results of different users apart while the mode is not Off.

Example

create table orders (id serial primary key, owner text not null, item text not null);

-- A role for every myDBR user. The connection account of myDBR (mydbr_service) may switch to it.
create role mydbr_anna nologin;
grant select on orders to mydbr_anna;
grant mydbr_anna to mydbr_service;

alter table orders enable row level security;
alter table orders force row level security;   -- the owner of the table obeys the policy too

create policy own_orders on orders
  using (owner = current_setting('mydbr.user', true));

The report needs no filter on the user:

create or replace function sp_DBR_my_orders()
returns setof refcursor
language plpgsql as $$
declare
  c refcursor;
begin
  c := null;
  open c for select id as "Id", item as "Item" from orders order by id;
  return next c;
end
$$;

Groups are handled by PostgreSQL. Grant the group role to the roles of its members, for example grant sales to mydbr_anna;, and write the policies for the group role. current_setting('mydbr.user', true) returns NULL when the setting is not set, so a policy that compares with it shows no rows to a report that runs without a user.

Settings for a single report

A report can use a different mode than the global setting. For example an administrative report that must see all rows can be set to Off.

1. Report editor. The Row-level security field of the report offers Use global setting (default), Off, Identity, Role and Fixed role. A fixed role is one database role that every user runs the report as. The setting is stored in the rls column of mydbr_reports.

2. Function comment. A tag in the comment of the function does the same and travels with the function, for example in a database dump:

   comment on function sp_DBR_all_orders() is 'All orders. @mydbr rls=off';
   comment on function sp_DBR_stock() is '@mydbr rls=role:mydbr_warehouse';

   The values are off, on (role if the global mode is off), identity, role and role:<rolename>.

If both are set, the setting of the report editor wins. The rls column is added by the installation script, also to an existing PostgreSQL installation.

Things to know

• The connection account must not be a superuser and must not have BYPASSRLS, because those accounts ignore row-level security. In role mode the policies are applied to the role that the report runs as, but an account with these attributes is a risk anyway.
• A table owner ignores the policies of its table unless the table has FORCE ROW LEVEL SECURITY.
• Row-level security protects the data from the users of the reports, not from the people who write them. A function can run RESET ROLE.
• In role mode a report that cannot be run inside a transaction is refused, because the role could not be limited to it.
• Copying a report keeps its row-level security setting, and the synchronization script carries it.