Re: [PATCHES] Users/Groups -> Roles

From: Stephen Frost <sfrost(at)snowman(dot)net>
To: Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr>
Cc: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: [PATCHES] Users/Groups -> Roles
Date: 2005-06-30 14:43:29
Message-ID: 20050630144329.GD24207@ns.snowman.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-patches

Fabien & Tom (if you're watching),

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Stephen Frost 2005-06-30 14:47:21 Re: Open items
Previous Message Fabien COELHO 2005-06-30 14:27:21 Re: [PATCHES] Users/Groups -> Roles

Browse pgsql-patches by date

  From Date Subject
Next Message Stephen Frost 2005-06-30 14:47:21 Re: Open items
Previous Message Fabien COELHO 2005-06-30 14:17:39 Re: [PATCHES] Users/Groups -> Roles