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