Views and permissions

From: Christian Schröder <cs(at)deriva(dot)de>
To: pgsql-general(at)postgresql(dot)org
Subject: Views and permissions
Date: 2008-01-21 08:34:17
Message-ID: 47945909.1090405@deriva.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi list,
yesterday I moved our database from one server to another. I did a full
dump of the database and imported the dump into the new server. Since
then I have a strange problem which I cannot explain ...
I have a table public."EDITORS":

Table "public.EDITORS"
Column | Type | Modifiers
----------+------------------------+---------------------
code | character(2) | not null
active | smallint | not null default -1
name | character varying(100) |
username | name | not null
Indexes:
"EDITORS_pkey" PRIMARY KEY, btree (code)
"EDITORS_username_key" UNIQUE, btree (username)

And I have a view "ts_frontend.v_editors":

View "ts_frontend.v_editors"
Column | Type | Modifiers
-----------+------------------------+-----------
code | character(2) |
name | character varying(100) |
username | name |
usergroup | text |
View definition:
SELECT "EDITORS".code, "EDITORS".name, "EDITORS".username, ( SELECT
CASE
WHEN "EDITORS".code = ANY (ARRAY['AF'::bpchar,
'CS'::bpchar, 'FK'::bpchar, 'FW'::bpchar, 'JK'::bpchar, 'JS'::bpchar,
'KJ'::bpchar, 'KR'::bpchar, 'MP'::bpchar, 'PB'::bpchar, 'RB'::bpchar,
'RR'::bpchar, 'SJ'::bpchar]) THEN 'a'::text
WHEN "EDITORS".code = ANY (ARRAY['JA'::bpchar,
'AG'::bpchar, 'BK'::bpchar]) THEN 'o'::text
ELSE 'z'::text
END AS "case") AS usergroup
FROM "EDITORS"
WHERE "EDITORS".active < 0
ORDER BY "EDITORS".name;

A user "www" has read access on both the view and the table. When I log
into the database as this user and execute the view's sql, everything
works fine. But when I try to select from the view, I get an "ERROR:
permission denied for relation EDITORS".
How can this happen? As far as I understand, views are simply rewrite
rules, so it should make no difference if I use the view or directly use
the sql. Moreover, this error never happened before I moved to the new
server. The new server completely replaced the old one (it has the same
name, ip address etc.) so I cannot imagine how the migration can
influence this behaviour.
If it is important: The postgresql version is 8.2.6.

Thanks a lot for any hints,
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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Albe Laurenz 2008-01-21 09:25:49 Re: Views and permissions
Previous Message Sim Zacks 2008-01-21 08:24:04 Re: planner and simple vs. complex statement was: example query for postgresql