BUG #4893: Grants on inherited tables checks before constraint exclusion see, that table not needed

From: "Vladimir Lavrentiev" <vyorick(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #4893: Grants on inherited tables checks before constraint exclusion see, that table not needed
Date: 2009-06-29 06:58:59
Message-ID: 200906290658.n5T6wxWr000229@wwwmaster.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs


The following bug has been logged online:

Bug reference: 4893
Logged by: Vladimir Lavrentiev
Email address: vyorick(at)gmail(dot)com
PostgreSQL version: 8.3.7-0, 8.4rc1
Operating system: Linux 2.6.27-11-generic x86_64 GNU/Linux
Description: Grants on inherited tables checks before constraint
exclusion see, that table not needed
Details:

connect as postgres

create table test(projectId bigint not NULL);
create table test1() inherits(test);
create table test2() inherits(test);
create table test3() inherits(test);

create index indx_test1 on test1(projectId);
create index indx_test2 on test2(projectId);
create index indx_test3 on test3(projectId);

alter table test1 add constraint check1 check (projectId=1);
alter table test2 add constraint check2 check (projectId=2);
alter table test3 add constraint check3 check (projectId=3);

revoke all on test1 from PUBLIC;
revoke all on test2 from PUBLIC;
revoke all on test3 from PUBLIC;

create user asdf encrypted password 'test';

grant select on test to asdf;
grant select on test2 to asdf;

explain analyse select * from test where projectId=2;

Result (cost=0.00..51.76 rows=22 width=8) (actual time=0.180..0.180 rows=0
loops=1)
-> Append (cost=0.00..51.76 rows=22 width=8) (actual time=0.178..0.178
rows=0 loops=1)
-> Seq Scan on test (cost=0.00..36.75 rows=11 width=8) (actual
time=0.002..0.002 rows=0 loops=1)
Filter: (projectid = 2)
-> Bitmap Heap Scan on test2 test (cost=4.34..15.01 rows=11
width=8) (actual time=0.175..0.175 rows=0 loops=1)
Recheck Cond: (projectid = 2)
-> Bitmap Index Scan on indx_test2 (cost=0.00..4.33 rows=11
width=0) (actual time=0.170..0.170 rows=0 loops=
1)
Index Cond: (projectid = 2)
Total runtime: 0.258 ms
(9 rows)

*****************************************************
All OK, in query plan used only tables test and test2;
*****************************************************
connect as asdf

explain analyse select * from test where projectId=2;
ERROR: permission denied for relation test1

Why postgres looking grants on test1 ?

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Craig Ringer 2009-06-29 11:04:49 Re: BUG #4890: Allow insert character has no equivalent in "LATIN2"
Previous Message Tom Lane 2009-06-28 23:48:38 Re: BUG #4892: postmaster died and didn't restart