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 .




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