How to Create a Role in PostgreSQL Database Server

Posted on

This is specifically written for describing on how to do the activity in the title of this article. It is how to create a role in PostgreSQL Database Server. In order to create a role in PostgreSQL Database Server, first of all there are several things need to be taken care of which is enlisted in the following entry :

1. The first step is to ensure that PostgreSQL Database Server intended to be accessed is available. This is done by various kind of methods including scanning the target of the connection. Basically, PostgreSQL Database Server is listening at port 5432 as the default port of PostgreSQL Database Server.

2. The second step is to ensure that PostgreSQL Database Server can be connected. This is actually related with the 1st entry, if the PostgreSQL Database Server is available, it must be accessed for further operation.

3. In the context of this article, the login process to the PostgreSQL Database Server can be executed as shown in the following output generated :

user@hostname:~$ psql -Upostgres
Password for user postgres:
psql (9.5.9, server 9.3.10)
Type "help" for help.

postgres=#

3. Check the current 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=#

Another way to select the available role is for executing the following command without having to enlist the specific role given to that role :

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

postgres=#

4. Execute the command for creating a new role as shown below :

To be able to execute it, the pattern for creating a new role is shown as follows :

create role role_name

The example is shown as follows :

postgres=# create role view_general;
CREATE ROLE
postgres=#

After adding a role, check back the available role 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 | {}  
 view_general| Cannot Login                                   | {}
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
postgres=# 

And the following is the available role list specified after adding a new role :

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

postgres=#

Leave a Reply