#!/bin/bash

#------------------------------------------------------------------------------#
# Test: User+Admin anlegen, DB+Tabelle anlegen, Test-INSERT
#------------------------------------------------------------------------------#

HA_IP="10.145.43.30"
HA_PORT="5433"
DB_ROOT="postgres"

DB_ADMIN="dbadmin"
DB_A_PW="Passwort02"

DB_USER="dbuser"
DB_U_PW="Passwort01"

DATENBANK="testdb"
TABELLE="testtab"

grep -F 'password: ' /etc/patroni/config.yml

#==============================================================================#
# User, Tabelle und Datenbank entfernen

echo "# ${DB_ROOT}"
echo "
-- Aktive Verbindungen beenden
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE usename IN ('${DB_ADMIN}', '${DB_USER}');

-- Datenbank entfernen
DROP DATABASE IF EXISTS ${DATENBANK};

-- Berechtigungen auf allen Datenbanken widerrufen:
DO \$\$
DECLARE
    db RECORD;
BEGIN
    FOR db IN SELECT datname FROM pg_database WHERE datistemplate = false LOOP
        EXECUTE format('REVOKE ALL PRIVILEGES ON DATABASE %I FROM ${DB_ADMIN}, ${DB_USER};', db.datname);
    END LOOP;
END \$\$;

-- Rechte auf Schema-Ebene widerrufen:
REVOKE ALL ON SCHEMA public FROM ${DB_ADMIN}, ${DB_USER};

-- Rechte auf allen Objekten widerrufen:
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM ${DB_ADMIN}, ${DB_USER};
REVOKE ALL ON ALL SEQUENCES IN SCHEMA public FROM ${DB_ADMIN}, ${DB_USER};
REVOKE ALL ON ALL FUNCTIONS IN SCHEMA public FROM ${DB_ADMIN}, ${DB_USER};

-- Default Privileges für diese User auf alle Datenbanken entfernen
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL ON TABLES FROM dbadmin;
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL ON TABLES FROM dbuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL ON SEQUENCES FROM dbadmin;
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL ON SEQUENCES FROM dbuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL ON FUNCTIONS FROM dbadmin;
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL ON FUNCTIONS FROM dbuser;

DROP USER IF EXISTS ${DB_ADMIN}, ${DB_USER};
" | psql -h${HA_IP} -p${HA_PORT} -U ${DB_ROOT}

#==============================================================================#

echo "# ${DB_ROOT}"
echo "
-- Benutzer anlegen
CREATE ROLE ${DB_ADMIN}
  LOGIN
  CREATEDB
  PASSWORD '${DB_A_PW}';
 
-- Rechte für eine Datenbank
GRANT USAGE, CREATE ON SCHEMA public TO ${DB_ADMIN};
 
-- Rechte auf alle vorhandenen Tabellen IN "public" erteilen
GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA public TO ${DB_ADMIN};
 
-- Rechte auf alle neuen Tabellen IN "public" erteilen
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER
ON TABLES
TO ${DB_ADMIN};
 
-- (Optional) Rechte auf Sequences (IDs!)
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO ${DB_ADMIN};
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO ${DB_ADMIN};
" | psql -h${HA_IP} -p${HA_PORT} -U ${DB_ROOT}

#------------------------------------------------------------------------------#

echo "# ${DB_ADMIN}"
echo "
-- Test-Datenbank anlegen
CREATE DATABASE ${DATENBANK};
" | psql -h${HA_IP} -p${HA_PORT} -d ${DB_ROOT} -U ${DB_ADMIN}

#------------------------------------------------------------------------------#

echo "# ${DB_ROOT}"
echo "
-- Benutzer anlegen
CREATE ROLE ${DB_USER}
  LOGIN
  PASSWORD '${DB_U_PW}';

-- Rechte für eine Datenbank
GRANT USAGE, CREATE ON SCHEMA public TO ${DB_USER};

-- Rechte auf alle vorhandenen Tabellen IN "public" erteilen
GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA public TO ${DB_USER};

-- Rechte auf alle neuen Tabellen IN "public" erteilen
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER
ON TABLES
TO ${DB_USER};
" | psql -h${HA_IP} -p${HA_PORT} -U ${DB_ROOT}

#------------------------------------------------------------------------------#

echo "# ${DB_ADMIN}"
echo "
-- Permission auf public Schema und der neuen DB erteilen
GRANT USAGE, CREATE ON SCHEMA public TO ${DB_ADMIN};
GRANT USAGE, CREATE ON SCHEMA public TO ${DB_USER};
" | psql -h${HA_IP} -p${HA_PORT} -d ${DATENBANK} -U ${DB_ADMIN}

#------------------------------------------------------------------------------#

echo "# ${DB_USER}"
echo "
CREATE TABLE ${TABELLE} (id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, code VARCHAR(10));
INSERT INTO ${TABELLE} (code) VALUES ('ABC');
SELECT * FROM ${TABELLE};
" | psql -h${HA_IP} -p${HA_PORT} -d ${DATENBANK} -U ${DB_USER}

#==============================================================================#

#echo "# ${DB_ROOT}"
#echo "
#-- Rechte auf eine bestimmte Datenbank erteilen
#GRANT CONNECT ON DATABASE ${DATENBANK} TO ${DB_ADMIN};
#GRANT CONNECT ON DATABASE ${DATENBANK} TO ${DB_USER};
#" | psql -h${HA_IP} -p${HA_PORT} -U ${DB_ROOT}

#------------------------------------------------------------------------------#

#echo "# ${DB_ROOT}"
#echo "
#-- Rechte auf eine bestimmte Tabelle erteilen
#GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON TABLE ${TABELLE} TO ${DB_ADMIN};
#GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON TABLE ${TABELLE} TO ${DB_USER};
#" | psql -h${HA_IP} -p${HA_PORT} -U ${DB_ROOT}
 
#==============================================================================#