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

The following bug has been logged online:

Bug reference:      4893
Logged by:          Vladimir Lavrentiev
Email address:      vyorick@gmail.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 ?

"Vladimir Lavrentiev" <vyorick@gmail.com> writes:
> Description:        Grants on inherited tables checks before constraint
> exclusion see, that table not needed

This is not a bug.  Sorry, but that's just the way it works: you need
suitable permission on every table referenced by the query.  Constraint
exclusion is just an optimization, it does not change the semantics;
in particular it doesn't change permissions requirements.

            regards, tom lane