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

[Pietro Rossin]

Hello, I'm going crazy with this problems...

I'll report real names and statements

*Here is the user login pippo *
******************
CREATE ROLE pippo LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE
NOREPLICATION;
GRANT sitarpa_catasto_scarichi_editors_ud TO pippo;
GRANT sitarpa_viewers TO pippo;
********************

*Here is the db admin role group*
**************
CREATE ROLE sitarpa_admins NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE
NOREPLICATION;
GRANT sitarpa_basi_amministrative_admins TO sitarpa_admins;
GRANT sitarpa_catasto_scarichi_admins TO sitarpa_admins;
and some others....
*************

*Here the role group sitarpa_catasto_scarichi_editors_ud:*
***********************
CREATE ROLE sitarpa_catasto_scarichi_editors_ud
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
**********************

*Here the role group sitarpa_viewers:*
****************************
CREATE ROLE sitarpa_viewers
  NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
*********************

*Here is the first schema called basi_amministrative*

****************
CREATE SCHEMA basi_amministrative  AUTHORIZATION sitarpa_admins;

GRANT ALL ON SCHEMA basi_amministrative TO sitarpa_admins;
GRANT USAGE ON SCHEMA basi_amministrative TO
sitarpa_basi_amministrative_admins;
GRANT USAGE ON SCHEMA basi_amministrative TO
sitarpa_basi_amministrative_editors;
GRANT USAGE ON SCHEMA basi_amministrative TO
sitarpa_basi_amministrative_viewers;
GRANT USAGE ON SCHEMA basi_amministrative TO sitarpa_viewers;
GRANT USAGE ON SCHEMA basi_amministrative TO pippo;


ALTER DEFAULT PRIVILEGES IN SCHEMA basi_amministrative
    GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON
TABLES
    TO sitarpa_basi_amministrative_admins;

ALTER DEFAULT PRIVILEGES IN SCHEMA basi_amministrative
    GRANT INSERT, SELECT, UPDATE, DELETE, TRIGGER ON TABLES
    TO sitarpa_basi_amministrative_editors;

ALTER DEFAULT PRIVILEGES IN SCHEMA basi_amministrative
    GRANT SELECT ON TABLES
    TO sitarpa_basi_amministrative_viewers;

ALTER DEFAULT PRIVILEGES IN SCHEMA basi_amministrative
    GRANT USAGE ON SEQUENCES
    TO sitarpa_basi_amministrative_editors;
*******************

*Here is the schema catasto_scarichi:*
*****************************
CREATE SCHEMA catasto_scarichi   AUTHORIZATION sitarpa_admins;

GRANT ALL ON SCHEMA catasto_scarichi TO sitarpa_admins;
GRANT USAGE ON SCHEMA catasto_scarichi TO sitarpa_catasto_scarichi_admins;
GRANT USAGE ON SCHEMA catasto_scarichi TO sitarpa_catasto_scarichi_editors;
GRANT USAGE ON SCHEMA catasto_scarichi TO sitarpa_catasto_scarichi_viewers;
GRANT USAGE ON SCHEMA catasto_scarichi TO
sitarpa_catasto_scarichi_editors_ud;

ALTER DEFAULT PRIVILEGES IN SCHEMA catasto_scarichi
    GRANT SELECT ON TABLES
    TO sitarpa_catasto_scarichi_viewers;

ALTER DEFAULT PRIVILEGES IN SCHEMA catasto_scarichi
    GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON
TABLES
    TO sitarpa_catasto_scarichi_admins;

ALTER DEFAULT PRIVILEGES IN SCHEMA catasto_scarichi
    GRANT INSERT, SELECT, UPDATE, DELETE, TRIGGER ON TABLES
    TO sitarpa_catasto_scarichi_editors;

ALTER DEFAULT PRIVILEGES IN SCHEMA catasto_scarichi
    GRANT SELECT, UPDATE, USAGE ON SEQUENCES
    TO sitarpa_catasto_scarichi_admins;

ALTER DEFAULT PRIVILEGES IN SCHEMA catasto_scarichi
    GRANT USAGE ON SEQUENCES
    TO sitarpa_catasto_scarichi_editors;
***************************************


*Here is the first table in the problemathic view:*

*********************
CREATE TABLE basi_amministrative.comuni_fvg_2014_3004
(
  id serial NOT NULL,
  geom geometry(MultiPolygonZ,3004),
  nome character varying(255),
  cod_istat character varying(255),
  CONSTRAINT comuni_fvg_2014_3004_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE basi_amministrative.comuni_fvg_2014_3004  OWNER TO
sitarpa_basi_amministrative_admins;
GRANT ALL ON TABLE basi_amministrative.comuni_fvg_2014_3004 TO
sitarpa_basi_amministrative_admins;
GRANT SELECT, UPDATE, INSERT, DELETE, TRIGGER ON TABLE
basi_amministrative.comuni_fvg_2014_3004 TO
sitarpa_basi_amministrative_editors;
GRANT SELECT ON TABLE basi_amministrative.comuni_fvg_2014_3004 TO
sitarpa_basi_amministrative_viewers;
GRANT SELECT ON TABLE basi_amministrative.comuni_fvg_2014_3004 TO
sitarpa_viewers;
GRANT SELECT ON TABLE basi_amministrative.comuni_fvg_2014_3004 TO pippo;

-- Index: basi_amministrative.sidx_comuni_fvg_2014_3004_geom

-- DROP INDEX basi_amministrative.sidx_comuni_fvg_2014_3004_geom;

CREATE INDEX sidx_comuni_fvg_2014_3004_geom
  ON basi_amministrative.comuni_fvg_2014_3004
  USING gist
  (geom);
*************************

*Here is the second table in the view*

***************************
CREATE TABLE catasto_scarichi.a_punti_scarico_sfioratori
(
  id serial NOT NULL,
  geom geometry(Point,3004),
  id_impianto integer,
  tipo character varying(255) DEFAULT 1,
  scarico_su character varying(50) DEFAULT 5,
  cod_ci_sup character varying(50),
  cod_ci_sott character varying(50),
  bacino_elementare character varying(50),
  codice_lims character varying(20),
  posizione_corretta boolean DEFAULT false,
  codice_scarico character varying(50),
  cod_asta_reg character varying(50),
  note character varying(500),
  utente_inser character varying(50),
  utente_modif character varying(50),
  data_modif timestamp(0) without time zone,
  data_inser timestamp(0) without time zone,
  tipo_bacino_elem character varying(50),
  tipo_ci_superficiale character varying(50),
  CONSTRAINT a_punti_scarico_sfioratori_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE catasto_scarichi.a_punti_scarico_sfioratori
  OWNER TO sitarpa_catasto_scarichi_admins;
GRANT ALL ON TABLE catasto_scarichi.a_punti_scarico_sfioratori TO
sitarpa_catasto_scarichi_admins;
GRANT SELECT, UPDATE, INSERT, DELETE, TRIGGER ON TABLE
catasto_scarichi.a_punti_scarico_sfioratori TO
sitarpa_catasto_scarichi_editors;
GRANT SELECT ON TABLE catasto_scarichi.a_punti_scarico_sfioratori TO
sitarpa_catasto_scarichi_viewers;
GRANT SELECT ON TABLE catasto_scarichi.a_punti_scarico_sfioratori TO
sitarpa_catasto_scarichi_editors_ud;

-- Index: catasto_scarichi.sidx_a_punti_scarico_sfioratori_geom

-- DROP INDEX catasto_scarichi.sidx_a_punti_scarico_sfioratori_geom;

CREATE INDEX sidx_a_punti_scarico_sfioratori_geom
  ON catasto_scarichi.a_punti_scarico_sfioratori
  USING gist
  (geom);
***************************


*Here is the view...*
***********************
CREATE OR REPLACE VIEW catasto_scarichi.scarichi_comuni AS 
 SELECT a.id,
    b.nome,
    b.cod_istat,
    a.id_impianto,
    a.tipo,
    a.scarico_su,
    a.cod_ci_sup,
    a.cod_ci_sott,
    a.bacino_elementare,
    a.codice_lims,
    a.posizione_corretta,
    a.codice_scarico,
    a.utente_inser,
    a.data_inser,
    a.utente_modif,
    a.data_modif,
    a.cod_asta_reg
   FROM catasto_scarichi.a_punti_scarico_sfioratori a,
    basi_amministrative.comuni_fvg_2014_3004 b
  WHERE st_contains(b.geom, a.geom);

ALTER TABLE catasto_scarichi.scarichi_comuni
  OWNER TO sitarpa_catasto_scarichi_admins;
GRANT ALL ON TABLE catasto_scarichi.scarichi_comuni TO
sitarpa_catasto_scarichi_admins;
GRANT SELECT, UPDATE, INSERT, DELETE, TRIGGER ON TABLE
catasto_scarichi.scarichi_comuni TO sitarpa_catasto_scarichi_editors;
GRANT SELECT ON TABLE catasto_scarichi.scarichi_comuni TO
sitarpa_catasto_scarichi_viewers;
GRANT SELECT ON TABLE catasto_scarichi.scarichi_comuni TO
sitarpa_catasto_scarichi_editors_ud;
GRANT SELECT ON TABLE catasto_scarichi.scarichi_comuni TO pippo;
***********************


If I select it, both as pippo and as a member of sitarpa_admins, an error
rises saying:

ERROR: permission denied for relation comuni_fvg_2014_3004


If I open a query window and execute:
*******************
SELECT a.id,
    b.nome,
    b.cod_istat,
    a.id_impianto,
    a.tipo,
    a.scarico_su,
    a.cod_ci_sup,
    a.cod_ci_sott,
    a.bacino_elementare,
    a.codice_lims,
    a.posizione_corretta,
    a.codice_scarico,
    a.utente_inser,
    a.data_inser,
    a.utente_modif,
    a.data_modif,
    a.cod_asta_reg
   FROM catasto_scarichi.a_punti_scarico_sfioratori a,
    basi_amministrative.comuni_fvg_2014_3004 b
  WHERE st_contains(b.geom, a.geom);
********************

No error is given and data are retrieved, both for pippo and sitarpa_admins
member..


Where is my mistake??

Thanks!
pietro







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