Create new Role and Database Postgresql

This is my snippet on how I started every project using postgresql

First thing first, make sure you already have postgresql client on your machine. Check with psql --version.

TL;DR.

postgres=> CREATE ROLE rolename with LOGIN ENCRYPTED PASSWORD 'password';
postgres=> CREATE DATABASE dbname;
postgres=> GRANT ALL PRIVILEGES ON DATABASE dbname TO rolename;

1. If the psql command is not found, install using these command

• MacOS with brew

brew install libpq
brew link --force libpq

• Ubuntu

sudo apt install postgresql-client

2. Login to remote database using this command and input the master password

psql -h dbhost.com -U postgres
Password for user postgres:

3. Create new role with password

postgres=> CREATE ROLE rolename with LOGIN ENCRYPTED PASSWORD 'password';

4. To check the user/role list, use this command

postgres=> \du

5. Create new database

postgres=> CREATE DATABASE dbname;

6. Grant all privilege to the new created user so the new user will be able to create schema, table, etc.

postgres=> GRANT ALL PRIVILEGES ON DATABASE dbname TO rolename;

That’s it, now you can try to test the new user and database to do the operations.