[pmg-devel] [PATCH pmg-api v2] 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>
---
changes from v1:
* rename cmd to $grantcmd
* actually use it and execute it with postgres_admin_cmd/psql
 src/PMG/DBTools.pm | 10 ++++++++++
 1 file changed, 10 insertions(+)

diff --git a/src/PMG/DBTools.pm b/src/PMG/DBTools.pm
index 0b37361..a437eb6 100644
--- a/src/PMG/DBTools.pm
+++ b/src/PMG/DBTools.pm
@@ -344,6 +344,16 @@ 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 $grantcmd = "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\";";
+
+    postgres_admin_cmd('psql', { input => $grantcmd }, '-d', $dbname);
 };
 
 sub create_ruledb {
-- 
2.30.2