How to Add Role Attribute for User in PostgreSQL Database

Add Role for a User in PostgreSQL Database

This article is focusing on how to add role attribute for the user exist in the PostgreSQL database. Basically, there are several role attributes in PostgreSQL database. Those roles are Superuser, Create role, Create DB, Replication among all of them. In order to add role to the user, the following are the steps to achieve it :

1. First of all, access and logging in to the PostgreSQL command console :

   [root@host ~]# psql -Upostgres -d db_apps
   psql (11.10)
   Type "help" for help.
   db_apps=# 
   

2. Following after, just execute the command for listing available users in the PostgreSQL database :

   db_apps=# \du
                                       List of roles
     Role name  |                         Attributes                         | Member of
   -------------+------------------------------------------------------------+-----------
    admin       | Superuser                                                  | {}
    user        | Cannot login                                               | {}
    postgres    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
    dbsys       | Create DB                                                  | {}
   

3. So, in order to add a new role attribute, just execute the following command. It is a command to add another role attribute to the user. The role attribute is ‘superuser’ to the user with the name of ‘dbsys’. So, the pattern exist as follows :

   alter user user_name with role_attribute_name

   The query or the command according to the pattern above exist below :

   db_apps=# alter user dbsys with superuser;
   ALTER ROLE
   db_apps=#
   

4. After adding the role, execute the previous command for listing users with their associated role attributes. It exist as follows :

   db_apps=# \du
                                       List of roles
     Role name  |                         Attributes                         | Member of
   -------------+------------------------------------------------------------+-----------
    admin       | Superuser                                                  | {}
    user        | Cannot login                                               | {}
    postgres    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
    dbsys       | Superuser, Create DB                                       | {}
   db_apps=#
   

   As in the above output command, the role attribute of user ‘dbsys’ has increased. Previously, it is only a ‘Create DB’ role attribute. Currently, it has a new one with ‘Superuser’ role attribute.