[andrewstuart]

If you’re interested in row level access control on Postgres, it works like this:

Prior to doing queries, you do a SQL query that sets a “Postgres environment variable”.

In very simplified terms, after that, queries automatically have a WHERE clause applied which ensures only rows with the value of the env variable are returned.

This is a good thing because it means you do not have to write WHERE customer = ‘blah’ anywhere.

[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.

[SahAssar]

> “Postgres environment variable”

I think most people think of environment variables as being for a whole process. For RLS this can be any GUC variable so it can be per-session, per transaction, etc.

Usually you would set it per transaction (and start a transaction for each request) and I think the important part you are missing to say is they are applied to any joins, CTE's, functions and views (as of the latest-1 version with the right flag).

So you write your schema, write your access (RLS) rules and can then write your queries as if you had access to the whole DB but the only parts you will see is what that user can access.

[john2360]

John here. Interesting! So, this is a per-session variable? Right now, we provide our customers with full logical separation in the same cluster. Do you have a preference for RLS or logical separation?

[andrewstuart]

I used RLS in Django by intercepting all outbound SQL and wrapping each statement in the commands to set the Postgres variable.

Note that a Postgres environment variable is not an operating system environment variable.

[adhamsalama]

TIL, thanks!