[postgis-users] Query/View permission deny - strange behaviour

[Pietro Rossin]

Hello
I have problems with postgres/postgis query/view permissions.

Postgresql 9.4 Postgis 2.1

I created a user pippo inherit.

I created a group catasto_editors inherit and a group basi_viewers.

I granted catasto_editors to pippo and basi_viewers to catasto_editors.

Then two schemas basi and catasto

GRANT USAGE ON SCHEMA catasto TO catasto_editors;
GRANT USAGE ON SCHEMA basi TO basi_viewers;

All tables/views in catasto are selectable/editable/updateable ecc ecc by
catasto_editors.
All tables/views in basi are selectable by basi_viewers.


Then I made a view in catasto

***********
CREATE OR REPLACE VIEW catasto.points_in_polygons AS 
 SELECT a.id,
    b.name,
    b.code,
    a.id_impianto,
    a.......
    FROM catasto.points a,
    basi.polygons b
  WHERE st_contains(b.geom, a.geom);

ALTER TABLE catasto.points_in_polygons   OWNER TO catasto_admins;
GRANT ALL ON TABLE catasto.points_in_polygons TO catasto_admins;
GRANT SELECT, UPDATE, INSERT, DELETE, TRIGGER ON TABLE
catasto.points_in_polygons TO catasto_editors;
********************

This doesn't work, an error occurs saying "ERROR: permission denied for
relation polygons SQL state: 42501"

Strange thing, if I execute:

**********
SELECT a.id,
    b.name,
    b.code,
    a.id_impianto,
    a.......
    FROM catasto.points a,
    basi.polygons b
  WHERE st_contains(b.geom, a.geom);
***********

the query itself works...

Also if I execute
*****
SELECT count(id)
  FROM basi.polygons;
*****

I get the item count..

Where is my mistake?

Thanks
Pietro









--
View this message in context: http://postgis.17.x6.nabble.com/Query-View-permission-deny-strange-behaviour-tp5010068.html
Sent from the PostGIS - User mailing list archive at Nabble.com.