Re: [PATCHES] Merge pg_shadow && pg_group -- UNTESTED

Stephen Frost <sfrost(at)snowman(dot)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