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
```
postgres
```
postgres
) user and bypassing any security policies that exist on vault.
I have created a separate
```
readonly_user
```
readonly_user
```
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
```
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
```
supabase_admin
```
supabase_admin
user I'm having trouble granting access to the new
```
readonly_user
```
readonly_user
```
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

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 $$;

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

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';

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

Similar Threads

Similar Threads

Similar Threads