#!/bin/bash
set -e
set -o pipefail

PKG="unifi-innerspace"
USER="unifi-innerspace"
GROUP="unifi-innerspace"
POSTGRES_HOST="/var/run/postgresql"
POSTGRES_PORT="5432"
DATABASE_SCRIPT="/usr/share/$PKG/app/workspace/backend/database.js"

as_postgres() {
  runuser --user postgres -- env PGPASSWORD= "$@"
}

# Set correct access rights
mkdir -p "/data/$PKG"
chown -R "$USER:$GROUP" "/data/$PKG"
chmod g+w "/data/$PKG"

# COPY PROGRAM is available to superusers and pg_execute_server_program members.
# Keep the application role outside both privilege sets while retaining CREATEDB for backup restore.
as_postgres psql \
  --host="$POSTGRES_HOST" \
  --port="$POSTGRES_PORT" \
  --username=postgres \
  --dbname=postgres \
  --no-password \
  --no-psqlrc \
  --set=ON_ERROR_STOP=1 \
  --set=app_role="$USER" <<'SQL'
SELECT format('CREATE ROLE %I', :'app_role')
WHERE NOT EXISTS (
  SELECT FROM pg_catalog.pg_roles WHERE rolname = :'app_role'
)
\gexec

SELECT format('REVOKE %I FROM %I', granted_role.rolname, member_role.rolname)
FROM pg_catalog.pg_auth_members AS membership
JOIN pg_catalog.pg_roles AS granted_role ON granted_role.oid = membership.roleid
JOIN pg_catalog.pg_roles AS member_role ON member_role.oid = membership.member
WHERE member_role.rolname = :'app_role'
\gexec

ALTER ROLE :"app_role"
  WITH LOGIN NOSUPERUSER CREATEDB NOCREATEROLE NOINHERIT NOREPLICATION NOBYPASSRLS PASSWORD NULL;
REVOKE pg_execute_server_program, pg_read_server_files, pg_write_server_files FROM :"app_role";
SQL

# Run schema migrations with the local administrator so existing postgres-owned installations remain upgradeable.
as_postgres env \
  PGHOST="$POSTGRES_HOST" \
  PGPORT="$POSTGRES_PORT" \
  PGUSER=postgres \
  PGDATABASE="$PKG" \
  "$DATABASE_SCRIPT"

as_postgres psql \
  --host="$POSTGRES_HOST" \
  --port="$POSTGRES_PORT" \
  --username=postgres \
  --dbname=postgres \
  --no-password \
  --no-psqlrc \
  --set=ON_ERROR_STOP=1 \
  --set=app_role="$USER" <<'SQL'
SELECT format('ALTER DATABASE %I OWNER TO %I', datname, :'app_role')
FROM pg_catalog.pg_database
WHERE datname IN ('unifi-innerspace', 'unifi-innerspace-temp')
\gexec
SQL

as_postgres psql \
  --host="$POSTGRES_HOST" \
  --port="$POSTGRES_PORT" \
  --username=postgres \
  --dbname="$PKG" \
  --no-password \
  --no-psqlrc \
  --set=ON_ERROR_STOP=1 \
  --set=app_role="$USER" <<'SQL'
GRANT USAGE ON SCHEMA public TO :"app_role";
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO :"app_role";
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO :"app_role";
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO :"app_role";

ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO :"app_role";
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES TO :"app_role";
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public
  GRANT EXECUTE ON FUNCTIONS TO :"app_role";
SQL
