Who can see what: row-level security

Once hundreds of people can build and open reports, the first question is what each of them is allowed to see. Most rules have two parts. Rows: a store manager sees her own store, a city manager sees the stores in her city, head office sees everything. Columns: almost nobody needs customer phone numbers or emails in a sales report, so those columns stay out of the shared datasets altogether.

Row-level security (RLS) is the mechanism for the first part. It has three pieces:

• A mapping from each user (or group) to the rows they may see, for example a table user_site with one row per user and store. It should be fed from the HR or staff system every night, so a manager who moves to another store loses the old one the next day.
• A policy: a filter the system adds to every query on the protected table, such as "only rows whose site_id is in this user's mapping".
• The user's identity reaching the place where the policy runs. This is the part that most often goes wrong.

The key property is that the filter is added by the server, after the user has written or chosen the query. The user cannot remove it. A filter set in the dashboard, such as a "Store" drop-down that defaults to the manager's store, is a convenience. Anyone who can edit the chart, export the data or open the dataset in another report can remove it.

In PostgreSQL the policy is part of the table. You switch row security on, then write a policy whose USING condition every row must pass:

ALTER TABLE sale ENABLE ROW LEVEL SECURITY;

CREATE POLICY sale_by_store ON sale
  FOR SELECT
  TO bi_reader                      -- the role the BI tool connects as
  USING (site_id IN (
    SELECT site_id FROM user_site
    WHERE user_email = current_setting('app.user_email', true)
  ));

-- Before each user's queries, the BI tool (not the user) runs:
SET app.user_email = 'ama@mangrove.example';

Three details matter. First, BI tools usually connect with one shared account, so current_user is the same for everyone. The tool has to pass the real viewer, here in a custom setting that current_setting reads. This is only safe when users cannot run their own SET on that connection. Second, if the setting is missing, current_setting(…, true) returns null, no row matches and the user sees nothing. That is the right way to fail. Third, the table owner, superusers and roles with the BYPASSRLS attribute skip policies, so the BI account must be an ordinary role that does not own the table (or the table needs FORCE ROW LEVEL SECURITY).

You can also enforce the rule in the BI tool or semantic layer instead of the database. Power BI defines row-level security as roles with DAX filter expressions; Looker uses access filters tied to user attributes; most other tools have a similar feature. Pick one place and enforce it there for every path to the data, including exports, scheduled emails, extracts and summary tables. A policy on sale does not protect a weekly_store_sales table built from it.