How create a row-level policy for purposes of using a new_user_id as a fk entry to another table



  • I have row-level security enabled on my users table. The table is part of a core schema. I have the following policy that provides access by the api user:

    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

    1. allows the api only to view the row/record where id = current_user_id()
    2. 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.



  • Per the documentation ( https://www.postgresql.org/docs/12/sql-createpolicy.html ) (read once more now with the clear question in mind), both the insert and select policies are read by postgres when using the insert ... returning command 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)"
    on core.users
    for select
    to api
    using (true);

    create policy "Api can do anything when current_user_id"
    on core.users
    for all -- where overlaps with other policies, think "or"
    to api
    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 using and 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).




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2