Обсуждение: Re: [PATCHES] Users/Groups -> Roles

Hi Fabien,

* Fabien COELHO (fabien.coelho@ensmp.fr) wrote:
> I've looked very quickly at the patch. ISTM that the proposed patch is a
> reworking of the user/group stuff, which are both unified for a new "role"
> concept where a user is a kind of role and a role can be a member of
> another role. Well, why not.

Right, it's a beginning to proper 'Role' support as defined by the SQL
specification.

> Some added files seems not to be provided in the patch :

pg_authid.h and pg_auth_members.h were attached to the email.  They're
also available at http://kenobi.snowman.net/~sfrost/pg_role/ ; but the
patch has already been applied by Tom to CVS HEAD (well, with lots of
modifications and whatnot), so you probably should just take a look at
that.

> Anyway, from what I can see in the patch it seems that the roles are per
> cluster, and not per catalog. So this is not so conceptually different
> from user/group as already provided in pg.

It's conceptually different from users/groups in that it's roles, which
aren't the same thing.  You're right, it's still per-cluster though.

> What would have been much more interesting for me would be a per catalog
> role, so that rights could be administrated locally in each database. I'm
> not sure how to provide such a feature, AFAICS the current version does
> not give me new abilities wrt right management.

I understand your concerns here and while I agree with the basic idea
that per-catalog role sets would be nice it wasn't what I had set out to
do with this patch.  Perhaps what you're asking for will be added later
on.  Some things this patch does do though are:

Allow role ownership.  This role can also have members, and doesn't
necessairly have to be allowed to log in.  Members of a role which owns
an object have owner-level rights on that object (so, fe: roles user1,
user2 and group1 where user1 and user2 are members of group1, a table
owned by group1 can be vacuumed, have columns added/removed, have
indexes create on it, etc, by user1 or user2).

Allow granting roles to other roles based on the 'with admin option'.
This means you don't have to be a superuser to add a member to a role
which you have the 'admin option' on.

There's other things (startup may be a bit faster since the pg_auth file
is sorted by the backend instead of during each startup, etc) but the
above were the types of things that I was looking to do mainly.

