Re: [PATCHES] Users/Groups -> Roles - Mailing list pgsql-hackers

>> The privilege management is about a catalog, so it better to have it in 
>> the catalog.
>
> Permissions are at a number of levels already: cluster, database,
> schema, table.  Permissions at different levels hasn't got anything to
> do w/ per-catalog roles.

Sorry for not being very clear. I see two "main" levels:

(1) the connection which is managed in pg_hba.conf. It is a sysadmin 
concern, where you decide who will be able to get into your system. This 
issue is *not* addressed by the SQL specs.

(2) once you're connected to a catalog, the ability to adjust/organize 
privileges for accessing data within this catalog. This is a dbadmin 
concern, where you decide for a given user which can access the database, 
what data should be readable. This issue is addressed by the SQL specs.

If you think unix, root decides the first, and each user decides the 
second for the files it owns.

>>  - for teaching purposes [...]
>
> Right, this can be done now.

There is the namespace collision issue, and although I might grant a 
student the privilege to create simple roles, I would not allow them to 
create new users for a basic practice;-)

>>  - for administration purposes, different databases have different
>>    requirements, and maybe different kind of role "readonly",
>>    "modifiable", "fulladmin" which could be attributed independently in
>>    each database.
>
> I don't see how this has got anything to do w/ per-catalog roles either...

Because of namespace collision. That what I mean by "independently" above.

>> So as to illustrate what I call an interference [...]
>
> Ah-hah, now here's something we can talk about: namespace collision.

That is just what I meant in the last 10 mails when I mention per-catalog 
roles as better than per-cluster roles. I just did not put in the right
keywords to make myself clear:-( Sigh.

> That's an issue which per-catalog roles would help with.

Indeed.

> I'm not so sure that'd make administration *easier* though, I'd think 
> it'd make it harder, honestly, at least in the case where you've got 
> multiple databases that you want to give a certain person access to.

Sure, "grouping" at the cluster level is also useful.

> [...]
> As long as we can identify the database trying to be connected to at the 
> same time or before we get the username I don't think this would be too 
> hard to support.

Yet, but this is not what I'm looking for. I want the grouping 
per-catalog, but the user (or connectable-role now) is fine enough for me 
at the cluster level.

> Perhaps for 8.2 this could be done, though I'm still not convinced it's 
> a definite win.

For the "user per-catalog" part, I agree with you.

>> If you're right that having both "per-catalog" and "per-cluster" role with
>> some flag would be accepted into postgresql, then that will be fine with
>> me. I'll just argue for the per-catalog roles to be the default.
>
> It'd really be a create-role option, 'create role abc GLOBAL'

There is also a problem of namespace collision if you have both global and 
local roles. When I create a global role, pg cannot check that this name 
is not used in ANY of the databases. If you can have two "admin" roles, 
one global and one local... I doubt Tom will let pass such an improvement.
Also, I don't feel the upward compatibility constraint well with 
per-catalog roles once per-cluster roles are in place.

> or some such.  The resolution would then be check the per-catalog 
> pg_authid first and if nothing is found there go to the system-wide 
> pg_authid. That's kind of ugly, of course, and could slow things down 
> for people who prefer to have global roles instead of per-catalog roles.

If the per-catalog role is empty, I guess an obvious optimisation could be 
implemented;-)

Good night,

-- 
Fabien.