Creating user, database and adding access on PostgreSQL (and automate the process)

If you’re a software developer, Postgresql will be the go-to database for most of the developers nowadays. if anyone wondering why so then head over to this comparison https://www.2ndquadrant.com/en/postgresql/postgresql-vs-mysql/

In This Database How-to guide, we will learn, how to create db user, database and grant access to the user for the database created using psql cli too and we’ll also write a bash script to automate the whole process.

Create User, Database and grant access to user

Postgresql comes with nice tools support, like psql, createdb, createuser etc.when you install Postgres on your machine it creates a user called Postgres with admin access to Postgres database.

we will start by switching the current user to postgres user

sudo su - postgres

after switching to postgres user, we can use commands like createdb and createuser. we will first create a user and then database using these commands.

# creates a new user
createuser my_pg_user

# creates a new database
createdb my_awesome_db

Now we will use psql command to give password to user. and granting privileges on the database we created.

# replace my_pg_user and hard_password
psql -c "alter user my_pg_user with encrypted password 'hard_password';"

# granting privileges
psql -c "grant all privileges on database my_awesome_db to  my_pg_user;"

Now, It’s time to test if all this steps worked correctly or not.

# replace dbname and db user
psql --host=localhost --dbname=my_awesome_db --username=my_pg_user

it will ask for password and boom, you will be taken to psql interactive shell.

Create Bash Script to automate process to setup new postgres DB

#!/bin/bash

set -e

DB_NAME=${1:-my_pg_user}
DB_USER=${2:-my_awesome_db}
DB_USER_PASS=${3:-hard_password}

sudo su postgres <<EOF
createdb  $DB_NAME;
psql -c "CREATE USER $DB_USER WITH PASSWORD '$DB_USER_PASS';"
psql -c "grant all privileges on database $DB_NAME to $DB_USER;"
echo "Postgres User '$DB_USER' and database '$DB_NAME' created."
EOF

In the above script, you can pass user, database name, password as shell file arguments or you can also set default options.

I hope this tutorial helped you to fasten your development process, if you have any queries or suggestions, please leave comment below or tag me on twitter.