I have to create a rol with the only privilege of CREATE SESSION, i have already created but when i give the role to the user I can not connnect to de DB because the user lacks of CREATE SESSION privilege.

This is what I tried:

But at the end when I try to connect with the user alvaro_rol I recive this message: and it was supossed to connect to the DB because I create de role, I grant the CONNECT privilege to the role, i give the role to the user alvaro_rol and finaly I activate the role with the comand set role and I don know what to do next.

Thanks in advance!

CodePudding user response：

You will need to give the user the session privilege, either directly or through a non-password-protected role. The security guide says (emphasis added):

You can protect a role authorized by the database by assigning the role a password. If a user is granted a role protected by a password, then you can enable or disable the role by supplying the proper password for the role in the SET ROLE statement. You cannot authenticate a password-authenticated role on logon, even if you add it to the list of default roles. You must explicitly enable it with the SET ROLE statement using the required password.

Since you can't supply the role password during login/connect, you will have to connect first - which requires the session privilege - and then use

set role rol_conexion identified by conexion;

Currently setting that role would be a bit pointless, but in reality you would have a role providing other privileges that need to sit behind the role authorisation - and not create session.