Db as source of truth for exposed schemas

[steve-chavez]

Problem

• Schemas in db-schemas are validated and if one drops postgREST errs: Auto-removal of schemas in db-schemas once DROP SCHEMA .. is done #4364 (comment)
• db-schemas and db-extra-search-path are another layer on top of postgresql.

The main features both config options gives us are:

Default schema

• first of db-schemas

Exposed schemas

• db-schemas

Implicit schemas

These are schemas that the API role has USAGE but are not exposed

• db-extra-search-path

Solution

Option 1: Direct USAGE privilege

Default schema

• first of authenticator search_path

Exposed schemas

• schemas where the API role has direct USAGE privilege
  • Excludes USAGE granted through PUBLIC and memberships
  • Can exclude extensions schema if USAGE is granted to PUBLIC or through a membership
  • Excludes pg_catalog, information_schema

Implicit schemas

• authenticator search_path

Cons

Requirement to grant USAGE on schemas to all users separately is too much of an administrative burden and contrary to the goals of role system itself.
#4364 (comment)

Option 2: Direct and member USAGE privilege

Default schema

• first of authenticator search_path

Exposed schemas

• schemas where the API role has direct USAGE privilege or USAGE through membership
  • Excludes USAGE granted through PUBLIC
  • Can exclude extensions schema if USAGE is granted to PUBLIC
  • Excludes pg_catalog, information_schema

Implicit schemas

• authenticator search_path

Cons

Not flexible enough to easily restrict the set of exposed schemas. If extensions USAGE is not PUBLIC, then it will always be exposed. Another idea was mentioned:

Maybe grantor might be used for this purpose? Ie. expose schemas as above but take into account only USAGE grants granted by a specific grantor (configurable):
GRANT USAGE ON SCHEMA api_schema TO api_role GRANTED BY api_manager;
GRANT USAGE ON SCHEMA impl_schema TO api_role;
#4364 (comment)

But that would also defeat the purpose of this issue, since we'd have yet another config.

Option 3: USAGE privilege on the authenticator

From #4364 (comment)

Default schema

• first of authenticator search_path

Exposed schemas

• schemas where the authenticator has USAGE privilege, direct or indirect
  • Excludes PUBLIC, extensions is not a problem since authenticator doesn't need to have USAGE privilege on it.
  • Excludes pg_catalog, information_schema

Implicit schemas

• authenticator search_path

Pros

• Leaves the API roles alone (existing or new) and they can have USAGE privileges in whichever way.

Cons

Historically we've never required authenticator to have any privilege, so we'd have to deal with the consequences of USAGE privilege:

• If function privileges are not revoked from PUBLIC (default), then authenticator will be able to execute all functions. So /rpc is accessible for all.

  • We have a guide to solve this, so maybe not so critical.

• The danger of authenticator credentials being stolen and using it to access the db.
  I remember there was a discussion about this a long time ago. The main idea was that if authenticator creds were leaked, the damage caused would be much less compared to a traditional backend with usual postgres/superuser access. A db dump would not reveal any info. While you could impersonate the roles, if tables were protected with RLS filtered with application users' UUID, the attacker would have to guess UUIDs to extract some rows. So much more time consuming and during this an intrusion system could give a timely alert.
  Would this layer of defense weaken now that authenticator has USAGE on schemas? I don't think so, after all impersonating a role is easy and then the impersonated role has USAGE on the schemas. Also maybe this consequence doesn't matter at all and we shouldn't worry about it.

• Any more come to mind?

[steve-chavez]

So far I'm leaning towards Option 3 as it looks the cleanest.

[wolfgangwalther]

All 3 proposals have in common that they use the authenticator search_path for implicit schemas, which is only natural. I created #5043 to track this, I think we should treat it separately.

[wolfgangwalther]

Default schema

first of authenticator search_path

All 3 proposals mention the default schema being taken from the authenticator search_path. I think this is actively bad. I gave some reasons in #4364 (comment).

Big 👎 from me, but I don't have an alternative proposal.

Exposed schemas

schemas where the authenticator has USAGE privilege, direct or indirect

I agree with this part, this makes the most sense conceptually to me: While the authenticator role doesn't strictly need the privilege to expose any information about these schemas via openapi endpoints and such, we are essentially "giving PostgREST the right to expose the schema". Now the authenticator role is a special role for postgrest and its only purpose is to expose schemas. So we can treat "expose" and "use" equally, so we're at "giving the authenticator role the USAGE privilege". That checks all boxes for me.