Role based dynamic schemas

[cxt9]

I'm working on a project using PostgreSQL and PostgREST where each user has a dedicated schema (e.g., schema_{userId}) that is created dynamically when they log in.

My goal is to dynamically expose these schemas through PostgREST without needing to manually update the db-schemas configuration every time a new schema is created. Ideally, I'd like to include the schema name as part of the JWT token, so PostgREST can dynamically switch to the appropriate schema based on the JWT claims, without requiring any manual configuration updates.

While I can make it work by adding the Accept-Profile header to the request and manually including the schema in the db-schemas setting, this approach is not scalable as new schemas are created frequently.

I've also explored using a SQL function to dynamically update the pgrst.db_schemas configuration and trigger a reload via the NOTIFY command, but I'm facing challenges with ensuring the schemas are correctly recognized by PostgREST without manual intervention.

I'm seeking advice on how to fully automate this process, preferably by dynamically switching schemas based on the JWT token or another method, so that PostgREST can recognize and handle newly created schemas seamlessly. Any insights or suggestions would be greatly appreciated!

[wolfgangwalther]

Ideally, I'd like to include the schema name as part of the JWT token, so PostgREST can dynamically switch to the appropriate schema based on the JWT claims, without requiring any manual configuration updates.

This is impossible to do. PostgREST needs to know about the available schemas before, because it needs to load the schema cache - for each exposed schema. This happens after startup, so all exposed schemas need to be in db-schemas.

I've also explored using a SQL function to dynamically update the pgrst.db_schemas configuration and trigger a reload via the NOTIFY command, but I'm facing challenges with ensuring the schemas are correctly recognized by PostgREST without manual intervention.

Which challenges? This approach should work in theory.

[cxt9]

I have this:

CREATE OR REPLACE FUNCTION update_pgrst_schemas() RETURNS void AS $$
BEGIN
	
    -- Update the pgrst.db_schemas setting with all matching schemas
    PERFORM set_config('pgrst.db_schemas', (
        SELECT string_agg(nspname, ',')
        FROM pg_namespace
        WHERE nspname LIKE 'schema_%'
    ), true);

    -- Notify PostgREST to reload its configuration
    PERFORM pg_notify('pgrst', 'reload config');
END;
$$ LANGUAGE plpgsql;

I'm calling it while I have the schema in place. I get the following response when I call postgrest (with the schema name as Accept-Profile):

{ "code": "PGRST106", "details": null, "hint": null, "message": "The schema must be one of the following: public" }

Which I assume means schemas were not loaded by postgrest.

On a general note - is there a better practice for separating data between users? I know I can add row level security as an additional solution.

[wolfgangwalther]

On a general note - is there a better practice for separating data between users? I know I can add row level security as an additional solution.

Yes, absolutely. RLS is much better than "one schema per tenant". I strongly advise against this approach.

[wolfgangwalther]

PERFORM set_config('pgrst.db_schemas', (

This only changes the setting for the current transaction / session. Once you NOTIFY, PostgREST will load the config value in an entirely different session, so that setting is not going to be there anymore.

You'll need to do something like ALTER ROLE <authenticator-role> SET pgrst.db_schemas ... instead and then do the NOTIFY.