Clickhouse FDW w/ View and Vault Error

[kocobee]

Bug report

• [x] I confirm this is a bug with Supabase, not with my own application.
• [x] I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

Using Clickhouse private schema w/ a public view that exposes some data doesn't work when using Vault conn_string_id.

I was able to get clickhouse working w/ a FDW that stores everything in a private schema table. It exposes a public view. When switching to vault, and attempting to access my view, I get the error:

permission denied for schema vault

To Reproduce

Steps to reproduce the behavior, please provide code snippets or a repository:

1. Setup a new private schema (I called it clickhouse).
2. Follow all steps outlined in clickhouse docs (using Vault)
3. Create a foreign table in a private instead of public schema (i.e. create foreign table clickhouse.my_priv_table ...)
4. Create a public view from that table: CREATE VIEW my_public_table my WITH (security_invoker) AS SELECT * FROM clickhouse.my_priv_table;
5. Attempt to access the public view w/ an authenticated user.

Expected behavior

I should be able to access the public view when using vault to store connection details.

Screenshots

System information

• OS: [e.g. macOS, Windows]
• Browser (if applies) [e.g. chrome, safari]
• Version of supabase-js: [e.g. 6.0.2]
• Version of Node.js: [e.g. 10.10.0]

Additional context

Add any other context about the problem here.

[burmecia]

When the view is created with WITH (security_invoker), access to the underlying foreign table is determined by the permissions of the user executing the query, not the view owner. So you need to make sure the user who is using the view to have permission on vault schema because it needs to read encrypted secrets from vault schema.

To grant that permission, you can try below sqls:

GRANT USAGE ON SCHEMA vault TO <relevant role>;
GRANT SELECT ON TABLE vault.decrypted_secrets TO <relevant role>;

[kocobee]

Hmm, still getting the same error. Trying to access via authenticated user, and simulating via Supabase's role settings right now.

To reproduce, I ran the following as a postgres user in Supabase's sql editor (successfully):

grant USAGE on schema vault to authenticated;

grant select on all tables in schema vault to authenticated;

Then followed up with this as an authenticated user (in Supabase UI):

select * from vault.decrypted_secrets;

Still get:

[burmecia]

When you're impersonating an authenticated user in Supabase UI, can you make sure that user really has authenticated role?

[kocobee]

Getting the same error both in the Supabase dashboard, and via a logged in authenticated user (despite running migrations).

I was able to create a new schema via the dashboard called test, and grant the aforementioned permissions and then access it via an authenticated user. I am not able to do the same with vault.

CREATE SCHEMA test;

...

GRANT USAGE ON SCHEMA test TO authenticated;
GRANT ALL ON ALL TABLES IN SCHEMA test TO authenticated;

Works fine

GRANT USAGE ON SCHEMA vault TO authenticated;
GRANT ALL ON ALL TABLES IN SCHEMA vault TO authenticated;

Does not work.

[burmecia]

The problem is probably because GRANT USAGE ON SCHEMA vault TO authenticated; didn't take effects but that warning message doesn't shown on Supabase dashboard. If you're using psql with postgres user, you will see the warning message WARNING: no privileges were granted for "vault" when you run GRANT USAGE on SCHEMA vault.

So the thing is there are some extra permissions needed for custom users, for you case it might be better to raise a support ticket and we can help you sort it out.

[kocobee]

@burmecia I closed this, and will open a support ticket.

However, thinking this through, Supabase recommends the following:

1. Store things like sensitive connection details in Vault
2. Don't expose most foreign tables via the public schema. Instead, put them in a private schema and expose them via a view or function.

Having said that, should the examples be updated to include anything necessary to get both of those working simultaneously? In the current state, they don't. I'm just trying to follow the provided best practices, and I'm surprised others aren't hitting this as well.

It looks like Vault is owned by supabase_admin, which if I understand correctly, we don't have access to. So, we need to go through support? But, this repo recommends using Vault. Maybe I'm missing something.

[burmecia]

Yes, to grant the vault schema permission we need go through support as the supabase_admin role is managed by Supabase. We definitely need improvements on the docs to provide more on the best practices, it is already on our task list and thank you for your feedback.

[aplyd]

It looks like Vault is owned by supabase_admin, which if I understand correctly, we don't have access to. So, we need to go through support? But, this repo recommends using Vault. Maybe I'm missing something.

I'm also confused because the docs led me to a dead end. I created a public view to access my private stripe schema but now can't actually query the view from the client because the user needs access to the vault. Should I just resort to using a function instead?

[leftkickcemetery]

I am experiencing the same issue except with the stripe_wrapper fdw for Supabase. I also attempted to pinpoint the issue by testing various combinations of grant usage to schema, grant usage to foreign_data_wrapper stripe_wrapper, grant usage to schema vault, grant ALL to tables in vault and stripe.*; with multiple roles including dashboard_user (for security define), authenticated, etc, and then testing views. I experienced the same problem; able to access views in other schemas with security_invoker = true but receive error permission to vault schema denied no matter what i tried with stripe fdw, even though it seemed like would be permissive after all the grants. is there any place I can get help with this issue? I also could not find any docs on just disabling/removing the vault extension, so I was unable to try that.

[burmecia]

The vault schema contains sensitive data so it should be protected carefully. As the docs suggested, using security definer function to access foreign table is the best practice as it simplified the complex permissions grants. Note that security definer function must be defined by postgres user (you can do it in SQL Editor) so the other roles/users can access it.