Some checks failed
CI / test-and-build (push) Failing after 25s
Co-authored-by: Cursor <cursoragent@cursor.com>
52 lines
1.5 KiB
Bash
52 lines
1.5 KiB
Bash
#!/usr/bin/env bash
|
|
# Create limited Postgres role for pgAdmin over SSH tunnel (no public 5432).
|
|
set -euo pipefail
|
|
PASS_FILE="${HOME}/pgadmin-db-credentials.txt"
|
|
|
|
if [[ -f "$PASS_FILE" ]]; then
|
|
echo "Credentials file already exists: $PASS_FILE"
|
|
# still ensure role exists / password rotated only if FORCE=1
|
|
if [[ "${FORCE:-0}" != "1" ]]; then
|
|
exit 0
|
|
fi
|
|
fi
|
|
|
|
PASS="$(openssl rand -base64 24 | tr -d '\n=/+' | head -c 28)"
|
|
sudo -u postgres psql -v ON_ERROR_STOP=1 <<SQL
|
|
DO \$\$
|
|
BEGIN
|
|
IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'tloto_pgadmin') THEN
|
|
CREATE ROLE tloto_pgadmin LOGIN PASSWORD '${PASS}';
|
|
ELSE
|
|
ALTER ROLE tloto_pgadmin WITH LOGIN PASSWORD '${PASS}';
|
|
END IF;
|
|
END
|
|
\$\$;
|
|
GRANT CONNECT ON DATABASE games_platform_db TO tloto_pgadmin;
|
|
\c games_platform_db
|
|
GRANT USAGE ON SCHEMA public TO tloto_pgadmin;
|
|
GRANT SELECT ON ALL TABLES IN SCHEMA public TO tloto_pgadmin;
|
|
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO tloto_pgadmin;
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO tloto_pgadmin;
|
|
SQL
|
|
|
|
cat > "$PASS_FILE" <<EOF
|
|
pgAdmin via SSH tunnel (recommended)
|
|
|
|
1) On Windows PowerShell (keep window open):
|
|
ssh -N -L 5433:127.0.0.1:5432 deploy@191.215.41.53
|
|
|
|
2) In pgAdmin → Register Server:
|
|
Host: 127.0.0.1
|
|
Port: 5433
|
|
Maintenance DB: games_platform_db
|
|
Username: tloto_pgadmin
|
|
Password: ${PASS}
|
|
|
|
Role privileges: CONNECT + SELECT only (no DDL/DML by default).
|
|
Never open UFW 5432 to the internet.
|
|
Created: $(date -u +%Y-%m-%dT%H:%M:%SZ)
|
|
EOF
|
|
chmod 600 "$PASS_FILE"
|
|
echo "Wrote $PASS_FILE"
|