"David G. Johnston" <[email protected]> writes:
> On Tue, Mar 10, 2015 at 8:20 AM, Andrzej Pilacik <[email protected]> wrote:
>
> I see the issues that this person might be having. I am not doing a
> restore or working on an existing issue.
>
> My setup is very vanilla, anyone can create these tables and test, will
> get the same permission error... Â (I did it in a brand new environment)
>
> What I am asking here is why does a SUPERUSER not able to insert the data
> when the constraint is introduced.
>
> âTo prevent a super-user from shooting themselves in the
> foot. Begin allowed to insert otherwise invalid data is not
> something that the database allows a superuser to do. A
> superuser inserting a NULL into a NOT NULL column will also get an
> error. This is no different.
No, tha'ts not what this discussion is about... A superuser is
prohibited even from a valid insert if table owner is defficient in
whatever grants needed to validate the constraint.
Below demonstrates this issue which is apparently long-standing and well
enough known though I haven't run across it myself so rolled this test
case to have a look-see.
sj$ cat q
-- be a super user here
begin;
select version();
create role foo;
create schema authorization foo;
set role foo;
create table foo.referenced(a int primary key);
create table public.referring(like foo.referenced,
foreign key (a) references foo.referenced);
reset role;
select rolsuper from pg_authid where rolname = current_user;
insert into foo.referenced select 1;
insert into public.referring select 1;
revoke all on schema foo from foo;
insert into public.referring select 1;
-- FAIL
sj$ psql -ef q
begin;
BEGIN
select version();
version
----------------------------------------------------------------------------------------------
PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.7.2-5)
4.7.2, 64-bit
(1 row)
create role foo;
CREATE ROLE
create schema authorization foo;
CREATE SCHEMA
set role foo;
SET
create table foo.referenced(a int primary key);
CREATE TABLE
create table public.referring(like foo.referenced,
foreign key (a) references foo.referenced);
CREATE TABLE
reset role;
RESET
select rolsuper from pg_authid where rolname = current_user;
rolsuper
----------
t
(1 row)
insert into foo.referenced select 1;
INSERT 0 1
insert into public.referring select 1;
INSERT 0 1
revoke all on schema foo from foo;
REVOKE
insert into public.referring select 1;
psql:q:23: ERROR: permission denied for schema foo
LINE 1: SELECT 1 FROM ONLY "foo"."referenced" x WHERE "a" OPERATOR(p...
^
QUERY: SELECT 1 FROM ONLY "foo"."referenced" x WHERE "a"
OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x
HTH
>
> David J.
> â
> Â
>
--
Jerry Sievers
Postgres DBA/Development Consulting
e: [email protected]
p: 312.241.7800
--
Sent via pgsql-general mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
