Unable to run db_setup_postgres task on Postgres 15

[janko]

I recently installed Postgres 15.2 via Homebrew, and maybe I set up something incorrectly, but I'm not able to successfully run the db_setup_postgres Rake task for testing. I first ran createuser -s postgres, and then running the task gives me the following result:

psql -U postgres -c "CREATE USER rodauth_test PASSWORD 'rodauth_test'"
CREATE ROLE
psql -U postgres -c "CREATE USER rodauth_test_password PASSWORD 'rodauth_test'"
CREATE ROLE
createdb -U postgres -O rodauth_test rodauth_test
psql -U postgres -c "CREATE EXTENSION citext" rodauth_test
CREATE EXTENSION
psql -U postgres -c "CREATE EXTENSION pgcrypto" rodauth_test
CREATE EXTENSION
rake aborted!
Sequel::DatabaseError: PG::InsufficientPrivilege: ERROR:  permission denied for schema public
LINE 1: CREATE TABLE IF NOT EXISTS "schema_info_password" ("version"...
                                   ^
/Users/janko/Code/clones/rodauth/Rakefile:115:in `block (2 levels) in <top (required)>'
/Users/janko/Code/clones/rodauth/Rakefile:114:in `block in <top (required)>'

Caused by:
PG::InsufficientPrivilege: ERROR:  permission denied for schema public
LINE 1: CREATE TABLE IF NOT EXISTS "schema_info_password" ("version"...
                                   ^
/Users/janko/Code/clones/rodauth/Rakefile:115:in `block (2 levels) in <top (required)>'
/Users/janko/Code/clones/rodauth/Rakefile:114:in `block in <top (required)>'
Tasks: TOP => db_setup_postgres
(See full trace by running task with --trace)

Any idea what could be the problem here?

[janko]

I'm seeing the following breaking changes in the 15.0 release notes that might be related:

• Remove PUBLIC creation permission on the public schema (Noah Misch)

  The new default is one of the secure schema usage patterns that Section 5.9.6 has recommended since the security release for CVE-2018-1058. The change applies to new database clusters and to newly-created databases in existing clusters. Upgrading a cluster or restoring a database dump will preserve public's existing permissions.

  For existing databases, especially those having multiple users, consider revoking CREATE permission on the public schema to adopt this new default. For new databases having no need to defend against insider threats, granting CREATE permission will yield the behavior of prior releases.

• Change the owner of the public schema to be the new pg_database_owner role (Noah Misch)

  This allows each database's owner to have ownership privileges on the public schema within their database. Previously it was owned by the bootstrap superuser, so that non-superuser database owners could not do anything with it.

  This change applies to new database clusters and to newly-created databases in existing clusters. Upgrading a cluster or restoring a database dump will preserve public's existing ownership specification.

[jeremyevans]

Actually, looks like the failure is a little different that that. The password user cannot create the table because it doesn't have rights. Maybe we can temporarily grant schema rights, then remove them. I'll try that.

[jeremyevans]

That approach looks like it works. Can you try:

diff --git a/Rakefile b/Rakefile
index c3142a0..1891d71 100644
--- a/Rakefile
+++ b/Rakefile
@@ -111,9 +111,11 @@ task :db_setup_postgres do
   Sequel.connect("#{adapters[:postgres]}:///rodauth_test?user=rodauth_test&password=rodauth_test") do |db|
     Sequel::Migrator.run(db, 'spec/migrate')
   end
+  sh 'psql -U postgres -c "GRANT CREATE ON SCHEMA public TO rodauth_test_password" rodauth_test'
   Sequel.connect("#{adapters[:postgres]}:///rodauth_test?user=rodauth_test_password&password=rodauth_test") do |db|
     Sequel::Migrator.run(db, 'spec/migrate_password', :table=>'schema_info_password')
   end
+  sh 'psql -U postgres -c "REVOKE CREATE ON SCHEMA public FROM rodauth_test_password" rodauth_test'
 end

 desc "Teardown database used for testing on MySQL"

edited example to make sure the correct database is used.

[jeremyevans]

I've tested the above and it appears to work. Here's a potential README update to go along with it:

diff --git a/README.rdoc b/README.rdoc
index ad880c0..ca294af 100644
--- a/README.rdoc
+++ b/README.rdoc
@@ -318,6 +318,16 @@ bad idea), you don't need to use the PostgreSQL citext extension. Just
 remember to modify the migration below to use +String+ instead of +citext+
 for the email in that case.

+=== Grant schema rights (PostgreSQL 15+)
+
+PostgreSQL 15 changed default database security so that only the database
+owner has writable access to the public schema.  Rodauth expects the
++ph+ account to have writable access to the public schema when setting
+things up.  Temporarily grant that access (it will be revoked after the
+migation has run)
+
+  psql -U postgres -c "GRANT CREATE ON SCHEMA public TO ${DATABASE_NAME}_password" ${DATABASE_NAME}
+
 === Using non-default schema

 PostgreSQL sets up new tables in the public schema by default.
@@ -739,6 +749,13 @@ One thing to notice in the above migrations is that Rodauth uses additional
 tables for additional features, instead of additional columns in a single
 table.

+=== Revoking schema rights (PostgreSQL 15+)
+
+If you explicit granted access to the public schema before running the
+migration, revoke it afterward:
+
+  psql -U postgres -c "REVOKE CREATE ON SCHEMA public FROM ${DATABASE_NAME}_password" ${DATABASE_NAME}
+
 === Locking Down (PostgreSQL only)

 After running the migrations, you can increase security slightly by making

Does that look OK?

[janko]

The suggested changes work for me, thanks! 👍🏻

[jeremyevans]

Thanks, pushed as f604dae