Code: Select all
apt update
apt -y install postgresql postgresql-contrib
apt -y install php8.2-pgsql
# Create testuser and testdb with testpass
cd /
sudo -u postgres psql <<'EOF'
CREATE USER testuser WITH PASSWORD 'testpass';
CREATE DATABASE testdb OWNER testuser;
GRANT ALL PRIVILEGES ON DATABASE testdb TO testuser;
EOF
# Test connection
psql -U testuser -d testdb -h localhost
# you will be prompted for a password
# Access database without password
sudo -u postgres psql -d testdb
# Create user
sudo -u postgres psql -c "CREATE USER testuser WITH PASSWORD 'testpass';"
# Create DB
sudo -u postgres createdb testdb
# Create a DB that has an owner
sudo -u postgres createdb testdb -O testuser
# Grant
sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON DATABASE testdb TO testuser;"
# Dump:
sudo -u postgres pg_dump testdb > testdb.sql
# Restore
sudo -u postgres psql -d testdb < testdb.sql
# Drop user
sudo -u postgres psql -c "DROP OWNED BY testuser;"
sudo -u postgres psql -c "REASSIGN OWNED BY testuser TO postgres;"
sudo -u postgres psql -c "DROP USER testuser;"
# Drop database
sudo -u postgres psql -c "DROP DATABASE testdb;"
# List all databases
sudo -u postgres psql -l