Re: multiple databases vs multiple clusters on the same host - Mailing list pgsql-general

29.09.2013, 00:38, "Tomas Vondra" <tv@fuzzy.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
belongsto. 

Thus a combination of permissions can be granted to a user by including the user to a group. A permission can be given
orrevoked 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
correspondinguser login role is a member of the group role. Group has some permission if the group role is a member of
thepermission 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
therequested permission role. 

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