I'd like to see it possible to distinguish between 'superuser' and
'createrole' permissions, but I didn't get to that point with the roles
support (it's really a seperate issue anyway).
Thanks,
    Stephen

Fabien,

* Fabien COELHO (fabien@coelho.net) wrote:
> This is a very useful feature, and a key idea of the specs IMVVHO. ISTM
> that the way "fuse" user and role misses that important point, as I have
> not seen a "set role" in the grammar file.

'set role' is coming, sorry it wasn't in my initial patch.  We are
looking to support pretty much all of the SQL features 'Basic roles' and
'Extended roles'.  I think 'set role' and maybe some cleanup of
information_schema is all we need to claim 'Basic roles' support.  For
'Extended roles' I think we need revoke role cascade support.
Interestingly, the SQL2003 draft I'm looking at doesn't list 'drop role
<role name> cascade' as being valid, yet other places in the spec
specify 'drop role <role name> cascade' usage, so I think we should
support that.

> Although in the spec role rights are transitive in the role realm, it
> should *stop* at the user. If you drop the user concept, you just have a
> group with automatically provided rights.

I'm not quite sure what you mean here.  Role right resolution starts
from the user and then works backwards up the tree, with multi-level
resolution.  It wouldn't go past the logged in user since that's really
where it starts.

> The fact that the spec does not specify the USER stuff and specifies the
> ROLE stuff does not mean that having only roles is the good way to go.

I'm pretty sure we'll be able to match the SQL spec and support at least
everything we did before with users/groups...

> So for me we should have per-cluser users as they where up to now,
> per-catalog roles with the properties I described, and possibly
> per-cluster group just for the sake of compatibility/simplicity of the
> access control and managing group of users as a whole. ROLE should not
> replace USER/GROUP. It should be added next to it.

I don't see much point in having USER or GROUP when we have roles.  Is
there something specific that you feel can't be done with roles that
could be done w/ USER/GROUP?  Per-catalog roles is an interesting idea,
but I'd tend to think that if you want per-catalog roles, you'd want
per-catalog users too.  I don't have any problem with that, but I don't
see how not being per-catalog indicates we should have USER/GROUP
instead of roles.

> Maybe I'm wrong at my reading of the spec and its intent, and at my quick
> check through the status of the cvs head, but that's my current
> understanding, and I think it should be checked seriously.

I just went through the spec yesterday, check -hackers for my email
about what CVS head supports vs. what's in the SQL spec.  I don't see
any particular reason why we wouldn't be able to fully support 'Basic
roles' and 'Extended roles' in 8.1, I think we're quite close now...
Thanks,
    Stephen

Dear Stephen,

> Right, it's a beginning to proper 'Role' support as defined by the SQL
> specification.

Ok. AFAIC remember, the specification is pretty subtle and fuzzy enough so 
that there is room for little design options.

> I understand your concerns here and while I agree with the basic idea
> that per-catalog role sets would be nice it wasn't what I had set out to
> do with this patch.  Perhaps what you're asking for will be added later
> on.
>
> Some things this patch does do though are:
> [...]

Ok. I think I understand but I'm not sure it is done the right way.
Let me explain my (possibly wrong) point of view:

The standard talks about 2 distinct concepts: USER and ROLE (4.34). I'm
not sure it is a good idea to drop the user concept to replace it by role.
If you do so, you may miss something about what roles are about.

The SESSION_USER/CURRENT_USER has a CURRENT_ROLE which defines the rights 
at a given time. This role can be changed by the user, based on user/role 
membership, so the user can change its 'effective' rights. Roles are 
potential privileges that a user can set himself in if he/she desires so. 
example:

sh> psql -u calvin mydb
calvin@mydb>
-- I'm user calvin with no role or a default role on mydb.
-- I can do all which is allowed to 'calvin' as a user.

calvin@mydb> SET ROLE admin;
calvin/admin@mydb> ...
-- I'm allowed to do that if the role 'admin' is granted to 'calvin'
-- now I can do whatever is allowed to role 'admin'.

calvin/admin@mydb> SET ROLE basic;
calvin/basic@mydb> ...
-- now I can do what is allowed to role 'basic' and the roles 'basic' are in.
-- things that where allowed to admin may *not* be accessible now.

This is a very useful feature, and a key idea of the specs IMVVHO. ISTM 
that the way "fuse" user and role misses that important point, as I have 
not seen a "set role" in the grammar file.

Although in the spec role rights are transitive in the role realm, it 
should *stop* at the user. If you drop the user concept, you just have a 
group with automatically provided rights.

The fact that the spec does not specify the USER stuff and specifies the 
ROLE stuff does not mean that having only roles is the good way to go.


So for me we should have per-cluser users as they where up to now, 
per-catalog roles with the properties I described, and possibly 
per-cluster group just for the sake of compatibility/simplicity of the 
access control and managing group of users as a whole. ROLE should not 
replace USER/GROUP. It should be added next to it.


Maybe I'm wrong at my reading of the spec and its intent, and at my quick 
check through the status of the cvs head, but that's my current 
understanding, and I think it should be checked seriously.

Have a nice day,

-- 
Fabien

Dear Stephen,

Thanks again on working on this feature.

> Role right resolution starts from the user and then works backwards up 
> the tree, with multi-level resolution.  It wouldn't go past the logged 
> in user since that's really where it starts.

ISTM that the starting point should *not* be the user, but the 
CURRENT_ROLE, which must be something distinct: Even if I'm root, if a 
'SET ROLE very_limited_privileges' is performed, then the privileges in 
effect are those of the chosen role. That is what is told by section
4.34.1.1 "SQL-session authorization identifiers" of the SQL 2003 specs as 
I understand it.

If the user is kind of a role, then I'm afraid the whole point may be
missed. But maybe not, it would depend on the implementation details.

>> So for me we should have per-cluser users as they where up to now,
>> per-catalog roles with the properties I described, and possibly
>> per-cluster group just for the sake of compatibility/simplicity of the
>> access control and managing group of users as a whole. ROLE should not
>> replace USER/GROUP. It should be added next to it.
>
> I don't see much point in having USER or GROUP when we have roles.

Indeed, if you have per-cluster ROLE, you don't need GROUP anymore.

If USER is per-cluster for connection management and ROLE per-catalog for 
database access management, then you will need a per-cluster grouping
(say for pg_hba.conf...) which is just the current GROUP.

> Is there something specific that you feel can't be done with roles that 
> could be done w/ USER/GROUP?

No, it is the reverse: I'm afraid that the way it seems to be heading, no 
more will be done with role than with group before.


> Per-catalog roles is an interesting idea, but I'd tend to think that if 
> you want per-catalog roles, you'd want per-catalog users too.

I'm fine with per-cluster users.


> I just went through the spec yesterday, check -hackers for my email

Ok, I'm going to look into that.


> about what CVS head supports vs. what's in the SQL spec.  I don't see
> any particular reason why we wouldn't be able to fully support 'Basic
> roles' and 'Extended roles' in 8.1, I think we're quite close now...

I'm looking forward to the 'SET ROLE' implementation. If the 
interpretation of the privileges is restricted to the current role, then 
I'll be happy.

I still think that removing groups and having per-cluster roles is not a 
good idea. The better way would be to keep user/group and add per-catalog 
roles. There is an opportunity which is being missed, and that won't show 
up later. Well, I can see that I'm pretty alone to think that;-)

Thanks for your answer, have a nice day,

-- 
Fabien.

Fabien & Tom (if you're watching),

* Fabien COELHO (coelho@cri.ensmp.fr) wrote:
> >Role right resolution starts from the user and then works backwards up
> >the tree, with multi-level resolution.  It wouldn't go past the logged
> >in user since that's really where it starts.
>
> ISTM that the starting point should *not* be the user, but the
> CURRENT_ROLE, which must be something distinct: Even if I'm root, if a

Err, yes, from the CURRENT_ROLE, which is what we get back from
'GetUserId()', technically I think.  I'm not sure the stack has really
been implemented yet (I'd expect it to be done correctly w/ the SET ROLE
things).

> 'SET ROLE very_limited_privileges' is performed, then the privileges in
> effect are those of the chosen role. That is what is told by section
> 4.34.1.1 "SQL-session authorization identifiers" of the SQL 2003 specs as
> I understand it.

Right, that's what the behavior should be.

> If the user is kind of a role, then I'm afraid the whole point may be
> missed. But maybe not, it would depend on the implementation details.

No, I don't think the point will be missed at all.  I certainly
understand that privileges are dropped when doing SET ROLE.  Really, I
think SET ROLE and the associated SESSION_USER/CURRENT_USER/CURRENT_ROLE
need to be implemented/looked at carefully to make sure the right things
happen.

Tom, if you're watching, are you working on this?  I can probably spend
some time today on it, if that'd be helpful.

> >>So for me we should have per-cluser users as they where up to now,
> >>per-catalog roles with the properties I described, and possibly
> >>per-cluster group just for the sake of compatibility/simplicity of the
> >>access control and managing group of users as a whole. ROLE should not
> >>replace USER/GROUP. It should be added next to it.
> >
> >I don't see much point in having USER or GROUP when we have roles.
>
> Indeed, if you have per-cluster ROLE, you don't need GROUP anymore.
>
> If USER is per-cluster for connection management and ROLE per-catalog for
> database access management, then you will need a per-cluster grouping
> (say for pg_hba.conf...) which is just the current GROUP.
>
> >Is there something specific that you feel can't be done with roles that
> >could be done w/ USER/GROUP?
>
> No, it is the reverse: I'm afraid that the way it seems to be heading, no
> more will be done with role than with group before.

Already at least some of the things I was looking for with Roles can be
done, such as a role with members having ownership of an object- this
allows me to create 'admin' roles for a given area without having to
give them superuser().  It's not perfect yet, but it's getting closer.

> >Per-catalog roles is an interesting idea, but I'd tend to think that if
> >you want per-catalog roles, you'd want per-catalog users too.
>
> I'm fine with per-cluster users.

I'm pretty sure others have been asking about per-catalog users and if
we're going to accept that per-catalog roles makes sense I'd really
think per-catalog users would too.

> >about what CVS head supports vs. what's in the SQL spec.  I don't see
> >any particular reason why we wouldn't be able to fully support 'Basic
> >roles' and 'Extended roles' in 8.1, I think we're quite close now...
>
> I'm looking forward to the 'SET ROLE' implementation. If the
> interpretation of the privileges is restricted to the current role, then
> I'll be happy.

Right, according to the SQL spec it's a 'stack', where generally the
only thing visible, and what's used for permissions checking, etc, is
whatever is on the top of the stack, so after a 'SET ROLE' you only have
the permissions of that 'SET ROLE'.  The only concern I can see here is
that I'm pretty sure the SQL spec allows you to go back (Using 'SET
ROLE' with no argument, or maybe 'SET ROLE NONE', I'd have to
double-check).  That makes sense in some instances, but not in others.
There might be room to consider something like 'SET ROLE <role> FINAL'
or some such which disallows going back, though that'd be a PG extension
beyond the SQL spec I'm pretty sure.

> I still think that removing groups and having per-cluster roles is not a
> good idea. The better way would be to keep user/group and add per-catalog
> roles. There is an opportunity which is being missed, and that won't show
> up later. Well, I can see that I'm pretty alone to think that;-)

I really disagree with you here.  I feel it makes much more sense to do
this in stages, first user/group -> roles, then roles-per-catalog, which
means you can then have both per-catalog 'users' and per-catalog
'groups', if you want to limit your view to that.

> Thanks for your answer, have a nice day,
Thanks,
    Stephen

Stephen Frost <sfrost@snowman.net> writes:
> Tom, if you're watching, are you working on this?  I can probably spend
> some time today on it, if that'd be helpful.

I am not; I was hoping you'd deal with SET ROLE.  Is it really much
different from SET SESSION AUTHORIZATION?

> I'm pretty sure others have been asking about per-catalog users and if
> we're going to accept that per-catalog roles makes sense I'd really
> think per-catalog users would too.

We really can't do this.  Especially not 3 days before feature freeze.
        regards, tom lane

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Stephen Frost <sfrost@snowman.net> writes:
> > Tom, if you're watching, are you working on this?  I can probably spend
> > some time today on it, if that'd be helpful.
>
> I am not; I was hoping you'd deal with SET ROLE.  Is it really much
> different from SET SESSION AUTHORIZATION?

No, it's not, but it's going to need to be done carefully to make sure
GetUserId() returns the correct thing at the correct time and that the
other GetSessionUserId() calls are only used where they should be and
that they return the correct information too.

I'll work on SET ROLE and the associated CURRENT_* functions and
information_schema today and tommorow.

> > I'm pretty sure others have been asking about per-catalog users and if
> > we're going to accept that per-catalog roles makes sense I'd really
> > think per-catalog users would too.
>
> We really can't do this.  Especially not 3 days before feature freeze.

Right, I wasn't expecting that to be done in this round.  It's something
people have asked for though and so might be something to consider for
8.2.  I'm hoping your work on CREATEROLE will stem some of that demand
for per-catalog users/roles actually.  I've been trying to think what
else per-catalog users/roles would get us besides a segmented namespace.
I think one big issue is that we don't have a 'usage' database check
beyond pg_hba and so any user could get the schema definitions for any
database, which kind of sucks.  Is that maybe something we could try to
address for 8.1?
Thanks,
    Stephen

Stephen Frost <sfrost@snowman.net> writes:
> I think one big issue is that we don't have a 'usage' database check
> beyond pg_hba and so any user could get the schema definitions for any
> database, which kind of sucks.

Not unless he can connect to it.
        regards, tom lane

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Stephen Frost <sfrost@snowman.net> writes:
> > I think one big issue is that we don't have a 'usage' database check
> > beyond pg_hba and so any user could get the schema definitions for any
> > database, which kind of sucks.
>
> Not unless he can connect to it.

That's controlled by pg_hba.conf though, isn't it?  The idea being that
you'd like to give some people the ability to create users/roles, but to
limit the databases those created users/roles could connect to by, say,
requiring they have 'usage' or 'connect' permissions to that database,
which could be set by the database owner; without the database owner
having write permissions to the pg_hba.conf.

The scenario is one of an ISP who wants to give people Postgres access
but doesn't want to have to manage all the users.  So, the ISP creates
a database, an 'admin' role for a given customer and gives 'createrole'
permissions to that admin role.  The admin role can then create new
roles but can only give them access to connect to their database (since
that's the only one the admin either 'owns' or has 'create', etc,
privileges on).  I *think* (perhaps I'm wrong..) that the only thing we
lack to make this work is a permissions check on the connect to a given
database which can be managed by a user of the database (ie: not
pg_hba.conf).

Thinking about this a bit more I guess this would probably involve
basically moving pg_hba.conf into the database catalogs and then having
pg_hba.conf generated similar to how pg_authid is generated.  That's
probably too much to do for 8.1 then, I had been hoping there was a way
to do it which would be a smaller change than that.
Thanks,
    Stephen

Stephen Frost <sfrost@snowman.net> writes:
> That's controlled by pg_hba.conf though, isn't it?  The idea being that
> you'd like to give some people the ability to create users/roles, but to
> limit the databases those created users/roles could connect to by, say,
> requiring they have 'usage' or 'connect' permissions to that database,
> which could be set by the database owner; without the database owner
> having write permissions to the pg_hba.conf.

You can do that today by putting a group name in pg_hba.conf.  Roles
will make it more flexible; I don't see that we need anything more.

For instance, if pg_hba.conf says "samegroup" then you could manage
everything by associating a group with each database.
        regards, tom lane

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Stephen Frost <sfrost@snowman.net> writes:
> > That's controlled by pg_hba.conf though, isn't it?  The idea being that
> > you'd like to give some people the ability to create users/roles, but to
> > limit the databases those created users/roles could connect to by, say,
> > requiring they have 'usage' or 'connect' permissions to that database,
> > which could be set by the database owner; without the database owner
> > having write permissions to the pg_hba.conf.
>
> You can do that today by putting a group name in pg_hba.conf.  Roles
> will make it more flexible; I don't see that we need anything more.
>
> For instance, if pg_hba.conf says "samegroup" then you could manage
> everything by associating a group with each database.

Ahh, ok, good point.  Sorry, I'd forgotten about that flexibility of
pg_hba.conf.  Well, hopefully this will make some ISPs happy then. :)
Thanks,
    Stephen

Dear Stephen,

>> I still think that removing groups and having per-cluster roles is not a
>> good idea. The better way would be to keep user/group and add per-catalog
>> roles. There is an opportunity which is being missed, and that won't show
>> up later.
>
> I really disagree with you here.  I feel it makes much more sense to do
> this in stages, first user/group -> roles, then roles-per-catalog, which
> means you can then have both per-catalog 'users' and per-catalog
> 'groups', if you want to limit your view to that.

I don't think that having two kinds of roles (per-cluster and per-catalog) 
would be a practical thing from the user perspective. From the 
implementation point of view, two tables will be needed. If you don't 
create roles directly in the right scope, it will create confusion later.

The two concept need to have two different names so that they can be 
understood. Moreover, a working per-cluster grouping was already 
available. Changing the role scope will be much harder than creating a 
role directly in the good scope.

From the implementation perspective, there is more work at adding 
per-cluster roles and removing per-cluster group and then later try to add 
per-catalog roles than adding per-catalog roles directly without touching 
the existing group stuff.

So I'm afraid that the opportunity is missed and that per-catalog role 
will never get in. Well, at least you look more optimistic than me;-)

