Supabase•5d ago

Search path or grant lockdown mishap

As someone who doesn't claim to be an expert in SQL, I was trying to tighten my security to my entire DB, adding search_paths to the functions and revoking permissions to general users on public. I'm including a log of all the SQL changes I made, the initial changes were made to tighten security and they did that too well: now users who are authenticated and supposed to / previously get missing relations errors when accessing tables (i.e. the tables cannot be queried). It seems the query throwing this error is within the get_user_role function which is used everywhere to determine permissions. I would be happy to share any details necessary! https://hastebin.com/share/vapacetuqe.sql Thanks!

Hastebin

Hastebin is a free web-based pastebin service for storing and sharing text and code snippets with anyone. Get started now.

17 Replies

garyaustin•5d ago

You should not mess with grants on the public schema. You should move tables to private schemas if you don't want global access or use RLS. You can revoke grants on functions but need to do it individually on each function... https://discord.com/channels/839993398554656828/1431588976598454272/1433932196028354712 Really lost in what all you are doing there as you remove things then seem to add them back. What is the exact error your function is getting? This code should restore the default grants...

grant usage on schema public to postgres, anon, authenticated, service_role;

grant all privileges on all tables in schema public to postgres, anon, authenticated, service_role;
grant all privileges on all functions in schema public to postgres, anon, authenticated, service_role;
grant all privileges on all sequences in schema public to postgres, anon, authenticated, service_role;

alter default privileges in schema public grant all on tables to postgres, anon, authenticated, service_role;
alter default privileges in schema public grant all on functions to postgres, anon, authenticated, service_role;
alter default privileges in schema public grant all on sequences to postgres, anon, authenticated, service_role;

grant usage on schema public to postgres, anon, authenticated, service_role;

grant all privileges on all tables in schema public to postgres, anon, authenticated, service_role;
grant all privileges on all functions in schema public to postgres, anon, authenticated, service_role;
grant all privileges on all sequences in schema public to postgres, anon, authenticated, service_role;

alter default privileges in schema public grant all on tables to postgres, anon, authenticated, service_role;
alter default privileges in schema public grant all on functions to postgres, anon, authenticated, service_role;
alter default privileges in schema public grant all on sequences to postgres, anon, authenticated, service_role;

BrennanOP•5d ago

The remove and readd wasn't an educated move, it was me removing things then trying to figure out what not to remove - I'll add those back, in some research I might've misunderstood but I thought without revoking certain priviledges anon or auth would be able to create functions or tables or things, is that not the case?

garyaustin•5d ago

They cannot create tables and functions. They are granted ability to use/execute by default. Also there is no ability to generate tables and functions from the REST API.

BrennanOP•5d ago

I'm actually suprised that didn't resolve the issue: Runtime error:

index-rZ2P2vLJ.js:303 Error checking if client exists:  
{code: '42P01', details: null, hint: null, message: 'relation "staff_profiles" does not exist'}
code
: 
"42P01"
details
: 
null
hint
: 
null
message
: 
"relation \"staff_profiles\" does not exist"
[[Prototype]]
: 
Object

index-rZ2P2vLJ.js:303 Error checking if client exists:  
{code: '42P01', details: null, hint: null, message: 'relation "staff_profiles" does not exist'}
code
: 
"42P01"
details
: 
null
hint
: 
null
message
: 
"relation \"staff_profiles\" does not exist"
[[Prototype]]
: 
Object

Responsible code: (init useEffect responsible for determining user permissions)

