8

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.

7

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).

|improve this answer|||||
  • 1
    Why can't I assign table privileges for login roles in pgAdmin? Only group roles are listed there. – Vlad Feb 9 '16 at 14:49
  • 4
    It must be some design issue/bug - I was wondering myself. Such issues are the reasons why I am exclusively using psql and normal SQL statements for everything. – dezso Feb 9 '16 at 15:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.