launchpad-reviewers team mailing list archive

[Guruprasad <mp+480156@xxxxxxxxxxxxxxxxxx>]

Guruprasad has proposed merging ~lgp171188/launchpad:fix-postgres-16-test-failures into launchpad:master.

Commit message:
Fix the test failures with PostgreSQL 16

* Update test_disconnectionerror_view_integration asserts for Postgres 16
* Grant CREATE on the 'public' schema in dev environments for Postgres 15+

  Postgres 15+ changed the previous behaviour to grant it to all users.

Requested reviews:
  Launchpad code reviewers (launchpad-reviewers)

For more details, see:
https://code.launchpad.net/~lgp171188/launchpad/+git/launchpad/+merge/480156
-- 
Your team Launchpad code reviewers is requested to review the proposed merge of ~lgp171188/launchpad:fix-postgres-16-test-failures into launchpad:master.

diff --git a/database/schema/Makefile b/database/schema/Makefile
index 0b33795..b1ff5e1 100644
--- a/database/schema/Makefile
+++ b/database/schema/Makefile
@@ -117,7 +117,7 @@ create:
 	@ echo "* Patching the database schema"
 	@ ./upgrade.py --separate-sessions -d ${EMPTY_DBNAME}
 	@ echo "* Security setup"
-	@ ./security.py -q -d ${EMPTY_DBNAME}
+	@ ./security.py -q -d ${EMPTY_DBNAME} --grant-create-on-public-schema
 	@ echo "* Disabling autovacuum"
 	@ ./unautovacuumable.py -d ${EMPTY_DBNAME}
 	@ echo "* Vacuuming"
diff --git a/database/schema/security.py b/database/schema/security.py
index d742577..d89172a 100755
--- a/database/schema/security.py
+++ b/database/schema/security.py
@@ -646,6 +646,20 @@ def reset_permissions(con, config, options):
             "GRANT USAGE ON SCHEMA %s TO PUBLIC"
             % (quote_identifier(schema_name),)
         )
+
+    # XXX 2025-01-27 lgp171188: PostgreSQL 15+ stopped granting
+    # the permission to create tables in the 'public' namespace
+    # for all non-owner users as a part of security strengthening,
+    # in favour of requiring the users to do this manually as needed.
+    # We conditionally enable it here as various Launchpad roles
+    # need it to be able to run the Launchpad test suite without any errors.
+    if (
+        options.grant_create_on_public_schema and
+        con.server_version // 10000 >= 15
+    ):
+        log.debug("Granting CREATE on schema 'public' on PostgreSQL 15+.")
+        cur.execute("GRANT CREATE on SCHEMA public TO PUBLIC")
+
     for obj in schema.values():
         if obj.schema not in public_schemas:
             continue
@@ -760,6 +774,20 @@ if __name__ == "__main__":
         default="postgres",
         help="Owner of PostgreSQL objects",
     )
+    parser.add_option(
+        "--grant-create-on-public-schema",
+        dest="grant_create_on_public_schema",
+        default=False,
+        action="store_true",
+        help=(
+            "Grant CREATE on the 'public' schema in PostgreSQL 15+ to "
+            "all users. PostgreSQL <= 14 allowed this access automatically."
+            "This should only be used in the dev/test environments via the"
+            "'make schema' invocation and not anywhere in a production or"
+            "production-like environment."
+        )
+    )
+
     db_options(parser)
     logger_options(parser)
 
diff --git a/lib/lp/services/webapp/tests/test_error.py b/lib/lp/services/webapp/tests/test_error.py
index ee6a4cd..c223924 100644
--- a/lib/lp/services/webapp/tests/test_error.py
+++ b/lib/lp/services/webapp/tests/test_error.py
@@ -276,12 +276,14 @@ class TestDatabaseErrorViews(TestCase):
                 MatchesAny(
                     # libpq < 14.0
                     Disconnects("database removed"),
-                    # libpq >= 14.0
+                    # libpq ~= 14.0
                     DisconnectsWithMessageRegex(
                         libpq_14_connection_error_prefix_regex
                         + ": ERROR: database does not allow connections: "
                         r"launchpad_ftest_.*"
                     ),
+                    # libpq ~= 16.0
+                    Disconnects("server closed the connection unexpectedly"),
                 )
             ),
         )