dbis_core/GRANT_PERMISSIONS_AND_MIGRATE.md

Grant Database Permissions and Run Migration

Quick Steps

Option 1: Automated Script (Recommended)

cd /home/intlc/projects/proxmox/dbis_core
./scripts/grant-database-permissions.sh
./scripts/run-chart-of-accounts-migration.sh

Option 2: Manual Steps

Step 1: Grant Permissions

# SSH to Proxmox host
ssh root@192.168.11.10

# Enter database container
pct exec 10100 -- bash

# Switch to postgres user and run SQL
su - postgres -c "psql -d dbis_core << 'EOF'
GRANT CONNECT ON DATABASE dbis_core TO dbis;
GRANT ALL PRIVILEGES ON DATABASE dbis_core TO dbis;
ALTER USER dbis CREATEDB;
\c dbis_core
GRANT ALL ON SCHEMA public TO dbis;
GRANT CREATE ON SCHEMA public TO dbis;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO dbis;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO dbis;
EOF"

Step 2: Run Migration

# From your local machine
cd /home/intlc/projects/proxmox/dbis_core
./scripts/run-chart-of-accounts-migration.sh

What These Commands Do

1. GRANT CONNECT - Allows dbis user to connect to the database
2. GRANT ALL PRIVILEGES - Grants all database-level privileges
3. ALTER USER ... CREATEDB - Allows user to create databases (for migrations)
4. GRANT ALL ON SCHEMA public - Full access to public schema
5. GRANT CREATE ON SCHEMA public - Can create objects in schema
6. ALTER DEFAULT PRIVILEGES - Sets default permissions for future tables/sequences

Verification

After granting permissions, verify:

# Test connection
psql "postgresql://dbis:8cba649443f97436db43b34ab2c0e75b5cf15611bef9c099cee6fb22cc3d7771@192.168.11.105:5432/dbis_core" -c "SELECT current_user, current_database();"

Should return:

 current_user | current_database 
--------------+------------------
 dbis         | dbis_core

---

Ready to grant permissions and run migration!