-- 
Fabien.

Fabien,

* Fabien COELHO (coelho@cri.ensmp.fr) wrote:
> >I really disagree with you here.  I feel it makes much more sense to do
> >this in stages, first user/group -> roles, then roles-per-catalog, which
> >means you can then have both per-catalog 'users' and per-catalog
> >'groups', if you want to limit your view to that.
>
> I don't think that having two kinds of roles (per-cluster and per-catalog)
> would be a practical thing from the user perspective. From the
> implementation point of view, two tables will be needed. If you don't
> create roles directly in the right scope, it will create confusion later.
>
> The two concept need to have two different names so that they can be
> understood. Moreover, a working per-cluster grouping was already
> available. Changing the role scope will be much harder than creating a
> role directly in the good scope.

The two concepts certainly don't need different names to distinguish
them.  A simple distinction such as superusers are per-cluster and all
other roles are not would be sufficient.  I expect that's the kind of
thing people would be looking for anyway.

> >From the implementation perspective, there is more work at adding
> per-cluster roles and removing per-cluster group and then later try to add
> per-catalog roles than adding per-catalog roles directly without touching
> the existing group stuff.

Having just spent a fair bit of time on the implementation, I have to
disagree with you here.

> So I'm afraid that the opportunity is missed and that per-catalog role
> will never get in. Well, at least you look more optimistic than me;-)

