Re: Views and permissions

Christian Schröder wrote:
>> Can you show us the permissions for "ts_frontend.v_editors" as well
>> as for any "EDITORS" table you find (e.g. using \z in psql).
>>
> Access privileges for database "zertifikate"
> Schema | Name | Type | Access privileges
> --------+---------+-------+--------------------------------------------------------------
> public | EDITORS | table | {chschroe=arwdxt/chschroe,zert_readers=r/chschroe,zert_writers=arwd/chschroe,ts_frontend=x/chschroe}
> (1 row)
>
> Access privileges for database "zertifikate"
> Schema | Name | Type | Access privileges
> -------------+-----------+------+--------------------------------------------------------
> ts_frontend | v_editors | view | {ts_frontend=arwdxt/ts_frontend,www=r/ts_frontend,backup=r/ts_frontend}
> (1 row)
>
> The user "www" is a member of the "zert_readers" group:
>
> zertifikate=# select pg_has_role('www', 'zert_readers', 'MEMBER');
> pg_has_role
> -------------
> t
> (1 row)
>
> I have also tried to drop and recreate the view, but it didn't help.

I could reproduce the behaviour on 8.3 Beta 4.

User ts_frontend, the owner of the view ts_frontend.v_editors, does not
have the SELECT privilege on the underlying table public."EDITORS".

Because of that neither he nor anybody else can select from the view,
although ts_frontend is able to create the view.

This is strange because ts_frontend can select from "EDITORS" because
of the membership to role zert_readers.

It seems that in this situation, role membership of the view owner
is not checked. I don't know if that is intentional - I couldn't find
anything about it in the documentation.

Maybe somebody else can shed light on this.

Anyway, you can fix the problem with
GRANT SELECT ON public."EDITORS" TO ts_frontend;

Since this database is from a pg_dump from another database where things
worked as expected:
- What is the version of that database?
- Do permissions look identical in that database?

Yours,
Laurenz Albe