How to create a PostgreSQL database and user with full access using psql and shell script

PostgreSQL is a powerful, open-source relational database system. In this guide, we will demonstrate how to create a PostgreSQL database and a user with full access using both the PostgreSQL interactive terminal (psql) and an automated shell script.

1. Creating a PostgreSQL Database and User via psql

Follow these steps to create a database and a user manually using psql.

Step 1: Access PostgreSQL

First, log in to the PostgreSQL interactive terminal as the postgres user:

sudo -u postgres psql

Step 2: Create a New Database

Run the following command to create a new database:

CREATE DATABASE mydatabase;

If you want to setup timezone, use below command

ALTER DATABASE mydatabase SET timezone TO 'Asia/Kolkata';

Step 3: Create a New User

Create a new PostgreSQL user and set a password:

CREATE USER myuser WITH ENCRYPTED PASSWORD 'mypassword';

Step 4: Grant All Privileges to the User

Grant all privileges on the new database to the new user:

GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser;

Step 5: Set Default Time Zone

To set the default time zone for the user, run:

ALTER ROLE myuser SET timezone = 'Asia/Kolkata';

Step 6: Exit psql

Exit the PostgreSQL terminal:

\q

Now, the user myuser has full access to the mydatabase database, and the default time zone is set to Asia/Kolkata.

2. Automating the Process Using a Shell Script

To automate the above steps, create a shell script that executes the required SQL commands.

Step 1: Create the Shell Script

Create a new shell script file, e.g., create_db_user.sh:

touch create_db_user.sh
chmod +x create_db_user.sh
nano create_db_user.sh

Step 2: Add the Following Script Content

#!/bin/bash

DB_NAME="mydatabase"
DB_USER="myuser"
DB_PASS="mypassword"
TIMEZONE="Asia/Kolkata"

# Run PostgreSQL commands as the postgres user
sudo -u postgres psql <<EOF
CREATE DATABASE $DB_NAME;
CREATE USER $DB_USER WITH ENCRYPTED PASSWORD '$DB_PASS';
GRANT ALL PRIVILEGES ON DATABASE $DB_NAME TO $DB_USER;
ALTER ROLE $DB_USER SET timezone = '$TIMEZONE';
ALTER DATABASE $DB_NAME SET timezone = '$TIMEZONE';
EOF

echo "Database, user, and timezone setup successfully."

Step 3: Execute the Shell Script

Run the script using:

./create_db_user.sh

This script will create the PostgreSQL database and user with full privileges automatically and set the default time zone.