useEffect(() => {
        if (!user || loading) return;

        async function fetchPermissions() {
            const roles: UserRole[] = [];

            // Check client_profiles

            const { data: clientExistsData, error: clientExistsError } = await supabase
                .from('client_profiles')
                .select('id, status')
                .eq('user_id', user!.id)
                .limit(1)
                .maybeSingle()

            if (clientExistsError) console.error("Error checking if client exists: ", clientExistsError);

useEffect(() => {
        if (!user || loading) return;

        async function fetchPermissions() {
            const roles: UserRole[] = [];

            // Check client_profiles

            const { data: clientExistsData, error: clientExistsError } = await supabase
                .from('client_profiles')
                .select('id, status')
                .eq('user_id', user!.id)
                .limit(1)
                .maybeSingle()

            if (clientExistsError) console.error("Error checking if client exists: ", clientExistsError);

But because it's not an issue w/ client_profiles table, it's likely the issue on this:

CREATE POLICY "admin full access" ON client_profiles
FOR ALL USING (get_user_role() = 'admin') WITH CHECK (get_user_role() = 'admin');

CREATE POLICY "admin full access" ON client_profiles
FOR ALL USING (get_user_role() = 'admin') WITH CHECK (get_user_role() = 'admin');

Which refers to

CREATE FUNCTION public.get_user_role()
RETURNS TEXT
LANGUAGE sql
SECURITY DEFINER
AS $$
  SELECT
    CASE
      WHEN s.user_id IS NOT NULL THEN
        CASE
          WHEN s.broad_permission = 'staff' THEN 'staff'
          WHEN s.broad_permission IN ('admin', 'both') THEN 'admin'
          ELSE NULL
        END
      WHEN c.user_id IS NOT NULL THEN 'client'
      ELSE NULL
    END
  FROM
    (SELECT user_id, broad_permission FROM staff_profiles WHERE user_id = auth.uid() LIMIT 1) s
    FULL OUTER JOIN
    (SELECT user_id FROM client_profiles WHERE user_id = auth.uid() LIMIT 1) c
    ON TRUE
$$;

CREATE FUNCTION public.get_user_role()
RETURNS TEXT
LANGUAGE sql
SECURITY DEFINER
AS $$
  SELECT
    CASE
      WHEN s.user_id IS NOT NULL THEN
        CASE
          WHEN s.broad_permission = 'staff' THEN 'staff'
          WHEN s.broad_permission IN ('admin', 'both') THEN 'admin'
          ELSE NULL
        END
      WHEN c.user_id IS NOT NULL THEN 'client'
      ELSE NULL
    END
  FROM
    (SELECT user_id, broad_permission FROM staff_profiles WHERE user_id = auth.uid() LIMIT 1) s
    FULL OUTER JOIN
    (SELECT user_id FROM client_profiles WHERE user_id = auth.uid() LIMIT 1) c
    ON TRUE
$$;

garyaustin•5d ago

That is not a grant error.

BrennanOP•5d ago

Given that we reset the grants, it would seem the only culprite would be from search_path errors?

garyaustin•5d ago

That is either a table spelling error or a search path error.

BrennanOP•5d ago

Certainly the latter, those tables / routines have worked for a long time and no table name alteration is made

ALTER FUNCTION set_modified_fields() SET search_path = 'public, auth';
ALTER FUNCTION update_client_profile_on_password_set() SET search_path = 'public, auth';
ALTER FUNCTION public.get_user_role() SET search_path = 'public, auth';
ALTER FUNCTION get_clients_conditionally(uuid, timestamp with time zone, timestamp with time zone, integer) SET search_path = 'public, auth';
ALTER FUNCTION public.notify_control_plane() SET search_path = 'public, auth';
GRANT EXECUTE ON FUNCTIONS TO authenticated;

ALTER FUNCTION set_modified_fields() SET search_path = 'public, auth';
ALTER FUNCTION update_client_profile_on_password_set() SET search_path = 'public, auth';
ALTER FUNCTION public.get_user_role() SET search_path = 'public, auth';
ALTER FUNCTION get_clients_conditionally(uuid, timestamp with time zone, timestamp with time zone, integer) SET search_path = 'public, auth';
ALTER FUNCTION public.notify_control_plane() SET search_path = 'public, auth';
GRANT EXECUTE ON FUNCTIONS TO authenticated;

I set these search_paths to resolve the warnings in the dashboard, maybe this was incorrect?

garyaustin•5d ago

It is always best to just force the table with public.client_profiles if that is the schema it is in. Then search path can be ''.

BrennanOP•5d ago

Maybe I shouldn't worry about the warning but wouldn't that throw a warning in the dashboard? (I'm trying to have high security compliance for Hipaa reasons)

garyaustin•5d ago

What does throw a warning in dashboard mean? Setting the search path for the function to '' is normally what Supabase likes to see for their security checks.

BrennanOP•5d ago

BrennanOP•5d ago

Oh I blanked - search path '' is different from empty search path

garyaustin•5d ago

That warning is because you should not have installed pg_net in public. I believe by default supabase installs it to extensions schema.

BrennanOP•5d ago

Yeah, I'll resolve that shortly, I was just showing what I meant by dashboard warning, but I can revert those paths to '' - When you say force the table with public.x What adjustment in specific is that? Is that just in SQL functions or on items in the JS API?

garyaustin•5d ago

In the function code add public. to the front of the table name. That then does not use a search path.

BrennanOP•5d ago

Thanks! I'll try that!

Gaming

Programming

Search path or grant lockdown mishap

Did you find this page helpful?