Re: fixing CREATEROLE - Mailing list pgsql-hackers

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