Tags
PostgreSQL
script
  1. ai postgresql
  2. systemctl status postgresql
  3. /etc/postgresql/15/main/pg_hba.conf

psql

  1. LANG=C
  2. psql -U postgres -c "SELECT datname FROM pg_catalog.pg_database;"
  3. psql -U postgres -c "DROP DATABASE DBNAME;"
  4. psql -U postgres -c "SELECT * FROM pg_catalog.pg_user;"
  5. psql -U postgres -c "REVOKE USAGE ON SCHEMA public FROM DBUSERNAME;" 
  6. psql -U postgres -c "DROP USER DBUSERNAME;"

create_db.sh

#!/bin/bash
# Script Version: 01
# Creates a PostgreSQL database and user with read-only permissions

export LANG=C  # Ensure consistent locale

read -e -p "Name der Datenbank (und Benutzer): " NAME
DB="$NAME"
USER="$NAME"

# Generate password with OpenSSL
PASS=$(openssl rand -hex 16)

echo "📦 Erstelle Datenbank: $DB"
echo "👤 Erstelle Benutzer: $USER"
echo "🔐 Generiertes Passwort: $PASS"

psql -U postgres -c "CREATE DATABASE $DB;"
psql -U postgres -c "CREATE USER $USER WITH PASSWORD '$PASS';"
psql -U postgres -c "REVOKE ALL ON DATABASE $DB FROM PUBLIC;"
psql -U postgres -c "GRANT CONNECT ON DATABASE $DB TO $USER;"
psql -U postgres -d $DB -c "GRANT USAGE ON SCHEMA public TO $USER;"
psql -U postgres -d $DB -c "GRANT SELECT ON ALL TABLES IN SCHEMA public TO $USER;"
psql -U postgres -d $DB -c "ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO $USER;"

echo "✅ Datenbank '$DB' und Benutzer '$USER' wurden mit Lesezugriff erstellt."
echo "🔑 Passwort: $PASS"