Unable to do configuration reloading with aggregate functions

[akshaysasidrn]

I'm trying to enable aggregate functions dynamically by setting the config and make PostgREST reload it.
I verified that it works when I set it through the environment variable, but the stateless approach with configuration reloading fails.
Am I doing something wrong? 🤔

This is the query that I'm running:

SELECT set_config('pgrst.db_aggregates_enabled', 'true', false)
NOTIFY pgrst, 'reload schema'

PostgREST logs:

27/May/2024:19:07:51 +0000: Config reloaded
27/May/2024:19:07:51 +0000: Schema cache loaded
^[[A172.17.0.1 - postgres [27/May/2024:19:08:00 +0000] "GET /1d02d90d-df49-488a-b661-64bbad67a04d?select=marks.sum()&order=id.desc HTTP/1.1" 400 - "" "curl/8.4.0"

curl:

 ❯ curl "localhost:3001/1d02d90d-df49-488a-b661-64bbad67a04d?select=marks.sum()"                                                                                                                     
{"code":"PGRST123","details":null,"hint":null,"message":"Use of aggregate functions is not allowed"}

[steve-chavez]

SELECT set_config('pgrst.db_aggregates_enabled', 'true', false)

The above alone won't work, you need to put it inside a function as described on https://postgrest.org/en/v12/references/configuration.html#in-database-configuration.

Alternatively if you have superuser you can do ALTER ROLE authenticator SET pgrst.db_aggregates_enabled TO true.

[akshaysasidrn]

Got it. Thank you for the explanation and the quick response. 👍🏾

[akshaysasidrn]

@steve-chavez I'm still facing trouble in getting it resolved.

I ran the SQL statements with psql onto the db which PostgREST connects to

     GRANT USAGE ON SCHEMA postgrest TO postgres;

      create or replace function postgrest.pre_config()
      returns void as $$
        select
          set_config('pgrst.db_aggregates_enabled', 'true', false)
      $$ language sql;

Started the PostgREST server:

docker run --name postgrest -p 3001:3000 \
  -e PGRST_DB_URI="postgres://postgres:[email protected]/mydb" 
  -e PGRST_DB_ANON_ROLE="postgres" 
  -e PGRST_JWT_SECRET="thesafestsecretever" 
  -e PGRST_LOG_LEVEL="info" -e PGRST_ADMIN_SERVER_PORT="3002" 
  -e PGRST_DB_PRE_CONFIG="postgrest.pre_config" \
  postgrest/postgrest


27/May/2024:21:47:08 +0000: Starting PostgREST 12.0.2 (a4e00ff)...
27/May/2024:21:47:08 +0000: Attempting to connect to the database...
27/May/2024:21:47:08 +0000: Connection successful
27/May/2024:21:47:08 +0000: Config reloaded
27/May/2024:21:47:08 +0000: Schema cache loaded

curl:

curl "localhost:3001/1d02d90d-df49-488a-b661-64bbad67a04d?select=marks.sum()"
{"code":"PGRST123","details":null,"hint":null,"message":"Use of aggregate functions is not allowed"}

I tried dropping the function and reloading the PostgREST server to see that it throws an error, to confirm that my config was picked up before.

drop function postgrest.pre_config;
NOTIFY pgrst, 'reload schema';

^[[A27/May/2024:21:58:55 +0000: Starting PostgREST 12.0.2 (a4e00ff)...
27/May/2024:21:58:55 +0000: Attempting to connect to the database...
27/May/2024:21:58:55 +0000: Connection successful
27/May/2024:21:58:55 +0000: An error ocurred when trying to query database settings for the config parameters
27/May/2024:21:58:55 +0000: {"code":"42883","details":null,"hint":"No function matches the given name and argument types. You might need to add explicit type casts.","message":"function postgrest.pre_config() does not exist"}
27/May/2024:21:58:55 +0000: Config reloaded
27/May/2024:21:58:55 +0000: Schema cache loaded

Not sure if I missed something from the docs yet again 😅

[laurenceisla]

I don't see a problem with your configuration. I also copy pasted your pre_config as-is and it worked. Maybe you have previously SET the configuration to the postgres role? That will override the pre_config setting. Try doing this:

ALTER ROLE postgres RESET pgrst.db_aggregates_enabled;

Then restart the container and try again.

---

As a side-note, you can check what configuration values are being applied by executing postgrest --dump-config. Add that command at the end of docker run to get the values:

  ...
  -e PGRST_DB_PRE_CONFIG="postgrest.pre_config" \
  postgrest/postgrest \
  postgrest --dump-config

db-aggregates-enabled = true
db-anon-role = "postgres"
db-channel = "pgrst"
...

[akshaysasidrn]

Maybe you have previously SET the configuration to the postgres role? That will override the pre_config setting. 🎯

Yeah, I had. It works now with the reset. Thank you!