Re: Views and permissions

Christian Schröder wrote:
> 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.

One possibility I see is that there is more than one table
called "EDITORS" and they get confused.

What do you get when you

SELECT t.oid, n.nspname, t.relname
FROM pg_catalog.pg_class t JOIN
     pg_catalog.pg_namespace n ON t.relnamespace = n.oid
WHERE t.relname='EDITORS';

Can you show us the permissions for "ts_frontend.v_editors" as well
as for any "EDITORS" table you find (e.g. using \z in psql).

Yours,
Laurenz Albe