Merge pg_shadow && pg_group -- UNTESTED

Hi Stephen and Hackers,

Moved to -hackers.

Here's a proof-of-concept pretty much untested (it compiles) patch
against HEAD for review of the general approach I'm taking to
merging pg_shadow and pg_group. This is in order to support group
ownership and eventually roles.

I have to disagree with your model. Roles are not so simple like you
try to describe in your patch. I'm suposing this because your using
role* in all of the 'pg_shadow'.
What's Role? A set of relations with their respective privileges and
a set of users and/or roles.

Sometime ago, I drafted a model I think it can be useful. Here it is:

Another catalog relation named 'pg_role' with the following members:
- rolsysid (role id)
- rolname (role name)
- rolowner (role owner)
- rolmembs[] (list of users that belong to the role)
- rolrels[] (list of relations + their permissions)
- hasroles (have dependent roles?)

where:

rolmembs[] is:
- userid (user id or group id or role id)

rolrels[] is:
- relid (relation oid)
- rs_privs (privileges)

What do we do with 'groups'? Well, we can have three categories of
object owners: users, groups and roles. So the 'group owner' can be
implemented with this model.

What about dependent roles? It will be in 'pg_depend'.

Advantages:
1. Don't require changing the actual catalog model. Just an increment.
2. Can't introduce too much overhead. Once roles are in another catalog
table, we need to search it only if it's required.
3. All serious commercial databases have it. And of course, PostgreSQL
community want it too. :-)

Disadvantages:
1. Some overhead when checking for roles and dependent roles.

Comments and/or ideas?

=====
Euler Taveira de Oliveira
euler[at]yahoo_com_br

__________________________________________________
Converse com seus amigos em tempo real com o Yahoo! Messenger
http://br.download.yahoo.com/messenger/

* Euler Taveira de Oliveira (eulerto@yahoo.com.br) wrote:

Here's a proof-of-concept pretty much untested (it compiles) patch
against HEAD for review of the general approach I'm taking to
merging pg_shadow and pg_group. This is in order to support group
ownership and eventually roles.

I have to disagree with your model. Roles are not so simple like you
try to describe in your patch. I'm suposing this because your using
role* in all of the 'pg_shadow'.

The particular name isn't really important- and don't take it to mean
very much...

What's Role? A set of relations with their respective privileges and
a set of users and/or roles.

That's a good question- I'm not really very familiar with roles. :) I'm
honestly more interested in group ownership...

Advantages:
1. Don't require changing the actual catalog model. Just an increment.

I'm not sure what the value of this is..

2. Can't introduce too much overhead. Once roles are in another catalog
table, we need to search it only if it's required.

ok.

3. All serious commercial databases have it. And of course, PostgreSQL
community want it too. :-)

Well, yes, we want roles, we're discussing implementations though, and I
don't see this as an 'advantage' of your approach. :)

Disadvantages:
1. Some overhead when checking for roles and dependent roles.

It was Tom's suggestion that pg_shadow and pg_group be merged to
guarntee unique in the 'id's, which needs to be there unless you want to
change pg_object (iirc? whatever table it is) to handle additional
information about what kind of 'id' it is (role, user or group).

Stephen

Hi Stephen,

I have to disagree with your model. Roles are not so simple like

you

try to describe in your patch. I'm suposing this because your using
role* in all of the 'pg_shadow'.

The particular name isn't really important- and don't take it to mean
very much...

OK. So let it 'use*'.

What's Role? A set of relations with their respective privileges

and

a set of users and/or roles.

That's a good question- I'm not really very familiar with roles. :)
I'm
honestly more interested in group ownership...

OK. Thinking better, keep up your work. I'm going to keep my eyes on
it.

=====
Euler Taveira de Oliveira
euler[at]yahoo_com_br

_______________________________________________________
Yahoo! Acesso Gr�tis - Instale o discador do Yahoo! agora. http://br.acesso.yahoo.com/ - Internet r�pida e gr�tis

Stephen Frost <sfrost@snowman.net> writes:

Here's a proof-of-concept pretty much untested (it compiles) patch
against HEAD for review of the general approach I'm taking to
merging pg_shadow and pg_group. This is in order to support group
ownership and eventually roles. This patch includes my grammar and
get_grosysid move patches, and so conflicts with them.

