Postgres role without attribute Login

Question

I am a bit confused by the role attribute LOGIN | NONLOGIN, where postgres docs says:

These clauses determine whether a role is allowed to log in; that is, whether the role can be given as the initial session authorization name during client connection. A role having the LOGIN attribute can be thought of as a user. Roles without this attribute are useful for managing database privileges, but are not users in the usual sense of the word. If not specified, NONLOGIN is the default, except when CREATE ROLE is invoked through its alternative spelling CREATE USER.

What is a possible restriction with NONLOGIN? Why would people want a role with NONLOGIN in the first place?

Answer 1

The clause is NOLOGIN, not NONLOGIN. (I suggest copy/paste for quotes.)

The clause is for "group" roles, which used to actually be GROUP before Postgres 8.1 - which is still supported. See CREATE GROUP in the manual. Related:

• Why did PostgreSQL merge users and groups into roles?

Group roles are very useful to bundle privileges and then grant / revoke the whole package to LOGIN roles. See:

• Grant privileges for a particular database in PostgreSQL

Or to act as demons within the database:

• Allow insertion only from within a trigger

Answer 2

With NOLOGIN attribute role has no have permissions to connect to database. And vice versa with LOGIN.

In a simple way - USER is a ROLE with LOGIN attribute ('everything' here is a role in a Postgres). Group role - is a role without LOGIN attribute (or with NOLOGIN attribute).

Therefore you can add role to a role to have more efficient permissions and grants management and decrease security threat.

For example I want to create a role with only read permissions, let's say only_read_permission

CREATE ROLE only_read_permission;

and I want have numerous users in database that can have access for reading each others tables.

Let's create a user (a role with LOGIN attribute) +only_read_permission role:

CREATE USER mikeaksenov IN ROLE only_read_permission;

And let's all my new created tables will have select permissions to all other users that have only_read_permission role:

ALTER DEFAULT PRIVILEGES FOR ROLE mikeaksenov
GRANT SELECT ON TABLES to only_read_permission;

It's just example, so next time I can create update_permission role and add it to required users. And here is the questions - why do I need give my group roles a LOGIN attribute? 1. It's no much security here 2. There is no point in it (in this particular situation)

Another situation if you see that user doing a bad stuff you can just provide him with NOLOGIN attribute and then have a conversation with him (or anything else)

Anyway, you can do whatever you want in Postgres, it's quite agile.

It's quite confusing in a first place, but then you may start to love how you can precisely manage a user access.