Clarification needed: temporary read-only PostgreSQL login role via Supabase Management API #52074
Replies: 3 comments
|
Not official, but I can answer part of this from the Supabase CLI source, since the CLI calls this exact endpoint itself. What's confirmed from the CLI code (
What I can't confirm from code is the exact grants for select rolname, rolbypassrls, rolvaliduntil
from pg_roles where rolname like 'cli_login_%';
select r.rolname as member, g.rolname as granted_role
from pg_auth_members m
join pg_roles r on r.oid = m.member
join pg_roles g on g.oid = m.roleid
where r.rolname like 'cli_login_%';This matters for a backup. If the role doesn't bypass RLS, For the actual goal (a logical backup without disrupting production), the least-risk path is to let the CLI handle it, since it already uses this endpoint under the hood: supabase link --project-ref <ref>
supabase db dump --linked -f schema.sql
supabase db dump --linked --data-only -f data.sql
supabase db dump --linked --role-only -f roles.sqlIt runs |
|
Here is a comprehensive breakdown of the Direct Answers to Your 7 Questions1. Exact Privileges & RLS Behavior
2. Default Lifetime (TTL)
3. Handling of Existing Roles
4. Production Availability & Connection Pool Impact
5. Revocation Scope
6. Backup Compatibility with
|
|
RagedPanda's CLI-source answer matches what I can verify. A few points in the longer answer don't match the docs:
For a full dump without the |
Uh oh!
There was an error while loading. Please reload this page.
Hello Supabase community,
I am looking for clarification regarding the experimental Supabase Management API endpoint:
POST /v1/projects/{ref}/cli/login-roleDocumentation:
https://supabase.com/docs/reference/api/v1-create-login-role
Context
We operate a production SaaS application on Supabase Free, using PostgreSQL 17.
The application handles real customer data, invoicing and payments. We need to perform a logical PostgreSQL backup using
pg_dump, without interrupting production services or resetting the existingpostgrespassword.We are considering the endpoint above with
read_only=true, but some aspects of its behavior are not sufficiently documented for us to safely use it in production.Questions
Privileges: What exact PostgreSQL privileges does a login role created with
read_only=truereceive? Does it inherit fromsupabase_read_only_user, includingpg_read_all_dataorBYPASSRLS?TTL: What is the default lifetime of the temporary role? Can its TTL be specified or determined before creation?
Existing roles: If an expired
cli_login_postgresrole already exists, does the endpoint create a new role, reuse the existing role, or rotate its credentials?Production impact: Can creating this temporary role modify existing PostgreSQL passwords, connection pools, application sessions or other database credentials?
Revocation: The documented DELETE endpoint appears to operate at project scope. Does it revoke all CLI login roles, or can a specific temporary role be revoked without affecting other active CLI sessions?
Backup compatibility: Is a role created with
read_only=truesuitable for a complete logical backup using PostgreSQL 17pg_dump, including application schemas, Auth, Storage metadata, RLS policies, privileges and migration history? What are the known limitations?Cost: Does creating this temporary login role incur any additional charges on the Free plan?
Our requirement
We need a supported and predictable method that preserves production availability and does not change existing PostgreSQL credentials.
We have not executed the POST request or created any temporary role.
If this experimental endpoint is not recommended for production backups, what officially supported alternative would you recommend that avoids resetting the existing
postgrespassword?Thank you for any clarification or pointers to the relevant documentation.
All reactions