One point is that you can't simply whack pg_shadow around and eliminate
pg_group, because that will break lord-knows-how-much client software
that looks at these tables. What I'm envisioning is to create a new
system catalog (say pg_role) that holds the New Truth, and then make
pg_shadow and pg_group be predefined views on this catalog that provide
as much backwards compatibility as we can manage.

I believe this was done once before already --- I think that the pg_user
view exists to emulate a prior incarnation of pg_shadow.

A related point is that I hope soon to get rid of type AclId and
usesysid/grosysid/rolesysid and start identifying roles by Oids.
This is connected to Alvaro's work to create proper dependencies
for object owners and privilege entries: once that exists and you
can't drop a referenced role, there will be no need to allow explicit
setting of the SYSID for a new user. Not sure if you want to do any
of the associated changes in your patch, but if int4 is bugging you
then feel free to change it.

regards, tom lane

Euler Taveira de Oliveira <eulerto@yahoo.com.br> writes:

What's Role? A set of relations with their respective privileges and
a set of users and/or roles.

Huh? How did relations get into it?

What do we do with 'groups'? Well, we can have three categories of
object owners: users, groups and roles. So the 'group owner' can be
implemented with this model.

Why wouldn't we fold all three into one concept? In particular, I
really fail to see why we'd still keep a separate notion of groups.

regards, tom lane

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

Stephen Frost <sfrost@snowman.net> writes:

Here's a proof-of-concept pretty much untested (it compiles) patch
against HEAD for review of the general approach I'm taking to
merging pg_shadow and pg_group. This is in order to support group
ownership and eventually roles. This patch includes my grammar and
get_grosysid move patches, and so conflicts with them.

One point is that you can't simply whack pg_shadow around and eliminate
pg_group, because that will break lord-knows-how-much client software
that looks at these tables. What I'm envisioning is to create a new
system catalog (say pg_role) that holds the New Truth, and then make
pg_shadow and pg_group be predefined views on this catalog that provide
as much backwards compatibility as we can manage.

Ok. Can I get some help defining what the New Truth will look like
then? I understand users and groups pretty well but I'm not 100% sure
about roles. Is it as simple as what my changed pg_shadow looks like?
What's the difference between a role, a user and a group? Can a role
log in/have a password? Can a role own an object? If a role owns an
object, can any users who have that role {drop, create index, etc} it?

Once we get the layout of pg_role defined I think I'll be able to make
much better progress towards what you're looking for. :)

A related point is that I hope soon to get rid of type AclId and
usesysid/grosysid/rolesysid and start identifying roles by Oids.

Alright. That doesn't sound too bad.

This is connected to Alvaro's work to create proper dependencies
for object owners and privilege entries: once that exists and you
can't drop a referenced role, there will be no need to allow explicit
setting of the SYSID for a new user. Not sure if you want to do any
of the associated changes in your patch, but if int4 is bugging you
then feel free to change it.

Ok, I probably will. Should I be concerned with trying to make
'smallish' patches that build upon each other (ie: change to pg_role
first, then change AclId to Oid, or whatever) or will one larger patch
that takes care of it all be ok?

Thanks,

Stephen

Stephen Frost <sfrost@snowman.net> writes:

Ok. Can I get some help defining what the New Truth will look like
then? I understand users and groups pretty well but I'm not 100% sure
about roles.

I looked through SQL99 a bit (see 4.31 "Basic security model") and think
I now have some handle on this. According to the spec a "role" is
more or less exactly what we think of as a "group", with the extension
that roles can have other roles as members (barring circularity).
In particular the spec draws a distinction between "user identifiers"
and "role identifiers", although this distinction seems very nearly 100%
useless because the two sorts of identifiers can be used almost
interchangeably (an "authorization identifier" means either one, and in
most places "authorization identifier" is what is relevant). AFAICT the
only really solid reason for the distinction is that you have to log in
initially as a user and not as a role. That strikes me as a security
policy --- it's analogous to saying you can't log in directly as root
but have to su to root from your personal login --- which may be a good
thing for a given site to enforce but IMHO it should not be hard-wired
into the security mechanism.

