MIME-Version: 1.0
References: <CAFCRh--7cj8WjA1173R4XD+Qvf6SWROq5iN5JB_ux32JYH9gMA@mail.gmail.com>
 <1b6d736c-3046-4b8f-a636-eaa323be7eb4@technowledgy.de>
In-Reply-To: <1b6d736c-3046-4b8f-a636-eaa323be7eb4@technowledgy.de>
From: Dominique Devienne <ddevienne@gmail.com>
Date: Mon, 14 Apr 2025 13:08:20 +0200
Message-ID: <CAFCRh-9kH4qWD5Nq_HJrJjd75XNcwvFJFztTK3ypRYo7kD7Z0w@mail.gmail.com>
Subject: Re: Event-Triggers for DB owners instead of just SUPERUSER
To: Wolfgang Walther <walther@technowledgy.de>, tgl@sss.pgh.pa.us
Cc: pgsql-general@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAFCRh-9kH4qWD5Nq_HJrJjd75XNcwvFJFztTK3ypRYo7kD7Z0w%40mail.gmail.com>
Precedence: bulk

On Mon, Apr 14, 2025 at 12:09=E2=80=AFPM Wolfgang Walther
<walther@technowledgy.de> wrote:
> > From Dominique Devienne:
> > Can't event-triggers also be available to DB owners, instead of just SU=
PERUSER?
>
> There's a recent -hackers thread exactly about this:
> https://www.postgresql.org/message-id/flat/CAGRrpzbtYDkg7_xwfzrqByYgCJQbb=
L38tADyuN%2B6tAkbA-Pnkg%40mail.gmail.com

Hi. Thanks for the pointer Wolfgang.

I've perused that thread, and Tom seems pretty against it, bummer...
(see extract below).

From Tom:
> Here is the real problem: database owners are not specially
> privileged in Postgres. Yeah, they can drop their DB, but they
> don't have automatic permissions to mess with other people's
> objects inside the DB. (Much the same can be said of schema
> owners.) So any proposal that effectively gives DB owners
> such privileges is going to fail. I realize that some other
> DBMSes assign more privileges to schema or DB owners, but we
> don't and I don't think we're open to changing that.

Tom, I don't want to mess with other people's data.
I want to track DDL changes in the schemas the "app" manages
in that DB, for auditing reasons. I don't want the DB owner to
do DML changes, except to the tables it can INSERT into,
as usual, per the normal access rules.

And the DB Owner will NOT have SUPERUSER.
Not even the ROLE that created the DB or its owner.

> I think you need to be thinking of this in terms of "what sort
> of feature can we add that can be allowed to any SQL user?"
> The notion I proposed earlier that an event trigger only fires
> on queries executed by roles the trigger's owner belongs to
> is (AFAICS) safe to allow to anyone. If that's not good enough
> for your notion of what a DB owner should be able to do, the
> answer is to grant the DB owner membership in every role that
> uses her database. That's effectively what the feature you're
> suggesting would do anyway.

In my case, the DB Owner is not a MEMBER per-se (no set_option
or inherit_option), BUT it will be the one who did create the ROLEs
that will own the DB objects (SCHEMAs and TABLEs and co.),
so it will have admin_option.

So if admin_option is "good enough" to generate ROLE cycles,
with the mess that created in "my app" against v16+, then it should
also be "good enough" to fire event triggers on too.

Like someone on that thread, I'm not sure to see where the privilege
escalation is coming from. It sounds as-if the event trigger executes
with SUPERUSER. But does it? In my case, the trigger would execute
as the DB owner, based on the SECURITY DEFINER of the trigger proc,
and that ROLE can't write (or mess) nilly willy in the normal access rules.
So where's the problem exactly?

In practice, as I wrote, in my case, the DB Owner is the one which
created all ROLEs that owns any of the SCHEMAs, so it could mess
with those, since it has ADMIN OPTION on all those ROLEs. But I
don't see how that's any different for event-triggers compared
to non-trigger SQL.

I'm sure my view is naive, from not knowing PostgreSQL enough.
But that's why I'm asking questions here, to get educated. And
presenting my use-cases, to inform future devs. Thanks, --DD