Views and permissions

Поиск
Список
Период
Сортировка
От Christian Schröder
Тема Views and permissions
Дата
Msg-id 47945909.1090405@deriva.de
обсуждение исходный текст
Ответы Re: Views and permissions  ("Albe Laurenz" <laurenz.albe@wien.gv.at>)
Список 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 по дате отправления:

Предыдущее
От: Sim Zacks
Дата:
Сообщение: Re: planner and simple vs. complex statement was: example query for postgresql
Следующее
От: "Albe Laurenz"
Дата:
Сообщение: Re: Views and permissions