[postgis-users] Query/View permission deny - strange behaviour
Ben Madin
ben at ausvet.com.au
Tue May 17 14:44:56 PDT 2016
What do you mean:
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
How do you "select" it?
Cheers
Ben
On Monday, 16 May 2016, Pietro Rossin <pietro.rossin at arpa.fvg.it> wrote:
> 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.
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org <javascript:;>
> http://lists.osgeo.org/mailman/listinfo/postgis-users
--
Sent from my iPhone.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20160518/0a63874d/attachment.html>
More information about the postgis-users
mailing list