plan_filter.statement_cost_limit is not applied to anonymous queries

[derhuerst]

Environment

I'm using Docker for Mac v4.24.0 on macOS v13.6.1 on an Apple Silicon M2 machine. I use linux/amd64 images though.

I'm using PostgREST 11.2.2 via Docker:

docker run --rm -it postgrest/postgrest /bin/sh -c 'postgrest -v'
# PostgREST 11.2.2 (f884da7)

I'm running it against PostgreSQL 15 (custom Docker image that combines postgis/postgis:15-3.3-alpine with pg_plan_filter).

Description of issue

I cannot get the automatic role settings documented in #2561 to work with pg_plan_filter.

---

I have imported my data into a database gtfs_1699493108 and set up PostgREST as follows (shortened for readability):

CREATE SCHEMA IF NOT EXISTS "api";
BEGIN;

-- …

CREATE OR REPLACE VIEW "api".arrivals_departures AS
-- …
;

-- …

-- pattern from https://stackoverflow.com/a/8099557
DO
$$
BEGIN
	IF EXISTS (
		SELECT FROM pg_catalog.pg_roles
		WHERE  rolname = 'web_anon'
	) THEN
		-- Roles are shared across databases, so we have remove previously configured privileges.
		-- This might of course interfere with other programs running on the DBMS!
		-- todo: find a cleaner solution
		RAISE WARNING 'Role web_anon already exists. Reassigning owned DB objects to current_user().';
		REASSIGN OWNED BY web_anon TO SESSION_USER;
	ELSE
		BEGIN
			CREATE ROLE web_anon NOLOGIN NOINHERIT;
		EXCEPTION
			WHEN duplicate_object THEN
				RAISE NOTICE 'Role web_anon was just created by a concurrent transaction.';
		END;
	END IF;
END
$$;


-- https://postgrest.org/en/stable/tutorials/tut0.html#step-4-create-database-for-api
-- https://postgrest.org/en/stable/explanations/db_authz.html
-- todo: is this secure?
GRANT USAGE ON SCHEMA "api" TO web_anon;
GRANT SELECT ON ALL TABLES IN SCHEMA "api" TO web_anon;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA "api" TO web_anon;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA "api" TO web_anon;

