Creating postgresql Database and user in a nutshell.

I will not write much but just the code required to create a user and let the user access the db.

Create User
CREATE USER tom WITH PASSWORD 'myPassword';

Create a DB
CREATE DATABASE jerry;

Grant Privileges to user on the DB
GRANT ALL PRIVILEGES ON DATABASE jerry to tom;

Grant Privileges to table

GRANT ALL PRIVILEGES ON TABLE side_adzone TO tom;

Grant Privileges to user for copying files (especially import csv to DB)

GRANT pg_read_server_files TO tom;

Change ownership of the DB
ALTER DATABASE jerry OWNER TO tom;

Delete user
DROP USER tom;

Update user password

ALTER USER user_name WITH PASSWORD 'new_password';

Drop DB
DROP DATABASE jerry;

Backup and Restore databases

To take a backup use this command. The following command works only with postgres user so first change the user to postgres su postgres and then dump. make sure the directory is writable by postgres user.
pg_dump db_name > db_name.sql

To restore run the command as postgres user
psql db_name < db_name.sql

Shell script to create a user, database, grant privileges and change ownership in one go.

#!/bin/bash

# PostgreSQL Database Setup Script
# This script creates a user, database, and grants privileges

# Exit on any error
set -e

# Database connection parameters (modify as needed)
POSTGRES_HOST=${POSTGRES_HOST:-localhost}
POSTGRES_PORT=${POSTGRES_PORT:-5432}
POSTGRES_ADMIN_USER=${POSTGRES_ADMIN_USER:-postgres}

# New user and database details
DB_USER="tom"
DB_PASSWORD="myPassword"
DB_NAME="jerry"

echo "Setting up PostgreSQL database and user..."

# Create user and database
psql -h "$POSTGRES_HOST" -p "$POSTGRES_PORT" -U "$POSTGRES_ADMIN_USER" -c "
-- Create user
CREATE USER $DB_USER WITH PASSWORD '$DB_PASSWORD';

-- Create database
CREATE DATABASE $DB_NAME;

-- Grant all privileges on database to user
GRANT ALL PRIVILEGES ON DATABASE $DB_NAME TO $DB_USER;

-- Change database owner to the user
ALTER DATABASE $DB_NAME OWNER TO $DB_USER;
"

echo "Database setup completed successfully!"
echo "User: $DB_USER"
echo "Database: $DB_NAME"
echo "Connection string: postgresql://$DB_USER:$DB_PASSWORD@$POSTGRES_HOST:$POSTGRES_PORT/$DB_NAME"%