Thread: Problem whith table views.
Hi,
We have a problem here : we crate 2 tables, and for each of them there is a
view for selecting (almost) all fields of the table. Both tables are
owned by "dba", and noone else has rights on them.
Both views are GRANTed select to group 'mygroup' (which is used by
apache to get the data).
But when apache tries to SELECT from one view it works fine, but on the
other one it fails.
Any ideas ?
------------------------------ Definitions of tables and views follow -----------------------------------------
First table is :
CREATE TABLE tbl_schema (
cle_schema Serial,
code_tbl varchar(20) NOT NULL,
code_schema varchar(20) UNIQUE NOT NULL,
lib_schema varchar(64) NOT NULL,
descr text,
cle_wf_etat int4 NOT NULL,
date_creation datetime DEFAULT TEXT 'now' NOT NULL,
date_maj datetime DEFAULT TEXT 'now' NOT NULL,
version int4 NOT NULL,
PRIMARY KEY (cle_schema),
FOREIGN KEY (cle_wf_etat) REFERENCES tbl_wf_etat(cle_wf_etat)
);
With this view :
CREATE VIEW vue_schema AS
SELECT code_schema, lib_schema
FROM tbl_schema;
GRANT SELECT ON vue_schema TO GROUP ghs;
Second table is :
CREATE TABLE tbl_type_doc (
cle_type_doc Serial,
code_type_doc varchar(20) NOT NULL UNIQUE,
lib_type_doc varchar(64),
descr text,
is_dossier bool DEFAULT 'f' NOT NULL,
date_creation datetime DEFAULT TEXT 'now' NOT NULL,
date_maj datetime DEFAULT TEXT 'now' NOT NULL,
version int4 NOT NULL,
PRIMARY KEY (cle_type_doc)
);
With this view :
CREATE VIEW vue_type_doc AS
SELECT cle_type_doc, code_type_doc, lib_type_doc, descr, is_dossier
FROM tbl_type_doc;
GRANT SELECT ON vue_type_doc TO GROUP ghs;
--
nicolas@bouthors.org -- http://nicolas.bouthors.org/ -- +33 6 2071 6234
Administateur Systèmes et Réseaux -- GHS -- 38, Rue du Texel
Jan Wieck said :
>> > But when apache tries to SELECT from one view it works fine, but on the
>> > other one it fails.
>>
>> Define 'fails'
"Permission denied".
And when I grant "select" on the table, selecting from the view works.
Whithout any grant selecting from the other view works fine.
Nico
--
nicolas@bouthors.org -- http://nicolas.bouthors.org/ -- +33 6 2071 6234
Administateur Systèmes et Réseaux -- GHS -- 38, Rue du Texel
Nicolas Bouthors wrote:
> Hi,
>
> We have a problem here : we crate 2 tables, and for each of them there is a
> view for selecting (almost) all fields of the table. Both tables are
> owned by "dba", and noone else has rights on them.
>
> Both views are GRANTed select to group 'mygroup' (which is used by
> apache to get the data).
>
> But when apache tries to SELECT from one view it works fine, but on the
> other one it fails.
Define 'fails'
Jan
> ------------------------------ Definitions of tables and views follow -----------------------------------------
>
> First table is :
> CREATE TABLE tbl_schema (
> cle_schema Serial,
> code_tbl varchar(20) NOT NULL,
> code_schema varchar(20) UNIQUE NOT NULL,
> lib_schema varchar(64) NOT NULL,
> descr text,
> cle_wf_etat int4 NOT NULL,
> date_creation datetime DEFAULT TEXT 'now' NOT NULL,
> date_maj datetime DEFAULT TEXT 'now' NOT NULL,
> version int4 NOT NULL,
> PRIMARY KEY (cle_schema),
> FOREIGN KEY (cle_wf_etat) REFERENCES tbl_wf_etat(cle_wf_etat)
> );
>
> With this view :
> CREATE VIEW vue_schema AS
> SELECT code_schema, lib_schema
> FROM tbl_schema;
> GRANT SELECT ON vue_schema TO GROUP ghs;
>
> Second table is :
> CREATE TABLE tbl_type_doc (
> cle_type_doc Serial,
> code_type_doc varchar(20) NOT NULL UNIQUE,
> lib_type_doc varchar(64),
> descr text,
> is_dossier bool DEFAULT 'f' NOT NULL,
> date_creation datetime DEFAULT TEXT 'now' NOT NULL,
> date_maj datetime DEFAULT TEXT 'now' NOT NULL,
> version int4 NOT NULL,
> PRIMARY KEY (cle_type_doc)
> );
>
> With this view :
> CREATE VIEW vue_type_doc AS
> SELECT cle_type_doc, code_type_doc, lib_type_doc, descr, is_dossier
> FROM tbl_type_doc;
>
> GRANT SELECT ON vue_type_doc TO GROUP ghs;
>
> --
> nicolas@bouthors.org -- http://nicolas.bouthors.org/ -- +33 6 2071 6234
> Administateur Systèmes et Réseaux -- GHS -- 38, Rue du Texel
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
Nicolas Bouthors wrote:
> Jan Wieck said :
> >> > But when apache tries to SELECT from one view it works fine, but on the
> >> > other one it fails.
> >>
> >> Define 'fails'
>
> "Permission denied".
>
> And when I grant "select" on the table, selecting from the view works.
> Whithout any grant selecting from the other view works fine.
>
> Nico
Do you do the CREATE, GRANT allways as the same user?
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
Jan Wieck said :
>> Do you do the CREATE, GRANT allways as the same user?
Yes
--
nicolas@bouthors.org -- http://nicolas.bouthors.org/ -- +33 6 2071 6234
Administateur Systèmes et Réseaux -- GHS -- 38, Rue du Texel
I think the problem is with the foreign-key reference. Does the calling
user have permission to do SELECT FOR UPDATE on the referenced table?
regards, tom lane
Tom Lane wrote:
> I think the problem is with the foreign-key reference. Does the calling
> user have permission to do SELECT FOR UPDATE on the referenced table?
Why do you say "I think" when you "know"? :-)
That is the problem.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com