Views and permissions
От | Christian Schröder |
---|---|
Тема | Views and permissions |
Дата | |
Msg-id | 47945909.1090405@deriva.de обсуждение исходный текст |
Ответы |
Re: Views and permissions
|
Список | 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
В списке pgsql-general по дате отправления: