postgresql: cleanup code

diff --git a/hosts/losurdo/postgresql.nix b/hosts/losurdo/postgresql.nix
index ad3a92d32a5c63486490abb9c7bd74ceea878b00..6467f42f8b62995fe600ac9ecaad233ce2914064 100644 (file)
--- a/hosts/losurdo/postgresql.nix
+++ b/hosts/losurdo/postgresql.nix
@@ -48,7 +48,9 @@ services.postgresql = {
     ssl_key_file = "/var/lib/acme/${domain}/key.pem";
     unix_socket_permissions = "0770";
   };
-  authentication = lib.mkForce ''
+  # If one record is chosen and the auth fails,
+  # subsequent records are not considered.
+  authentication = ''
     # CONNECTION  DATABASE USER      AUTH  OPTIONS
     local         all      postgres  peer  map=admin
     local         samerole all       peer  map=user
@@ -69,12 +71,14 @@ fileSystems."/var/lib/postgresql" = {
   # though loading OpenConcerto's initial SQL
   # takes 1m40s instead of 40s :\
 };
+/*
 services.syncoid.commands = {
   "${hostName}/var/postgresql" = {
     sendOptions = "raw";
     target = "backup@mermet.${domain}:rpool/backup/${hostName}/var/postgresql";
   };
 };
+*/
 services.sanoid.datasets = {
   "${hostName}/var/postgresql" = {
     use_template = [ "snap" ];
@@ -92,9 +96,9 @@ systemd.services.postgresql = {
       REVOKE ALL ON DATABASE template0 FROM public;
       REVOKE ALL ON DATABASE template1 FROM public;
       REVOKE ALL ON SCHEMA public FROM public;
-      GRANT  ALL ON SCHEMA public TO ${postgresql.superUser};
+      GRANT  ALL ON SCHEMA public TO "${postgresql.superUser}";
       REVOKE ALL ON ALL TABLES IN SCHEMA public FROM public;
-      GRANT  ALL ON ALL TABLES IN SCHEMA public TO ${postgresql.superUser};
+      GRANT  ALL ON ALL TABLES IN SCHEMA public TO "${postgresql.superUser}";
 
       -- Disallow access to database and user names for everyone
       REVOKE ALL ON pg_catalog.pg_user         FROM public;
@@ -114,19 +118,19 @@ systemd.services.postgresql = {
       local template=''${template:-template1}
       $PSQL -tAc "SELECT 1 FROM pg_catalog.pg_database WHERE datname = '$db'" | grep -q 1 || {
         $PSQL -d "$template" -AqtX --set ON_ERROR_STOP=1 -f - <<EOF
-          CREATE ROLE $owner NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT LOGIN PASSWORD '$pass';
-          CREATE DATABASE $db WITH OWNER=$owner
+          CREATE ROLE "$owner" NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT ''${pass:+LOGIN PASSWORD '$pass'};
+          CREATE DATABASE "$db" WITH OWNER="$owner"
            ''${encoding:+ENCODING='$encoding'}
            ''${lc_collate:+LC_COLLATE='$lc_collate'}
            ''${lc_ctype:+LC_CTYPE='$lc_ctype'}
            ''${tablespace:+TABLESPACE='$tablespace'}
            ''${connection_limit:+CONNECTION LIMIT=$connection_limit}
           ;
-          REVOKE ALL ON DATABASE $db FROM public;
+          REVOKE ALL ON DATABASE "$db" FROM public;
     EOF
-        $PSQL -d $db -AqtX --set ON_ERROR_STOP=1 -f - <<EOF
+        $PSQL -d "$db" -AqtX --set ON_ERROR_STOP=1 -f - <<EOF
           -- Grant all rights to the public schema in the new database to the main user
-          GRANT ALL ON SCHEMA public TO $owner WITH GRANT OPTION;
+          GRANT ALL ON SCHEMA public TO "$owner" WITH GRANT OPTION;
     EOF
         $PSQL -d "$db" -AqtX --set ON_ERROR_STOP=1 -f -
       }
@@ -136,10 +140,10 @@ systemd.services.postgresql = {
       local db=$1
       local user=$2
       $PSQL -tAc "SELECT 1 FROM pg_catalog.pg_roles WHERE rolname='$user'" | grep -q 1 ||
-      $PSQL -d $db -AqtX --set ON_ERROR_STOP=1 -f - <<EOF
-        CREATE ROLE $user NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT LOGIN ENCRYPTED PASSWORD '$pass';
-        GRANT USAGE ON SCHEMA public TO $user;
-        GRANT CONNECT,TEMPORARY ON DATABASE $db TO $user;
+      $PSQL -d "$db" -AqtX --set ON_ERROR_STOP=1 -f - <<EOF
+        CREATE ROLE "$user" NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT ''${pass:+LOGIN ENCRYPTED PASSWORD '$pass'};
+        GRANT USAGE ON SCHEMA public TO "$user";
+        GRANT CONNECT,TEMPORARY ON DATABASE "$db" TO "$user";
     EOF
     }
   '';

diff --git a/hosts/losurdo/postgresql/openconcerto.nix b/hosts/losurdo/postgresql/openconcerto.nix
index 45271b59e6c6576d9dae50d265b0793ebcb05d3f..4b537c0f74cd394cfd6181c0fdd6a37a0f3c523b 100644 (file)
--- a/hosts/losurdo/postgresql/openconcerto.nix
+++ b/hosts/losurdo/postgresql/openconcerto.nix
@@ -58,7 +58,7 @@ systemd.services.postgresql = {
     owner=${owner} \
     pass=$(cat ${gnupg.secrets."postgresql/pass/${owner}".path}) \
     pg_createdb ${db} >/dev/null
-    
+
     $PSQL -d "${db}" -AqtX --set ON_ERROR_STOP=1 -f - <<EOF
       -- Reallow this to avoid the error:
       -- "Couldn't refresh the graph"
@@ -71,7 +71,7 @@ systemd.services.postgresql = {
       GRANT SELECT ON pg_catalog.pg_tablespace TO ${owner};
       -- Reallow this to allow pgadmin3
       GRANT SELECT ON pg_catalog.pg_user       TO ${owner};
-      
+
       -- Enable PL/PGSQL
       CREATE OR REPLACE LANGUAGE plpgsql;
     EOF