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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
