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)

Errors:
when i trying to connect the newly created user am getting following errors

[postgres@slave ~]$ psql -U u2
psql: FATAL:  Peer authentication failed for user "u2"

Solution
1.Then i found that you need to “trust” local users in pg_hba.conf,this file is said to be the PostgreSQL Client Authentication Configuration File.

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            ident
# IPv6 local connections:
host    all             all             ::1/128                 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            ident
host    replication     all             ::1/128                 ident

2.then i comment the present local type and added a new line to access database for all local users, if you change the peer connection means postgres user cannot connect directly  to postgres server without password prompt so before changing peer connection change the postgres password using ALTER command

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
#local   all             all                                     peer
 local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            ident
# IPv6 local connections:
host    all             all             ::1/128                 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            ident

3.Then restarted the postgresql server

/usr/pgsql-10/bin/pg_ctl -D /var/lib/pgsql/10/data restart

4. connect the postgresql server

[postgres@slave data]$ psql -U u2 -d postgres
Password for user u2:
psql (10.4)
Type "help" for help.

postgres=#

Note:The peer authentication method works by obtaining the client's operating system user name from the kernel and using it as the allowed database user name (with optional user name mapping). This method is only supported on local connections.