Re: [PATCHES] Users/Groups -> Roles

Fabien,

* Fabien COELHO (coelho(at)cri(dot)ensmp(dot)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