Auto-removal of schemas in `db-schemas` once `DROP SCHEMA ..` is done

[steve-chavez]

Problem

With the change on #3644 (particularly b6863f1, released on PostgREST v13) schema-based multi-tenancy becomes more complicated as the user has to remember to remove schemas from db-schemas once DROP SCHEMA is done, otherwise PostgREST will fail.

Proposal 1

Automate schema removal by extending the Finer-Grained Event Trigger.

Proposal 2

Rollback to becoming lenient and don't fail on nonexistent schemas on db-schemas.

[steve-chavez]

Thinking more about this problem, I'm wondering if it still makes sense to have db_schemas and if instead we should just switch to detect schemas that have GRANT USAGE on authenticator.

At one point, I recall we didn't chose to base exposed schemas on the USAGE privilege because:

• By default every role has the USAGE privilege on the public schema -- so public would be an exposed schema by default.
  • But nowadays we default db_schemas to public (ref), so the above argument doesn't make sense anymore.
  • If users don't want to expose this, they can REVOKE the USAGE privilege on authenticator, which is the same as not putting it inside db_schemas.
• Both information_schema and pg_catalog have the USAGE privilege, but we discard these schemas explicitly (Emit warning when adding pg_catalog on db-schemas #4359)

If we did this, we would be more close to database as source of truth. If a schema is dropped, then its usage privilege too. This also removes the need for the event trigger.

[steve-chavez]

I think we could make such a change with a new config flag to make a new version backwards compatible, as otherwise it would be a big breaking change.

• db-schemas-detect-usage=true: detects USAGE and ignore whatever is on db-schemas.
• db-schemas-detect-usage=false: would maintain backwards compatibility.

[wolfgangwalther]

Thinking more about this problem, I'm wondering if it still makes sense to have db_schemas and if instead we should just switch to detect schemas that have GRANT USAGE on authenticator.

I'm not sure how that relates. I don't think the authenticator role even requires usage privileges on these schemas? IIRC, it is entirely fine, (with recent enough PostgreSQL?) to set the membership of the authenticator role in the roles we do SET ROLE to, to not inherit the privileges, but just allow select.

In this case, the authenticator role doesn't have any of these privileges.

[steve-chavez]

I'm not sure how that relates. I don't think the authenticator role even requires usage privileges on these schemas?

Right, the problem remains though, this new schema validation it's not Database as SSoT. It's too much trouble for schema-based multi-tenancy, and I'm not seeing any gains from it.

Rollback to becoming lenient and don't fail on nonexistent schemas on db-schemas.

I think we should do this instead, because as mentioned on #3644 (comment), even PostgreSQL itself is lenient when adding non-existent schemas to the search_path:

set search_path to anything, here, even, nonexistent; -- this won't fail

This would be more Database as SSoT, as we at least follow postgresql behavior.

[steve-chavez]

set search_path to anything, here, even, nonexistent; -- this won't fail
This would be more Database as SSoT, as we at least follow postgresql behavior.

Another option to consider is relying on the authenticator search_path instead of db-schemas. If pg decides to do the validation later on, we would at least be transparent to its behavior.

[wolfgangwalther]

Another option to consider is relying on the authenticator search_path instead of db-schemas.

This seems more fit as an in-db replacement for db-extra-search-path.

[wolfgangwalther]

A different approach that combines ideas discussed elsewhere:

• Finally make the schema first-class and part of the URI: /<schema>/<view>
• Remove db-schemas entirely.
• Store the schema cache as a map of SchemaName -> SchemaCache
• Load schema cache dynamically when a new schema is first requested.
• Remove the whole schema from the cache when a "schema not found" error is hit for that schema.

[steve-chavez]

A different approach that combines ideas discussed elsewhere:
Finally make the schema first-class and part of the URI: //

Not opposed to that alternative right now (and it could come after #4270), but that would be a major breaking change and break all the client libraries we have in the ecosystem, it's going to take at least a year (if not more) to stabilize and update them all.

Rollback to becoming lenient and don't fail on nonexistent schemas on db-schemas.
I think we should do this instead, because as mentioned on #3644 (comment), even PostgreSQL itself is lenient when adding non-existent schemas to the search_path:

The simplest solution I'm seeing in the short-term for this issue is the above one.

---

I thought about basing the exposed schemas on the USAGE privilege of the children of authenticator, but that would be a confusing interface.

[steve-chavez]

Proposal 1
Automate schema removal by extending the Finer-Grained Event Trigger.

Working on the above, assuming the list of schemas is like x, y, z, ll. Removing y can be done like:

select string_agg(trim(e), ', ')
from unnest(string_to_array('x, y, z, ll', ',')) e
where trim(e) <> 'y';

 string_agg 
------------
 x, z, ll
(1 row)

Now in the event trigger we would have to alter the config of the role via ALTER ROLE authenticator SET pgrst.db_schemas=.. or recreate the pre-config create or replace function postgrest.pre_config.., this part does feel a bit icky. The above would also fail if some schema is named like "a,weird,schema".

[steve-chavez]

@wolfgangwalther Any opposition towards reverting 86c3257 and doing a patch release?

As proven here, this behavior is too strict and doesn't match pg behavior with search_path plus it's too cumbersome for schema-based multi-tenancy,

Also, the commit says it's a "feat" but I don't see any gain for the users.