S
Supabase•8mo ago
et

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) user and bypassing any security policies that exist on vault. - I have created a separate readonly_user that is used for some internal admin work. This user also needs access to some of the information within 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 user I'm having trouble granting access to the new 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 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. 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.
2 Replies
garyaustin
garyaustin•8mo ago
Can you use a security definer function to fetch the secrets?
et
etOP•8mo ago
yeah good call^. I needed to define the security definer function outside of the vault schema + do some cleanup on the EXECUTE privileges, but it works. Thanks!

Did you find this page helpful?