3 ms·
Can you refer to roles? Something like “set local role (SELECT name FROM roles WHERE id = 7)”
by WinterMount223 5y ago
Can you refer to roles? Something like “set local role (SELECT name FROM roles WHERE id = 7)”
- Winsaucerer 5y agoI'm doing something like this. It's an experiment at the moment, so I haven't used it in anger yet. Rather than having a role for each application user, I have my own application's notion of a role/account/user, with its own table in my database. For each transaction: SET LOCAL ROLE webuser (used by all transactions that come from the web application) SET LOCAL "request.web.sub" = '<internal application's primary key for this specific user' Then I can in queries check for the current role (where by 'role' I mean my application's user/account/role set via "request.web.sub", not a postgres role) via: create or replace function auth.fn_requesting_role() RETURNS uuid LANGUAGE sql AS $func$ with crole as ( select coalesce( nullif(current_setting('request.web.sub', true), ''), nullif(current_setting('request.jwt.sub', true), '') )::uuid as role_id ) select crole.role_id::uuid from crole join auth.role on role.role_id = crole.role_id::uuid; $func$; You can then find out the current requesting user/account/role ID in RLS policies and other functions, and apply whatever permissions you like there. The reason I have 'request.jwt.sub' is just for future if I want to allow requests to come from PostgREST as well and use the same authorisation checks.
- RedShift1 5y agoI'm sure you can but I'm not smart enough to figure that out (I think some quoting is missing): EXECUTE 'SET ROLE ' || (SELECT rolname FROM pg_roles WHERE oid = 17026) But I wouldn't use those oid's because you are leaking some implementation detail, it's probably best to just stick with the actual role names instead of a reference.