#17 Users vs Roles in POSTGRES.

Hello and good day, today's article is about users and roles. In postgres world user and role are the most used entity for logins. Now what is the difference between them.

Theoretically Role is used for grouping the users to a specific function(s)\group(s)\permissions and users are used for getting into the system or login to the database system.

In practical usage and a more appropriate scheme would be to use the roles to define or put certain restrictions at usage level and then assign the users to those. Let's take an example of an organization which uses and application for employee records. Now we want the HR department to have all the access to read, delete and modify records, Managers can read and modify but cannot delete any records and team members can only read but not modify or delete any records.

If you have an organisation which is only about 10 to 15 people its manageable to just create those users and provide access but if you have thousands of people working in your organization with different designations(which is the most practical way it is in almost all the companies), levels of access etc., then it is going to be very problematic to administer or manage them considering there will be employees who will be joining & leaving the company.

So this where you create roles in the DB and just assign the users to that role and all the permissions are taken care of. There is a very interesting bit on auditing which I will cover in another article as its off of the scope of this article(pg_permissions).

Now coming back to users and roles, you will create Roles such as HR, Managers, Team_members and GRANT them specific permissions as specified earlier Read/Update/delete for HR, Read/Update for Managers and ReadOnly for Team_members. So whenever you have a new employee and once their designation is identified they can be mapped to the right group for the respective Roles.

Now, you can actually use the role and make it act like a 'user' as well and you can't tell difference and vice versa. Now let's take a more practical approach and see it in action.

How does it look like when you create a user ?

postgres=# create user tom;
CREATE ROLE

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+
 tom       |                                                            | {}

postgres=# create role HR;
CREATE ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+--
 hr        | Cannot login                                               |
 tom       |                                                            | {}

Now when you look closely you will see even if you send a command to postgres for user creation it doesn't treat it differently and sent an output saying CREATE ROLE done. Also, if you see the main and the ONLY major difference, by looking at the list of users and roles, is the GRANT for login to the server. ROLES cannot login to the server by DEFAULT and USERS can.

See the screenshot#2 where hr 'cannot login'. We should not change the properties of not being to login for roles if you are using them to segregate and identify specific groups. It's almost similar to the grouping that happens in the objects in active directory(windows domain & active directory structures).

Postgres gives the flexibility & feasibility of using users\roles in different ways but the core usage of roles is for grouping & users is to gain access or login to the systems. I will be covering permissions and access in another article until then have a good day and happy learning!!

Please feel free to leave your comments below so that I can improve what needs to be improved. Thanks again for coming and reading the article.