Re: fixing CREATEROLE

Robert Haas:
> It seems
> to me that the root of any fix in this area must be to change the rule
> that CREATEROLE can administer any role whatsoever.

Agreed.

> Instead, I propose
> to change things so that you can only administer roles for which you
> have ADMIN OPTION. [...] > I'm curious to hear what other people think of these proposals, [...]
> Third, someone could well have a better or just
> different idea how to fix the problems in this area than what I'm
> proposing here.

Once you can restrict CREATEROLE to only manage "your own" (no matter
how that is defined, e.g. via ADMIN or through some "ownership" concept)
roles, the possibility to "namespace" those roles somehow will become a
lot more important. For example in a multi-tenant setup in the same
cluster, where each tenant has their own database and admin user with a
restricted CREATEROLE privilege, it will very quickly be at least quite
annoying to have conflicts with other tenants' role names. I'm not sure
whether it could even be a serious problem, because I should still be
able to GRANT my own roles to other roles from other tenants - and that
could affect matching of +group records in pg_hba.conf?

My suggestion to $subject and the namespace problem would be to
introduce database-specific roles, i.e. add a database column to
pg_authid. Having this column set to 0 will make the role a cluster-wide
role ("cluster role") just as currently the case. But having a database
oid set will make the role exist in the context of that database only
("database role"). Then, the following principles should be enforced:

- database roles can not share the same name with a cluster role.
- database roles can have the same name as database roles in other
databases.
- database roles can not be members of database roles in **other**
databases.
- database roles with CREATEROLE can only create or alter database roles
in their own database, but not roles in other databases and also not
cluster roles.
- database roles with CREATEROLE can GRANT all database roles in the
same database, but only those cluster roles they have ADMIN privilege on.
- database roles with CREATEROLE can not set SUPERUSER.

To be able to create database roles with a cluster role, there needs to
be some syntax, e.g. something like

CREATE ROLE name IN DATABASE dbname ...

A database role with CREATEROLE should not need to use that syntax,
though - every CREATE ROLE should be IN DATABASE anyway.

With database roles, it would be possible to hand out CREATEROLE without
the ability to grant SUPERUSER or any of the built-in roles. It would be
much more useful on top of that, too. Not only is the namespace problem
mentioned above solved, but it would also be possible to let pg_dump
dump a whole database, including the database roles and their
memberships. This would allow dumping (and restoring) a single
tenant/application including the relevant roles and privileges - without
dumping all roles in the cluster. Plus, it's backwards compatible
because without creating database roles, everything stays exactly the same.

Best,

Wolfgang