What the difference between User, Login and role in postgresql?

Question

In my case I wanted to prevent table and view listing from db that has 2 schemas:

• public
• bi

And I wanted to prevent any table and view listing from public to a specific user therefore I consulted this answer: https://dba.stackexchange.com/a/177272/118215

But whist I was reading the answer, I noticed that ROLE is different than USER and login is a different "entity" from the ones above.

Despite being related I cannot understand the different between:

• Login
• ROLE
• USER

Therefore can someone explain to the the difference?

Answer 1

The only entity PostgreSQL has is a ROLE. The ROLE can own objects (tables, views) and be a "group role". But there is no such entities as LOGIN and USER. Other DBMS have "login", "user", "group", "role" as separate entities (Oracle, MSSQL, etc). But PostgreSQL is unique that way, it has only ROLE.

If ROLE has login attribute - it is called a LOGIN. Then a client (a human, or some automatic service) connects to database, the client supplies a name of such role. Any role can have a login attribute.

There are no USER entity. For convenience, the DBMS has two functions

create user name
drop user name

but they are just wrappers around

create role name login
drop role name

The easy way to think about it: A USER is just a ROLE which belongs to a single person. But this is just a convenience.

If you want several people have access to a table, the most convenient way to do it is to create a group role (GRANT group_role TO role1, role2, role3), and give group_role access to the table. Now when a person logins to the role1 and tries to access a table - the permission would be checked against role1 and group_role - if at least one of them has access - success.

Answer 2

Roles are like (for example) Administrator, Manager, Secretary, Developer. It is like a Business function. You can assign them permissions to different entities as required (such as being able to Select from certain tables only). Roles can also optionally log in.

Users can belong to a Role. More commonly, multiple users are assigned to a Role. This way you can control how things are accessed in a more structured fashion. Users can also log in.

See What is the difference between a user and a role?

And difference between roles and users