Re: [PATCHES] Users/Groups -> Roles

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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

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