Horton

Re: fixing CREATEROLE

David G. Johnston <david.g.johnston@gmail.com>

View thread View on postgr.es
From: "David G. Johnston" <david.g.johnston@gmail.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Mark Dilger <mark.dilger@enterprisedb.com>, Tom Lane <tgl@sss.pgh.pa.us>, "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Date: 2022-11-23T20:59:31Z
Lists: pgsql-hackers 
On Wed, Nov 23, 2022 at 1:04 PM Robert Haas <robertmhaas@gmail.com> wrote:

>
> I'm not very certain about any of that stuff; I don't have a clear
> mental model of how it should work, or even what exact problem we're
> trying to solve. To me, the patches that I posted make sense as far as
> they go, but I'm not under the illusion that they solve all the
> problems in this area, or even that I understand what all of the
> problems are.
>
>
I haven't yet formed a complete thought here but is there any reason we
cannot convert the permission-like attributes to predefined roles?

pg_login
pg_replication
pg_bypassrls
pg_createdb
pg_createrole
pg_haspassword (password and valid until)
pg_hasconnlimit

Presently, attributes are never inherited, but having that be controlled
via the INHERIT property of the grant seems desirable.

WITH ADMIN controls passing on of membership to other roles.

Example:
I have pg_createrole (set, noinherit, no with admin), pg_password (no set,
inherit, no with admin), and pg_createdb (set, inherit, with admin),
pg_login (no set, inherit, with admin)
Roles I create cannot be members of pg_createrole or pg_password but can be
given pg_createdb and pg_login (this would be a way to enforce external
authentication for roles created by me)
I can execute CREATE DATABASE due to inheriting pg_createdb
I must set role to pg_createrole in order to execute CREATE ROLE
Since I don't have admin on pg_createrole I cannot change my own
set/inherit, but I could do that for pg_createdb

David J.

Commits

  1. Add new GUC createrole_self_grant.

  2. Restrict the privileges of CREATEROLE users.

  3. Pass down current user ID to AddRoleMems and DelRoleMems.

  4. Refactor permissions-checking for role grants.

  5. Improve documentation of the CREATEROLE attibute.

  6. Make role grant system more consistent with other privileges.