[taler-grid5k] 07/189: fix sharding permissions

[gnunet]

This is an automated email from the git hooks/post-receive script.

marco-boss pushed a commit to branch master
in repository grid5k.

commit 1b1de43691764f4143d1c331b33ba1170065c8dd
Author: Boss Marco <bossm8@bfh.ch>
AuthorDate: Sun Feb 20 11:39:04 2022 +0100

    fix sharding permissions
---
 experiment/scripts/database.sh | 81 +++++++++++++++++++++++++++---------------
 sql/exchange-0002.sql          |  4 +--
 2 files changed, 53 insertions(+), 32 deletions(-)

diff --git a/experiment/scripts/database.sh b/experiment/scripts/database.sh
index e8bbdb0..320849d 100755
--- a/experiment/scripts/database.sh
+++ b/experiment/scripts/database.sh
@@ -138,34 +138,62 @@ function setup_pgbouncer() {
   fi
 }
 
+function setup_distributed_db() {
+
+  echo "OVERRIDING EXCHANGE SQL INIT SCRIPT"
+  cp ${G5K_HOME}/sql/exchange-0001.sql /usr/share/taler/sql/exchange/
+  chmod o+r /usr/share/taler/sql/exchange/exchange-0001.sql
+
+  sudo -u taler-exchange-httpd taler-exchange-dbinit
+
+  cp ${G5K_HOME}/sql/exchange-0002.sql /tmp
+  chmod o+r /tmp/exchange-0002.sql
+
+  su taler-exchange-httpd -s /bin/bash << EOF
+psql -d "${DB_NAME}" -tAf /tmp/exchange-0002.sql
+EOF
+
+}
+
 function setup_shards() {
+  setup_distributed_db
+
+  su postgres << EOF
+psql -d "${DB_NAME}" -tAc "CREATE EXTENSION IF NOT EXISTS postgres_fdw;"
+EOF
+
   su taler-exchange-httpd -s /bin/bash << EOF
-psql -tAc SELECT prepare_sharding();
+psql -d "${DB_NAME}" -tAc "SELECT prepare_sharding();"
 EOF
 
   let "i=1"
   for SHOST in ${SHARD_HOSTS//|/ }; do
-    su taler-exchange-httpd -s /bin/bash << EOF
-psql -tAc SELECT create_shard_server('${SHOST}',
-                                     5432,
-                                     '${DB_USER}',
-                                     '${DB_PASSWORD}',
-                                    '$(echo ${SHOST} | cut -d "." -f 2)',
-                                     ${NUM_SHARDS},
-                                     ${i},
-                                     '${DB_NAME}');
+  SHARD_NAME=$(echo ${SHOST} | cut -d "." -f 1)
+    su postgres << EOF
+psql -d "${DB_NAME}" -tAc "SELECT create_shard_server('${SHOST}',
+                                                      5432,
+                                                      '${DB_USER}',
+                                                      '${DB_PASSWORD}',
+                                                      '${SHARD_NAME}',
+                                                      ${NUM_SHARDS},
+                                                      ${i},
+                                                      '${DB_NAME}');"
+psql -d "${DB_NAME}" -tAc "GRANT ALL PRIVILEGES ON
+                           FOREIGN SERVER \"shard_${SHARD_NAME}\"
+                           TO \"taler-exchange-httpd\";"
 EOF
   let "i=i+1"
   done
 
   su taler-exchange-httpd -s /bin/bash << EOF
-psql -tAc SELECT drop_default_partitions();
+psql -d "${DB_NAME}" -tAc SELECT drop_default_partitions();
 EOF
 }
 
 function setup_partitions() {
+  setup_distributed_db
   su taler-exchange-httpd -s /bin/bash << EOF
-psql -tAc SELECT create_partitions(${NUM_PARTITIONS});
+psql -d "${DB_NAME}" -tAc SELECT create_partitions(${NUM_PARTITIONS});
 EOF
 }
 
@@ -175,6 +203,7 @@ function init_db() {
 
   # Create the role taler-exchange-httpd and the database
   su postgres << EOF
+psql postgres -tAc "DROP DATABASE IF EXISTS '${DB_NAME}';"
 psql postgres -tAc "SELECT 1 FROM pg_roles WHERE 
rolname='taler-exchange-httpd'" | \
   grep -q 1 || \
   createuser taler-exchange-httpd
@@ -183,13 +212,17 @@ psql -tAc "SELECT 1 FROM pg_database WHERE 
datname='${DB_NAME}'" | \
   createdb -O taler-exchange-httpd "${DB_NAME}"
 EOF
  
-  echo "OVERRIDING EXCHANGE SQL INIT SCRIPT"
-  cp ${G5K_HOME}/sql/exchange-0001.sql /usr/share/taler/sql/exchange/
-
   sudo -u taler-exchange-httpd taler-exchange-dbinit -r || true
   sudo -u taler-exchange-httpd taler-exchange-dbinit -s || true
-  sudo -u taler-exchange-httpd taler-exchange-dbinit
   
+  if [ "${SHARD_DB}" = "true" ]; then
+    setup_shards
+  elif [ "${PARTITION_DB}" = "true" ]; then
+    setup_partitions
+  else
+    sudo -u taler-exchange-httpd taler-exchange-dbinit
+  fi
+
   # Create the remote user "$DB_USER" and load pg_stat_statements for metrics
   su postgres << EOF
 psql postgres -tAc "SELECT 1 FROM pg_roles WHERE rolname='${DB_USER}'" | \
@@ -199,30 +232,20 @@ psql postgres -tAc "SELECT 1 FROM pg_roles WHERE 
rolname='${DB_USER}'" | \
     CREATE EXTENSION pg_stat_statements;
 END
 EOF
-  
+
   # Grant access to the databse to the remote user
   su taler-exchange-httpd -s /bin/bash << EOF
 psql -d "${DB_NAME}"
 GRANT SELECT,INSERT,UPDATE ON ALL TABLES IN SCHEMA public TO "${DB_USER}";
 GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO "${DB_USER}";
 EOF
-
-  su taler-exchange-httpd -s /bin/bash << EOF
-psql -tAf ${G5K_HOME}/sql/exchange-0002.sql
-EOF
-
-  if [ "${SHARD_DB}" = "true" ]; then
-    setup_shards
-  elif [ "${PARTITION_DB}" = "true" ]; then
-    setup_partitions
-  fi
 }
 
 case ${1} in 
   init)
     setup_config
-    #setup_disks
-    #setup_ram_storage
+    # setup_disks
+    # setup_ram_storage
     init_db
     setup_pgbouncer
     restart_rsyslog
diff --git a/sql/exchange-0002.sql b/sql/exchange-0002.sql
index e2a8f5f..7075813 100644
--- a/sql/exchange-0002.sql
+++ b/sql/exchange-0002.sql
@@ -153,8 +153,6 @@ CREATE OR REPLACE FUNCTION prepare_sharding()
 AS $$
 BEGIN
 
-  CREATE EXTENSION IF NOT EXISTS postgres_fdw;
-
   PERFORM detach_default_partitions();
 
   ALTER TABLE IF EXISTS wire_targets DROP CONSTRAINT IF EXISTS 
wire_targets_pkey CASCADE;
@@ -227,7 +225,7 @@ BEGIN
   );
 
   EXECUTE FORMAT(
-    'CREATE USER MAPPING IF NOT EXISTS FOR admin SERVER %I '
+    'CREATE USER MAPPING IF NOT EXISTS FOR "taler-exchange-httpd" SERVER %I '
       'OPTIONS (user %L, password %L)',
     'shard_' || suffix,
     usr,

-- 
To stop receiving notification emails like this one, please contact
gnunet@gnunet.org.