Re: multiple databases vs multiple clusters on the same host

29.09.2013, 00:38, "Tomas Vondra" <tv(at)fuzzy(dot)cz>:
> On 28 Září 2013, 21:30, Eugene Ostrovsky wrote:
>
>>  Thanks for the answer!
>>
>>  About you questions:
>>  1. Postgres 9.3
>>  2. There are about 30-50 user connections. Actually Only 2 of databases
>>  are used intensively, others only in rare cases.
>>  3. Hardware is  AMD Phenom II X4 965, 8 Gb RAM, 2 SATA2 HDD in software
>>  mirror raid
>>  4. The reason to switch to multiple clusters is that my software uses
>>  roles (login users and groups) for a single database. There are some
>>  problems with it in case of several databases because in postgres roles
>>  are shared between all the databases in the same cluster.
>
> Wouldn't it be easier just setup unique roles for each database? It's much
> better solution than deploying 10 separate clusters (which you'll learn
> soon, if you go in this direction).
>
> And what are those "some problems" that you mentioned? Seems to me this
> thread started from the wrong end - setting up multiple clusters instead
> of tackling the actual problem first.
>
> Tomas

Well, I’ll try to describe the main issue about the roles.

There are a set of permissions defining what users can and cannot do.
There are a set of groups such as “Administrator”, “Manager”, etc. Any subset of permissions can be granted to any group.
There are login users which belong to one or more groups. Each user gets all the permissions of all the groups it belongs to.

Thus a combination of permissions can be granted to a user by including the user to a group. A permission can be given or revoked to/from a number of users by granting/revoking the permission to/from corresponding group.

All the three entities (permissions, groups and users) are implemented as sql roles. User belongs to a group If corresponding user login role is a member of the group role. Group has some permission if the group role is a member of the permission role.

Permission checking is done by either of two ways:
1. If the permission can be expressed in terms of sql privileges those privileges are granted to the permission role. In this case permission checks are performed by postgres itself.
2. In other cases application (e.g. in trigger function) explicitly checks if current session user is a member of the requested permission role.

This works fine for a single database. But for different databases different relations between permissions-groups-users are requested. E.g. different set of permissions for “Manager” group or different group membership for a given user. This doesn’t work for several databases in a single cluster as the roles and their relations a common for the whole cluster.