Honestly, this comes across to me the same as saying that because we
have databases we'd never have schemas.

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

Fabien COELHO <coelho@cri.ensmp.fr> writes:
> Moreover, a working per-cluster grouping was already 
> available.

Only for sufficiently small values of "working".  The lack of ability
for groups to contain other groups and for groups to be the direct
owners of objects were both pretty serious restrictions, which are now
fixed.
        regards, tom lane

Dear Stephen,

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

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

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.

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.

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

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.


Thanks for all your answers, have a nice day,

-- 
Fabien.

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

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

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
 

* Bruce Momjian (pgman@candle.pha.pa.us) wrote:
> Stupid question, but how do roles relate to our existing "groups"?

Uhhh.  There are no longer "groups", they've been replaced with roles
(which can have members).
Thanks,
    Stephen

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Stupid question, but how do roles relate to our existing "groups"?

As committed, roles subsume both users and groups: a role that permits
login (rolcanlogin) acts as a user, and a role that has members is a
group.  It is possible for the same role to do both things, though I'm
not sure that it's good security policy to set up a role that way.

The advantage over what we had is exactly that there isn't any
distinction, and thus groups can do everything users can and
vice versa:* groups can own objects* groups can contain other groups (we forbid loops though)

Also there is a notion of "admin option" for groups, which is like
"grant option" for privileges: you can designate certain members of
a group as being able to grant ownership in that group to others,
without having to make them superusers.
        regards, tom lane

