Thread: [BUGS] permissions denial to superuser with foreign keys
I thought superusers bypassed permissions checks, but was surprised where in this case they do not:
create user test;
create schema test;
grant all on SCHEMA test to test;
\c postgres test
create table a (x serial primary key);
create table b (x int references a(x) );
insert into a values (1);
\c postgres postgres
revoke all on SCHEMA test from test;
delete from test.a;
ERROR: permission denied for schema test
LINE 1: SELECT 1 FROM ONLY "test"."a" x WHERE "x" OPERATOR(pg_catalo...
^
QUERY: SELECT 1 FROM ONLY "test"."a" x WHERE "x" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x
So apparently this is because the bypass is based on the euid, not ruid, at least in that part of the code.
Is this the way it is supposed to be? It seems like a bad idea to make the superuser jump through a bunch of hoops to do what he wants, it is just more places where a mistake might be made.
This does not seem to be version-specific.
Cheers,
Jeff
Jeff Janes <jeff.janes@gmail.com> writes:
> I thought superusers bypassed permissions checks, but was surprised where
> in this case they do not:
This amounts to asking for superuser permissions to propagate into
SECURITY DEFINER functions called by the superuser, which strikes me
as an utterly horrid idea.
(RI triggers aren't actually implemented with the SECURITY DEFINER
mechanism, but they act like they are.)
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs