I'm looking at representing privileges granted to groups in the
information schema.  For that purpose I would like to use the views that
the standard defines for "roles".  Therefore I ask whether everyone agrees
that groups and roles are basically equivalent concepts (and perhaps that
we might in the future strive to make groups more compatible with the
roles as defined in the SQL standard).  Or does anyone see that roles
might be implemented separately from groups sometime?

-- 
Peter Eisentraut   peter_e@gmx.net


Fri, 6 Jun 2003 21:21:05 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Groups and roles
Peter Eisentraut <peter_e@gmx.net> writes:
> ... Therefore I ask whether everyone agrees
> that groups and roles are basically equivalent concepts (and perhaps that
> we might in the future strive to make groups more compatible with the
> roles as defined in the SQL standard).  Or does anyone see that roles
> might be implemented separately from groups sometime?

Just reading section 4.31.3 of the SQL99 draft, it seems that roles are
pretty much interchangeable with groups, except that a role can be a
member of another role while we don't presently allow groups to be
members of other groups.

So it seems that your question breaks down to:

1. Do we want to someday allow groups to have groups as members?  (Seems
reasonable to me.)

2. Are there any other differences between groups and roles?  (I'm not
sure about this one.)

			regards, tom lane

Sat, 07 Jun 2003 11:40:45 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Groups and roles 
Just interesting if we could inplement some kind of RBAC
(role based access control). Here is the reference:
http://csrc.nist.gov/rbac/

We've used a lot simple (flat) RBAC built on top of postgresql, but
would like to see more powerful (with roles hierarchy) rbac built-in.

	Oleg
On Sat, 7 Jun 2003, Tom Lane wrote:

> Peter Eisentraut <peter_e@gmx.net> writes:
> > ... Therefore I ask whether everyone agrees
> > that groups and roles are basically equivalent concepts (and perhaps that
> > we might in the future strive to make groups more compatible with the
> > roles as defined in the SQL standard).  Or does anyone see that roles
> > might be implemented separately from groups sometime?
>
> Just reading section 4.31.3 of the SQL99 draft, it seems that roles are
> pretty much interchangeable with groups, except that a role can be a
> member of another role while we don't presently allow groups to be
> members of other groups.
>
> So it seems that your question breaks down to:
>
> 1. Do we want to someday allow groups to have groups as members?  (Seems
> reasonable to me.)
>
> 2. Are there any other differences between groups and roles?  (I'm not
> sure about this one.)
>
> 			regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

	Regards,
		Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

Mon, 9 Jun 2003 13:00:41 +0400 (MSD)
Oleg Bartunov <oleg@sai.msu.su>
Re: Groups and roles 
Tom Lane wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
> 
>>... Therefore I ask whether everyone agrees
>>that groups and roles are basically equivalent concepts (and perhaps that
>>we might in the future strive to make groups more compatible with the
>>roles as defined in the SQL standard).  Or does anyone see that roles
>>might be implemented separately from groups sometime?
> 
> 
> Just reading section 4.31.3 of the SQL99 draft, it seems that roles are
> pretty much interchangeable with groups, except that a role can be a
> member of another role while we don't presently allow groups to be
> members of other groups.
> 
> So it seems that your question breaks down to:
> 
> 1. Do we want to someday allow groups to have groups as members?  (Seems
> reasonable to me.)

Makes sense ...

> 2. Are there any other differences between groups and roles?  (I'm not
> sure about this one.)

To me some differences would make sense sense if we had additional 
priviledges. In Oracle a user needs a punch of rights to connect, to 
execute SQL, and so forth. If we had these features it would make sense 
to treat roles and groups seperately because:
Imagine having groups having rights on dozens of tables. If these groups 
were assigned to a role it would be an easy task to block numerous 
groups from executing SQL at once. Currently a user has all rights of 
all groups he belongs to so it is damn hard to say that 1000 users 
should not be allowed to do anything for a period of time (because of 
maintenance or so). If all those users (but the superuser) had a certain 
role, the role could be modified instead of those 1000 users/groups (eg. 
REVOKE login, execute_sql FROM some_role).

Currently roles don't make too much sense to me because we don't have 
the permissions for making roles useful (personally I don't think that 
it makes to have this stuff anyway).

