Replies: 3 comments
|
I think the important distinction here is between a role that's read-only by default and a credential that's safe even when someone can execute arbitrary SQL with it. Supabase's own backup-role documentation explicitly acknowledges that the standard recipe doesn't provide the stronger guarantee you're looking for. For example, You can audit some of those capabilities with: SELECT
has_database_privilege(
'backup_reader', current_database(), 'TEMPORARY'
) AS can_temp,
has_function_privilege(
'backup_reader', 'pg_catalog.lo_create(oid)', 'EXECUTE'
) AS can_call_lo_create;But passing those checks wouldn't prove the entire callable surface is safe. If that security boundary is mandatory, I'd separate the backup operation from any credential exposed to arbitrary SQL execution. Supabase-managed daily backups on Pro or higher also avoid distributing a dedicated I couldn't find a documented customer-managed role configuration that guarantees all of your requirements on hosted Supabase, particularly around managed functions and future extension changes. I think this needs an explicit answer from Supabase about what they officially support rather than a collection of potentially unsafe |
|
Here is an architectural breakdown of the security boundaries on hosted Supabase, addressing each of your six design criteria and defining what PostgreSQL permissions can (and cannot) enforce against an adversary with arbitrary SQL execution: The Fundamental Architectural Reality: Permission vs. ConfigurationIn PostgreSQL, an enforced security boundary cannot rely on session settings like The security boundary must therefore be enforced strictly through Discretionary Access Control (DAC) and Object Ownership. 1. The Core Backup Role: True Table-Level Read-OnlyIn PostgreSQL 14+ (which all modern hosted Supabase instances run), the cleanest, least-privilege base is built using -- 1. Create the dedicated backup role
CREATE ROLE backup_role WITH LOGIN PASSWORD 'secure_random_credential';
-- 2. Grant read-only access to all current and future tables/views/sequences
GRANT pg_read_all_data TO backup_role;
-- 3. Allow pg_dump to read rows protected by RLS
ALTER ROLE backup_role BYPASSRLS;
-- 4. Strip database-level temporary table creation
REVOKE TEMP ON DATABASE postgres FROM backup_role;
REVOKE CREATE ON SCHEMA public FROM backup_role;Why this is a hard boundary for data modification: 2. Handling
|
|
Some statements in the longer answer above won't do what it says, so test before relying on it:
Check the effective result: select has_database_privilege('backup_role', current_database(), 'TEMPORARY') as can_temp,
has_function_privilege('backup_role', 'pg_catalog.lo_create(oid)', 'EXECUTE') as can_lo_create,
has_table_privilege('backup_role', 'vault.decrypted_secrets', 'SELECT') as can_read_vault;If any is still true after the revokes, the boundary isn't there. A customer role can't remove PUBLIC EXECUTE from functions it doesn't own, so for the strict version I agree with kirlyr: keep this credential away from anything that can run arbitrary SQL, and get a written answer from support. |
Uh oh!
There was an error while loading. Please reload this page.
I am looking for a supported least-privilege design for a dedicated logical-backup role on hosted Supabase, preferably on the Free plan.
The goal is a server-enforced boundary: the backup role should be able to export the approved backup scope with
pg_dump, but must not modify persistent data, schema, privileges, sequences or large objects, or initiate external side effects through callable routines—even if its credential is used to submit arbitrary SQL or start a READ WRITE transaction. This is not a request to prevent every internal disk write or all resource-exhaustion attacks.Supabase's backup-role guide describes
pg_read_all_data,BYPASSRLS, anddefault_transaction_read_only. I understand these address export coverage and accidental writes, rather than necessarily providing the stronger boundary above:https://supabase.com/kb/troubleshooting/pg_dump-fails-with-query-would-be-affected-by-row-level-security-policy
What is the recommended hosted-Supabase approach for the following?
lo_create,lo_from_bytea,lo_put, andlo_unlink, including equivalent entry points, while preserving necessary backup reads.Pointers to official documentation, supported ACL patterns, affected platform dependencies, and safe negative-test criteria would be helpful. Please distinguish a community workaround from a Supabase-supported configuration, and state whether an alternative requires a paid plan or another service.
This is a general design question, not a request to change a project. The proposed backup role remains NOLOGIN while the design is unresolved. No project identifiers, credentials, user data, or dumps are needed for this question.
All reactions