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 .

role:

edb=# create role trole WITH PASSWORD 'role123' VALID UNTIL 'infinity';

CREATE ROLE

edb=# grant connect on database postgres to trole ;

GRANT

-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:

User:

edb=# CREATE user tuser WITH PASSWORD 'user123' VALID UNTIL 'infinity';

CREATE ROLE

edb=# exit

-bash-4.2$ psql -U tuser -d postgres

Password for user tuser:

psql.bin (10.11.19)

Type "help" for help.

postgres=>

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 .

Recent Posts