I guess adding a simple field to the system tables would be enough.
If we had a field "active" (bool) in pg_shadow and pg_group we could 
solve some basic problems such as banning 10000 groups at once more easily.
This is the only problem we have from time to time. If there is too much 
user rights stuff around administering will be more painful (see Oracle).

	Regards,

		Hans

-- 
Cybertec Geschwinde u Schoenig
Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria
Tel: +43/2952/30706; +43/664/233 90 75
www.cybertec.at, www.postgresql.at, kernel.cybertec.at



Tue, 10 Jun 2003 09:59:14 +0200
=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <hs@cybertec.at>
Re: Groups and roles
Tom Lane writes:

> 1. Do we want to someday allow groups to have groups as members?  (Seems
> reasonable to me.)

I agree.

> 2. Are there any other differences between groups and roles?  (I'm not
> sure about this one.)

One other difference I found is that roles can be enabled or disabled (as
session state).  According to the SQL standard, only one role can be
active at once, but I think this is useless.  According to the Oracle
documentation, it seems that in Oracle many roles can be active (namely
all of those granted to you), but they can be selectively enabled or
disabled.  This seems like a reasonable feature, but it's not terribly
important.

Another issue is that users and roles share a namespace.  We might have to
deal with that sometime, but it's not a problem as far as the information
schema is concerned.

-- 
Peter Eisentraut   peter_e@gmx.net


Tue, 10 Jun 2003 19:45:09 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: Groups and roles 
Hans-Jï¿œrgen Schï¿œnig writes:

> Imagine having groups having rights on dozens of tables. If these groups
> were assigned to a role it would be an easy task to block numerous
> groups from executing SQL at once. Currently a user has all rights of
> all groups he belongs to so it is damn hard to say that 1000 users
> should not be allowed to do anything for a period of time (because of
> maintenance or so). If all those users (but the superuser) had a certain
> role, the role could be modified instead of those 1000 users/groups (eg.
> REVOKE login, execute_sql FROM some_role).

I think you can do that with groups: Create a number of groups, say
users1, users2, etc., and then, at the predermined hour, you do:

BEGIN;
REVOKE privilege FROM users1;
GRANT privilege TO users2;
COMMIT;

This might be helped if groups could contain other groups, so that
"privilege" could be a group/role name, to ease administration, but that
does not create any distinction between the concepts role and group.

-- 
Peter Eisentraut   peter_e@gmx.net


Tue, 10 Jun 2003 19:45:50 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: Groups and roles
Peter Eisentraut <peter_e@gmx.net> writes:
> Another issue is that users and roles share a namespace.  We might have to
> deal with that sometime, but it's not a problem as far as the information
> schema is concerned.

I've been thinking for awhile that the ACL code would be simplified if
userids and groupids shared a numberspace, or whatever you want to call
it (ie, a given ID number cannot belong to both a user and a group).
I think that implementing that would require at least a partial merge
of pg_shadow and pg_group --- unless you want to get into implementing
cross-table unique indexes.

If we agreed that they share a namespace as well, the merge could be
taken further.  Perhaps more usefully, the GRANT/REVOKE syntax and the
display format for ACL lists could be simplified, since there'd be no
need for a syntactic marker as to whether a given name is a user or a
group.

Not sure how many people would complain if they couldn't have a user and
a group of the same name.

			regards, tom lane

Tue, 10 Jun 2003 15:20:38 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Groups and roles 

It would be nice to merge them, but with Unix having separate
namespaces, I am not sure it is a good idea to diverge from that.

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

