4 ms·

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

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.

john2360 2y ago
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 2y ago
  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 2y ago
TIL, thanks!
mmastrac 2y ago
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 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-tenants-in-postgres https://www.crunchydata.com/blog/row-level-security-for-tena...
- andrewstuart 2y ago
  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 2y ago
> “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 2y ago

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 2y ago
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 2y ago

TIL, thanks!

mmastrac 2y ago

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 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-tenants-in-postgres https://www.crunchydata.com/blog/row-level-security-for-tena...

andrewstuart 2y ago
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 2y ago

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