Create Database, Add User and Grant Privileges

In this article we will learn how to Create Database, Add User and Grant Privileges to the User

This tutorial is taken from the following link. Many thanks to the one who created it.

https://medium.com/coding-blocks/creating-user-database-and-adding-access-on-postgresql-8bfcd2f4a91e

Prerequisite Knowledge

To understand this upgrade guide you dont need to be an expert but having some basic knowledge of linux terminal commands and postgres Database will help you.

Steps for Creating Database, Add User and grant privileges

Step 1 : Creating user
Step 2 : Creating Database
Step 3 : Giving the user a password
Step 4 : Granting privileges on database

We also have an alternate option : Doing purely via psql

Alternate Option : Doing purely via psql

Let us begin the step by step guide to create database, add user and grant privileges

One nice thing about PGSQL is it comes with some utility binaries like createuser and createdb. So we will be making use of that. As the default configuration of Postgres is, a user called postgres is made on and the user postgres has full superadmin access to entire PostgreSQL instance running on your OS.

                     Code
                  
    $ sudo -u postgres psql
                    
                  

The above command gets you the psql command line interface in full admin mode.

In the following commands, keep in mind the < angular brackets > are to denote variables you have to set yourself. In the actual command, omit the <>

Step 1 : Creating user

                     Code
                  
    $ sudo -u postgres createuser <username>
                    
                  

Step 2 : Creating Database

                     Code
                  
    $ sudo -u postgres createdb <dbname>
                    
                  

Step 3 : Giving the user a password

                     Code
                  
    $ sudo -u postgres psql
    psql=# alter user <username> with encrypted password '<password>';
                    
                  

Step 4 : Granting privileges on database

                     Code
                  
    psql=# grant all privileges on database <dbname> to <username> ;
                    
                  

And yeah, that should be pretty much it !

Alternate Option : Doing purely via psql

Your OS might not have the createuser or createdb binaries, or you may, for some reason want to do it purely via psql, then these are the three magic commands —

                     Code
                  
    CREATE DATABASE yourdbname;
    CREATE USER youruser WITH ENCRYPTED PASSWORD 'yourpass';
    GRANT ALL PRIVILEGES ON DATABASE yourdbname TO youruser;
                    
                  

Links and Resources

https://medium.com/coding-blocks/creating-user-database-and-adding-access-on-postgresql-8bfcd2f4a91e