public inbox for [email protected]  

help / color / mirror / Atom feed

From: Robert Haas <[email protected]>
To: Laurenz Albe <[email protected]>
Cc: Dominique Devienne <[email protected]>
Cc: [email protected]
Subject: Re: Why no pg_has_role(..., 'ADMIN')?
Date: Fri, 20 Sep 2024 12:51:01 -0400
Message-ID: <CA+TgmobvCGAHPZxX1rNgUb1cGcmD5e8ESGVSL=OyVqDYCAV3EQ@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAFCRh-8JNEy+dV4SXFOrWca50u+d=--TO4cq=+ac1oBtfJy4AA@mail.gmail.com>
	<[email protected]>

On Fri, Sep 20, 2024 at 12:37 PM Laurenz Albe <[email protected]> wrote:
> > But knowing whether DROP ROLE will work,
> > w/o invalidating the current transaction,
> > seems like something quite useful to know now, no?
> >
> > I can query pg_auth_members for admin_option,
> > but only easily for direct membership. Taking into
> > account indirect membership, which I assume applies,
> > is exactly why pg_has_role() exists, no?
>
> That would be a useful addition, yes.

I think this already exists. The full list of modes supported by
pg_has_role() is listed in convert_role_priv_string(). You can do
something like pg_has_role('alice', 'USAGE WITH ADMIN OPTION'). This
is not new: it worked in older releases too, but AFAIK it's never been
mentioned in the documentation.

However, the precise rule for DROP ROLE in v16+ is not just that you
need to have ADMIN OPTION on the role. The rule is:

1. You must have ADMIN OPTION on the target role.
2. You must also have CREATEROLE.
3. If the target role is SUPERUSER, you must be SUPERUSER.

If I'm not wrong, pg_has_role(..., 'USAGE WITH ADMIN OPTION') will
test #1 for you, but not #2 or #3.

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

view thread (8+ messages)  latest in thread

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]
  Subject: Re: Why no pg_has_role(..., 'ADMIN')?
  In-Reply-To: <CA+TgmobvCGAHPZxX1rNgUb1cGcmD5e8ESGVSL=OyVqDYCAV3EQ@mail.gmail.com>

* 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