Secret Key + security_invoker view not returning results

[ConorCorp]

Describe the bug

Hi supabase team,

I'm unable to access a view from a js client with a secret key.

I've created a view that joins a profiles table to auth.last_sign_in_at.

I can query this fine when its public (and thus unsafe), but when i add:

alter view profiles_plus_auth
set (security_invoker = true);

the view returns null from the js client.

I would have expected that with the secret key being used, it would bypass any RLS issues and return the data from the table, while not allowing public or authenticated users to see it.

I know that my secret key works for other tables and commands like const data = await supabase.auth.admin.listUsers();, just not for the view with auth involved.

Library affected

supabase-js

Reproduction

No response

Steps to reproduce

1. Create a js client using the secret key.
2. Create the tables

create view public.profiles_plus_auth as
select
  p.id,
  p.name,
  au.last_sign_in_at
from
  profiles p
  left join auth.users au on p.id = au.id;

alter view profiles_plus_auth
set (security_invoker = true);

3. Try to query this table.

System Info

"@supabase/supabase-js": "2.91.0"

 System:
    OS: macOS 14.5
    CPU: (8) arm64 Apple M1 Pro
    Memory: 132.66 MB / 16.00 GB
    Shell: 5.9 - /bin/zsh
  Binaries:
    Node: 24.8.0 - /Users/schoko/.nvm/versions/node/v24.8.0/bin/node
    Yarn: 1.22.19 - /usr/local/bin/yarn
    npm: 11.6.0 - /Users/schoko/.nvm/versions/node/v24.8.0/bin/npm
    bun: 1.2.19 - /opt/homebrew/bin/bun
    Deno: 2.4.5 - /opt/homebrew/bin/deno
    Watchman: 2025.08.04.00 - /opt/homebrew/bin/watchman
  Browsers:
    Chrome: 142.0.7444.176
    Firefox: 121.0.1
    Safari: 17.5

Used Package Manager

npm

Logs

No response

Validations

• Follow our Code of Conduct
• Read the Contributing Guidelines.
• Read the docs.
• Check that there isn't already an issue that reports the same bug to avoid creating a duplicate.
• Make sure this is a Supabase JS Library issue and not an issue with the Supabase platform. If it's a Supabase platform related bug, it should likely be reported to supabase/supabase instead.
• Check that this is a concrete bug. For Q&A open a GitHub Discussion or join our Discord Chat Server.
• The provided reproduction is a minimal reproducible example of the bug.

[7ttp]

Hi @ConorCorp, thanks for reporting! 💚

This is expected behavior. With security_invoker = true,
the view runs with the caller's permissions, and the authenticator role doesn't have access to auth.users by default.

I think the fix would be to run GRANT SELECT ON auth.users TO authenticator in your SQL editor.
Please lmk if this fixes the issue for you!

[ConorCorp]

@7ttp Sorry if I am misunderstanding here, but that doesn't make sense to me.

I am trying to make this call with the secret key which should have full access to everything, not the authenticator role.

Only use in backend components of your app: servers, already secured APIs (admin panels), Edge Functions, microservices, etc. [Secret Keys] provide full access to your project's data, bypassing Row Level Security.

I would also like the table not to be available to authenticated users.

[ConorCorp]

@7ttp Also, just to mention GRANT SELECT ON auth.users TO authenticator does not fix this.

[ConorCorp]

Ahhh ok, so i see that this request is an error in the logs now. So my secret key request turns into the authenticator role. Eitherway, before and after granting the select authenticator for auth.users, the same error still appears

{
  "event_message": "permission denied for table users",
  "id": "f1325cd9-2185-4ff0-bfc6-cf773ed20050",
  "metadata": [
    {
      "file": null,
      "host": "xxxxx",
      "metadata": [],
      "parsed": [
        {
          "application_name": "postgrest",
          "backend_type": "client backend",
          "command_tag": "BIND",
          "connection_from": "::1:37475",
          "context": null,
          "database_name": "postgres",
          "detail": null,
          "error_severity": "ERROR",
          "hint": null,
          "internal_query": null,
          "internal_query_pos": null,
          "leader_pid": null,
          "location": null,
          "process_id": 714298,
          "query": "WITH pgrst_source AS ( SELECT \"public\".\"profiles_plus_auth\".* FROM \"public\".\"profiles_plus_auth\"   LIMIT $1 OFFSET $2 )  SELECT null::bigint AS total_result_set, pg_catalog.count(_postgrest_t) AS page_total, coalesce(json_agg(_postgrest_t), '[]') AS body, nullif(current_setting('response.headers', true), '') AS response_headers, nullif(current_setting('response.status', true), '') AS response_status, '' AS response_inserted FROM ( SELECT * FROM pgrst_source ) _postgrest_t",
          "query_id": 4489963879035509000,
          "query_pos": null,
          "session_id": "6971d672.ae63a",
          "session_line_num": 4,
          "session_start_time": "2026-01-22 07:49:06 UTC",
          "sql_state_code": "42501",
          "timestamp": "2026-01-22 07:49:06.558 UTC",
          "transaction_id": 0,
          "user_name": "authenticator",
          "virtual_transaction_id": "51/4556"
        }
      ],
      "parsed_from": null,
      "project": null,
      "source_type": null
    }
  ],
  "timestamp": 1769068146558000
}

[7ttp]

Ahhh ok, so i see that this request is an error in the logs now. So my secret key request turns into the authenticator role. Eitherway, before and after granting the select authenticator for auth.users, the same error still appears

