How to Create Role in PostgreSQL with Password

This is another article which consists of description about how to perform one the administration task. Normally, in every kind of database, there is an administration task for creating a role or a user. After creating the role or the user, it is useful for connecting to the database itself. Off course, if the role or the user is available and it exist, using the correct password, the connection to the database is possible. So, in the first place, just create the role or the user in order to connect to the database.

It is no different with PostgreSQL. Since it is also a type of database, it is possible to create a role or a user as one of its administration task. Without further explanation, below is the step for creating role or user in PostgreSQL :

1. Make sure to connect to the PostgreSQL database using the admin account. Normally, the admin account is ‘postgres’. The following is the command for connecting to the PostgreSQL database using the admin account :

user@hostname:~$ psql -Upostgres postgres
psql (10.5)
Type "help" for help.
postgres=> 

2. After connecting to the database using the admin account, execute the following query to create a new role or a new user :

postgres=# CREATE role test WITH LOGIN ENCRYPTED PASSWORD 'password';
CREATE ROLE
postgres=# \q
user@hostname:~$ 

3. Check the user by executing the following command or query. It is useful to list all the available user in the PostgreSQL database. The following is the execution of the command :

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

As shown in the above output of the command, there is a new user or role named ‘test’. The creation process of the role or the username is a success.

4. Finally, this is the last step. After creating the role or the user, test it to connect to the PostgreSQL database as follows :

user@hostname:~$ psql -Utest postgres 
psql (10.5)
Type "help" for help.
postgres=> \q
user@hostname:~$