Thanks, TODO updated.  We still support CREATE GROUP?  It translates to
roles?

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

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Stupid question, but how do roles relate to our existing "groups"?
> 
> As committed, roles subsume both users and groups: a role that permits
> login (rolcanlogin) acts as a user, and a role that has members is a
> group.  It is possible for the same role to do both things, though I'm
> not sure that it's good security policy to set up a role that way.
> 
> The advantage over what we had is exactly that there isn't any
> distinction, and thus groups can do everything users can and
> vice versa:
>     * groups can own objects
>     * groups can contain other groups (we forbid loops though)
> 
> Also there is a notion of "admin option" for groups, which is like
> "grant option" for privileges: you can designate certain members of
> a group as being able to grant ownership in that group to others,
> without having to make them superusers.
> 
>             regards, tom lane
> 

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

* Bruce Momjian (pgman@candle.pha.pa.us) wrote:
> Thanks, TODO updated.  We still support CREATE GROUP?  It translates to
> roles?

Yes, CREATE USER too.
Stephen

> Tom Lane wrote:
> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > Stupid question, but how do roles relate to our existing "groups"?
> >
> > As committed, roles subsume both users and groups: a role that permits
> > login (rolcanlogin) acts as a user, and a role that has members is a
> > group.  It is possible for the same role to do both things, though I'm
> > not sure that it's good security policy to set up a role that way.
> >
> > The advantage over what we had is exactly that there isn't any
> > distinction, and thus groups can do everything users can and
> > vice versa:
> >     * groups can own objects
> >     * groups can contain other groups (we forbid loops though)
> >
> > Also there is a notion of "admin option" for groups, which is like
> > "grant option" for privileges: you can designate certain members of
> > a group as being able to grant ownership in that group to others,
> > without having to make them superusers.
> >
> >             regards, tom lane
> >
>
> --
>   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, Pennsylvania 19073

