Allow configurable role settings

[steve-chavez]

Problem

Right now we can set statement_timeout at a global level, by setting a config on the authenticator role:

ALTER ROLE authenticator SET statement_timeout = 5000;

This statement_timeout will be respected because GUC settings kick in at login time.

However if we want to set different statement_timeout for anon and web_user.

ALTER ROLE anon SET statement_timeout = 500;
ALTER ROLE web_user SET statement_timeout = 3000;

These will not be picked up and their statement_timeout won't be enforced.

Proposal

Query the settings specified on a config

db-tx-settings = 'statement_timeout'

These will be queried from pg_db_role_setting and then cached so we can do

BEGIN;
select set_config('role', 'anon', true), set_config('statement_timeout', '500', true);...
END;

For every transaction.

Further possiblities

This would allow us to integrate with other extensions, like https://github.com/pgexperts/pg_plan_filter. So we could say

db-tx-settings = 'statement_timeout, plan_filter.statement_cost_limit'

ALTER ROLE anon SET plan_filter.statement_cost_limit = 1000.0;

And plan_filter.statement_cost_limit would be enforced for every transaction.

---

This would solve #249

[steve-chavez]

db-tx-settings = 'statement_timeout'

We could avoid the above config by instead doing

ALTER ROLE anon SET pgrst.tx.statement_timeout = 500;
ALTER ROLE anon SET pgrst.tx.plan_filter.statement_cost_limit = 1000.0;

Which would namespace our settings and make it clear that they only work through REST.

Maybe also enable different settings per route

ALTER ROLE anon SET pgrst.tx.projects.statement_timeout = 500; -- /projects path, unforunately we cannot use `/` inside a setting
ALTER ROLE anon SET pgrst.tx.clients.plan_filter.statement_cost_limit = 1000.0; -- /clients path

Which reminds me of #2468, that could be done in the same way.

ALTER ROLE anon SET pgrst.tx.projects.isolation = 'repeatable-read'; 

[wolfgangwalther]

The problem here is that SET ROLE does not load settings like a login does. Why not just fake this after our set role command?

WITH
role_setting (database, setting) AS (
  SELECT setdatabase,
         unnest(setconfig)
    FROM pg_catalog.pg_db_role_setting
   WHERE setrole = CURRENT_USER::regrole::oid
         AND setdatabase IN (0, (SELECT oid FROM pg_catalog.pg_database WHERE datname = CURRENT_CATALOG))
),
kv_settings (database, k, v) AS (
  SELECT distinct on (k)
         database,
         substr(setting, 1, strpos(setting, '=') - 1) as k,
         substr(setting, strpos(setting, '=') + 1) as v
    FROM role_setting
   ORDER BY k, database DESC
)
SELECT set_config(k, v, true)
  FROM kv_settings;

Something like this. This would allow to just do ALTER ROLE anon SET statement_timeout = 500;. Without the need to implement more config options or cache even more data.

[steve-chavez]

I think that query would be too expensive to run at the start of every transaction(would have to test it but it seems so, considering it's done for each request). Additionally it would not allow us to change the isolation as proposed above nor it would allow a per route setting.

[steve-chavez]

Without the need to implement more config options or cache even more data.

Not having a config for this definitely has some value though. Since we do the role switching ourselves users expect ALTER ROLE anon SET statetment_timeout to work transparently.

I still would like to cache these configs for perf though.

Another option is to run the above query and get the settings for all the roles that are members of the connection role(authenticator) and cache the settings. They would get reloaded with NOTIFY pgrst. I think this is more reasonable to add.

This logic might later be useful for #2446 (comment) as well.