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';


edb=# exit

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

Password for user tuser:

psql.bin (10.11.19)

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 .

29 views0 comments

Recent Posts

See All

This is the step by step documents to install and configure Enterprisedb Failover Manager (EFM) 4.2 in RHEL 8 #EDB #EFM #postgrsql 1- Prerequisite : - EDB v12 is installed in the primary and master s