Strange permission problem regarding pg_settings

**Joe Conway** · Nov 12 '05, 01:04 AM

Re: Strange permission problem regarding pg_settings

Florian G. Pflug wrote:[color=blue]
> I installed a postgres-application (which was developed on debian woody)
> on red hat 9 today, using the postgres 7.3 rpms from redhad.
> One of my the triggers uses the pg_settings table (more precisely, it
> updates that table to change the search_path temporarily). With the
> postgres 7.3 (and 7.4 too) installed on my debian development system,
> this worked fine. On redhat 9, however, I get an "pg_setting s:
> permission denied" error when my trigger is executed.[/color]

I've got Red Hat 9 here, but it is hard to guess what might be wrong
without seeing some details. Can you post a self-contained example that
recreates the problem?

Joe

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

**Florian G. Pflug** · Nov 12 '05, 01:05 AM

Re: Strange permission problem regarding pg_settings

On Dec 10, 2003, at 8:19 AM, Joe Conway wrote:[color=blue]
> Florian G. Pflug wrote:[color=green]
>> I installed a postgres-application (which was developed on debian
>> woody) on red hat 9 today, using the postgres 7.3 rpms from redhad.
>> One of my the triggers uses the pg_settings table (more precisely, it
>> updates that table to change the search_path temporarily). With the
>> postgres 7.3 (and 7.4 too) installed on my debian development system,
>> this worked fine. On redhat 9, however, I get an "pg_setting s:
>> permission denied" error when my trigger is executed.[/color]
> I've got Red Hat 9 here, but it is hard to guess what might be wrong
> without seeing some details. Can you post a self-contained example
> that recreates the problem?[/color]

This is what I did:
As user postgres (connected to template1)
..) create user testuser password 'pw' nocreatedb nocreateuser
..) create database testdb owner testuser encoding 'utf-8'

As user testuser (connected to testdb) :
..) update pg_settings set setting='public ' where name='search_pa th' ;
this gives "pg_setting s: permission denied"

..) select set_config('sea rch_path', 'public', 'f') ;
this works, and sets the search_path as expected to 'public'

On debian(woody), with a woody-backport of postgresql-7.3 installed
(the packages for sid recompiled for woody, and installed with dpkg),
the "update pg_settings..." statement works.

As soon as I remove the "nocreateus er" from the "create user
testuser...." line (or alter the user afterwards), it works on redhat
too (but the user is superuser then, of course...)

If you need further information, or want me to test something, just say
so ;-)

greetings, Florian Pflug

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

PostgreSQL: Not Found

http://www.postgresql.org/docs/faqs/FAQ.html

**Tom Lane** · Nov 12 '05, 01:05 AM

Re: Strange permission problem regarding pg_settings

"Florian G. Pflug" <fgp@phlo.org > writes:[color=blue]
> As user testuser (connected to testdb) :
> .) update pg_settings set setting='public ' where name='search_pa th' ;
> this gives "pg_setting s: permission denied"[/color]

Hm. Works fine here. What do you get from

select relacl, relacl is null from pg_class where relname = 'pg_settings';

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org

**Florian G. Pflug** · Nov 12 '05, 01:05 AM

Re: Strange permission problem regarding pg_settings

On Dec 10, 2003, at 5:35 PM, Tom Lane wrote:[color=blue]
> "Florian G. Pflug" <fgp@phlo.org > writes:[color=green]
>> As user testuser (connected to testdb) :
>> .) update pg_settings set setting='public ' where name='search_pa th' ;
>> this gives "pg_setting s: permission denied"[/color]
> Hm. Works fine here. What do you get from
> select relacl, relacl is null from pg_class where relname =
> 'pg_settings';[/color]

testdb=> select relacl, relacl is null from pg_class where relname =
'pg_settings' ;
relacl | ?column?
--------+----------
{=r} | f
(1 row)

mfg, Florian Pflug

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

**Joe Conway** · Nov 12 '05, 01:05 AM

Re: Strange permission problem regarding pg_settings

Tom Lane wrote:[color=blue]
> "Florian G. Pflug" <fgp@phlo.org > writes:[color=green]
>>As user testuser (connected to testdb) :
>>.) update pg_settings set setting='public ' where name='search_pa th' ;
>>this gives "pg_setting s: permission denied"[/color]
>
> Hm. Works fine here. What do you get from
>
> select relacl, relacl is null from pg_class where relname = 'pg_settings';[/color]

Works fine here too, on RH9:

Welcome to psql 7.3.5, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

regression=# \c template1
You are now connected to database template1.
template1=# create user testuser password 'pw' nocreatedb nocreateuser;
CREATE USER
template1=# create database testdb owner testuser encoding 'utf-8';
CREATE DATABASE
template1=# \c testdb testuser
You are now connected to database testdb as user testuser.
testdb=> update pg_settings set setting='public ' where name='search_pa th' ;
set_config
------------
public
(1 row)

