Request fails if impersonated role doesn't have permission to set GUC from authenticator role

[colophonemes]

Environment

• PostgreSQL version: 13.10 (Ubuntu)
• PostgREST version: postgrest/postgrest:v11.1.0
• Operating system: AWS ECS / Docker

Description of issue

If the authenticator role has a custom config applied to it (in our case log_min_duration_statement = -1), requests fail with the following permissions error:

{
  "code": "42501",
  "details": null,
  "hint": null,
  "message": "permission denied to set parameter \"log_min_duration_statement\""
}

This seems to be a regression on v11 (specifically I think related to #2561), because we've had no issues running against the same database using v10.2.0.

It feels like what's happening is that PostgREST is switching to a less-privileged role (e.g. from api_authenticator -> api_user), and then trying to apply the superset of GUCs from both the authenticator and user roles. Then, if the user role doesn't have permission to apply the config change, it errors.

[steve-chavez]

@colophonemes Thanks for the report. Can you share the output (redacted) of the following query?

select r.rolname, r.rolconfig
from pg_roles r ;

If the authenticator role has a custom config applied to it (in our case log_min_duration_statement = -1), requests fail with the following permissions error:

So I'm not able to reproduce the error when adding a log_min_duration_statement to the authenticator but by adding the same setting to the anonymous role:

ALTER ROLE postgrest_test_anonymous SET log_min_duration_statement to 1;

I do get the error:

curl localhost:3000/projects

{"code":"42501","details":null,"hint":null,"message":"permission denied to set parameter \"log_min_duration_statement\""}

So you likely have the setting on members of authenticator, #2561 only adds settings for those but not for the authenticator.

---

We should still correct this. i.e. not add settings that require high privileges for authenticator members.

[steve-chavez]

We should still correct this. i.e. not add settings that require high privileges for authenticator members.

Fortunately pg_settings has a context column that will let us filter these settings:

postgres=> select context from pg_settings where name = 'log_min_duration_statement';
-[ RECORD 1 ]------
context | superuser

We should apply only the ones that have a user context:

postgres=> select distinct(context) from pg_settings;
-[ RECORD 1 ]--------------
context | postmaster
-[ RECORD 2 ]--------------
context | superuser-backend
-[ RECORD 3 ]--------------
context | user
-[ RECORD 4 ]--------------
context | internal
-[ RECORD 5 ]--------------
context | backend
-[ RECORD 6 ]--------------
context | sighup
-[ RECORD 7 ]--------------
context | superuser

[colophonemes]

Can you share the output (redacted) of the following query?

Ah, unfortunately we've already removed that setting from the DB so I can't provide query output, however, you were absolutely right that it wasn't just the authenticator role, and indeed the user role had an identical setting applied as well (which presumably had just never been set during a PostgREST transaction before). Removing the setting from the user role allowed queries to work again on v11.1.0.