Re: Views and permissions

=?ISO-8859-1?Q?Christian_Schr=F6der?= <cs(at)deriva(dot)de> writes:
> Albe Laurenz wrote:
>> 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.

Table accesses done by a view are checked according to the privileges
of the owner of the view, not of whoever invoked the view. It's a
bit inconsistent because function calls done in the view are not handled
that way (though I hope we change them to match, someday).

> 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.

You can use "pg_dumpall -g" to get a dump of just global objects (roles
and tablespaces). If you do want to stick to hand-rolled scripts, then
yeah, you need to take another look at it. Since 8.1 there is very
little difference between users and groups --- they are all roles, and
the only actual difference is the default settings of their LOGIN and
INHERITS flags. See the CREATE ROLE reference page for details.

regards, tom lane