PostgreSQL User Creation

• CREATE USER adds a new user to a PostgreSQL database cluster
• when the user create a table, those table belong to the PUBLIC schema,Database can be split up between multiple users using schemas.

SYNTAX:

CREATE USER name [ [ WITH ] option [ ... ] ];

where option can be:   
      SYSID uid 
    | CREATEDB | NOCREATEDB
    | CREATEUSER | NOCREATEUSER
    | IN GROUP groupname [, ...]
    | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
    | VALID UNTIL 'abstime' 

Explanation:

PARAMETERS	EXPLANATION
name	The name of the new user
uid	user ID of the new user
CREATEDB| NOCREATEDB	If CREATEDB is specified, the user being defined will be allowed to create his own databases. Using NOCREATEDB will deny a user the ability to create databases. If not specified, NOCREATEDB is the default.
CREATEUSER| NOCREATEUSER	user will be permitted to create new users himself. If not specified, NOCREATEUSER is the default.
groupname	A name of an existing group into which to insert the user as a new member
password	Sets the user's password
ENCRYPTED UNENCRYPTED	system cannot decrypt the specified encrypted password string  DEFAULT is ENCRYPTED (ENCRYPTED-means password stored as encrypted model so you can’t see actual password but  you can see encrypted password like "md505ea766c2bc9e19f34b66114ace97598" UNENCRYPTED means you can see actual password in PG_SHADOW & PG_AUTHID Views)
abstime	VALID UNTIL Password validation time. If this clause is omitted the password will be valid for all time.

Database User Creation:

--create simple user

postgres=# CREATE USER nijam;   
CREATE ROLE

--create the unencrypted user 

postgres=# create user u2 WITH UNENCRYPTED PASSWORD 'u2';
CREATE ROLE

--create the user with password validation time

postgres=# CREATE USER u3 WITH PASSWORD 'u3' VALID UNTIL '2017-06-06';
CREATE ROLE

--Here createdb privillege having postgres(true values) user only so just createdb privilege u4 user

postgres=# select usename,passwd,valuntil,usecreatedb from PG_SHADOW;
 usename  |               passwd                |        valuntil        | usecreatedb 
----------+-------------------------------------+------------------------+-------------
 postgres | md505ea766c2bc9e19f34b66114ace97598 |                        | t
 rep      | md5df2c887bcb2c49b903aa33bdbc5c2984 |                        | f
 u1       |                                     |                        | f
 u2       | u2                                  |                        | f
 u3       | md5dad1ef51b879799793dc38d714b97063 | 2017-06-06 00:00:00-04 | f
 nijam    |                                     |                        | f
(6 rows)

--create u4 user

postgres=# CREATE user u4 WITH PASSWORD 'u4' CREATEDB;
CREATE ROLE

--then ckeck the privileges

postgres=# select usename,passwd,valuntil,usecreatedb from PG_SHADOW;
 usename  |               passwd                |        valuntil        | usecreatedb 
----------+-------------------------------------+------------------------+-------------
 postgres | md505ea766c2bc9e19f34b66114ace97598 |                        | t
 rep      | md5df2c887bcb2c49b903aa33bdbc5c2984 |                        | f
 u1       |                                     |                        | f
 u2       | u2                                  |                        | f
 u3       | md5dad1ef51b879799793dc38d714b97063 | 2017-06-06 00:00:00-04 | f
 nijam    |                                     |                        | f
 u4       | md54af10c3137cf79c12265e8d288070711 |                        | t
(7 rows)