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@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
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; -- errorsThe 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:
Show quoted text
"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
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.