3 ms·
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.or
by 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 agoYep 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.