Re: Permissions on Views

PostgreSQL 7.4.2 on i386-redhat-linux-gnu, compiled by GCC 2.96

I have a simple VIEW such as the following:

CREATE OR REPLACE VIEW vu_tbl_useracc AS
SELECT ua.id_user, ua.id_cmpy, ua.id_contrib, ua.dt_edited, ua.id_editedby
FROM tbl_useracc ua;

GRANT SELECT, INSERT ON TABLE vu_tbl_useracc TO GROUP grp_cisx_admin;

CREATE OR REPLACE RULE rul_i01 AS ON INSERT TO vu_tbl_useracc DO INSTEAD INSERT INTO tbl_useracc (id_user, id_cmpy, id_contrib)
VALUES (new.id_user, new.id_cmpy, new.id_contrib);

Now whenever I attempt to INSERT into the view I get the following error.
ERROR: permission denied for relation vu_tbl_useracc

The error goes away if I grant both INSERT and UPDATE permissions to the above group.

I didn't have this problem on version 7.3.4 which is what I have upgraded from.

Regards
Donald Fraser

"Donald Fraser" <demolish(at)cwgsy(dot)net> writes:
> Now whenever I attempt to INSERT into the view I get the following error.
> ERROR: permission denied for relation vu_tbl_useracc

I tried to reproduce this and couldn't.

> I didn't have this problem on version 7.3.4 which is what I have upgraded f=
> rom.

7.3.4 had some rather serious permission-checking bugs, so the fact that
your code worked in 7.3.4 is no guarantee that it's right. I'd suggest
rechecking your permissions ...

regards, tom lane