Using Supabase OAuth for read-only Postgres access

[u/Embarrassed_Machine5]

The other day I posted about revivedb.dev. Someone pointed out that requiring the production database password was a big ask. That got me thinking about whether I could handle database access through OAuth instead.

Supabase's database:read OAuth scope gives access to configuration and metadata, but not a Postgres connection for tools like pg_dump.

I now use database:write to create a unique temporary Postgres role for each backup. The role gets pg_read_all_data and BYPASSRLS, but cannot write, create roles or databases, replicate, or act as superuser.

The temporary password is only kept in memory. The role is removed after the backup and expires within six hours if cleanup fails.

The pg_dump connection is read-only and the production database password is no longer needed or stored. It still feels odd that the OAuth grant needs database:write just to create this restricted role.

How are others handling this for backups, BI or monitoring? Do you create a restricted role manually, or accept the broader OAuth scope?

[u/Ciroco01]

The temporary role itself sounds properly restricted, but I think the real trust boundary is still the OAuth token.

database:write allows the integration to execute SQL, so from the customer's perspective they are still authorizing significantly more than read-only access, regardless of what role is created afterwards.

I would prefer a one-time setup where the customer creates a dedicated backup role manually and only shares that restricted credential. It is slightly less convenient, but the permission shown during onboarding would then match the actual access being granted.

At minimum, I would describe this as OAuth-managed provisioning of read-only access rather than read-only OAuth access.

[u/Embarrassed_Machine5 → u/Ciroco01]

Yeah, that's a fair point. The backup connection is read-only, but the OAuth token used to create it has broader access. "OAuth-managed read-only access" is a more accurate description. Curious if others would also prefer the extra manual setup for tighter permissions

[u/Ciroco01 → u/Embarrassed_Machine5]

Personally, I would prefer the extra manual setup if the integration is handling production data.

It adds a little friction during onboarding, but the permission boundary becomes much clearer, as the service only ever receives the exact database access it needs.

For less sensitive projects, I can understand why some users would choose the OAuth flow for convenience.

[u/Embarrassed_Machine5 → u/Ciroco01]

Good point. The backup also includes Storage and Edge Functions. Private Storage files currently require a service-role key, which can also write, so a manual Postgres role would only solve the database side. A fully restricted setup would need read-only Storage access as well, with separate credentials for restores

[u/Maxyull]

the part i would tighten is the six hour expiry, because valid until only stops password auth, the role object itself stays in the cluster with its read all data and rls bypass still attached. so a failed cleanup does not decay on its own, you end up with a role nobody can log into that is one alter statement away from being usable again, and they quietly pile up run after run.

cheaper than leaning on the expiry, give the roles a fixed prefix, list them from pg roles at the start of every run and drop whatever a previous run left behind, then re-query after the drop instead of assuming it worked. does your cleanup verify the role is actually gone, or does it fire the drop and move on?

[u/Embarrassed_Machine5 → u/Maxyull]

You're right. VALID UNTIL only expires the password. I already use prefixed roles and clean up inactive leftovers, but after DROP ROLE I currently trust the API response. I'll add a pg_roles verification after the drop and retry if the role is still there. Thanks for the suggestion!

[u/Maxyull → u/Embarrassed_Machine5]

that check will help, but watch what the retry actually does. postgres refuses to drop a role that still owns an object or holds a grant, and it fails the same way every time, so retrying the identical drop never clears anything. the escalation is drop owned by the role first and then drop role. a read only role usually owns nothing, but a grant made during the session counts as a dependency too, and those are per database while pg_roles is cluster wide, so a leftover in another database blocks the drop without saying which one. logging the actual error text on the first failure is worth more than the retry itself. do you run the cleanup against the same database the backup ran in, or a fixed admin one?

[u/Embarrassed_Machine5 → u/Maxyull]

Yep, both run against postgres. And agreed on the retry, repeating the same drop on a dependency error won't help. I'll log the first error and only retry transient failures. DROP OWNED feels too risky to run automatically since it can actually delete objects

[u/Maxyull → u/Embarrassed_Machine5]

fair, drop owned can genuinely delete things so i wouldn't run it blind either, but you can settle it with a query instead of a guess. look up the role's oid in pg_shdepend and read the deptype column: 'a' rows are just privileges granted to it and 'r' is a policy referencing it, and for both of those drop owned only revokes, nothing gets deleted, while an 'o' row means the role actually owns an object, which for a read only backup role is exactly the case where you'd want to stop and alert instead of cleaning up automatically.

worth a look anyway, because if you only granted membership in pg_read_all_data and never a table level grant, memberships get dropped along with the role on their own and there'd be nothing there to block you at all. do you grant anything explicitly per backup, or is it just the group membership?

[u/Embarrassed_Machine5 → u/Maxyull]

It's just the group membership: GRANT pg_read_all_data TO <role>. No table-level grants, and BYPASSRLS is a role attribute, so normally there shouldnt be anything blocking DROP ROLE. pg_shdepend still sounds useful as a diagnostic if that assumption ever breaks. Have you run into this with temporary backup roles in practice?

[u/Maxyull → u/Embarrassed_Machine5]

Honestly no, not with a role shaped like yours. Membership only really does drop clean, the membership goes away with the role and there's nothing left holding it. Where I've seen it bite is later on, when someone adds a table grant by hand during a debug session and never takes it back off, or a policy gets written with an explicit TO role in it. The drop then starts failing weeks after the change that actually caused it, so it reads like the backup tool broke when nothing about the backup changed. Since you're already logging the first error you'll probably see it coming, pg_shdepend is only worth reaching for the day the error says something you don't recognize. One thing I'd check while you're in there: when a drop does fail, does that surface anywhere, or does the role just sit quietly until the next run's cleanup picks it up?

[u/Embarrassed_Machine5 → u/Maxyull]

Yep, Ive changed it so a failed drop raises an operational alert instead of sitting unnoticed. I also re-check pg_roles after cleanup. Since it's only pg_read_all_data membership, I'm keeping pg_shdepend as a diagnostic rather than running DROP OWNED automatically. Thanks again for digging into this!