public inbox for [email protected]
help / color / mirror / Atom feedFrom: Pavel Luzanov <[email protected]>
To: Robert Haas <[email protected]>
To: Dominique Devienne <[email protected]>
Cc: David G. Johnston <[email protected]>
Cc: Wolfgang Walther <[email protected]>
Cc: [email protected]
Subject: Re: Backward compat issue with v16 around ROLEs
Date: Fri, 13 Sep 2024 01:03:53 +0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <CA+TgmoZMqsg6-6qN_fuMZTGu=Vdyjv-u9ZgWbEnOTvRE450uvQ@mail.gmail.com>
References: <CAFCRh-8+PGGTuqg=rSKA533D0dqYAgq69UzSqMm67VEW02nZyQ@mail.gmail.com>
<CAKFQuwYK2Vdnbdaxh9QF_0PYpztg51nc-iqYeiKDfpzek7hTdQ@mail.gmail.com>
<CAFCRh-8ttK7AexZtZq-vcj+u5e2F93HEs63jrkEH0pq6Gf1TWw@mail.gmail.com>
<[email protected]>
<CAKFQuwZL96kB2mR4SG0=Hig21mwv5AhkxjZRGCYoqeYzPBv6Tw@mail.gmail.com>
<[email protected]>
<CAFCRh-_ZVP4emEKSGYJoM2hP657z1ZTq=UVkb7xZCYdByawFKQ@mail.gmail.com>
<CAFCRh-8d9+GZSXzK=UhJKOq+BTVt9eG0E4Zu6dALo-OaOpunYQ@mail.gmail.com>
<CA+TgmoZMqsg6-6qN_fuMZTGu=Vdyjv-u9ZgWbEnOTvRE450uvQ@mail.gmail.com>
On 13.09.2024 00:11, Robert Haas wrote:
> The prohibition against circular grants is really annoying in your use
> case. If dd_owner creates dd_user, then dd_user is granted to
> dd_owner, which means that dd_owner cannot be granted (directly or
> indirectly) to dd_user.
In search of workaround...
So, now in v16 we need a third role to made this grants.
There is a not very nice way to use the third role implicitly,
through security definer stored routines.
-- run by superuser
create role dd_owner createrole;
CREATE ROLE
create role dd_admin noinherit;
CREATE ROLE
grant dd_owner to dd_admin;
GRANT ROLE
create procedure create_role (role text, member regrole)
language plpgsql security definer as $$
begin
execute (format('create role %I in role %I', role, member));
end;
$$;
CREATE PROCEDURE
revoke execute on procedure create_role from public;
REVOKE
grant execute on procedure create_role to dd_owner;
GRANT
set role dd_owner;
SET
call create_role('dd_user', 'dd_admin');
CALL
\du dd*
List of roles
Role name | Attributes
-----------+------------------------------
dd_admin | No inheritance, Cannot login
dd_owner | Create role, Cannot login
dd_user | Cannot login
\drg
List of role grants
Role name | Member of | Options | Grantor
-----------+-----------+--------------+----------
dd_admin | dd_owner | SET | postgres
dd_user | dd_admin | INHERIT, SET | postgres
(2 rows)
Ido notknowhowapplicablethisis for Dominique.Perhapsabettersolution is to review andmakechangesto roles&grants systemby explicitlyintroducingand using a thirdrole.
--
Pavel Luzanov
Postgres Professional:https://postgrespro.com
view thread (15+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Backward compat issue with v16 around ROLEs
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox