What is the theory behind ROLE & USER creation in PostgreSQL .....
Some differentiation between user & role
1- CREATE USER is an alias for Create ROLE .
2- A role is an entity that can own database objects and have database privileges. Role stands for both user & group ,based on the way we use it .
3-With CREATE USER, LOGIN is assumed by default, whereas with CREATE ROLE NOLOGIN is default .
edb=# create role trole WITH PASSWORD 'role123' VALID UNTIL 'infinity';
edb=# grant connect on database postgres to trole ;
-bash-4.2$ psql -U trole -d postgres
Password for user trole:
psql.bin: FATAL: role "trole" is not permitted to log in
As we can see ,for a role we didn't define the login parameter ,so even if we grant connect
the role couldn't connect to the db .
Now see how a create user will work:
edb=# CREATE user tuser WITH PASSWORD 'user123' VALID UNTIL 'infinity';
-bash-4.2$ psql -U tuser -d postgres
Password for user tuser:
Type "help" for help.
So even if we didn't grant any privilege ,user created with CREATE USER could login with out any issues .
4-When we create an user ,we can login to all database by default in the postgre server .
So to restrict the connectivity to a particular database ,we have to revoke the privileges from the user on the other db .