SEO

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



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Albe Laurenz wrote:
> 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';
>
As expected, there is only one table:

oid | nspname | relname
---------+---------+---------
3045229 | public | EDITORS
(1 row)

> 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).
>
Access privileges for database
"zertifikate"
Schema | Name | Type |
Access privileges
--------+---------+-------+------------------------------------------------------------------------------------------------------
public | EDITORS | table |
{chschroe=arwdxt/chschroe,zert_readers=r/chschroe,zert_writers=arwd/chschroe,ts_frontend=x/chschroe}
(1 row)

Access privileges for database "zertifikate"
Schema | Name | Type | Access
privileges
-------------+-----------+------+-------------------------------------------------------------------------
ts_frontend | v_editors | view |
{ts_frontend=arwdxt/ts_frontend,www=r/ts_frontend,backup=r/ts_frontend}
(1 row)

The user "www" is a member of the "zert_readers" group:

zertifikate=# select pg_has_role('www', 'zert_readers', 'MEMBER');
pg_has_role
-------------
t
(1 row)

I have also tried to drop and recreate the view, but it didn't help.

Regards,
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


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Christian Schröder wrote:
>> 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).
>>
> Access privileges for database "zertifikate"
> Schema | Name | Type | Access privileges
> --------+---------+-------+--------------------------------------------------------------
> public | EDITORS | table | {chschroe=arwdxt/chschroe,zert_readers=r/chschroe,zert_writers=arwd/chschroe,ts_frontend=x/chschroe}
> (1 row)
>
> Access privileges for database "zertifikate"
> Schema | Name | Type | Access privileges
> -------------+-----------+------+--------------------------------------------------------
> ts_frontend | v_editors | view | {ts_frontend=arwdxt/ts_frontend,www=r/ts_frontend,backup=r/ts_frontend}
> (1 row)
>
> The user "www" is a member of the "zert_readers" group:
>
> zertifikate=# select pg_has_role('www', 'zert_readers', 'MEMBER');
> pg_has_role
> -------------
> t
> (1 row)
>
> I have also tried to drop and recreate the view, but it didn't help.

I could reproduce the behaviour on 8.3 Beta 4.

User ts_frontend, the owner of the view ts_frontend.v_editors, does not
have the SELECT privilege on the underlying table public."EDITORS".

Because of that neither he nor anybody else can select from the view,
although ts_frontend is able to create the view.

This is strange because ts_frontend can select from "EDITORS" because
of the membership to role zert_readers.

It seems that in this situation, role membership of the view owner
is not checked. I don't know if that is intentional - I couldn't find
anything about it in the documentation.

Maybe somebody else can shed light on this.

Anyway, you can fix the problem with
GRANT SELECT ON public."EDITORS" TO ts_frontend;

Since this database is from a pg_dump from another database where things
worked as expected:
- What is the version of that database?
- Do permissions look identical in that database?

Yours,
Laurenz Albe

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Albe Laurenz wrote:
> User ts_frontend, the owner of the view ts_frontend.v_editors, does not
> have the SELECT privilege on the underlying table public."EDITORS".
>
> Because of that neither he nor anybody else can select from the view,
> although ts_frontend is able to create the view.
>
Indeed, you are right! Granting select permission to the "ts_frontend"
user (more precisely: granting membership to the "zert_readers" role)
solved the problem.
> This is strange because ts_frontend can select from "EDITORS" because
> of the membership to role zert_readers.
>
No, the user "ts_frontend" is (was) not a member of the group
"zert_readers", but the user "www" who uses the view is. Until now I
always thought that the user that *uses* the view must have the
appropriate privileges, but it seems to depend also on the privileges of
the user that *defines* the view.
> Since this database is from a pg_dump from another database where things
> worked as expected:
> - What is the version of that database?
> - Do permissions look identical in that database?
>
Ok, I have found my mistake: During migration of the roles, I did not
handle roles the way it should have been. I only migrated group
memberships for users, but not for other groups. Maybe I should correct
my migration script and remove the distinction between users and groups
at all. Or is there a way to migrate the roles using the PostgreSQL
tools? I normally dump the databases one by one (using "pg_dump" and not
"pg_dumpall"), so the system catalogs (especially the roles) must be
transferred separately.

That doesn't explain why views behave the way they do, but at least it
describes why things suddenly stopped working.

Many thanks for your help!!

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



---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

=?ISO-8859-1?Q?Christian_Schr=F6der?= <cs@deriva.de> writes:
> Albe Laurenz wrote:
>> This is strange because ts_frontend can select from "EDITORS" because
>> of the membership to role zert_readers.
>>
> No, the user "ts_frontend" is (was) not a member of the group
> "zert_readers", but the user "www" who uses the view is. Until now I
> always thought that the user that *uses* the view must have the
> appropriate privileges, but it seems to depend also on the privileges of
> the user that *defines* the view.

Table accesses done by a view are checked according to the privileges
of the owner of the view, not of whoever invoked the view. It's a
bit inconsistent because function calls done in the view are not handled
that way (though I hope we change them to match, someday).

> Ok, I have found my mistake: During migration of the roles, I did not
> handle roles the way it should have been. I only migrated group
> memberships for users, but not for other groups. Maybe I should correct
> my migration script and remove the distinction between users and groups
> at all. Or is there a way to migrate the roles using the PostgreSQL
> tools? I normally dump the databases one by one (using "pg_dump" and not
> "pg_dumpall"), so the system catalogs (especially the roles) must be
> transferred separately.

You can use "pg_dumpall -g" to get a dump of just global objects (roles
and tablespaces). If you do want to stick to hand-rolled scripts, then
yeah, you need to take another look at it. Since 8.1 there is very
little difference between users and groups --- they are all roles, and
the only actual difference is the default settings of their LOGIN and
INHERITS flags. See the CREATE ROLE reference page for details.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Tom Lane wrote:
> Table accesses done by a view are checked according to the privileges
> of the owner of the view, not of whoever invoked the view. It's a
> bit inconsistent because function calls done in the view are not handled
> that way (though I hope we change them to match, someday).
>
Phew, sometimes I'm surprised about my own stupidity! I used this more
than once to create views that gave people access to tables they would
otherwise not be allowed to read, but I simply did not recognize that
it's simply the same in this case.
And by now I also found the section in the manual where this is
described (35.4, if someone is interested).
> You can use "pg_dumpall -g" to get a dump of just global objects (roles
> and tablespaces). If you do want to stick to hand-rolled scripts, then
>
Thanks, I didn't know this option. The next migration will be much
easier with this!
> yeah, you need to take another look at it. Since 8.1 there is very
> little difference between users and groups --- they are all roles, and
> the only actual difference is the default settings of their LOGIN and
> INHERITS flags. See the CREATE ROLE reference page for details.
>
Yes, I know the new role concept, but I didn't realize that it had these
impacts on my script. Anyway, I won't need it anymore, now that you told
me the "pg_dumpall -g" solution.

Thanks again,
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


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Christian Schröder wrote:
> Indeed, you are right! Granting select permission to the "ts_frontend"
> user (more precisely: granting membership to the "zert_readers" role)
> solved the problem.
>
>> This is strange because ts_frontend can select from "EDITORS" because
>> of the membership to role zert_readers.
>
> No, the user "ts_frontend" is (was) not a member of the group
> "zert_readers", but the user "www" who uses the view is.

My mistake, I got confused in your role hierarchy.
Anyway, the problem is solved, and the cause is clear :^)

Yours,
Laurenz Albe

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match