What is the different between user and role in PostgreSQL Database Server

By Posted on

This article is going to highlight the topic or subject which is stated in the title of this article. It is written for explaining the different between user and role in PostgreSQL Database Server. The article itself probably will be updated in such a way if there are several additional highlights arise which is not included in this article. Below is the differentiationi between role and user :

1. The create process

The first difference which is tested on PostgreSQL Database Server 9.5.9 is on the create process of each of it. Basically, it depends on the syntax or query executed to create each of it. First of all, in the case of role, it has the pattern for creating a role can be shown in the following : 

CREATE ROLE role_name

The above pattern can be executed as follows : 

postgres=# create role myrole with password 'myrolepassword';
CREATE ROLE
postgres=#

And in the case of user :

CREATE USER user_name 

postgres=# create user myuser with password 'myuserpassword';
CREATE ROLE
postgres=#

2. The default privilege given after the create process. If a user which is created, that created user have a login privilege but the created role doesn’t have any privilege to login to the PostgreSQL Database Server. It can be shown in the following command executed : 

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

Based on the above command executed to list all roles or users with their privileges or attributes respectively, it can be realized that that a created user doesn’t have any attribute but on the other hand, the created role has an additional information on the ‘List of roles Attributes ‘. The information is ‘Cannot login’ which is made logging in using role is impossible. The following is the prove of it : 

user@hostname:~$ psql -Umyrole dbtest
Password for user myrole:
psql: FATAL: role "myrole" is not permitted to log in
user@hostname:~$

On the other hand, using the user to get in and login to PostgreSQL Database Server can be done as shown below : 

user@hostname:~$ psql -Umyuser dbtest
Password for user myuser:
psql (9.5.9, server 9.3.10)
Type "help" for help.
dbtest=>




		

		
	





	

		

							
Leave a Reply