{
  "event_message": "permission denied for table users",
  "id": "f1325cd9-2185-4ff0-bfc6-cf773ed20050",
  "metadata": [
    {
      "file": null,
      "host": "xxxxx",
      "metadata": [],
      "parsed": [
        {
          "application_name": "postgrest",
          "backend_type": "client backend",
          "command_tag": "BIND",
          "connection_from": "::1:37475",
          "context": null,
          "database_name": "postgres",
          "detail": null,
          "error_severity": "ERROR",
          "hint": null,
          "internal_query": null,
          "internal_query_pos": null,
          "leader_pid": null,
          "location": null,
          "process_id": 714298,
          "query": "WITH pgrst_source AS ( SELECT \"public\".\"profiles_plus_auth\".* FROM \"public\".\"profiles_plus_auth\"   LIMIT $1 OFFSET $2 )  SELECT null::bigint AS total_result_set, pg_catalog.count(_postgrest_t) AS page_total, coalesce(json_agg(_postgrest_t), '[]') AS body, nullif(current_setting('response.headers', true), '') AS response_headers, nullif(current_setting('response.status', true), '') AS response_status, '' AS response_inserted FROM ( SELECT * FROM pgrst_source ) _postgrest_t",
          "query_id": 4489963879035509000,
          "query_pos": null,
          "session_id": "6971d672.ae63a",
          "session_line_num": 4,
          "session_start_time": "2026-01-22 07:49:06 UTC",
          "sql_state_code": "42501",
          "timestamp": "2026-01-22 07:49:06.558 UTC",
          "transaction_id": 0,
          "user_name": "authenticator",
          "virtual_transaction_id": "51/4556"
        }
      ],
      "parsed_from": null,
      "project": null,
      "source_type": null
    }
  ],
  "timestamp": 1769068146558000
}

thanks for the logs!
The error shows you need USAGE permission on the schema too.

Try running both:

GRANT USAGE ON SCHEMA auth TO authenticator;
GRANT SELECT ON auth.users TO authenticator;

Without USAGE on the schema, the authenticator role can't access any tables in auth, even with SELECT permission.
That should fix the error.
lmk how it goes after this! 😁💚

[ConorCorp]

[mandarini]

Hi @ConorCorp, thanks for opening this issue! However, I believe that this is not a supabase-js issue, rather it's a misunderstanding of how security_invoker views work with PostgREST's connection model.

Why This Isn't a Client Library Bug

When you use a service role key with PostgREST:

1. PostgREST always connects to Postgres as the authenticator role (for security isolation)
2. It then uses SET ROLE service_role based on your JWT
3. Most queries run with service_role privileges (bypassing RLS)

However, security_invoker views use the session user's permissions (authenticator), NOT the current role (service_role). This is PostgreSQL behavior, not a supabase-js issue.

Your service role key is working correctly. The problem is that security_invoker is the wrong tool for your use case.

I believe that @7ttp 's suggestion opens a security hole. The suggestion to run:

GRANT USAGE ON SCHEMA auth TO authenticator;
GRANT SELECT ON auth.users TO authenticator;

Creates a security vulnerability because:

• authenticator is the role that all PostgREST connections use (including anon and authenticated users)
• This grants ALL users potential access to auth.users, not just service role requests
• You explicitly said you want this data not available to authenticated users
• This violates the principle of least privilege

Some suggested solutions

You want admin-level access (to auth.users) that's only available with the service role key. Here are the proper patterns:

Solution 1: Security Definer View + RLS (Recommended)

-- Step 1: Create view WITHOUT security_invoker
-- (default is security_definer, which runs with owner's permissions)
create view public.profiles_plus_auth as
select
  p.id,
  p.name,
  au.last_sign_in_at
from
  profiles p
  left join auth.users au on p.id = au.id;

-- Step 2: Enable RLS on the view
alter view public.profiles_plus_auth set (security_barrier = true);
alter table public.profiles_plus_auth enable row level security;

-- Step 3: No policies needed!
-- By default, RLS blocks all access except service_role (which bypasses RLS)
-- Authenticated/anon users: BLOCKED
-- Service role key: ALLOWED

This should work for your case because:

• View runs with postgres permissions → can access auth.users
• RLS blocks authenticated and anon roles → secure
• service_role bypasses RLS → your queries work

Solution 2: Security Definer Function

If you prefer more explicit control:

-- Create a function that runs with elevated privileges
create or replace function public.get_profiles_plus_auth()
returns table(
  id uuid,
  name text,
  last_sign_in_at timestamptz
)
language sql
security definer
set search_path = public
as $$
  select
    p.id,
    p.name,
    au.last_sign_in_at
  from
    profiles p
    left join auth.users au on p.id = au.id;
$$;

-- Enable RLS on the function (treat it like a table)
alter function public.get_profiles_plus_auth() owner to postgres;
revoke execute on function public.get_profiles_plus_auth() from public;
revoke execute on function public.get_profiles_plus_auth() from anon;
revoke execute on function public.get_profiles_plus_auth() from authenticated;
grant execute on function public.get_profiles_plus_auth() to service_role;

Call it from your client:

const { data, error } = await supabase.rpc('get_profiles_plus_auth');

Why security_invoker would not work

security_invoker = true means: "Run this view with the caller's permissions"

• For anon users → runs with anon permissions
• For authenticated users → runs with authenticated permissions
• For service_role requests via PostgREST → runs with authenticator permissions (because that's the session user)

You wanted the opposite: elevated permissions to access auth.users, regardless of who calls it, then control access via RLS.

What I would recomment

1. Remove security_invoker from your view
2. Implement Security Definer View + RLS
3. Test with both service role key (should work) and authenticated users (should be blocked)

Let me know if this resolves the issue! 💚

Some resources you can use:

• https://supabase.com/docs/guides/database/postgres/row-level-security
• https://supabase.com/docs/guides/functions/auth
• https://supabase.com/docs/guides/api/securing-your-api