The implementation reason for not having a hard distinction is mainly
that we want to have a single unique-identifier space for both users and
roles. This simplifies representation of ACLs (which will no longer
need extra bits to identify whether an entry references a user or a
group) and allows us to have groups as members of other groups without
messy complication there.

It's not entirely clear to me whether the spec allows roles to be
directly owners of objects, but I think we should allow it.

So I'm envisioning something like

CREATE TABLE pg_role (
rolname name, -- name of role
rolsuper boolean, -- superuser?
rolcreateuser boolean, -- can create more users?
rolcreatedb boolean, -- can create databases?
rolcatupdate boolean, -- can hack system catalogs?
rolcanlogin boolean, -- can log in as this role?
rolvaliduntil timestamptz, -- password
rolpassword text, -- password expiration time
rolmembers oid[], -- OIDs of members, if any
roladmin boolean[], -- do members have ADMIN OPTION
rolconfig text[] -- ALTER USER SET guc = value
) WITH OIDS;

Some notes:

It might be better to call this by some name other than "pg_role",
since what it defines is not exactly roles in the sense that SQL99
uses; but I don't have a good idea what to use instead.
"pg_authorization" would work but it's unwieldy.

OIDs of rows in this table replace AclIds.

I'm supposing that we should separate "superuserness" from "can create
users" (presumably a non-superuser with rolcreateuser would only be
allowed to create non-super users). The lack of distinction on this
point has been a conceptual problem for newbies for a long time, and an
admin issue too. As long as we are hacking this table we should fix it.

If you want to enforce a hard distinction between users and roles (groups)
then you'd prohibit rolcanlogin from being true when rolmembers is
nonempty, but as said above I'm not sure the system should enforce that.

rolpassword, rolvaliduntil, and rolconfig are irrelevant if not rolcanlogin.

The roladmin[] bool array indicates whether members were granted
admission WITH ADMIN OPTION, which means they can grant membership to
others (analogous to WITH GRANT OPTION for individual privileges).
I'm not sure this is sufficient ... we may need to record who granted
membership to each member as well, in order to process revocation.

It might be better to lose the rolmembers/roladmin columns and instead
represent membership in a separate table, roughly

CREATE TABLE pg_role_members (
role oid,
member oid,
grantor oid,
admin_option bool,
primary key (role, member, grantor)
);

This is cleaner from a relational theory point of view but is probably
harder for the system to process. One advantage is that it is easier to
find out "which roles does user X belong to?" ... but I'm not sure we
care about making that fast.

One thing that needs to be thought about before going too far is exactly
how ACL rights testing will work, particularly in the face of roles
being members of other roles. That is the one performance-critical
operation that uses these data structures, so we ought to design around
making it fast.

Ok, I probably will. Should I be concerned with trying to make
'smallish' patches that build upon each other (ie: change to pg_role
first, then change AclId to Oid, or whatever) or will one larger patch
that takes care of it all be ok?

Smaller patches are easier to review, for sure. Also, you'll need to
coordinate with Alvaro's work on dependencies for global objects.

regards, tom lane

Stephan,

On Sun, Jan 23, 2005 at 03:14:04PM -0500, Tom Lane wrote:

Smaller patches are easier to review, for sure. Also, you'll need to
coordinate with Alvaro's work on dependencies for global objects.

If you want, I can send you the current patch so you can see what has
changed in it, maybe merge it with some work of yours for separate
submittal.

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"Siempre hay que alimentar a los dioses, aunque la tierra est� seca" (Orual)

* Alvaro Herrera (alvherre@dcc.uchile.cl) wrote:

On Sun, Jan 23, 2005 at 03:14:04PM -0500, Tom Lane wrote:

Smaller patches are easier to review, for sure. Also, you'll need to
coordinate with Alvaro's work on dependencies for global objects.

If you want, I can send you the current patch so you can see what has
changed in it, maybe merge it with some work of yours for separate
submittal.

Yeah, I'd appriciate seeing it and seeing if/how much it conflicts with
what I'm working on. I'll probably be going back to scratch since there
isn't really anything worthwhile in my patch to build on working towards
what Tom's laid out. Not a problem though, it didn't take me very long
to code. :)

Thanks,

