Error "relation "sessions" does not exist" during auth token refresh

We're using Supabase auth
Our users get logged out an hour after logging in, during token refresh
The logging out happens because of token revocation due to the error "relation 'sessions' does not exist". sessions is a table in the auth schema.
we have a custom_access_token auth hook set up. The hook is in the public schema and uses the definer tag
logs show the custom_access_token hook runs successfully upon token refresh. But token refresh fails with the error shown in the below log
Edit: The 'sessions' table is in the auth schema, but the supabase_auth_admin isn't able to access it

Log event metadata

[
  {
    "file": null,
    "host": "db-ynxjrpyghkowsvdunxda",
    "metadata": [],
    "parsed": [
      {
        "application_name": null,
        "backend_type": "client backend",
        "command_tag": "PARSE",
        "connection_from": "127.0.0.1:60983",
        "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": 692664,
        "query": "UPDATE \"sessions\" AS sessions SET \"ip\" = $1, \"refreshed_at\" = $2, \"updated_at\" = $3, \"user_agent\" = $4 WHERE sessions.id = $5",
        "query_id": 0,
        "query_pos": 8,
        "session_id": "67915fb4.a91b8",
        "session_line_num": 13,
        "session_start_time": "2025-01-22 21:14:28 UTC",
        "sql_state_code": "42P01",
        "timestamp": "2025-01-22 21:14:28.671 UTC",
        "transaction_id": 133169,
        "user_name": "supabase_auth_admin",
        "virtual_transaction_id": "8/653992"
      }
    ],
    "parsed_from": null,
    "project": null,
    "source_type": null
  }
]

[
  {
    "file": null,
    "host": "db-ynxjrpyghkowsvdunxda",
    "metadata": [],
    "parsed": [
      {
        "application_name": null,
        "backend_type": "client backend",
        "command_tag": "PARSE",
        "connection_from": "127.0.0.1:60983",
        "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": 692664,
        "query": "UPDATE \"sessions\" AS sessions SET \"ip\" = $1, \"refreshed_at\" = $2, \"updated_at\" = $3, \"user_agent\" = $4 WHERE sessions.id = $5",
        "query_id": 0,
        "query_pos": 8,
        "session_id": "67915fb4.a91b8",
        "session_line_num": 13,
        "session_start_time": "2025-01-22 21:14:28 UTC",
        "sql_state_code": "42P01",
        "timestamp": "2025-01-22 21:14:28.671 UTC",
        "transaction_id": 133169,
        "user_name": "supabase_auth_admin",
        "virtual_transaction_id": "8/653992"
      }
    ],
    "parsed_from": null,
    "project": null,
    "source_type": null
  }
]

[
  {
    "file": null,
    "host": "db-ynxjrpyghkowsvdunxda",
    "metadata": [],
    "parsed": [
      {
        "application_name": null,
        "backend_type": "client backend",
        "command_tag": "PARSE",
        "connection_from": "127.0.0.1:60983",
        "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": 692664,
        "query": "UPDATE \"sessions\" AS sessions SET \"ip\" = $1, \"refreshed_at\" = $2, \"updated_at\" = $3, \"user_agent\" = $4 WHERE sessions.id = $5",
        "query_id": 0,
        "query_pos": 8,
        "session_id": "67915fb4.a91b8",
        "session_line_num": 13,
        "session_start_time": "2025-01-22 21:14:28 UTC",
        "sql_state_code": "42P01",
        "timestamp": "2025-01-22 21:14:28.671 UTC",
        "transaction_id": 133169,
        "user_name": "supabase_auth_admin",
        "virtual_transaction_id": "8/653992"
      }
    ],
    "parsed_from": null,
    "project": null,
    "source_type": null
  }
]

[
  {
    "file": null,
    "host": "db-ynxjrpyghkowsvdunxda",
    "metadata": [],
    "parsed": [
      {
        "application_name": null,
        "backend_type": "client backend",
        "command_tag": "PARSE",
        "connection_from": "127.0.0.1:60983",
        "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": 692664,
        "query": "UPDATE \"sessions\" AS sessions SET \"ip\" = $1, \"refreshed_at\" = $2, \"updated_at\" = $3, \"user_agent\" = $4 WHERE sessions.id = $5",
        "query_id": 0,
        "query_pos": 8,
        "session_id": "67915fb4.a91b8",
        "session_line_num": 13,
        "session_start_time": "2025-01-22 21:14:28 UTC",
        "sql_state_code": "42P01",
        "timestamp": "2025-01-22 21:14:28.671 UTC",
        "transaction_id": 133169,
        "user_name": "supabase_auth_admin",
        "virtual_transaction_id": "8/653992"
      }
    ],
    "parsed_from": null,
    "project": null,
    "source_type": null
  }
]

Any troubleshooting tips?

garyaustin•1/22/25, 10:21 PM

Is the table in the auth schema, you were not clear if it is there or it should be there.

A grant error should give permission error. If the table is there it is like your search path is messed up for the supabase_auth_admin.

trustedfox.OP•1/22/25, 10:23 PM

The 'sessions' table is in the auth schema, but the supabase_auth_admin isn't able to access it

garyaustin•1/22/25, 10:23 PM

Are you changing the search path in your hook?

garyaustin•1/22/25, 10:24 PM

Did this ever work and just stopped?

trustedfox.OP•1/22/25, 10:24 PM

Updated the post to indicate the above

garyaustin•1/22/25, 10:24 PM

It is not a grant issue, that would be a different error.

trustedfox.OP•1/22/25, 10:24 PM

yes, it did work and just stopped working after some edits to the hook.

garyaustin•1/22/25, 10:26 PM

So search path and what is odd is that role has to meet RLS...

trustedfox.OP•1/22/25, 10:28 PM

here's the hook setup

trustedfox.OP•1/22/25, 10:32 PM

Any tips on what I could do to get more debug info? like print the search path for e.g.

garyaustin•1/22/25, 10:33 PM

You have not shown your hook code. Does it change the search path?

garyaustin•1/22/25, 10:34 PM

Run this in SQL editor:

SELECT r.rolname, d.datname, rs.setconfig
FROM   pg_db_role_setting rs
LEFT   JOIN pg_roles      r ON r.oid = rs.setrole
LEFT   JOIN pg_database   d ON d.oid = rs.setdatabase
WHERE  r.rolname = 'supabase_auth_admin'

SELECT r.rolname, d.datname, rs.setconfig
FROM   pg_db_role_setting rs
LEFT   JOIN pg_roles      r ON r.oid = rs.setrole
LEFT   JOIN pg_database   d ON d.oid = rs.setdatabase
WHERE  r.rolname = 'supabase_auth_admin'

trustedfox.OP•1/22/25, 10:36 PM

So, yes, we do update the search path with this code execute format('set search_path to %I', customer_schema)execute format('set search_path to %I', customer_schema) . Will try resetting it and the end of the hook and see how that goes

garyaustin•1/22/25, 10:37 PM

You should just set the search path for the function.

garyaustin•1/22/25, 10:38 PM

I assume you are trying to get to another table in the function.

trustedfox.OP•1/22/25, 10:40 PM

the customer_schemacustomer_schema is discovered in the function based on user metadata. So, I wouldn't be able to set the search path for the function,correct?

garyaustin•1/22/25, 10:41 PM

Yeah, but not how you are doing it.

trustedfox.OP•1/22/25, 10:42 PM

Could you point me to any examples so I can fix the way we're doing it in the hook? Thanks a ton btw for the guidance, really helpful. Atleast now I know what to fix

garyaustin•1/22/25, 10:45 PM

Something like this LANGUAGE PLPGSQL SECURITY DEFINER SET search_path = blah,blahLANGUAGE PLPGSQL SECURITY DEFINER SET search_path = blah,blah

garyaustin•1/22/25, 10:45 PM

But you are better off just use schema.table in the code.

garyaustin•1/22/25, 10:46 PM

Are you saying your schema is dynamic?

trustedfox.OP•1/22/25, 10:47 PM

that's correct, the schema is dynamic. We do a

public

public

table lookup based on user metadata to set the customer_schemacustomer_schema variable. Then access a table in the customer_schemacustomer_schema schema

garyaustin•1/22/25, 10:48 PM

Plus we are still guessing this is it, because it matches the symptom. It would be good to do a quick test without your format set search_path. I'm guessing that is changing the schema for the rest of the operations auth_admin is going to try and to.

garyaustin•1/22/25, 10:48 PM

You'll have to research how to do schema.table where schema is a variable in your query. Probably formatting the query rather than the search path.

garyaustin•1/22/25, 10:49 PM

An AI probably knows.

trustedfox.OP•1/22/25, 10:50 PM

will test by using the fully qualified table name rather than setting the search path...

garyaustin•1/22/25, 11:00 PM

You could probably add auth into your search path you were setting I guess. Just not a fan of changing search paths out of where you need them.

trustedfox.OP•1/22/25, 11:10 PM

Got it, thanks. For now, I've removed the 'set search path' from the hook and using a fully qualified name in the query. Currently running a test, token_refresh should take place in about an hour, triggering the hook. Will know if the fix works then.

garyaustin•1/22/25, 11:11 PM

If you are testing in dev change your jwt expire to a few minutes.

trustedfox.OP•1/22/25, 11:12 PM

Sounds good, I'll look into doing that

trustedfox.OP•1/22/25, 11:22 PM

"formatting the query rather than the search path." --- this fix worked, thanks!

Error "relation "sessions" does not exist" during auth token refresh

Similar Threads

Similar Threads

Similar Threads