Is the PostgreSQL 'connection limit' shared across all users in a role?

Question

I have created a role that many users can share:

> create role foo_read_only connection limit 5;

I have then assigned permissions to that role:

> grant select on table1 to foo_read_only;

Then I have assigned that role to people:

> grant foo_read_only to fred;
> grant foo_read_only to ginger;

Question: Do both fred and ginger get a connection limit of 5, or would that limit be shared between them?

Thanks!

Answer 1

The CONNECTION LIMIT attribute only affects roles that are able to log in (that is "users", or those roles which have the LOGIN attribute set).

Roles that are a member of another role with a CONNECTION LIMIT do not inherit that limit, so in the situation you describe above fred and ginger would have the default connection limit of -1 (unlimited).

To limit fred and ginger you would need to specifically set a CONNECTION LIMIT on those roles.

Answer 2

Building on the answer by @voretaq7, the question then becomes, how to enforce connection limits for members of a role? Since inheritance does not work (the 2011 answer is not contradicted in Postgresql 17 man pages, one should have a handy way of updating it.

Thanks to answers provided here and here, I provide some handy code to do that:

SELECT format('ALTER ROLE %I CONNECTION LIMIT 15', r.rolname)
FROM pg_catalog.pg_roles r JOIN pg_catalog.pg_auth_members m
ON (m.member = r.oid)
JOIN pg_roles r1 ON (m.roleid=r1.oid)                                  
WHERE r.rolcanlogin AND r1.rolname=$$GROUP_ROLE_NAME$$
\gexec

and replacing 15 and GROUP_ROLE_NAME as required.