Thread: BUG #17626: Permission denied errors should list role as well as user
BUG #17626: Permission denied errors should list role as well as user
From
PG Bug reporting form
Date:
The following bug has been logged on the website: Bug reference: 17626 Logged by: Loren Siebert Email address: loren@paradigm.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@postgresql.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
Re: BUG #17626: Permission denied errors should list role as well as user
From
"David G. Johnston"
Date:
On Mon, Oct 3, 2022 at 6:51 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
PG Bug reporting form <noreply@postgresql.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.g.johnston@gmail.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@sss.pgh.pa.us> wrote:
"David G. Johnston" <david.g.johnston@gmail.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
Re: BUG #17626: Permission denied errors should list role as well as user
From
"David G. Johnston"
Date:
On Mon, Oct 3, 2022, 08:53 Tom Lane <tgl@sss.pgh.pa.us> wrote:
"David G. Johnston" <david.g.johnston@gmail.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.