Thread: Switching to NOINHERIT user triggers #XX000 error

Hi. Still working on porting our stuff to v16+, with the ROLE changes.

I'm facing a new issue. The same code and unit test works in v14,
when the LOGIN user running the test is INHERIT or NOINHERIT.

On v16 and v17 though, it works with INHERIT, but with NOINHERIT, I get:

GRANT "...:USER" TO "ddevienne" #XX000: ERROR:  no possible grantors

Any clue to what this means, so I can start looking in the right place?

In all 3 (or 6) cases, the code is the same, the client-side is
running libpq 17,
it's only the server that changes. The LOGIN user is the same, with the same
pg_roles attributes, with again the variable being INHERIT or not. --DD

Dominique Devienne <ddevienne@gmail.com> writes:
> On v16 and v17 though, it works with INHERIT, but with NOINHERIT, I get:
> GRANT "...:USER" TO "ddevienne" #XX000: ERROR:  no possible grantors
> Any clue to what this means, so I can start looking in the right place?

\errverbose in psql would help you locate what part of the code
is throwing that.  (Or if triggering it from psql is not convenient,
set log_error_verbosity = verbose and find the info in the postmaster
log.)

This is probably a bug, at least to the extent that we don't like
XX000 errors to be easily reachable, so please let us know what
you find out.

            regards, tom lane

On Tue, Nov 5, 2024 at 6:42 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Dominique Devienne <ddevienne@gmail.com> writes:
> > On v16 and v17 though, it works with INHERIT, but with NOINHERIT, I get:
> > GRANT "...:USER" TO "ddevienne" #XX000: ERROR:  no possible grantors
> > Any clue to what this means, so I can start looking in the right place?
>
> \errverbose in psql would help you locate what part of the code
> is throwing that.  (Or if triggering it from psql is not convenient,
> set log_error_verbosity = verbose and find the info in the postmaster
> log.)
>
> This is probably a bug, at least to the extent that we don't like
> XX000 errors to be easily reachable, so please let us know what
> you find out.

Hi Tom.

Finding where the error is thrown is easy, it's when I'm doing that grant.
But the code creates two larguish schemas before that and several ROLEs,
so it's not easy to share.

I trapped the error in the code, and here's the details

ERROR:  XX000: no possible grantors
LOCATION:  check_role_grantor, user.c:2229

I used PQresultVerboseErrorMessage().
Tell me if you need some other error field
This is against a 17.0 server, for the line number above.

If I shared (privately) a libpq client-side trace with you Tom, would
that help you?

This particular test, I could redesign to work-around this issue probably.
But this is not the only tests that fails with a NOINHERIT user on v16+,
and I haven't tracked down all the reasons yet...

PS: I can't easily get server-side logs

Dominique Devienne <ddevienne@gmail.com> writes:
> On Tue, Nov 5, 2024 at 6:42 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> This is probably a bug, at least to the extent that we don't like
>> XX000 errors to be easily reachable, so please let us know what
>> you find out.

> Finding where the error is thrown is easy, it's when I'm doing that grant.
> But the code creates two larguish schemas before that and several ROLEs,
> so it's not easy to share.

Really, we need a self-contained example case to do anything
about this.  Maybe you could anonymize the object names in
your test?

            regards, tom lane

On Tue, Nov 5, 2024 at 7:25 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Dominique Devienne <ddevienne@gmail.com> writes:
> > On Tue, Nov 5, 2024 at 6:42 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> This is probably a bug, at least to the extent that we don't like
> >> XX000 errors to be easily reachable, so please let us know what
> >> you find out.
>
> > Finding where the error is thrown is easy, it's when I'm doing that grant.
> > But the code creates two larguish schemas before that and several ROLEs,
> > so it's not easy to share.
>
> Really, we need a self-contained example case to do anything
> about this.  Maybe you could anonymize the object names in
> your test?

I understand, thanks. I might be able to do that. We'll see. --DD