testdb=> select relacl, relacl is null from pg_class where relname =
'pg_settings';
relacl | ?column?
--------+----------
{=r} | f
(1 row)

Joe

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org

**Tom Lane** · Nov 12 '05, 01:05 AM

Re: Strange permission problem regarding pg_settings

Joe Conway <mail@joeconway .com> writes:[color=blue]
> Works fine here too, on RH9:[/color]
[color=blue]
> testdb=> update pg_settings set setting='public ' where name='search_pa th' ;
> set_config
> ------------
> public
> (1 row)[/color]
[color=blue]
> testdb=> select relacl, relacl is null from pg_class where relname =
> 'pg_settings';
> relacl | ?column?
> --------+----------
> {=r} | f
> (1 row)[/color]

Hm. By rights it *should* fail, since the ACL is clearly not granting
UPDATE permissions to anybody.

The fact that it fails to fail seems to be because the rules on
pg_settings rewrite the UPDATE into DO INSTEAD NOTHING (which does
nothing, in particular makes no permission checks) and a SELECT,
which only requires read-permission on pg_settings. This is probably
bogus and we ought to see what we can do about fixing it. (And we'd
better fix initdb to grant UPDATE on pg_settings to public, too.)

Now, why does Florian see a permissions failure (which is really the
*right* behavior) when we don't? He didn't say exactly which PG version
he was running, but I see a likely-related bug fix between 7.3.2 and
7.3.3:

2003-02-13 16:40 tgl

* src/backend/rewrite/rewriteHandler. c (REL7_3_STABLE) : Repair rule
permissions-checking bug reported by Tim Burgess 10-Feb-02: the
table(s) modified by the original query would get checked for the
type of write permission needed by a rule query.

This fix may need to be rethought. I'm not sure though where is a clean
place to plug in the UPDATE permissions check given that the rules for
this case do not generate any UPDATE query.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

**Joe Conway** · Nov 12 '05, 01:05 AM

Re: Strange permission problem regarding pg_settings

Tom Lane wrote:[color=blue]
> Now, why does Florian see a permissions failure (which is really the
> *right* behavior) when we don't? He didn't say exactly which PG version
> he was running, but I see a likely-related bug fix between 7.3.2 and
> 7.3.3:[/color]

That seems to be it:

# psql regression
Welcome to psql 7.3.2, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

regression=# \c template1
You are now connected to database template1.
template1=# create user testuser password 'pw' nocreatedb nocreateuser;
CREATE USER
template1=# create database testdb owner testuser encoding 'utf-8';
CREATE DATABASE
template1=# \c testdb testuser
You are now connected to database testdb as user testuser.
testdb=> update pg_settings set setting='public ' where name='search_pa th' ;
ERROR: pg_settings: permission denied
[color=blue]
> This fix may need to be rethought. I'm not sure though where is a clean
> place to plug in the UPDATE permissions check given that the rules for
> this case do not generate any UPDATE query.[/color]

Do you want me to take a look at this, or are you planning to?

Joe

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

PostgreSQL: Not Found

http://www.postgresql.org/docs/faqs/FAQ.html

**Tom Lane** · Nov 12 '05, 01:05 AM

Re: Strange permission problem regarding pg_settings

Joe Conway <mail@joeconway .com> writes:[color=blue]
> Tom Lane wrote:[color=green]
>> This fix may need to be rethought. I'm not sure though where is a clean
>> place to plug in the UPDATE permissions check given that the rules for
>> this case do not generate any UPDATE query.[/color][/color]
[color=blue]
> Do you want me to take a look at this, or are you planning to?[/color]

If you have any ideas, feel free to take a shot. I've not thought of
anything I like.

I suspect the fact that the pre-patch code made the "right" permissions
check was really coincidental, and that the correct fix will not involve
reversion of that patch but rather adding a facility somewhere to ensure
that the original view gets properly permission-checked even if there's
a DO INSTEAD NOTHING rule. However, before biting that bullet it'd
probably be good to understand in detail what's happening in both the
7.3.2 and CVS-tip code. I have not looked at just why that patch
changes this example's behavior.

regards, tom lane

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

**Joe Conway** · Nov 12 '05, 01:17 AM

Re: Strange permission problem regarding pg_settings

Tom Lane wrote:[color=blue]
> I suspect the fact that the pre-patch code made the "right" permissions
> check was really coincidental, and that the correct fix will not involve
> reversion of that patch but rather adding a facility somewhere to ensure
> that the original view gets properly permission-checked even if there's
> a DO INSTEAD NOTHING rule. However, before biting that bullet it'd
> probably be good to understand in detail what's happening in both the
> 7.3.2 and CVS-tip code. I have not looked at just why that patch
> changes this example's behavior.
>[/color]

I just started looking at this again. There is definitely an issue in
cvs tip:

create table t(f1 int, f2 text);
insert into t values(1, 'abc');
create view v as select * from t;
CREATE RULE v_upd AS ON UPDATE TO v DO INSTEAD
UPDATE t SET f1 = NEW.f1, f2 = NEW.f2 WHERE f1 = OLD.f1;
create user user1;

