Create User and Give Access in postgreSQL

Steps to do:

=> adduser - UNIX/Linux adduser command to add a user to /etc/passwd file
=> psql => It is a terminal-based front-end to PostgreSQL.
=> CREATE USER - Adds a new user to a PostgreSQL database cluster.
=> CREATE DATABASE - create a new database
=> GRANT ALL PRIVILEGES - define access privileges

 

Add a user to PostgreSQL database

To create a normal user and an associated database you need to type the following commands. The easiest way to use is to create a Linux / UNUX IDENT authentication i.e. add user tom to UNIX or Linux system first.

Step # 1: Add a Linux/UNIX user called tom

Type the following commands to create a UNIX/Linux user called tom:
# adduser test
# passwd test


Step # 2: Become a superuser

You need to login as database super user under postgresql server. Again the simplest way to connect as the postgres user is to change to the postgres unix user on the database server using su command as follows:
# su - postgres

Step #3: Connect to database server

Type the following command
$ psql template1
OR
$ psql -d template1 -U postgres
Output:

Welcome to psql 7.4.16, the PostgreSQL interactive terminal.
Type:  \\copyright for distribution terms
       \\h for help with SQL commands
       \\? for help on internal slash commands
       \\g or terminate with semicolon to execute query
       \\q to quit
template1=# 

Step #4: Add a user called test

Type the following command to create a user called tom with a password called myPassword (you need to type command highlighted with red color):
template1=# CREATE USER test WITH PASSWORD 'test123';

Step #5: Add a database called company

Type the following command (you need to type command highlighted with red color):
template1=# CREATE DATABASE company;
Now grant all privileges on database
template1=# GRANT ALL PRIVILEGES ON DATABASE company to test;
Type \q to quit:
template1=# \q

Step #6: Test 'test' user login

$ su - test
$ psql -d company -U test

Output:

Welcome to psql 7.4.16, the PostgreSQL interactive terminal.
Type:  \\copyright for distribution terms
       \\h for help with SQL commands
       \\? for help on internal slash commands
       \\g or terminate with semicolon to execute query
       \\q to quit
company=> 

Step #7: Create Role to access login:

User test will be under role test. In order to login as test you need to

type following commands. Login as test or use su command:

company=> CREATE ROLE test LOGIN INHERIT;

company=> CREATE USER test; 

company=> CREATE ROLE admin NOINHERIT;
company=> GRANT admin TO test;

Role test has been created to login within the system and admin role is 

assigned tothe role test bythe third command. So all privileges of admin

will be copied to the role test.

Step #8: Checking User permission and database:

$ su - test 

$ psql company

** List all the databases for the user 'test' 

company=> \l

company=> \l
        List of databases
   Name    |  Owner   | Encoding 
-----------+----------+----------
 company   | test      | UTF8
 postgres  | postgres | UTF8
 template0 | postgres | UTF8
 template1 | postgres | UTF8
 (4 rows)

** List of tables for a specfic DB for example here 'company'

company=>SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

or

company=> \d
                   List of relations
 Schema |            Name            |   Type   | Owner 
--------+----------------------------+----------+-------
 public | alarmtab                   | table    | cmi
 public | audittab                   | table    | cmi
 public | batch_jobs                 | table    | cmi
 public | batch_jobs_id_seq          | sequence | cmi
 public | batch_transactions         | table    | cmi
 public | batch_transactions_id_seq  | sequence | cmi
 public | batch_transactions_reports | table    | cmi
 public | cmdclasstab                | table    | cmi
 public | cmdclasstab_id_seq         | sequence | cmi
 public | usertab                    | table    | cmi
 public | usertab_id_seq             | sequence | cmi
(11 rows)

** List roles and their permission and group member:

company=> \du

Role name |                   Attributes                   | Member of  
-----------+------------------------------------------------+------------
 test      |                                                | {admin}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 admin     |                                                | {}

Help:

1. Steps Need to Follow 

2. Role Realted Doc