Supabase•15mo ago•

2 replies

Grant Vault Access to New Role

I'm running into an edge case with vault and would appreciate some advice if folks have worked around this.

- In my application I rely on the supabase vault for a handful of operations.
- Everything works fine in my application as I'm connecting to my db as the primary (

postgres

postgres

) user and bypassing any security policies that exist on vault.
- I have created a separate

readonly_user

readonly_user

that is used for some internal admin work. This user also needs access to some of the information within

vault.decrypted_secrets

vault.decrypted_secrets

(maybe this violates the intended design of the vault... but this isn't my concern right now).
- Because the vault schema is owned by

supabase_admin

supabase_admin

user I'm having trouble granting access to the new

readonly_user

readonly_user

(even though the vault docs indicate I should be able to grant such access somehow https://supabase.com/docs/guides/database/vault#:~:text=4b19%2D86e1%2Dfebf3cd40619-,Which,-roles%20should%20have)
- I'm running the following query (in supabase UI -->

postgres

postgres

user)

DO $$
BEGIN
    -- Grant schema usage
    EXECUTE 'GRANT USAGE ON SCHEMA vault TO readonly_user';
    
    -- Grant select on all existing tables in the schema
    EXECUTE 'GRANT SELECT ON ALL TABLES IN SCHEMA vault TO readonly_user';
    
    -- Set default privileges for future tables
    EXECUTE 'ALTER DEFAULT PRIVILEGES IN SCHEMA vault GRANT SELECT ON TABLES TO readonly_user';
END $$;

DO $$
BEGIN
    -- Grant schema usage
    EXECUTE 'GRANT USAGE ON SCHEMA vault TO readonly_user';
    
    -- Grant select on all existing tables in the schema
    EXECUTE 'GRANT SELECT ON ALL TABLES IN SCHEMA vault TO readonly_user';
    
    -- Set default privileges for future tables
    EXECUTE 'ALTER DEFAULT PRIVILEGES IN SCHEMA vault GRANT SELECT ON TABLES TO readonly_user';
END $$;

- the query doesn't throw an error but doesn't add any privileges (I'm assuming because I'm not running this as the

supabase_admin

supabase_admin

. Confirmed by running:

SELECT * 
FROM information_schema.role_table_grants
WHERE table_schema = 'vault';

SELECT * 
FROM information_schema.role_table_grants
WHERE table_schema = 'vault';

Any guidance would be appreciated.

Vault | Supabase Docs

Vault is a Postgres extension and accompanying Supabase UI that makes it safe and easy to store encrypted secrets.

Grant Vault Access to New Role

postgres

postgres

) user and bypassing any security policies that exist on vault.
- I have created a separate

readonly_user

readonly_user

that is used for some internal admin work. This user also needs access to some of the information within

vault.decrypted_secrets

vault.decrypted_secrets

(maybe this violates the intended design of the vault... but this isn't my concern right now).
- Because the vault schema is owned by

supabase_admin

supabase_admin

user I'm having trouble granting access to the new

readonly_user

readonly_user

postgres

postgres

user)

DO $$
BEGIN
    -- Grant schema usage
    EXECUTE 'GRANT USAGE ON SCHEMA vault TO readonly_user';
    
    -- Grant select on all existing tables in the schema
    EXECUTE 'GRANT SELECT ON ALL TABLES IN SCHEMA vault TO readonly_user';
    
    -- Set default privileges for future tables
    EXECUTE 'ALTER DEFAULT PRIVILEGES IN SCHEMA vault GRANT SELECT ON TABLES TO readonly_user';
END $$;

DO $$
BEGIN
    -- Grant schema usage
    EXECUTE 'GRANT USAGE ON SCHEMA vault TO readonly_user';
    
    -- Grant select on all existing tables in the schema
    EXECUTE 'GRANT SELECT ON ALL TABLES IN SCHEMA vault TO readonly_user';
    
    -- Set default privileges for future tables
    EXECUTE 'ALTER DEFAULT PRIVILEGES IN SCHEMA vault GRANT SELECT ON TABLES TO readonly_user';
END $$;

- the query doesn't throw an error but doesn't add any privileges (I'm assuming because I'm not running this as the

supabase_admin

supabase_admin

. Confirmed by running:

SELECT * 
FROM information_schema.role_table_grants
WHERE table_schema = 'vault';

SELECT * 
FROM information_schema.role_table_grants
WHERE table_schema = 'vault';

Any guidance would be appreciated.

Vault | Supabase Docs

Vault is a Postgres extension and accompanying Supabase UI that makes it safe and easy to store encrypted secrets.

Grant Vault Access to New Role

Grant Vault Access to New Role

Similar Threads

Similar Threads

Similar Threads