Stephen

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

Stephen Frost <sfrost@snowman.net> writes:

Ok. Can I get some help defining what the New Truth will look like
then? I understand users and groups pretty well but I'm not 100% sure
about roles.

I looked through SQL99 a bit (see 4.31 "Basic security model") and think

Ah, I was looking through SQL2003 recently, I don't think much has
changed in that area though.

I now have some handle on this. According to the spec a "role" is
more or less exactly what we think of as a "group", with the extension
that roles can have other roles as members (barring circularity).

Right, ok.

In particular the spec draws a distinction between "user identifiers"
and "role identifiers", although this distinction seems very nearly 100%
useless because the two sorts of identifiers can be used almost
interchangeably (an "authorization identifier" means either one, and in
most places "authorization identifier" is what is relevant). AFAICT the
only really solid reason for the distinction is that you have to log in
initially as a user and not as a role. That strikes me as a security
policy --- it's analogous to saying you can't log in directly as root
but have to su to root from your personal login --- which may be a good
thing for a given site to enforce but IMHO it should not be hard-wired
into the security mechanism.

Ok, I agree, though personally I don't like the idea of permitting
role-logins, but no need to have the security system force it.

The implementation reason for not having a hard distinction is mainly
that we want to have a single unique-identifier space for both users and
roles. This simplifies representation of ACLs (which will no longer
need extra bits to identify whether an entry references a user or a
group) and allows us to have groups as members of other groups without
messy complication there.

The other difference would seem to be that "user identifiers" can't be
granted to users whereas "role identifiers" can be. Following this,
"rolmembers" must be NULL if rolcanlogin is true, no? That breaks if
roles can log in though. Or should we just allow granting of "user
identifiers" to other users- but if we do should the user be permitted
to do that?

It's not entirely clear to me whether the spec allows roles to be
directly owners of objects, but I think we should allow it.

I agree, and in fact group/role ownership is what I'm specifically
interested in, though I'd like role support too and so I'm happy to
implement it along the way. :)

So I'm envisioning something like

CREATE TABLE pg_role (
rolname name, -- name of role
rolsuper boolean, -- superuser?
rolcreateuser boolean, -- can create more users?
rolcreatedb boolean, -- can create databases?
rolcatupdate boolean, -- can hack system catalogs?
rolcanlogin boolean, -- can log in as this role?
rolvaliduntil timestamptz, -- password
rolpassword text, -- password expiration time
rolmembers oid[], -- OIDs of members, if any
roladmin boolean[], -- do members have ADMIN OPTION
rolconfig text[] -- ALTER USER SET guc = value
) WITH OIDS;

It might be better to call this by some name other than "pg_role",
since what it defines is not exactly roles in the sense that SQL99
uses; but I don't have a good idea what to use instead.
"pg_authorization" would work but it's unwieldy.

Hmmm, I agree pg_role isn't quite right. pg_auth would be shorter than
pg_authorization, but it isn't intuitive what it is. How about
pg_ident? It's not users or roles, but it's identifiers. Perhaps
pg_authid?

OIDs of rows in this table replace AclIds.

ok.

I'm supposing that we should separate "superuserness" from "can create
users" (presumably a non-superuser with rolcreateuser would only be
allowed to create non-super users). The lack of distinction on this
point has been a conceptual problem for newbies for a long time, and an
admin issue too. As long as we are hacking this table we should fix it.

Agreed.

If you want to enforce a hard distinction between users and roles (groups)
then you'd prohibit rolcanlogin from being true when rolmembers is
nonempty, but as said above I'm not sure the system should enforce that.

Right, but there's still the issue of granting "users" to users.

rolpassword, rolvaliduntil, and rolconfig are irrelevant if not rolcanlogin.

Right.

The roladmin[] bool array indicates whether members were granted
admission WITH ADMIN OPTION, which means they can grant membership to
others (analogous to WITH GRANT OPTION for individual privileges).
I'm not sure this is sufficient ... we may need to record who granted
membership to each member as well, in order to process revocation.

I think we'll probably need to record who granted membership too, to
prevent circulation as well as revocation processing..

It might be better to lose the rolmembers/roladmin columns and instead
represent membership in a separate table, roughly

