From ea521101a6c18ebbda19438e2b01120c971f690f Mon Sep 17 00:00:00 2001
From: "Karl O. Pinc" <kop@karlpinc.com>
Date: Wed, 13 Sep 2023 15:15:50 -0500
Subject: [PATCH] Drop the PUBLIC schema with a cluster initialization target

---
 db/schemas/createschemas.m4 |  1 -
 make_files/make_db.mk       | 36 +++++++++++++++++++++++++++++++++---
 2 files changed, 33 insertions(+), 4 deletions(-)

diff --git a/db/schemas/createschemas.m4 b/db/schemas/createschemas.m4
index 9e5fbe2..3aaecd1 100644
--- a/db/schemas/createschemas.m4
+++ b/db/schemas/createschemas.m4
@@ -21,7 +21,6 @@ include(`copyright.m4')dnl
 include(`constants.m4')dnl
 dnl
 
-DROP SCHEMA IF EXISTS public RESTRICT;
 CREATE SCHEMA IF NOT EXISTS lookup;
 CREATE SCHEMA IF NOT EXISTS sokwedb;
 CREATE SCHEMA IF NOT EXISTS upload;
diff --git a/make_files/make_db.mk b/make_files/make_db.mk
index 3e9cc72..a0edf61 100644
--- a/make_files/make_db.mk
+++ b/make_files/make_db.mk
@@ -458,15 +458,21 @@ $(DB_TARGETS) db/creategroups.sql: %.sql: %.m4 $(DB_DEPENDS)
 ##
 ##  1) If you already have a Postgres role that can login, create roles, and
 ##  create databases then that role should be used as the administrative
-##  user.  Otherwise, first create an administrative user (as root) with
-##  "create-adminuser".
+##  user.  Otherwise, first create an administrative user (while logged in
+##  to Unix as root) with "create-adminuser".
 ##
 ##  2) Use the administrative user to create the "group" roles
 ##  ("create-groups") and then the database ("init-database").
 ##  Hint: Use TARGET_DB=postgres for all of this, since your new database
 ##  won't yet exist.
 ##
-##  The database will be owned by the admin group.
+##  3) Drop the "public" schema.  If you are using PG v15 or later use the
+##  "drop-public" target.  If you have an administrative user that has
+##  true Postgres superuser privileges that can be used with the
+##  "drop-public" target.  Otherwise use the "drop-public-root" target
+##  while logged in to Unix as the root user.
+##
+##  The "init-database" target creates a database owned by the admin group.
 ##
 ##  After cluster initialization create at least one login for an individual
 ##  using the administrative user.  Give the logins elevated priviliges with
@@ -500,8 +506,10 @@ CREATE_SUPERUSER := CREATE ROLE $(TARGET_ROLE) LOGIN SUPERUSER \
                     PASSWORD '$(NEW_PASSWORD)';
 CREATE_ADMINUSER := CREATE ROLE $(TARGET_ROLE) LOGIN CREATEDB CREATEROLE \
                     PASSWORD '$(NEW_PASSWORD)';
+DROP_PUBLIC := DROP SCHEMA public RESTRICT;
 SET_PASSWORD := ALTER ROLE $(TARGET_ROLE) PASSWORD '$(NEW_PASSWORD)'
 
+
 ## The available targets for make (make TARGET) are:
 ##
 ##   create-adminuser   This target only works when the PostgreSQL cluster
@@ -556,6 +564,28 @@ init-database:
 	  ) \
 	  | psql $(PSQL_ARGS) $(PSQL_SINGLE_TRANS) --set=ON_ERROR_STOP=y
 
+##   drop-public        Drop the public schema in TARGET_DB after connecting
+##                      to the database as the ADMINUSER.
+.PHONY: drop-public
+drop-public:
+	printf '$(DROP_PUBLIC)\n' \
+	  | psql $(PSQL_ARGS) $(PSQL_SINGLE_TRANS) --set=ON_ERROR_STOP=y
+
+##   drop-public-root   This target only works when the PostgreSQL cluster
+##                      with which you are interacting is on the local
+##                      host.  Drop the public schema in TARGET_DB
+##                      while logged in to Unix as the root user.
+# As of PG v15 this target is no longer necessary.  The problem is
+# that PUBLIC is owned by postgres, so can only be dropped by a pg
+# SUPERUSER or by the postgres user.  PG v15 introduced the
+# pg_database_owner pre-defined role and database owners can drop the
+# public schema.
+
+.PHONY: drop-public-root
+drop-public-root:
+	printf '$(DROP_PUBLIC)\n' \
+	  | su postgres -c 'psql -h $(HOST) -d $(TARGET_DB) -q -U postgres'
+
 ##
 ## Lesser used targets:
 ##
-- 
2.34.1