PostgreSQL User Creation Using Utility

• createuser creates a new PostgreSQL user. Only superusers (users with usesuper set in the pg_shadow table) can create new PostgreSQL users, so createuser must be invoked by someone who can connect as a PostgreSQL superuser.
• Being a superuser also implies the ability to bypass access permission checks within the database, so superuserdom should not be granted lightly.

SYNTAX:

createuser [option...] [username]

OPTION	EXPLANATION
username	name of the PostgreSQL user
-a --adduser	The new user is allowed to create other users. (Note: Actually, this makes the new user a superuser. The option is poorly named.)
-A --no-adduser	The new user is not allowed to create other users (i.e., the new user is a regular user, not a superuser). This is the default.
-d --createdb	The new user is allowed to create databases.
-D --no-createdb	The new user is not allowed to create databases. This is the default.
-e --echo	Echo the commands that createuser generates and sends to the server.
-E --encrypted	Encrypts the user's password stored in the database. If not specified, the default password behavior is used. (ENCRYPTED-means password stored as encrypted model so you can’t see actual password but  you can see some encrypted value like “md5df2c887bcb2c49b903aa33bdbc5c2984”
-i number --sysid number	Allows you to pick a non-default user ID for the new user. This is not necessary, but some people like it.
-N --unencrypted	Does not encrypt the user's password stored in the database. If not specified, the default password behavior is used. (you can see password using PG-SHADOW VIEWS
-P --pwprompt (caps letter P)	If given, createuser will issue a prompt for the password of the new user. This is not necessary if you do not plan on using password authentication.
-q --quiet	Do not display a response.
-h host --host host	Specifies the host name of the machine on which the server is running. If the value begins with a slash, it is used as the directory for the Unix domain socket.
-p port --port port	Specifies the TCP port or local Unix domain socket file extension on which the server is listening for connections.
-U username --username username	User name to connect as (not the user name to create).
-W --password	Force password prompt (to connect to the server, not for the password of the new user).

WHEN you create user on command line mode The database server must be running at the targeted host

--create a u5 user on command mode

[root@p1 bin]# cd /opt/PostgreSQL/9.3/bin/
[root@p1 bin]# ./createuser u5
Password: 

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
 u5       |                                     |                        | f
(8 rows)

----To create the  user u7 using the server on host eden, port 5432, avoiding the prompts and taking a look at the underlying command:

Note:when u create the user it will some hostname and pg_hba.conf error i gave solution for below this link

-bash-3.2$ hostname
p1

-bash-3.2$ ./createuser -h p1 -p 5432 -D -A -e u7
Password: 
CREATE ROLE u7 NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;

--Check the user whether created or not

postgres=# \du 
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 nijam     |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 rep       | Replication                                   +| {}
           | 1 connection                                   | 
 u1        |                                                | {}
 u2        |                                                | {}
 u3        | Password valid until 2017-06-06 00:00:00-04    | {}
 u4        | Create DB                                      | {}
 u5        |                                                | {}
 u6        |                                                | {}
 u7        |                                                | {}
                                         

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
 u5       |                                     |                        | f
 u6       |                                     |                        | f
 u7       |                                     |                        | f
(10 rows)

--To create the user joe as a superuser, and assign a password immediately:

-bash-3.2$ ./createuser -P -d -a -e u8
Enter password for new role: 
Enter it again: 
Password: 
CREATE ROLE u8 PASSWORD 'md5b9f930ae0484417a1883fd3f7cdb490e' SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;

Note:Here first asking password for new user second is confirmation third is super user password

--let us check the new user roles

postgres=# \du                                                       
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 nijam     |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 rep       | Replication                                   +| {}
           | 1 connection                                   | 
 u1        |                                                | {}
 u2        |                                                | {}
 u3        | Password valid until 2017-06-06 00:00:00-04    | {}
 u4        | Create DB                                      | {}
 u5        |                                                | {}
 u6        |                                                | {}
 u7        |                                                | {}
 u8        | Superuser, Create role, Create DB              | {}