CREATE TABLE pg_role_members (
role oid,
member oid,
grantor oid,
admin_option bool,
primary key (role, member, grantor)
);

This is cleaner from a relational theory point of view but is probably
harder for the system to process. One advantage is that it is easier to
find out "which roles does user X belong to?" ... but I'm not sure we
care about making that fast.

I like this approach more.

One thing that needs to be thought about before going too far is exactly
how ACL rights testing will work, particularly in the face of roles
being members of other roles. That is the one performance-critical
operation that uses these data structures, so we ought to design around
making it fast.

I agree that it should be fast- but I think it should be possible to
implement it in such a way that if you don't make roles members of other
roles then you won't pay a performance penelty for the fact that we
support that ability. If you use it, it'll be a bit more expensive to
check permissions where you do.

Ok, I probably will. Should I be concerned with trying to make
'smallish' patches that build upon each other (ie: change to pg_role
first, then change AclId to Oid, or whatever) or will one larger patch
that takes care of it all be ok?

Smaller patches are easier to review, for sure. Also, you'll need to
coordinate with Alvaro's work on dependencies for global objects.

Right, ok. I'll look at what Alvaro's got and think about the approach
and milestones/patches to get from where we're at now to what we want.

Stephen

Stephen Frost wrote:

The other difference would seem to be that "user identifiers" can't
be granted to users whereas "role identifiers" can be. Following
this, "rolmembers" must be NULL if rolcanlogin is true, no? That
breaks if roles can log in though. Or should we just allow granting
of "user identifiers" to other users- but if we do should the user be
permitted to do that?

If he has admin option on his own role, sure. But I suppose by default
we wouldn't.

One use case I see is if someone goes on vacation he can temporarily
grant the privileges held by his user account to others without
actually giving out the login data.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

On Sun, Jan 23, 2005 at 15:14:04 -0500,
Tom Lane <tgl@sss.pgh.pa.us> wrote:

It's not entirely clear to me whether the spec allows roles to be
directly owners of objects, but I think we should allow it.

I aggree with this. This can simplify maintainance as members of a group
come and go.

* Peter Eisentraut (peter_e@gmx.net) wrote:

If he has admin option on his own role, sure. But I suppose by default
we wouldn't.

One use case I see is if someone goes on vacation he can temporarily
grant the privileges held by his user account to others without
actually giving out the login data.

Alright. I've thought about this some more and I think I agree with it.
A user doesn't implicitly have all rights on his own oid, but I guess
that wasn't ever really the case anyway (can't give himself superuser
rights, etc). I'll begin working on this soon (possibly as soon as
Thursday evening) unless someone else has comments on it.

Thanks,

Stephen

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

Stephen Frost <sfrost@snowman.net> writes:

Ok. Can I get some help defining what the New Truth will look like
then? I understand users and groups pretty well but I'm not 100% sure
about roles.

So I'm envisioning something like

[...]

It might be better to call this by some name other than "pg_role",

[...]

It might be better to lose the rolmembers/roladmin columns and instead
represent membership in a separate table, roughly

[...]

One thing that needs to be thought about before going too far is exactly
how ACL rights testing will work, particularly in the face of roles
being members of other roles. That is the one performance-critical
operation that uses these data structures, so we ought to design around
making it fast.

Alright, here's a patch against head which adds in the tables pg_authid
and pg_auth_members as described in your previous mail. I've gotten a
bit farther than this in terms of implementation but here's something
for people to comment on, if they'd like to.

I've been thinking about the performance issues some and have to admit
that I havn't really come to much of a solution. It seems to me that
there's two ways to come at the issue:

a) start from the user:
Search for useroid in pg_auth_members.member
For each returned role, search for that role in member column
Repeat until all roles the useroid is in have been found
[Note: This could possibly be done and stored per-user on connection,
but it would mean we'd have to have a mechanism to update it when
necessary, possibly instigated by the user, or just force them to
reconnect ala unix group membership]
Look through ACL list to see if the useroid has permission or if any
of the roles found do.

b) start from the ACL list:
Search for each roleoid in pg_auth_members.role
For each returned member, search for that member in role column
Upon member == useroid match is found check for permission, if
granted then stop, otherwise continue processing
Has the advantage that the search stops once it's been determined
that permission is there and doesn't require updating.

If we do the user-part-of-which-roles search upon connection I expect
'a' would be quite fast, obviously it has it's drawbacks though. If we
feel that's not acceptable then I'm thinking 'b' would probably be
faster given that the ACL list is probably generally small and we can
short-circuit. I'm afraid 'b' might still be too slow though, comments?
thoughts? better ideas?

Thanks,

Stephen

a) start from the user:
Search for useroid in pg_auth_members.member
For each returned role, search for that role in member column
Repeat until all roles the useroid is in have been found
[Note: This could possibly be done and stored per-user on
connection,
but it would mean we'd have to have a mechanism to update it when
necessary, possibly instigated by the user, or just force them to
reconnect ala unix group membership]
Look through ACL list to see if the useroid has permission
or if any
of the roles found do.

b) start from the ACL list:
Search for each roleoid in pg_auth_members.role
For each returned member, search for that member in role column
Upon member == useroid match is found check for permission, if
granted then stop, otherwise continue processing
Has the advantage that the search stops once it's been determined
that permission is there and doesn't require updating.

If I may humbly suggest another option:

c) Use tables for users, roles, and user x role as you already have
(Or was a user's roles in an array? I forget)
Add a fourth table (access?) with the PK (user, priv, role).
Whenever a privilege is granted or revoked, for a user or a role,
insert or delete the appropriate rows in the access table.
This pre-loads all of the cost of maintaining the ACL and should
reduce the effort of checking a particular privilege to an index
seek.

With this method, a user can be granted a privilege by more than one role,
and if they are removed from one of those roles, the other still grants
the privilege. The access table can also store the privileges that each
role has by storing the role ID in the user ID column.

I know that it makes for a potentially huge table, but it makes the model
straightforward and reliable.

Examples:

Grant role 'foo' privilege 'bar':
INSERT INTO access (user, priv, role ) VALUES ( 'foo', 'bar', 'foo' );

Grant user 'baz' role 'foo':
INSERT INTO access ( user, priv, role )
SELECT 'baz', priv, role FROM access WHERE user = 'foo';

Remove user 'baz' from role 'foo':
DELETE FROM access WHERE user = 'baz' AND role = 'foo';

Remove privilege 'bar' from role 'foo':
DELETE FROM access WHERE priv = 'bar' AND role = 'foo';
-- Note that this automatically cleaned up all of the users, too.

Grant privilege 'bar' to user 'baz' without a role involved:
INSERT INTO access ( user, priv, role ) VALUES ( 'baz', 'bar', 'baz' );

Grant user 'postgres' privilege 'su' in a hard-to-revoke way:
INSERT INTO access ( user, priv, role ) VALUES ( 'postgres', 'su', '' );

Check to see if user 'baz' has privilege 'bar':
SELECT user, priv, role FROM access WHERE user = 'baz' AND priv = 'bar';
-- This even tells you the role(s) that grant the privilege.

Inheritance from role to role can even be handled by repeating the inserts
or deletes with appropriate roles. (This would even allow a role to inherit
a privilege from multiple parent roles, and work correctly if it is revoked
by one.)

Stephen Frost <sfrost@snowman.net> writes:

I've been thinking about the performance issues some and have to admit
that I havn't really come to much of a solution. It seems to me that
there's two ways to come at the issue:

a) start from the user:
...
b) start from the ACL list:
...

The current ACL-checking code does (b), relying on a function in_group()
that tests whether the target userid is a member of a given group.
I would suggest preserving this basic structure if only to avoid breaking
things unintentionally. However, you could think about caching the set
of groups that a user belongs to, thus combining the best features of
both (a) and (b). It's always bothered me that in_group() seemed like a
fairly expensive operation.

[Note: This could possibly be done and stored per-user on connection,
but it would mean we'd have to have a mechanism to update it when
necessary, possibly instigated by the user, or just force them to
reconnect ala unix group membership]

No, we'd drive it off syscache invalidation. Any change in pg_auth_members
would cause us to just discard the whole membership cache. This sort of
mechanism is already in use in a couple places (schema search list
maintenance is one example IIRC --- look at namespace.c).

regards, tom lane