COMMENT ON SCHEMA "api" IS
$$GTFS REST API
This REST API is created by running [PostgREST](https://postgrest.org/) on top of a [PostgreSQL](https://www.postgresql.org) DB generated using [gtfs-via-postgres](https://github.com/public-transport/gtfs-via-postgres).
$$;

COMMIT;

---

I then set timeout & cost limit on web_anon:

ALTER ROLE web_anon SET statement_timeout = 500;
ALTER ROLE web_anon SET plan_filter.statement_cost_limit = 500.0;

The query run by PostgREST during startup results in the following:

-- querying as `postgrest` using psql
with
role_setting as (
  select r.rolname, unnest(r.rolconfig) as setting
  from pg_auth_members m
  join pg_roles r on r.oid = m.roleid
  where member = current_user::regrole::oid
),
kv_settings AS (
  SELECT
    rolname,
    substr(setting, 1, strpos(setting, '=') - 1) as key,
    lower(substr(setting, strpos(setting, '=') + 1)) as value
  FROM role_setting
),
iso_setting AS (
  SELECT rolname, value
  FROM kv_settings
  WHERE key = 'default_transaction_isolation'
)
select
  kv.rolname,
  i.value as iso_lvl,
  coalesce(array_agg(row(kv.key, kv.value)) filter (where key <> 'default_transaction_isolation'), '{}') as role_settings
from kv_settings kv
join pg_settings ps on ps.name = kv.key and ps.context = 'user'
left join iso_setting i on i.rolname = kv.rolname
group by kv.rolname, i.value;

--  rolname  | iso_lvl |                             role_settings                              
-- ----------+---------+------------------------------------------------------------------------
--  web_anon |         | {"(plan_filter.statement_cost_limit,500.0)","(statement_timeout,500)"}
-- (1 row)

When I query PostgREST anonymously:

curl 'http://localhost:4000/arrivals_departures?limit=10000' -f -L -v

it fails, with PostgreSQL showing the following logs:

2023-11-09 01:48:49.277 UTC [3360] LOG:  statement: SET client_encoding = 'UTF8';SET client_min_messages TO WARNING;
2023-11-09 01:48:49.281 UTC [3360] LOG:  execute 0: BEGIN ISOLATION LEVEL READ COMMITTED READ ONLY
2023-11-09 01:48:49.291 UTC [3360] LOG:  execute 1: select set_config($1, $2, true), set_config($3, $4, true), set_config($5, $6, true), set_config($7, $8, true), set_config($9, $10, true), set_config($11, $12, true), set_config($13, $14, true), set_config($15, $16, true)
2023-11-09 01:48:49.291 UTC [3360] DETAIL:  parameters: $1 = 'search_path', $2 = '"api", "public"', $3 = 'role', $4 = 'web_anon', $5 = 'statement_timeout', $6 = '500', $7 = 'request.jwt.claims', $8 = '{"role":"web_anon"}', $9 = 'request.method', $10 = 'GET', $11 = 'request.path', $12 = '/arrivals_departures', $13 = 'request.headers', $14 = '{"user-agent":"curl/8.2.1","accept":"*/*","host":"localhost:4000"}', $15 = 'request.cookies', $16 = '{}'
2023-11-09 01:48:49.416 UTC [3360] LOG:  execute 2: WITH pgrst_source AS ( SELECT "api"."arrivals_departures".* FROM "api"."arrivals_departures"    LIMIT $1 OFFSET $2 )  SELECT null::bigint AS total_result_set, pg_catalog.count(_postgrest_t) AS page_total, coalesce(json_agg(_postgrest_t), '[]') AS body, nullif(current_setting('response.headers', true), '') AS response_headers, nullif(current_setting('response.status', true), '') AS response_status FROM ( SELECT * FROM pgrst_source ) _postgrest_t
2023-11-09 01:48:49.416 UTC [3360] DETAIL:  parameters: $1 = '10000', $2 = '0'
2023-11-09 01:48:49.812 UTC [3360] ERROR:  canceling statement due to statement timeout
2023-11-09 01:48:49.812 UTC [3360] STATEMENT:  WITH pgrst_source AS ( SELECT "api"."arrivals_departures".* FROM "api"."arrivals_departures"    LIMIT $1 OFFSET $2 )  SELECT null::bigint AS total_result_set, pg_catalog.count(_postgrest_t) AS page_total, coalesce(json_agg(_postgrest_t), '[]') AS body, nullif(current_setting('response.headers', true), '') AS response_headers, nullif(current_setting('response.status', true), '') AS response_status FROM ( SELECT * FROM pgrst_source ) _postgrest_t
2023-11-09 01:48:49.814 UTC [3360] LOG:  execute 3: ABORT

Note that statement_timeout = 500 is being set within the transaction, but plan_filter.statement_cost_limit = 500.0 is not being set.

[derhuerst]

possibly related: PostgREST/postgrest-docs#152

[wolfgangwalther]

I don't think this is going to work at all, because plan_filter.statement_cost_limit is SUSET, i.e. you need SUPERUSER privileges to set it:

https://github.com/pgexperts/pg_plan_filter/blob/5081a7b5cb890876e67d8e7486b6a64c38c9a492/plan_filter.c#L69-L80

This means that PostgREST can not do SET plan_filter.statement_cost_limit TO <value_from_role>; before the query.

SUSET gucs will only work when set directly on the authenticator role.

[wolfgangwalther]

Actually, this could work with PG 15+, because you could do:

GRANT SET ON PARAMETER plan_filter.statement_cost_limit TO authenticator;

[steve-chavez]

Right. We've moved on from applying superuser settings because of #2896.

[wolfgangwalther]

Right. We've moved on from applying superuser settings because of #2896.

So, the GRANT will not work, yet, because we filter those out anyway, right?

[wolfgangwalther]

There is also a has_parameter_privilege(...) function, which could be used to check whether a superuser setting could still be set.