Database API 42501 errors

[TheOtherBrian1]

Postgres 42501 errors, often reported by clients as 401 or 403 errors, imply the request lacked adequate privileges. They can be viewed in the log explorer by running:

select
    cast(postgres_logs.timestamp as datetime) as timestamp,
    event_message,
    parsed.error_severity,
    parsed.user_name,
    parsed.query,
    parsed.detail,
    parsed.hint
from
    postgres_logs
    cross join unnest(metadata) as metadata
    cross join unnest(metadata.parsed) as parsed
where
    regexp_contains(parsed.error_severity, 'ERROR|FATAL|PANIC')
      and
       parsed.sql_state_code = '42501'
order by
    timestamp desc
limit 100;

They tend to be caused by one of the listed factors

Attempted to access a forbidden schema

API roles cannot access certain schemas, most notably auth and vault. This restriction extends to Foreign Data Wrappers relying on vault. While you can bypass it using a security definer function, these schemas are intentionally restricted for security reasons.

Revoked object level access:

By default, API roles have privileges only within the public schema. Even if another schema is exposed, you must grant the relevant roles database-level privileges. To allow full visibility, use the following command:

GRANT USAGE ON SCHEMA myschema TO anon, authenticated, service_role;
GRANT ALL ON ALL TABLES IN SCHEMA myschema TO anon, authenticated, service_role;
GRANT ALL ON ALL ROUTINES IN SCHEMA myschema TO anon, authenticated, service_role;
GRANT ALL ON ALL SEQUENCES IN SCHEMA myschema TO anon, authenticated, service_role;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA myschema GRANT ALL ON TABLES TO anon, authenticated, service_role;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA myschema GRANT ALL ON ROUTINES TO anon, authenticated, service_role;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA myschema GRANT ALL ON SEQUENCES TO anon, authenticated, service_role;

RLS:

If the anon or authenticated roles attempt to UPDATE or INSERT values without the necessary RLS permissions, Postgres will return a 42501 error.