set log_min_messages to 'fatal' breaks on RDS

[johndpope]

Bug Report

• I confirm this is a bug with Supabase, not with my own application.
• I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

The supabase/realtime service (version v2.34.31) fails to apply migrations when connected to an external Amazon RDS PostgreSQL database. The failure occurs because the migration process attempts to set the log_min_messages parameter to 'fatal' within a SQL function definition, but the supabase_admin user lacks the necessary privileges to modify this parameter. This results in a MigrationsFailedToRun error with the message permission denied to set parameter "log_min_messages" (PostgreSQL error code 42501).

This issue appears to be specific to external databases like RDS, where superuser privileges are restricted by the hosting provider, unlike Supabase’s managed database environment where such operations might be permitted.

To Reproduce

Steps to reproduce the behavior:

1. Set up an external Amazon RDS PostgreSQL instance (e.g., version 17.2).
2. Configure the supabase/realtime service to connect to the RDS instance using the supabase_admin user with credentials and the following environment variables in docker-compose.yml:

   realtime:
     image: supabase/realtime:v2.34.31
     environment:
       DB_HOST: blabla
       DB_PORT: 5432
       DB_USER: supabase_admin
       DB_PASSWORD: your-super-secret-and-long-postgres-password
       DB_NAME: postgres
       REALTIME_DB_SCHEMA: _realtime
       REALTIME_MIGRATION_SCHEMA: _realtime
       DB_AFTER_CONNECT_QUERY: 'SET search_path TO _realtime'
   
   
   

Run the realtime service using docker compose up -d --build -e POP_DEBUG=true.
Observe the logs, which will show the migration attempt and the subsequent error.
Expected behavior
The realtime service should successfully apply its migrations on the external RDS database without attempting to set restricted parameters like log_min_messages. Alternatively, the service should gracefully handle cases where the supabase_admin user lacks the privilege to set such parameters, falling back to a default behavior or logging a warning instead of failing.

Screenshots
N/A (Logs provided below serve as evidence).

System information
OS: Linux (AWS EC2 instance)
Browser (if applies): N/A
Version of supabase-js: N/A (issue is with the realtime service, not supabase-js)
Version of Node.js: N/A
Docker Compose version: 2.x (latest stable)
supabase/realtime image version: v2.34.31
PostgreSQL version on RDS: 17.2
Additional context
Logs

this file sets. log_min_messages
20230328144023_create_list_changes_function.ex

supabase-realtime  | 03:10:27.610 request_id=GCtSWgv4R_3_HEYAADIC [info] HEAD /api/tenants/realtime-dev/health
supabase-realtime  | 03:10:27.610 request_id=GCtSWgv4R_3_HEYAADIC project=realtime-dev external_id=realtime-dev [warning] Connection process starting up
supabase-realtime  | 03:10:27.618 project=realtime-dev external_id=realtime-dev [info] Applying migrations to mly-pg-live-prod.cz82q6siq69n.ap-southeast-2.rds.amazonaws.com
supabase-realtime  | 03:10:27.635 [info] == Running 20230328144023 Realtime.Tenants.Migrations.CreateListChangesFunction.change/0 forward
supabase-realtime  | 03:10:27.635 [info] execute "create or replace function realtime.list_changes(publication name, slot_name name, max_changes int, max_record_bytes int)\n      returns setof realtime.wal_rls\n      language sql\n      set log_min_messages to 'fatal'\n    as $$  \n      with pub as (\n        select\n          concat_ws(\n            ',',\n            case when bool_or(pubinsert) then 'insert' else null end,\n            case when bool_or(pubupdate) then 'update' else null end,\n            case when bool_or(pubdelete) then 'delete' else null end\n          ) as w2j_actions,\n          coalesce(\n            string_agg(\n              realtime.quote_wal2json(format('%I.%I', schemaname, tablename)::regclass),\n              ','\n            ) filter (where ppt.tablename is not null and ppt.tablename not like '% %'),\n            ''\n          ) w2j_add_tables\n        from\n          pg_publication pp\n          left join pg_publication_tables ppt\n            on pp.pubname = ppt.pubname\n        where\n          pp.pubname = publication\n        group by\n          pp.pubname\n        limit 1\n      ),\n      w2j as (\n        select\n          x.*, pub.w2j_add_tables\n        from\n          pub,\n          pg_logical_slot_get_changes(\n            slot_name, null, max_changes,\n            'include-pk', 'true',\n            'include-transaction', 'false',\n            'include-timestamp', 'true',\n            'include-type-oids', 'true',\n            'format-version', '2',\n            'actions', pub.w2j_actions,\n            'add-tables', pub.w2j_add_tables\n          ) x\n      )\n      select\n        xyz.wal,\n        xyz.is_rls_enabled,\n        xyz.subscription_ids,\n        xyz.errors\n      from\n        w2j,\n        realtime.apply_rls(\n          wal := w2j.data::jsonb,\n          max_record_bytes := max_record_bytes\n        ) xyz(wal, is_rls_enabled, subscription_ids, errors)\n      where\n        w2j.w2j_add_tables <> ''\n        and xyz.subscription_ids[1] is not null\n      $$;"
supabase-realtime  | 03:10:27.637 project=realtime-dev external_id=realtime-dev error_code=MigrationsFailedToRun [error] MigrationsFailedToRun: %Postgrex.Error{
supabase-realtime  |   message: nil,
supabase-realtime  |   postgres: %{
supabase-realtime  |     code: :insufficient_privilege,
supabase-realtime  |     line: "3626",
supabase-realtime  |     message: "permission denied to set parameter \"log_min_messages\"",
supabase-realtime  |     file: "guc.c",
supabase-realtime  |     unknown: "ERROR",
supabase-realtime  |     severity: "ERROR",
supabase-realtime  |     pg_code: "42501",
supabase-realtime  |     routine: "set_config_with_handle"
supabase-realtime  |   },
supabase-realtime  |   connection_id: 19657,
supabase-realtime  |   query: nil
supabase-realtime  | }

Additional Notes
The RDS instance was initialized with rds_bootstrap.sql, which created the realtime schema to address a previous "schema does not exist" error. This resolved the earlier issue, but the migration failure persists due to the privilege restriction.
The supabase_admin user is created with CREATEDB CREATEROLE privileges via rds_bootstrap.sql, but this is insufficient for setting log_min_messages on RDS, where such changes are restricted to the rdsadmin role or via the RDS parameter group.
This issue does not occur when using a Supabase-managed database, suggesting that Supabase’s internal environment might grant additional privileges or handle this parameter differently.
Possible Workaround
A temporary workaround is to adjust the RDS parameter group to set log_min_messages to fatal or a compatible value, but this requires administrative access to the RDS instance and may not be ideal for all users.

Request
Please consider modifying the realtime service to:

Avoid setting log_min_messages during migrations on external databases where this privilege is restricted.
Provide a configuration option to disable parameter modifications during migration.
Update documentation to note this limitation when using external databases like RDS.

[johndpope]

i bypass this problem in my own fork - https://github.com/johndpope/realtime
https://github.com/johndpope/rds-supabase - but hitting another wall where the replication isnt working with supabase_admin user.