86 lines
2.5 KiB
Bash
86 lines
2.5 KiB
Bash
|
#!/bin/bash
|
||
|
|
# Grant Database Permissions for dbis user
|
||
|
|
# Run this on the Proxmox host to grant permissions in the database container
|
||
|
|
|
||
|
|
set -e
|
||
|
|
|
||
|
|
VMID="${VMID:-10100}"
|
||
|
|
DB_NAME="${DB_NAME:-dbis_core}"
|
||
|
|
DB_USER="${DB_USER:-dbis}"
|
||
|
|
|
||
|
|
echo "=========================================="
|
||
|
|
echo "Granting Database Permissions"
|
||
|
|
echo "=========================================="
|
||
|
|
echo ""
|
||
|
|
echo "VMID: $VMID"
|
||
|
|
echo "Database: $DB_NAME"
|
||
|
|
echo "User: $DB_USER"
|
||
|
|
echo ""
|
||
|
|
|
||
|
|
# Check if pct command exists (must run on Proxmox host)
|
||
|
|
if ! command -v pct &> /dev/null; then
|
||
|
|
echo "❌ Error: This script must be run on the Proxmox host (pct command not found)"
|
||
|
|
echo ""
|
||
|
|
echo "Alternative: Run these commands manually:"
|
||
|
|
echo " ssh root@192.168.11.10"
|
||
|
|
echo " pct exec $VMID -- bash"
|
||
|
|
echo " su - postgres -c \"psql -d $DB_NAME\""
|
||
|
|
echo ""
|
||
|
|
exit 1
|
||
|
|
fi
|
||
|
|
|
||
|
|
# Check if container exists
|
||
|
|
if ! pct list | grep -q "^\s*$VMID\s"; then
|
||
|
|
echo "❌ Error: Container $VMID not found"
|
||
|
|
exit 1
|
||
|
|
fi
|
||
|
|
|
||
|
|
echo "Step 1: Granting database-level permissions..."
|
||
|
|
pct exec "$VMID" -- bash -c "su - postgres -c \"psql -d postgres << 'EOF'
|
||
|
|
GRANT CONNECT ON DATABASE $DB_NAME TO $DB_USER;
|
||
|
|
GRANT ALL PRIVILEGES ON DATABASE $DB_NAME TO $DB_USER;
|
||
|
|
ALTER USER $DB_USER CREATEDB;
|
||
|
|
EOF\""
|
||
|
|
|
||
|
|
if [ $? -ne 0 ]; then
|
||
|
|
echo "❌ Failed to grant database-level permissions"
|
||
|
|
exit 1
|
||
|
|
fi
|
||
|
|
|
||
|
|
echo "✅ Database-level permissions granted"
|
||
|
|
echo ""
|
||
|
|
|
||
|
|
echo "Step 2: Granting schema-level permissions..."
|
||
|
|
pct exec "$VMID" -- bash -c "su - postgres -c \"psql -d $DB_NAME << 'EOF'
|
||
|
|
GRANT ALL ON SCHEMA public TO $DB_USER;
|
||
|
|
GRANT CREATE ON SCHEMA public TO $DB_USER;
|
||
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO $DB_USER;
|
||
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO $DB_USER;
|
||
|
|
EOF\""
|
||
|
|
|
||
|
|
if [ $? -ne 0 ]; then
|
||
|
|
echo "❌ Failed to grant schema-level permissions"
|
||
|
|
exit 1
|
||
|
|
fi
|
||
|
|
|
||
|
|
echo "✅ Schema-level permissions granted"
|
||
|
|
echo ""
|
||
|
|
|
||
|
|
echo "Step 3: Verifying permissions..."
|
||
|
|
pct exec "$VMID" -- bash -c "su - postgres -c \"psql -d $DB_NAME -c 'SELECT current_user, current_database();'\"" > /dev/null 2>&1
|
||
|
|
|
||
|
|
if [ $? -eq 0 ]; then
|
||
|
|
echo "✅ Permissions verified - user $DB_USER can connect to $DB_NAME"
|
||
|
|
else
|
||
|
|
echo "⚠️ Verification had issues, but permissions may still be granted"
|
||
|
|
fi
|
||
|
|
|
||
|
|
echo ""
|
||
|
|
echo "=========================================="
|
||
|
|
echo "✅ Database permissions granted!"
|
||
|
|
echo "=========================================="
|
||
|
|
echo ""
|
||
|
|
echo "Next step: Run the migration from your local machine:"
|
||
|
|
echo " cd /home/intlc/projects/proxmox/dbis_core"
|
||
|
|
echo " ./scripts/run-chart-of-accounts-migration.sh"
|