What do you mean:<div><br></div><div><font size="2"><span style="background-color:rgba(255,255,255,0)">If I select it, both as pippo and as a member of sitarpa_admins, an error<br>rises saying:<br><br>ERROR: permission denied for relation comuni_fvg_2014_3004</span></font><br><br>How do you "select" it? </div><div><br></div><div>Cheers</div><div><br></div><div>Ben <span></span><br><br><br>On Monday, 16 May 2016, Pietro Rossin <<a href="mailto:pietro.rossin@arpa.fvg.it">pietro.rossin@arpa.fvg.it</a>> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Hello, I'm going crazy with this problems...<br>
<br>
I'll report real names and statements<br>
<br>
*Here is the user login pippo *<br>
******************<br>
CREATE ROLE pippo LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE<br>
NOREPLICATION;<br>
GRANT sitarpa_catasto_scarichi_editors_ud TO pippo;<br>
GRANT sitarpa_viewers TO pippo;<br>
********************<br>
<br>
*Here is the db admin role group*<br>
**************<br>
CREATE ROLE sitarpa_admins NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE<br>
NOREPLICATION;<br>
GRANT sitarpa_basi_amministrative_admins TO sitarpa_admins;<br>
GRANT sitarpa_catasto_scarichi_admins TO sitarpa_admins;<br>
and some others....<br>
*************<br>
<br>
*Here the role group sitarpa_catasto_scarichi_editors_ud:*<br>
***********************<br>
CREATE ROLE sitarpa_catasto_scarichi_editors_ud<br>
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;<br>
**********************<br>
<br>
*Here the role group sitarpa_viewers:*<br>
****************************<br>
CREATE ROLE sitarpa_viewers<br>
NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;<br>
*********************<br>
<br>
*Here is the first schema called basi_amministrative*<br>
<br>
****************<br>
CREATE SCHEMA basi_amministrative AUTHORIZATION sitarpa_admins;<br>
<br>
GRANT ALL ON SCHEMA basi_amministrative TO sitarpa_admins;<br>
GRANT USAGE ON SCHEMA basi_amministrative TO<br>
sitarpa_basi_amministrative_admins;<br>
GRANT USAGE ON SCHEMA basi_amministrative TO<br>
sitarpa_basi_amministrative_editors;<br>
GRANT USAGE ON SCHEMA basi_amministrative TO<br>
sitarpa_basi_amministrative_viewers;<br>
GRANT USAGE ON SCHEMA basi_amministrative TO sitarpa_viewers;<br>
GRANT USAGE ON SCHEMA basi_amministrative TO pippo;<br>
<br>
<br>
ALTER DEFAULT PRIVILEGES IN SCHEMA basi_amministrative<br>
GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON<br>
TABLES<br>
TO sitarpa_basi_amministrative_admins;<br>
<br>
ALTER DEFAULT PRIVILEGES IN SCHEMA basi_amministrative<br>
GRANT INSERT, SELECT, UPDATE, DELETE, TRIGGER ON TABLES<br>
TO sitarpa_basi_amministrative_editors;<br>
<br>
ALTER DEFAULT PRIVILEGES IN SCHEMA basi_amministrative<br>
GRANT SELECT ON TABLES<br>
TO sitarpa_basi_amministrative_viewers;<br>
<br>
ALTER DEFAULT PRIVILEGES IN SCHEMA basi_amministrative<br>
GRANT USAGE ON SEQUENCES<br>
TO sitarpa_basi_amministrative_editors;<br>
*******************<br>
<br>
*Here is the schema catasto_scarichi:*<br>
*****************************<br>
CREATE SCHEMA catasto_scarichi AUTHORIZATION sitarpa_admins;<br>
<br>
GRANT ALL ON SCHEMA catasto_scarichi TO sitarpa_admins;<br>
GRANT USAGE ON SCHEMA catasto_scarichi TO sitarpa_catasto_scarichi_admins;<br>
GRANT USAGE ON SCHEMA catasto_scarichi TO sitarpa_catasto_scarichi_editors;<br>
GRANT USAGE ON SCHEMA catasto_scarichi TO sitarpa_catasto_scarichi_viewers;<br>
GRANT USAGE ON SCHEMA catasto_scarichi TO<br>
sitarpa_catasto_scarichi_editors_ud;<br>
<br>
ALTER DEFAULT PRIVILEGES IN SCHEMA catasto_scarichi<br>
GRANT SELECT ON TABLES<br>
TO sitarpa_catasto_scarichi_viewers;<br>
<br>
ALTER DEFAULT PRIVILEGES IN SCHEMA catasto_scarichi<br>
GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON<br>
TABLES<br>
TO sitarpa_catasto_scarichi_admins;<br>
<br>
ALTER DEFAULT PRIVILEGES IN SCHEMA catasto_scarichi<br>
GRANT INSERT, SELECT, UPDATE, DELETE, TRIGGER ON TABLES<br>
TO sitarpa_catasto_scarichi_editors;<br>
<br>
ALTER DEFAULT PRIVILEGES IN SCHEMA catasto_scarichi<br>
GRANT SELECT, UPDATE, USAGE ON SEQUENCES<br>
TO sitarpa_catasto_scarichi_admins;<br>
<br>
ALTER DEFAULT PRIVILEGES IN SCHEMA catasto_scarichi<br>
GRANT USAGE ON SEQUENCES<br>
TO sitarpa_catasto_scarichi_editors;<br>
***************************************<br>
<br>
<br>
*Here is the first table in the problemathic view:*<br>
<br>
*********************<br>
CREATE TABLE basi_amministrative.comuni_fvg_2014_3004<br>
(<br>
id serial NOT NULL,<br>
geom geometry(MultiPolygonZ,3004),<br>
nome character varying(255),<br>
cod_istat character varying(255),<br>
CONSTRAINT comuni_fvg_2014_3004_pkey PRIMARY KEY (id)<br>
)<br>
WITH (<br>
OIDS=FALSE<br>
);<br>
ALTER TABLE basi_amministrative.comuni_fvg_2014_3004 OWNER TO<br>
sitarpa_basi_amministrative_admins;<br>
GRANT ALL ON TABLE basi_amministrative.comuni_fvg_2014_3004 TO<br>
sitarpa_basi_amministrative_admins;<br>
GRANT SELECT, UPDATE, INSERT, DELETE, TRIGGER ON TABLE<br>
basi_amministrative.comuni_fvg_2014_3004 TO<br>
sitarpa_basi_amministrative_editors;<br>
GRANT SELECT ON TABLE basi_amministrative.comuni_fvg_2014_3004 TO<br>
sitarpa_basi_amministrative_viewers;<br>
GRANT SELECT ON TABLE basi_amministrative.comuni_fvg_2014_3004 TO<br>
sitarpa_viewers;<br>
GRANT SELECT ON TABLE basi_amministrative.comuni_fvg_2014_3004 TO pippo;<br>
<br>
-- Index: basi_amministrative.sidx_comuni_fvg_2014_3004_geom<br>
<br>
-- DROP INDEX basi_amministrative.sidx_comuni_fvg_2014_3004_geom;<br>
<br>
CREATE INDEX sidx_comuni_fvg_2014_3004_geom<br>
ON basi_amministrative.comuni_fvg_2014_3004<br>
USING gist<br>
(geom);<br>
*************************<br>
<br>
*Here is the second table in the view*<br>
<br>
***************************<br>
CREATE TABLE catasto_scarichi.a_punti_scarico_sfioratori<br>
(<br>
id serial NOT NULL,<br>
geom geometry(Point,3004),<br>
id_impianto integer,<br>
tipo character varying(255) DEFAULT 1,<br>
scarico_su character varying(50) DEFAULT 5,<br>
cod_ci_sup character varying(50),<br>
cod_ci_sott character varying(50),<br>
bacino_elementare character varying(50),<br>
codice_lims character varying(20),<br>
posizione_corretta boolean DEFAULT false,<br>
codice_scarico character varying(50),<br>
cod_asta_reg character varying(50),<br>
note character varying(500),<br>
utente_inser character varying(50),<br>
utente_modif character varying(50),<br>
data_modif timestamp(0) without time zone,<br>
data_inser timestamp(0) without time zone,<br>
tipo_bacino_elem character varying(50),<br>
tipo_ci_superficiale character varying(50),<br>
CONSTRAINT a_punti_scarico_sfioratori_pkey PRIMARY KEY (id)<br>
)<br>
WITH (<br>
OIDS=FALSE<br>
);<br>
ALTER TABLE catasto_scarichi.a_punti_scarico_sfioratori<br>
OWNER TO sitarpa_catasto_scarichi_admins;<br>
GRANT ALL ON TABLE catasto_scarichi.a_punti_scarico_sfioratori TO<br>
sitarpa_catasto_scarichi_admins;<br>
GRANT SELECT, UPDATE, INSERT, DELETE, TRIGGER ON TABLE<br>
catasto_scarichi.a_punti_scarico_sfioratori TO<br>
sitarpa_catasto_scarichi_editors;<br>
GRANT SELECT ON TABLE catasto_scarichi.a_punti_scarico_sfioratori TO<br>
sitarpa_catasto_scarichi_viewers;<br>
GRANT SELECT ON TABLE catasto_scarichi.a_punti_scarico_sfioratori TO<br>
sitarpa_catasto_scarichi_editors_ud;<br>
<br>
-- Index: catasto_scarichi.sidx_a_punti_scarico_sfioratori_geom<br>
<br>
-- DROP INDEX catasto_scarichi.sidx_a_punti_scarico_sfioratori_geom;<br>
<br>
CREATE INDEX sidx_a_punti_scarico_sfioratori_geom<br>
ON catasto_scarichi.a_punti_scarico_sfioratori<br>
USING gist<br>
(geom);<br>
***************************<br>
<br>
<br>
*Here is the view...*<br>
***********************<br>
CREATE OR REPLACE VIEW catasto_scarichi.scarichi_comuni AS<br>
SELECT <a href="http://a.id" target="_blank">a.id</a>,<br>
b.nome,<br>
b.cod_istat,<br>
a.id_impianto,<br>
a.tipo,<br>
a.scarico_su,<br>
a.cod_ci_sup,<br>
a.cod_ci_sott,<br>
a.bacino_elementare,<br>
a.codice_lims,<br>
a.posizione_corretta,<br>
a.codice_scarico,<br>
a.utente_inser,<br>
a.data_inser,<br>
a.utente_modif,<br>
a.data_modif,<br>
a.cod_asta_reg<br>
FROM catasto_scarichi.a_punti_scarico_sfioratori a,<br>
basi_amministrative.comuni_fvg_2014_3004 b<br>
WHERE st_contains(b.geom, a.geom);<br>
<br>
ALTER TABLE catasto_scarichi.scarichi_comuni<br>
OWNER TO sitarpa_catasto_scarichi_admins;<br>
GRANT ALL ON TABLE catasto_scarichi.scarichi_comuni TO<br>
sitarpa_catasto_scarichi_admins;<br>
GRANT SELECT, UPDATE, INSERT, DELETE, TRIGGER ON TABLE<br>
catasto_scarichi.scarichi_comuni TO sitarpa_catasto_scarichi_editors;<br>
GRANT SELECT ON TABLE catasto_scarichi.scarichi_comuni TO<br>
sitarpa_catasto_scarichi_viewers;<br>
GRANT SELECT ON TABLE catasto_scarichi.scarichi_comuni TO<br>
sitarpa_catasto_scarichi_editors_ud;<br>
GRANT SELECT ON TABLE catasto_scarichi.scarichi_comuni TO pippo;<br>
***********************<br>
<br>
<br>
If I select it, both as pippo and as a member of sitarpa_admins, an error<br>
rises saying:<br>
<br>
ERROR: permission denied for relation comuni_fvg_2014_3004<br>
<br>
<br>
If I open a query window and execute:<br>
*******************<br>
SELECT <a href="http://a.id" target="_blank">a.id</a>,<br>
b.nome,<br>
b.cod_istat,<br>
a.id_impianto,<br>
a.tipo,<br>
a.scarico_su,<br>
a.cod_ci_sup,<br>
a.cod_ci_sott,<br>
a.bacino_elementare,<br>
a.codice_lims,<br>
a.posizione_corretta,<br>
a.codice_scarico,<br>
a.utente_inser,<br>
a.data_inser,<br>
a.utente_modif,<br>
a.data_modif,<br>
a.cod_asta_reg<br>
FROM catasto_scarichi.a_punti_scarico_sfioratori a,<br>
basi_amministrative.comuni_fvg_2014_3004 b<br>
WHERE st_contains(b.geom, a.geom);<br>
********************<br>
<br>
No error is given and data are retrieved, both for pippo and sitarpa_admins<br>
member..<br>
<br>
<br>
Where is my mistake??<br>
<br>
Thanks!<br>
pietro<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
--<br>
View this message in context: <a href="http://postgis.17.x6.nabble.com/Query-View-permission-deny-strange-behaviour-tp5010068p5010075.html" target="_blank">http://postgis.17.x6.nabble.com/Query-View-permission-deny-strange-behaviour-tp5010068p5010075.html</a><br>
Sent from the PostGIS - User mailing list archive at Nabble.com.<br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="javascript:;" onclick="_e(event, 'cvml', 'postgis-users@lists.osgeo.org')">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/mailman/listinfo/postgis-users</a></blockquote></div><br><br>-- <br>Sent from my iPhone.<br>