Re: Strange permission problem regarding pg_settings

On Dec 10, 2003, at 8:19 AM, Joe Conway wrote:
> Florian G. Pflug wrote:
>> 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_settings:
>> permission denied" error when my trigger is executed.
> 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?

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_path' ;
this gives "pg_settings: permission denied"

.) select set_config('search_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 "nocreateuser" 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