What's the difference between group roles and login roles?

It looks like for single user I should create a login role and than respective group roles because I can't directly assign privileges on login role. Is it true? What's the purpose of such separation?

Also as I understand it's only possible to authenticate with login role. So what's the purpose of the password field in a group role? Or it's possible to authenticate as group role? Then why anyone would need login roles?

Also it looks like I can set both login and group roles in pg_hba.

All these questions are basically answered in the documentation. To be specific:


A role is an entity that can own database objects and have database privileges; a role can be considered a "user", a "group", or both depending on how it is used.

That implies that internally there is no difference between these, except the LOGIN option. If you specify LOGIN (or use CREATE USER):

CREATE ROLE dezso WITH LOGIN PASSWORD 'bla';

dezso will become a login role, otherwise not. You can specify passwords for non-login roles, too - if you later decide to make it a login role, use

CREATE ROLE non_login_role PASSWORD 'bla';

--later
ALTER ROLE non_login_role WITH LOGIN;

You are right when saying 'it's only possible to authenticate with login role'. Note, however, that a password is not always necessary - if you use anything else than the password or md5 authentication methods. On the other hand this means that a role without the LOGIN option set cannot log in in any way.

Finally, you may set rules in pg_hba.conf for non-login roles, but as it's used only for authentication, those roles will have no effect (unless you enable login later, as above).

StackExchange Database Administrators Q#128578, answer score: 7