[mmastrac]

Adding to parent comment's context -- it's specifically called "row-level security". The docs show a number of examples for this:

[0] https://www.postgresql.org/docs/current/ddl-rowsecurity.html

  -- a policy on the account relation to allow only members of the managers role to access rows, and only rows of their accounts
  CREATE TABLE accounts (manager text, company text, contact_email text);

  ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;

  CREATE POLICY account_managers ON accounts TO managers
    USING (manager = current_user);

EDIT: That page doesn't cover session vars, but this one does:

https://www.crunchydata.com/blog/row-level-security-for-tena...

[andrewstuart]

Yep thanks for fleshing it out.

After configuring it as the parent post says, you set the environment variable like so:

SET myapp.manager = '123e4567-e89b-12d3-a456-426614174000';

Then you can just query the database and it will only return records where manager = '123e4567-e89b-12d3-a456-426614174000'

It's something like that anyway - you have to do lots of reading the docs and fiddling to make sure all the bits and pieces are set up right for it to work - which is why these folks are creating a SAAS to do all the thinking for you.

The real benefit of RLS is developers don't have to put "WHERE company_id=whatevere" on all queries, along with the risk that leaving it out or writing it wrong will reveal one client's data in another clients user interface.