Re: [PATCHES] Users/Groups -> Roles

Fabien,

* Fabien COELHO (coelho@cri.ensmp.fr) wrote:
> >Please outline exactly what you're really looking for.  Let's drop the
> >idea of per-cluster users/groups/roles/whatever and instead consider
> >what specific capabilities you're looking for.
>
> I think from a conceptual point of view that the ability to manage
> permissions at the database level (per-catalog role) is a good thing (tm)
> for everybody. 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.

> My personnal uses are two fold :
>
>  - for teaching purposes, I can give every student his/her database
>    and have her/him practice db privileges independently. They
>    can create their own roles and do whatever with them...

Right, this can be done now.  When you set up each student with his/her
database create two roles:
Role 1: Has createrole permissions and admin permissions on role 2.
Role 2: Add into pg_hba.conf as a role with permission to access the db.

When a student wants to create another role with access to the db they
just have to log in as Role 1 and create the role and add that role to
Role 2.  That role can then log in to their database.

>  - 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...

> Basically, I want to be able to delegate to someone the right management
> for one database, including the creation of roles and so on, without
> interference from one database to another.

That's what createrole should let you do w/ current CVS HEAD.  Don't
thank me though, Tom did the heavy lifting wrt that.

> So as to illustrate what I call an interference: say you have 2 databases
> which where on 2 clusters and you want to transfert them into the same
> cluster. If the same role name was used in both database, you may have
> interferences, people being given rights on one database and applying them
> to the other if they can connect to it.

Ah-hah, now here's something we can talk about: namespace collision.
That's an issue which per-catalog roles would help with.  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.

> >We can then decide if those capabilities are best provided through
> >per-catalog roles, if they're already covered with the existing
> >framework, or if there's some other, better solution.
>
> One inelegant solution is to prefix the role names with the database name,
> but that is just a discipline and cannot be inforced. I think we can do
> better.

That's essentially all you're really asking for though, and is something
which could be done in the current framework.  It'd probably be more
elegant to have a per-catalog pg_authid though.  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.  Perhaps for 8.2 this could be done, though I'm still not
convinced it's a definite win.

> 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' 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.
Thanks,
    Stephen