Wrong ownership for some built-in event triggers

[steve-chavez]

Problem

See the functions and event trigger owned by postgres:

select proname, proowner::regrole from pg_proc where prorettype = 'event_trigger'::regtype;

proname	proowner
event_trigger_in	supabase_admin
pgrst_drop_watch	supabase_admin
grant_pg_graphql_access	supabase_admin
trg_mask_update	supabase_admin
pgrst_ddl_watch	supabase_admin
grant_pg_net_access	postgres
set_graphql_placeholder	supabase_admin
increment_schema_version	supabase_admin
grant_pg_cron_access	postgres

select evtname, evtowner::regrole, evtfoid::regproc from pg_event_trigger;

evtname	evtowner	evtfoid
issue_pg_net_access	postgres	grant_pg_net_access
issue_pg_graphql_access	supabase_admin	grant_pg_graphql_access
issue_graphql_placeholder	supabase_admin	set_graphql_placeholder
pgrst_ddl_watch	supabase_admin	pgrst_ddl_watch
pgrst_drop_watch	supabase_admin	pgrst_drop_watch
pgsodium_trg_mask_update	supabase_admin	pgsodium.trg_mask_update
graphql_watch_ddl	supabase_admin	graphql.increment_schema_version
graphql_watch_drop	supabase_admin	graphql.increment_schema_version
issue_pg_cron_access	supabase_admin	grant_pg_cron_access

This means that any user can DROP those and cause services to malfunction:

drop function grant_pg_cron_access cascade;
drop function grant_pg_net_access cascade;

Both DROPs above work.

[steve-chavez]

Furthermore, it looks like it's possible to DROP any evtrig by doing CASCADE:

-- this works
drop function grant_pg_graphql_access cascade;

This despite being owned by the supabase_admin.

[soedirgo]

So resolution is to reassign the owner for BOTH the event trigger and the event trigger function to supabase_admin (CMIIW)

[steve-chavez]

@soedirgo Yes, correct. They all should be owned by supabase_admin.

I don't know if these evtrigs are created inside the dashboard somehow to have the postgres user, but if they are, it would be good to put them in some file under supabase/postgres instead. (any SQL inside dashboard code is too hard to find).

[soedirgo]

I think it's owned by postgres because of an erratic migrations setup - will look into this tmr

[steve-chavez]

The steps required for this:

1. Add a migration file for new projects on https://github.com/supabase/postgres/tree/develop/migrations/db/migrations

2. backpatch to existing projects using https://github.com/supabase/playbooks/blob/main/playbooks/postgres/running-ad-hoc-sql-on-user-projects.md (private link)

Notes

The problem seems to arise from:

• init scripts run as the postgres user https://github.com/supabase/postgres/tree/develop/migrations/db/init-scripts
• migrations run as the supabase_admin https://github.com/supabase/postgres/tree/develop/migrations/db/migrations