On Friday 01 July 2005 13:07, Stephen Frost wrote:
> * Bruce Momjian (pgman@candle.pha.pa.us) wrote:
> > Thanks, TODO updated.  We still support CREATE GROUP?  It translates to
> > roles?
>
> Yes,
<snip> 

However On Friday 01 July 2005 13:02, Stephen Frost wrote:
> * Bruce Momjian (pgman@candle.pha.pa.us) wrote:
> > Stupid question, but how do roles relate to our existing "groups"?
>
> Uhhh.  There are no longer "groups", they've been replaced with roles
> (which can have members).
>

Was following this conversation up till now, because these two statement seem 
to contradict each other.  Do we really support groups still, are is CREATE 
GROUP now syntactical sugar for some for of CREATE ROLE. 

-- 
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

Robert Treat <xzilla@users.sourceforge.net> writes:
> Was following this conversation up till now, because these two statement seem
> to contradict each other.  Do we really support groups still, are is CREATE 
> GROUP now syntactical sugar for some for of CREATE ROLE. 

CREATE GROUP and CREATE USER are both now syntactic sugar for CREATE
ROLE.
        regards, tom lane

* Robert Treat (xzilla@users.sourceforge.net) wrote:
> On Friday 01 July 2005 13:07, Stephen Frost wrote:
> However On Friday 01 July 2005 13:02, Stephen Frost wrote:
> > * Bruce Momjian (pgman@candle.pha.pa.us) wrote:
> > > Stupid question, but how do roles relate to our existing "groups"?
> >
> > Uhhh.  There are no longer "groups", they've been replaced with roles
> > (which can have members).
> >
>
> Was following this conversation up till now, because these two statement seem
> to contradict each other.  Do we really support groups still, are is CREATE
> GROUP now syntactical sugar for some for of CREATE ROLE.

