Avoid using public schema in Postgresql 15: (ERROR: permission denied for schema public)

[wvh]

Preflight checklist

• I could not find a solution in the existing issues, docs, nor discussions.
• I agree to follow this project's Code of Conduct.
• I have read and am following this repository's Contribution Guidelines.
• This issue affects my Ory Network project.
• I have joined the Ory Community Slack.
• I am signed up to the Ory Security Patch Newsletter.

Describe the bug

Hello!

Postgresql 15 removed permissions to the public schema by default. See for instance these release notes.

Just creating a schema with the same name as the database user, e.g. hydra, will let the user automatically use their default schema because that appears in the search_path before the public schema. This requires no action from Hydra.

However, the problem is that some of the migrations have hardcoded references to the public.* schema, triggering permissions problems for the SQL of those migrations.

I think it would be fine to just remove the public. prefix from the few migrations that have it and it's probably a mistake that that schema prefix is included anyway. Assuming the default schema is public is going to become problematic as people are upgrading Postgresql to version 15 and beyond.

Reproducing the bug

• install postgres 15
• run the Hydra quickstart
• some of the migrations will try to write to the public schema because they've hardcoded a fully qualified name in database references

Relevant log output

idp-hydra-migrate-1  | ERROR: permission denied for schema public (SQLSTATE 42501)

Relevant configuration

No response

Version

v2.0.2

On which operating system are you observing this issue?

Linux

In which environment are you deploying?

Docker Compose

Additional Context

No response

[aeneasr]

Thank you for the report! Could you please point to the migrations which use the public schema? :)

[wvh]

Sure! I don't think I have the Postgres error logs themselves anymore, but here are the migration files that refer to a full schema identifier:

$ rg -l "\bpublic\." persistence/sql/
persistence/sql/migrations/20211019000001000004_merge_authentication_request_tables.cockroach.up.sql
persistence/sql/migrations/20221109000010000001_fix_foreign_key.cockroach.up.sql
persistence/sql/migrations/20221109000010000000_fix_foreign_key.postgres.up.sql
persistence/sql/migrations/20211019000001000002_merge_authentication_request_tables.cockroach.up.sql
persistence/sql/migrations/20211019000001000002_merge_authentication_request_tables.postgres.up.sql
persistence/sql/migrations/20211019000001000003_merge_authentication_request_tables.cockroach.up.sql
persistence/sql/src/20211019000001_merge_authentication_request_tables/20211019000001000000_merge_authentication_request_tables.postgres.up.sql
persistence/sql/src/20211019000001_merge_authentication_request_tables/20211019000001000000_merge_authentication_request_tables.cockroach.up.sql

These will return an error on Postgres 15 as the public schema is not writeable by default; and if the user creates another schema as suggested by Postgres – for instance with the same name as the user, because that gets picked up automatically – then those few migrations will be applied to public instead of the actual schema.

[aeneasr]

Thank you! Anyone looking at this, we welcome contributions :)