Views and permissions

Поиск
Список
Период
Сортировка
Искать
От
Christian Schröder
Тема
Views and permissions
Дата
Msg-id
47945909.1090405@deriva.de
Список
Дерево обсуждения
Views and permissions Christian Schröder <cs@deriva.de>
Re: Views and permissions "Albe Laurenz" <laurenz.albe@wien.gv.at>
Re: Views and permissions Christian Schröder <cs@deriva.de>
Re: Views and permissions "Albe Laurenz" <laurenz.albe@wien.gv.at>
Re: Views and permissions Christian Schröder <cs@deriva.de>
Re: Views and permissions Tom Lane <tgl@sss.pgh.pa.us>
Re: Views and permissions Christian Schröder <cs@deriva.de>
Re: Views and permissions "Albe Laurenz" <laurenz.albe@wien.gv.at>
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
Дата:
От: Albe Laurenz
Дата:
Сообщение: Re: Views and permissions
FAQ