Re: BUG #17626: Permission denied errors should list role as well as user

The following bug has been logged on the website:

Bug reference: 17626
Logged by: Loren Siebert
Email address: loren(at)paradigm(dot)xyz
PostgreSQL version: 14.5
Operating system: Debian
Description:

I'm running this set of commands:
create user elevated_user IN ROLE pg_read_all_data;
create user regular_user;
create table foo (id int);
set role elevated_user;
select * from foo; -- works
set role regular_user;
select * from foo; -- errors

The error message looks like "user=elevated_user ERROR: permission denied
for table foo".
In fact, that user does have permission to access the table. But the
regular_role it has just assumed does not.
I would suggest that the error message look like "user=elevated_user,
role=regular_user ERROR: permission denied for table foo"

PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> I'm running this set of commands:
> create user elevated_user IN ROLE pg_read_all_data;
> create user regular_user;
> create table foo (id int);
> set role elevated_user;
> select * from foo; -- works
> set role regular_user;
> select * from foo; -- errors

> The error message looks like "user=elevated_user ERROR: permission denied
> for table foo".

No ... it just looks like

regression=> select * from foo; -- errors
ERROR: permission denied for table foo

I don't know where "user=elevated_user" is coming from in your
environment, but it seems like it must be from client-side code
that's not tracking the active role correctly.

regards, tom lane

On Mon, Oct 3, 2022 at 6:51 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> > I'm running this set of commands:
> > create user elevated_user IN ROLE pg_read_all_data;
> > create user regular_user;
> > create table foo (id int);
> > set role elevated_user;
> > select * from foo; -- works
> > set role regular_user;
> > select * from foo; -- errors
>
> > The error message looks like "user=elevated_user ERROR: permission
> denied
> > for table foo".
>
> No ... it just looks like
>
> regression=> select * from foo; -- errors
> ERROR: permission denied for table foo
>

Based on the subject line the proper wording would be "the error message
should look some like ..."

Basically, a permission check requires two things and the complaint is that
only one of those things is mentioned in the error message.

David J.

"David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> Basically, a permission check requires two things and the complaint is that
> only one of those things is mentioned in the error message.

No, the complaint is that the wrong thing is shown --- but I don't
know what's showing it; aclcheck_error() certainly doesn't.

There's a separate conversation to be had perhaps about whether
aclcheck_error's standard message *should* include the role name
whose permissions were checked. I have a vague feeling that that
omission was intentional, but it was so long ago that I don't
recall for sure. It seems like something that'd be good to show
in more complicated situations with views, security definer
functions, etc.

regards, tom lane

I think what may be happening here is that the Postgres error and "permission
denied for table foo" message are getting caught by the ORM I am using
(Prisma) and it is prepending "user=elevated_user" to that but no role
information. That's probably the more appropriate place for me to take this
up.

Thank you Tom and David for looking into this and for your work on Postgres!

On Mon, Oct 3, 2022 at 8:53 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> > Basically, a permission check requires two things and the complaint is
> that
> > only one of those things is mentioned in the error message.
>
> No, the complaint is that the wrong thing is shown --- but I don't
> know what's showing it; aclcheck_error() certainly doesn't.
>
> There's a separate conversation to be had perhaps about whether
> aclcheck_error's standard message *should* include the role name
> whose permissions were checked. I have a vague feeling that that
> omission was intentional, but it was so long ago that I don't
> recall for sure. It seems like something that'd be good to show
> in more complicated situations with views, security definer
> functions, etc.
>
> regards, tom lane
>

On Mon, Oct 3, 2022, 08:53 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> > Basically, a permission check requires two things and the complaint is
> that
> > only one of those things is mentioned in the error message.
>
> No, the complaint is that the wrong thing is shown --- but I don't
> know what's showing it; aclcheck_error() certainly doesn't.
>
> There's a separate conversation to be had perhaps about whether
> aclcheck_error's standard message *should* include the role name
> whose permissions were checked. I have a vague feeling that that
> omission was intentional, but it was so long ago that I don't
> recall for sure. It seems like something that'd be good to show
> in more complicated situations with views, security definer
> functions, etc.
>
>

Replied too early, I see that now. There have been a couple of recent
discussions that have made me want to see what role PostgreSQL is
considering in cases like you mention that my mind just went there.

David J.