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

Stupid question, but how do roles relate to our existing "groups"?

---------------------------------------------------------------------------

Fabien COELHO wrote:
> 
> >> 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.
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073