Postgres:Connect on Database

Question

Can someone help me why i am not able to login with user, when grant connct on database is assigned to role and then to user

pega=# CREATE ROLE admin_role LOGIN PASSWORD 'admin_password'; CREATE ROLE pega=# grant connect on database pega to admin_role; GRANT pega=# CREATE USER admin_user WITH ROLE admin_role; CREATE ROLE pega=# postgres@oracle-VirtualBox:~$ psql -d pega -U admin_user Password for user admin_user: psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: permission denied for database "pega" DETAIL: User does not have CONNECT privilege. postgres@oracle-VirtualBox:~$

---Where as the Grant connect on database to user works fine, but when the same grant issued to role and role to user ,user not able to login the database. postgres=# grant connect on database pega to admin_user; GRANT postgres=# \q postgres@oracle-VirtualBox:~$ psql -d pega -U admin_user Password for user admin_user: psql (16.1 (Ubuntu 16.1-1.pgdg20.04+1)) Type "help" for help. pega=>

Answer 1

Below worked , thanks.

pega=# CREATE ROLE admin_role;
CREATE ROLE
pega=# grant connect on database pega to admin_role;
GRANT
pega=#create user admin_user WITH PASSWORD 'admin_password';
CREATE ROLE
pega=#

Then from bash:

postgres@oracle-VirtualBox:~$ psql -d pega -U admin_user
Password for user admin_user:
psql (16.1 (Ubuntu 16.1-1.pgdg20.04+1))
Type "help" for help.