Re: Views and permissions

Albe Laurenz wrote:
> 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.
>
Indeed, you are right! Granting select permission to the "ts_frontend"
user (more precisely: granting membership to the "zert_readers" role)
solved the problem.
> This is strange because ts_frontend can select from "EDITORS" because
> of the membership to role zert_readers.
>
No, the user "ts_frontend" is (was) not a member of the group
"zert_readers", but the user "www" who uses the view is. Until now I
always thought that the user that *uses* the view must have the
appropriate privileges, but it seems to depend also on the privileges of
the user that *defines* the view.
> 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?
>
Ok, I have found my mistake: During migration of the roles, I did not
handle roles the way it should have been. I only migrated group
memberships for users, but not for other groups. Maybe I should correct
my migration script and remove the distinction between users and groups
at all. Or is there a way to migrate the roles using the PostgreSQL
tools? I normally dump the databases one by one (using "pg_dump" and not
"pg_dumpall"), so the system catalogs (especially the roles) must be
transferred separately.

That doesn't explain why views behave the way they do, but at least it
describes why things suddenly stopped working.

Many thanks for your help!!

Christian

--
Deriva GmbH Tel.: +49 551 489500-42
Financial IT and Consulting Fax: +49 551 489500-91
Hans-Böckler-Straße 2 http://www.deriva.de
D-37079 Göttingen

Deriva CA Certificate: http://www.deriva.de/deriva-ca.cer