How create a row-level policy for purposes of using a new_user_id as a fk entry to another table
briley last edited by
I have row-level security enabled on my
userstable. The table is part of a
coreschema. I have the following policy that provides access by the
create policy "Api(public) can view record when id=current_user_id" on core.users for all to api using(current_user_id() = id) with check (true);
However, the following procedure/function to register a new user violates the row-level security.
create or replace function api.register( auth_agent core.auth_agent, auth_id text, email text ) returns void as $$ declare new_user_id uuid; begin raise info ' api.register: %', current_user; -- create a new users record insert into core.users (email) values (register.email) returning id into new_user_id; -- << Error causing
-- create a new entry in the auth_ids_link_user insert into core.auth_ids_link_user (auth_agent, auth_id, user_id) values (auth_agent, auth_id, new_user_id); -- login using the new record perform api.login(auth_agent, auth_id); end;
$$ security definer language plpgsql;
... the error:
new row violates row-level security policy for table "users" CONTEXT: SQL statement "insert into core.users (email) values (register.email)
What is the the correct policy that
- allows the api only to view the row/record where id = current_user_id()
- lets the api use the core.users.id value to insert a new record in a table that uses the id as a fk
Thank you in advance to providing where there is surely an "idiomatic" way to do this.
Analeea last edited by
Per the documentation ( https://www.postgresql.org/docs/12/sql-createpolicy.html ) (read once more now with the clear question in mind), both the
selectpolicies are read by postgres when using the
insert ... returningcommand combination.
The following policies did the trick:
-- using with check for new records create policy "Api can insert" on core.users for insert to api with check (true);
-- using using for existing records
create policy "Api can select (before current_user is set)"
create policy "Api can do anything when current_user_id"
for all -- where overlaps with other policies, think "or"
using(current_user_id() = id)
with check (current_user_id() = id);
Note: I had tried something similar prior to drafting this post, but only succeeded this-time-round by properly using
with check. The former is specific to new records (i.e., used in insert and update commands), while the latter is specific to existing records (i.e., used for select and delete commands).