db_pre_request issues in private schema

[cam-mcevenue]

I am trying to use db_pre_request to check user permissions on each request. It works like a charm when I put the function in the public schema like so:

create or replace function public.check_permissions()
    returns void
as $$
    with user_permissions as (
        select jsonb_build_object('permissions',jsonb_object_agg (permission, scope)) as data
        from access_control.get_permissions()
    )
    select set_config('request.claims'::text, (select data::text from user_permissions), false );
$$ language sql security definer;

alter role authenticator set pgrst.db_pre_request TO 'check_permissions';

The problem is this function is now exposed in the public API. So, I want to move it to a private schema.

Based on docs I did the following

-- create a dedicated schema, hidden from the API
create schema "postgrest";
-- grant usage on this schema to the authenticator
grant usage on schema "postgrest" to "authenticator";

select set_config('pgrst.db_extra_search_path','public,postgrest,extensions',true);

notify pgrst,'reload config';

create or replace function public.check_permissions()
    returns void
as $$
    with user_permissions as (
        select jsonb_build_object('permissions',jsonb_object_agg (permission, scope)) as data
        from access_control.get_permissions()
    )
    select set_config('request.claims'::text, (select data::text from user_permissions), false );
$$ language sql security definer;

alter role authenticator set pgrst.db_pre_request TO 'postgrest.check_permissions';

I now get a 403 forbidden error on every request.

I've tried

1. Removing schema name from set pgrst.db_preqeuest alter role authenticator set pgrst.db_pre_request TO 'check_permissions';
2. Giving all function privileges to authenticator alter default privileges for role "postgres" in schema "postgrest" grant all on functions to "authenticator";

Anyone successfully done this before?

[laurenceisla]

I think there's a mistake in the new pre-request function declaration?

...
notify pgrst,'reload config';

-- it should be postgrest.check_permissions()
create or replace function public.check_permissions()
    returns void
as $$
...

But still, the Pre Request function is executed after the role is impersonated. So you'll need to grant usage on the schema to that user (the one in the JWT "role" key), e.g. if the role is web_user, then:

grant usage on schema postgrest to web_user;

It works in the public schema because every user has that privilege by default.

I'm also wondering, does the function work as expected? I have that doubt because the pre request is done after the authentication. And what PostgREST and PostgreSQL versions are you using?

[cam-mcevenue]

Thanks for the response.

Yes, that was a typo my function does read postgrest.check_permission()

I figured it was a grant for a specific role that is causing issues. I think I may have to reach out to Supabase since it seems to be an issue with some of their database roles.

Granting all permissions to their authenticated and anon roles, and assigning that pre_request function, the function is never actually called. Granting permissions to the authenticator role, the function begins to get called, but breaks some other functionality.

I'm going to have to do more testing

PostgreSQL 15.1 (Ubuntu 15.1-1.pgdg20.04+1) on aarch64-unknown-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit

PostgREST v 11.1

[cam-mcevenue]

I figured out how to get this to work. Though I'm not exactly clear why. I think it has something to do with how authenticator switches into anon and authenticated roles (or whatever web_user and anon roles you've setup. These are the target roles in supabase)

In order to have the db_request_function in a private schema, you must grant the following:

create schema "private";

grant usage on schema private to authenticator, anon, authenticated;
alter default privileges for role "postgres" in schema "private" grant all on functions to authenticator, anon, authenticated;

create or replace function private.test() returns void
  language plpgsql
  security definer
  as $$
begin
    if not (current_setting('request.jwt.claims', true)::jsonb ? 'email') then
        perform set_config('response.status', '418', true);
   else
        perform set_config('response.status', '200', true);
   end if;
end
$$;

alter role authenticator set pgrst.db_pre_request TO 'private.test';
notify pgrst,'reload config';

I've been trying to get this to work for a few days, and one BIG gotcha is how you define the function, and what you do inside it. A few rules I've found through exploring the docs and testing to avoid issues.

1. Don't modify the database (insert, update, delete). Ideally, only manipulation should be to PostgRESTs request and response settings
2. Don't return anything (ie. return void)
3. Dont execute select statements on any function or table that is not accessible to any of the above mentioned roles (or use security definer like I did)

If you don't follow those rules, PostgREST might yell at you :)

[laurenceisla]

Great to know it's working now! Thanks for posting your solution.

Though I'm not exactly clear why. I think it has something to do with how authenticator switches into anon and authenticated roles (or whatever web_user and anon roles you've setup. These are the target roles in supabase)

Yes, that's exactly it. The Pre Request executes after the role is set in the database, in the transaction scoped settings. As the docs mention (emphasis mine):

The pre-request is a function that can run after the Transaction-Scoped Settings are set and before the Main query. It’s enabled with db-pre-request.

So, this query is obligatory:

-- authenticator should not be necessary
grant usage on schema private to anon, authenticated;

I also thought your functions had public access by default, but it seems that your environment has them disabled. It would be good to leave it that way (disabled as recommended by the docs), then you wouldn't need to alter default privileges and just grant permission to that function directly:

-- replace
alter default privileges for role "postgres" in schema "private" grant all on functions to authenticator, anon, authenticated;

-- with
grant execute on function private.test to anon, authenticated;

You could try to rebuild the private schema and check if it works that way.