-- this fails; as it should, I think
\c - user1
update v set f2 = 'def' where f1 = 1;
ERROR: permission denied for relation v

-- so grant SELECT on the view
\c - postgres
grant select on v to public;

-- this should fail, but doesn't
\c - user1
update v set f2 = 'def' where f1 = 1;
UPDATE 1

On 7.3.2 that last section of the above script gives:

\c - user1
update v set f2 = 'def' where f1 = 1;
ERROR: v: permission denied

The comment associated with the change says this:

* Also, we must disable write-access checking in all the RT entries
* copied from the main query. This is safe since in fact the rule
* action won't write on them, and it's necessary because the rule
* action may have a different commandType than the main query, causing
* ExecCheckRTEPer ms() to make an inappropriate check. The read-access
* checks can be left enabled, although they're probably redundant.
*/

So SELECT permissions get checked for user1, but write-access does not.
The underlying table should be checked for permissions based on the rule
owner per rewriteDefine.c around line 439 (line 387 in 7.3.2):

/*
* We want the rule's table references to be checked as though by the
* rule owner, not the user referencing the rule. Therefore, scan
* through the rule's rtables and set the checkAsUser field on all
* rtable entries.
*/

Since the rule owner in this case is also the creator of the table, the
UPDATE suceeds.

ISTM that we want the relations in the un-rewritten query checked based
on the basis of the user referencing the rule and for the modes used in
the un-rewritten query -- suggesting the change need be reverted. Then
we want the rule's table references checked based on rule owner and
actual operations performed. It looks like this part should be what's
happening.

I went back to the original complaint -- here is the example on a 7.3.2
installation:

regression=# create table table1 (test1 integer);
grant insert on table1 to pleb;
create rule test_rule as on insert to table1 do update table2 set test2
= 2 where test2 = 0;
\c - pleb;
insert into table1 values (1);
CREATE TABLE
regression=# create table table2 (test2 integer);
CREATE TABLE
regression=# create user pleb;
ERROR: CREATE USER: user name "pleb" already exists
regression=# grant insert on table1 to pleb;
GRANT
regression=# create rule test_rule as on insert to table1 do update
table2 set test2 = 2 where test2 = 0;
CREATE RULE
regression=# \c - pleb;
You are now connected as new user pleb.
regression=> insert into table1 values (1);
ERROR: table1: permission denied

A few NOTICES placed in ExecCheckRTEPer ms() reveals this:

regression=> insert into table1 values (1);
NOTICE: relOid = 1245674
NOTICE: userid = 101
NOTICE: operation = CMD_INSERT
NOTICE: relOid = 1245674
NOTICE: userid = 101
NOTICE: operation = CMD_UPDATE
ERROR: table1: permission denied

regression=> select oid, relname from pg_class where relname like 'table%';
oid | relname
---------+---------
1245674 | table1
1245676 | table2
(2 rows)

It seems that second pass through ExecCheckRTEPer ms() is not doing the
right thing. It ought to be checking table2 (not table1) for UPDATE as
userid == 1 (not 101), shouldn't it?

Any thoughts on where to look next?

Thanks,

Joe

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org

**Tom Lane** · Nov 12 '05, 01:18 AM

Re: Strange permission problem regarding pg_settings

[ please respect moving of thread to pg-hackers ]

Joe Conway <mail@joeconway .com> writes:[color=blue]
> ISTM that we want the relations in the un-rewritten query checked based
> on the basis of the user referencing the rule and for the modes used in
> the un-rewritten query -- suggesting the change need be reverted.[/color]

Reverting the change will bring back the bug for which it was created.
It does seem though that we have an inadequate model of how to perform
permission checks. In particular, the "write" flag bit in RTEs is
context dependent: it can mean insert, update, or delete permission
depending on the surrounding command.

The problem the earlier bug report identified is really that when an RTE
is copied from one query to another, the meaning of its "write" flag bit
changes --- incorrectly --- if the new query is of a different type.
I thought when making that patch that we could make an end-run around
this problem by zeroing out the flag bit, but what we're now realizing
is that that leaves us with no check at all in some scenarios (because
the original query will be dropped completely when INSTEAD is specified).

I begin to think that the only real solution is to change the RTE
representation to identify the exact permission bits to be checked for
each entry (say, replace the read and write booleans with a permission
bitmask). Then a view reference specifying INSERT permission check
could be copied into an UPDATE query without changing its permission
semantics.

This would be a fairly extensive change though. Does anyone see an
easier way?

Also, does anyone see a case where it would be correct for the checked
permission to change when an RTE is copied to a query of a different
type?

regards, tom lane

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

PostgreSQL: PostgreSQL Mailing List Archives

http://archives.postgresql.org

Strange permission problem regarding pg_settings

Strange permission problem regarding pg_settings

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment