CODEWITHSUNDEEP
supabase
tanius
tanius

Reputation: 16829

My Supabase tables of schema public became inaccessible by API

We have set up a Supabase database project (using their hosted service), and later requested through their support that they enable the new resumable uploads feature in our project. Afterwards, all our tables in the public database schema are no longer accessible by API. Tables in the storage database schema are still accessible though.

Example, for a table called programme:

curl 'https://your-project-ref-id.supabase.co/rest/v1/programme?select=*' -H "apikey: YOUR_SUPABASE_ANON_KEY" -H "Authorization: Bearer YOUR-AUTH-TOKEN"

{
  "code":"42P01",
  "details":null,
  "hint":null,
  "message":"relation \"storage.programme\" does not exist"
}

For similar requests, I would get this error message:

{
  "code":"PGRST106",
  "details":null,
  "hint":null,
  "message":"The schema must be one of the following: storage"
}

Another symptom is that the public schema's tables have vanished from the auto-generated Supabase API docs.

The public schema is however still listed as an exposed API schema in the Supabase project settings.

What could cause this issue and how to fix it?

Upvotes: 1

Views: 2142

Answers (1)

tanius
tanius

Reputation: 16829

This can be caused by accidental changes to the authenticator role in your PostgreSQL database. Whether this is indeed caused by a bug in the Supabase process that deploys the resumable uploads feature, I don't really know, but I suspect it.

To check whether this is the cause in your case, run the following query:

SELECT setrole::regrole, setconfig
  FROM pg_db_role_setting 
  WHERE setrole::regrole = 'authenticator'::regrole;

If you see an output like this that does not include public in the role's database schemas, you have found the issue right there:

setrole        | setconfig
---------------+--------------------------------------------------------
authenticator  | {
               |   "session_preload_libraries=supautils, safeupdate",
               |   statement_timeout=8s,
               |   pgrst.db_schemas=storage
               | }

This setting is overriding the UI config, where, as you said, public is still included as a schema to expose by API. Or more precisely, the UI has public hardcoded into it, so that it does not reflect changes to or allow to make changes to its status of API exposure. Because it was intended in the Supabase that public is always exposed by API.

To correct this issue, you would add the public schema back to the schemas accessible by the authenticator role, including it in addition to the schemas listed there so far. In our case:

ALTER ROLE authenticator SET pgrst.db_schemas = 'public, storage';
NOTIFY pgrst;

This fixed API exposure of all public schema tables immediately, and these tables are also again included in the auto-generated API documentation in the Supabase backend.

Note that the modified role is only authenticator, not also the usual authenticated or anon roles you come into contact with in the Supabase PostgreSQL database. This is because the actual connection of an API request to the database is indeed made via role authenticator, which is a technicality of the PostgREST implementation:

[T]he anon and authenticated do NOT need explicit permissions to connect to the [database] […], because the connection from PostgREST is actually executed with the authenticator role and then it just switches to anon or authenticated once it's already connected. (source)

(Credits: Worked out together with Steve from Supabase support. Thank you Steve!)

Upvotes: 2

Related Questions