MIME-Version: 1.0
References: <CAFCRh--AXoYUj8-WDuWpUcWXC0UNAL9gjbTp=1hU-NJhRyR0vQ@mail.gmail.com>
 <fa68fc72-e109-452e-8642-2b99c613f870@aklaver.com> <CAFCRh-9AzsOBd6cPFsgmbw=Mf3nN5tHj9YYQQHZG0XqxDSMK=Q@mail.gmail.com>
 <508f71c4-f1b1-4685-921d-bec8b361be10@aklaver.com> <662792ed-810d-46f1-a0c3-d4b55e5469fc@aklaver.com>
 <CAFCRh-8D+R=xufTFYN8SDDeEVwDNCb7kcspt9hsRW2T-QOMoKg@mail.gmail.com>
 <693d1252-89e4-498d-a5a6-5de6524bbb34@dalibo.com> <CAFCRh--tSWRRCMvtSovtRDX1wce5KCOutaDRBD5JKWb9atLC_w@mail.gmail.com>
 <2071880.1753971113@sss.pgh.pa.us>
In-Reply-To: <2071880.1753971113@sss.pgh.pa.us>
From: Dominique Devienne <ddevienne@gmail.com>
Date: Thu, 31 Jul 2025 17:59:42 +0200
Message-ID: <CAFCRh-_+=xT4oj2NgVd8+n_hECFqkKzHTRbsito89P=o8oG4TA@mail.gmail.com>
Subject: Re: SET LOCAL ROLE inside SECURITY INVOKER (LANGUAGE plpgsql) function
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Guillaume Lelarge <guillaume.lelarge@dalibo.com>, 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-_%2B%3DxT4oj2NgVd8%2Bn_hECFqkKzHTRbsito89P%3Do8oG4TA%40mail.gmail.com>
Precedence: bulk

On Thu, Jul 31, 2025 at 4:11=E2=80=AFPM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Dominique Devienne <ddevienne@gmail.com> writes:
> > On Thu, Jul 31, 2025 at 11:35=E2=80=AFAM Guillaume Lelarge
> > <guillaume.lelarge@dalibo.com> wrote:
> >> It doesn't lie. The role has DELETE privilege. I guess what it lacks i=
s
> >> the SELECT privilege. If you do a "DELETE FROM ... WHERE ...", you nee=
d
> >> the SELECT privilege to perform the WHERE. Without "WHERE ...", it wou=
ld
> >> work without the SELECT privilege.
>
> > Right on the money! Merci Guillaume!!! --DD
>
> So the real problem here is that the "permission denied" error message
> doesn't tell you which permission you lack.  I think we've had prior
> discussions about improving that, but it never got done.

Indeed, a hint would have helped I'm sure.

But also, it's weird DELETE allows you to delete all rows.
Yet prevents you from deleting just one, i.e. a subset.
I get it, a WHERE needs to read, so needs SELECT.
Still, it obviously tripped me up here. And it's my bad.