From: Tom Lane <tgl@sss.pgh.pa.us>
To: Dominique Devienne <ddevienne@gmail.com>
cc: Guillaume Lelarge <guillaume.lelarge@dalibo.com>,
        pgsql-general@lists.postgresql.org
Subject: Re: SET LOCAL ROLE inside SECURITY INVOKER (LANGUAGE plpgsql) function
In-reply-to: <CAFCRh--tSWRRCMvtSovtRDX1wce5KCOutaDRBD5JKWb9atLC_w@mail.gmail.com>
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>
Comments: In-reply-to Dominique Devienne <ddevienne@gmail.com>
	message dated "Thu, 31 Jul 2025 13:37:37 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <2071879.1753971113.1@sss.pgh.pa.us>
Content-Transfer-Encoding: 8bit
Date: Thu, 31 Jul 2025 10:11:53 -0400
Message-ID: <2071880.1753971113@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/2071880.1753971113%40sss.pgh.pa.us>
Precedence: bulk

Dominique Devienne <ddevienne@gmail.com> writes:
> On Thu, Jul 31, 2025 at 11:35 AM Guillaume Lelarge
> <guillaume.lelarge@dalibo.com> wrote:
>> It doesn't lie. The role has DELETE privilege. I guess what it lacks is
>> the SELECT privilege. If you do a "DELETE FROM ... WHERE ...", you need
>> the SELECT privilege to perform the WHERE. Without "WHERE ...", it would
>> 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.  In some
cases it's a bit complicated -- for example, here either whole-table
SELECT or SELECT on the specific column would do.  But even pointing
you in the direction of "you need SELECT" would likely have led to
the correct line of thought, so maybe giving 100% of the details
isn't necessary.

			regards, tom lane