[pmg-devel] [PATCH pmg-api 1/3] dbtools: grant permissions public schema for created databases

[Dominik Csapak <d.csapak@proxmox.com>]

since postgres 15, the public schema is not world writeable anymore for
security reasons. In our environment, where the db is not externaly
reachable and no database users should exists except the ones we create,
we can safely give the permissions again to be able to use
the root/www-data user without modification of the remaining
code/privileges for postgres.

Signed-off-by: Dominik Csapak <d.csapak@proxmox.com>
---
 src/PMG/DBTools.pm | 8 ++++++++
 1 file changed, 8 insertions(+)

diff --git a/src/PMG/DBTools.pm b/src/PMG/DBTools.pm
index 0b37361..f8eb054 100644
--- a/src/PMG/DBTools.pm
+++ b/src/PMG/DBTools.pm
@@ -344,6 +344,14 @@ my $createdb = sub {
 	'--lc-ctype=C',
 	$dbname,
     );
+
+    # allow root and www-data to access the public SCHEMA like pre prostgres15
+    # this is not a security issue, since the db is not externally reachable anyway and no
+    # other users should exist
+    my $cmd = "GRANT CREATE ON SCHEMA public To \"root\";"
+	."GRANT USAGE ON SCHEMA public To \"root\";"
+	."GRANT CREATE ON SCHEMA public To \"www-data\";"
+	."GRANT USAGE ON SCHEMA public To \"www-data\";";
 };
 
 sub create_ruledb {
-- 
2.30.2