How to Create User in PostgreSQL Database Server

This is an article where the main content is focusing to show how to create a new user in PostgreSQL Database Server. In order to create a new user, there are several things that it must be exist and available as in the following list :

1. PostgreSQL Database Server must exist and available for further access. Just read the article for installing PostgreSQL Database Server in order to prepare for it. Furthermore, make sure that PostgreSQL Database Server is listening in any available port. By default the port number 5432 is the default port of PostgreSQL Database Server.

2. After checking the existance of PostgreSQL Database Server, don’t forget to ensure that PostgreSQL Database Server is accessible. Just execute the following command output to be able to access the PostgreSQL Database Server :

user@hostname:~$ psql -Upostgres
psql (12.2)
Type "help" for help.

postgres=# 

3. Before creating a new user, it is better to look at the available role in the PostgreSQL Database Server by executing the command as follows :

postgres=# \du
                              List of roles
  Role name  |                   Attributes                   | Member of 
-------------+------------------------------------------------+-----------                                                                                                                                                              
 postgres    | Superuser, Create role, Create DB, Replication | {}                                                                                                                                                                
                                                                                                                                                                 
                                                                                                                                                                                                                                  
postgres=#

The following command is also another way to seek for the the available role :

postgres=# select rolname from pg_roles;
rolname
--------------
postgres
(1 rows)

postgres=#

4. Since according to the above output command, the user ‘postgres’ is a user with a ‘Superuser’ role, it has the ability to create a new user. So, execute the command to create a new user with the pattern as shown below :

create user user_name

The example is shown as follows :

postgres=# create user myuser;
CREATE USER
postgres=#

After creating a new user, just execute the following command to show all the available user in PostgreSQL Database Server by executing the command ‘\du’ in PostgreSQL Command Console as shown below :

postgres=# \du
                              List of roles
  Role name  |                   Attributes                   | Member of 
-------------+------------------------------------------------+-----------                                                                                                                                                              
 postgres    | Superuser, Create role, Create DB, Replication | {}  
 myuser      |                                                | {} 
 view_general| Cannot Login                                   | {}
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
postgres=# 

5. Finally, try to login to the PostgreSQL Command Console using the user created before as follows :

user@hostname:~$ psql -Umyuser -d postgres
psql (12.2)
Type "help" for help.

postgres=>