Tom Lane wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
> > Another issue is that users and roles share a namespace.  We might have to
> > deal with that sometime, but it's not a problem as far as the information
> > schema is concerned.
> 
> I've been thinking for awhile that the ACL code would be simplified if
> userids and groupids shared a numberspace, or whatever you want to call
> it (ie, a given ID number cannot belong to both a user and a group).
> I think that implementing that would require at least a partial merge
> of pg_shadow and pg_group --- unless you want to get into implementing
> cross-table unique indexes.
> 
> If we agreed that they share a namespace as well, the merge could be
> taken further.  Perhaps more usefully, the GRANT/REVOKE syntax and the
> display format for ACL lists could be simplified, since there'd be no
> need for a syntactic marker as to whether a given name is a user or a
> group.
> 
> Not sure how many people would complain if they couldn't have a user and
> a group of the same name.
> 
> 			regards, tom lane
> 
> ---------------------------(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, Pennsylvania 19073

Tue, 10 Jun 2003 15:23:18 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Groups and roles
> > 1. Do we want to someday allow groups to have groups as members?  (Seems
> > reasonable to me.)
>
> I agree.

I think the other requirement of roles is that they are able to own objects.
ie. we need to allow groups to own objects.

Chris


Mon, 16 Jun 2003 13:17:21 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: Groups and roles 
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> I think the other requirement of roles is that they are able to own objects.
> ie. we need to allow groups to own objects.

Hm.  That seems to be another reason to unify usesysid and grosysid into
a single unique something-id.  Which probably implies unifying pg_shadow
and pg_group into one table.

			regards, tom lane

Mon, 16 Jun 2003 01:24:42 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Groups and roles 
> "Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> > I think the other requirement of roles is that they are able to own
objects.
> > ie. we need to allow groups to own objects.
>
> Hm.  That seems to be another reason to unify usesysid and grosysid into
> a single unique something-id.  Which probably implies unifying pg_shadow
> and pg_group into one table.

Bear in mind that I recalled that from something I thought you (or someone)
said on the list earlier and I thought you had forgotten about...

eg. I don't know that that's a requirement...

Chris


Mon, 16 Jun 2003 13:55:28 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: Groups and roles 
Christopher Kings-Lynne wrote:

>>>1. Do we want to someday allow groups to have groups as members?  (Seems
>>>reasonable to me.)
>>>      
>>>
>>I agree.
>>    
>>
>
>I think the other requirement of roles is that they are able to own objects.
>ie. we need to allow groups to own objects.
>
>  
>
This would also solve the problem that information_schema views will 
show only owned objects. If objects are created as owned by a proper 
role, not only the creator will be able to use these views.

Regards,
Andreas



Mon, 16 Jun 2003 10:27:41 +0200
Andreas Pflug <Andreas.Pflug@web.de>
Re: Groups and roles
Oleg Bartunov writes:

> Just interesting if we could inplement some kind of RBAC
> (role based access control). Here is the reference:
> http://csrc.nist.gov/rbac/

Apparently the authors of the SQL standard have read this document,
because the role system in SQL looks exactly like what this document
specifies.  It's a nice reference to know that we're doing something
right.

-- 
Peter Eisentraut   peter_e@gmx.net


Wed, 18 Jun 2003 20:52:25 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: Groups and roles 
Tom Lane writes:

> Hm.  That seems to be another reason to unify usesysid and grosysid into
> a single unique something-id.  Which probably implies unifying pg_shadow
> and pg_group into one table.

Maybe this is too radical, but why not merge "user" and "group" into one
animal?  Both exist to bear privileges.  The only difference is that
groups can contain other bearers of privileges, but then a user is just a
special case with zero members.  Once you allow groups to have the
possibilities that users currently have (createdb privilege, object
ownership), there is no difference left.  Of course, one such "animal"
would be the session user and interact with pg_hba.conf, but that is just
an ID, which may as well be a group.

-- 
Peter Eisentraut   peter_e@gmx.net


Wed, 18 Jun 2003 20:53:50 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: Groups and roles 
Peter Eisentraut <peter_e@gmx.net> writes:
> Maybe this is too radical, but why not merge "user" and "group" into one
> animal?  Both exist to bear privileges.  The only difference is that
> groups can contain other bearers of privileges, but then a user is just a
> special case with zero members.  Once you allow groups to have the
> possibilities that users currently have (createdb privilege, object
> ownership), there is no difference left.  Of course, one such "animal"
> would be the session user and interact with pg_hba.conf, but that is just
> an ID, which may as well be a group.

We could make them essentially the same kind of thing, but include a
flag column in the table saying whether anyone is allowed to actually
log in as this entity.  If the flag isn't on, the entity can own tables
and have members and so forth, but it can never be the value of
session_user.

In some circumstances I could see it making sense to allow logging in
directly as a group/role/whatchacallit.  In other cases you might want
a policy that one must log in as oneself --- compare the common Unix
practice that you must 'su' to root, not log in directly as root.
So it seems to make sense to allow the login flag to be on or off
independently of whether the entity has members.

It sounds like a reasonable idea to me.  We could preserve backwards
compatibility for most client-side code by making pg_shadow and pg_group
be views of the merged table.

			regards, tom lane

Wed, 18 Jun 2003 17:06:06 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Groups and roles 