Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group