CREATE GROUP just does a CREATE ROLE now, yeah.  You can check gram.y
for the details if you'd like.  We do still support \du and \dg
(pg_users and pg_groups respectively, iirc) for backwards compat. and to
help folks get used to the new stuff.
Thanks,
    Stephen

Fabien COELHO <coelho@cri.ensmp.fr> writes:
>> 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;-)

Why not?  With the setup Stephen suggests, they could create only new
users that could only get into their own database (since they'd not be 
able to grant connect rights to other databases).

We probably need to think a bit harder about the meaning of CREATEROLE
though.  Right now it gives free license not only to create roles but
to alter any property of existing roles.  This seems appropriate if you
think of it as a "safer form of superuser", which is how I was thinking
of it.  It would be too powerful for Fabien's situation though.
        regards, tom lane

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Fabien COELHO <coelho@cri.ensmp.fr> writes:
> >> 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;-)
>
> Why not?  With the setup Stephen suggests, they could create only new
> users that could only get into their own database (since they'd not be
> able to grant connect rights to other databases).

I'm curious why not too...  One issue I can think of is that
alter role ...  rename, etc could be a problem.

> We probably need to think a bit harder about the meaning of CREATEROLE
> though.  Right now it gives free license not only to create roles but
> to alter any property of existing roles.  This seems appropriate if you
> think of it as a "safer form of superuser", which is how I was thinking
> of it.  It would be too powerful for Fabien's situation though.

Well, what about it makes it 'too powerful'?  I think that's pretty much
the same question you're asking Fabien above.  I agree that only certain
properties should probably be modifiable though, one inparticular that
comes to mind is 'LOGIN'; I can see why you might want to allow only roles
which can't log in to be creatable by a given role.

It strikes me that it'd make some sense to have independent grant
control over each of the fields in pg_authid.  It would also make sense
to limit the power of alter role to certain roles based on who they were
created by (superuser vs. createrole).  It seems we probably need at
least an association to either catalog or creator for each role which
could then be used to limit alter role commands.  catalog probably makes
more sense in the long run, creator would be easier in the short-term.
Thanks,
    Stephen

Stephen Frost <sfrost@snowman.net> writes:
> * Tom Lane (tgl@sss.pgh.pa.us) wrote:
>> We probably need to think a bit harder about the meaning of CREATEROLE
>> though.  Right now it gives free license not only to create roles but
>> to alter any property of existing roles.  This seems appropriate if you
>> think of it as a "safer form of superuser", which is how I was thinking
>> of it.  It would be too powerful for Fabien's situation though.

> Well, what about it makes it 'too powerful'?

The fact that you could alter roles that (in some sense) don't belong to
you.  In particular you could allow yourself into other people's
databases, if you can alter the roles that they are using to define who
can connect to those databases.  Or cause denials of service by revoking
other people's memberships in those roles.

On the other hand, CREATEROLE as-is does exactly what it was intended
to do, namely let the DBA do all normal admin maintenance of users/groups
without taking the risks involved in doing stuff as superuser.  If we
restrict it more, then we'll be back to the situation where there are
routine manual admin tasks that require superuserdom.  So on reflection
I don't think we should restrict it.  If we need a more restrictive
feature, then we need a different feature.

I'm of the opinion that it's too late to do much about Fabien's use-case
in this devel cycle.  We could possibly have designed something rational
if it had been brought up earlier in the discussion of roles (which I
remind you has been going on for months).  But now it's too late to do
anything that wouldn't be a kluge, and probably a kluge we'd regret
later.

Possibly for 8.2 we could invent a notion of roles having owners.
Offhand I don't see any harm in letting non-CREATEROLE users create
non-login roles, and manipulate the membership of roles they have
created (or that have been assigned to them by a superuser).  On the
other hand, it could be that the WITH ADMIN OPTION feature is already
sufficient for this.  This really needs some thought ...
        regards, tom lane

Dear Tom,

> We probably need to think a bit harder about the meaning of CREATEROLE
> though.  Right now it gives free license not only to create roles but
> to alter any property of existing roles.  This seems appropriate if you
> think of it as a "safer form of superuser", which is how I was thinking
> of it.  It would be too powerful for Fabien's situation though.

Yes.

ISTM it would be a good thing to separate
 - sysadmin issues (create a user that can connect - pg_hba.conf) and
 - dbadmin issues (manage permission on data in a catalog).

The first item is the current pg superuser. I envision the second item as 
being a property of the database (catalog) OWNER, which can do whatever 
he/she wants at home, but should not bother others. So there is a need for 
a limited 'createrole' ability (simple roles that cannot be users (?), and 
avoid name space collision).

This is better for teaching, but also for any service provider which would 
host many users and databases, and would like to transfer them.

If you want to delegate data access privileges to someone, it is better
to have that restricted to the catalog to avoid name-space collision.

The same issue arises when moving a database from one cluster to another 
where some role names may already be used. That is the kind of think I 
also need to do as a dbadmin.

If one take a conceptual perspective, database access privilege management 
is a catalog-related task, so it seems better to have that in the catalog.

So I think all points that roles are better (more useful) per-catalog
than per-cluster.

If you just want to claim that 'pg has roles', you can do the marketing 
with per-cluster roles;-) If you want them more useful, you need them 
per-catalog.

-- 
Fabien.