Re: fixing CREATEROLE

On Tue, Nov 22, 2022 at 3:02 AM <walther(at)technowledgy(dot)de> wrote:
> 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 ...

I have three comments on this:

1. It's a good idea and might make for some interesting followup work.

2. There are some serious implementation challenges because the
constraints on duplicate object names must be something which can be
enforced by unique constraints on the relevant catalogs. Off-hand, I
don't see how to do that. It would be easy to make the cluster roles
all have unique names, and it would be easy to make the database roles
have unique names within each database, but I have no idea how you
would keep a database role from having the same name as a cluster
role. For anyone to try to implement this, we'd need to have a
solution to that problem.

3. I don't want to sidetrack this thread into talking about possible
future features or followup work. There is enough to do just getting
consensus on the design ideas that I proposed without addressing the
question of what else we might do later. I do not think there is any
reasonable argument that we can't clean up the CREATEROLE mess without
also implementing database-specific roles, and I have no intention of
including that in this patch series. Whether I or someone else might
work on it in the future is a question we can leave for another day.

--
Robert Haas
EDB: http://www.enterprisedb.com