关于pg_settings的奇怪权限问题 [英] Strange permission problem regarding pg_settings

问题描述

嗨


我今天在red hat 9上安装了一个postgres应用程序（这是在debian

woody上开发的），使用postgres 7.3 rpms来自redhad。

我的一个触发器使用pg_settings表（更准确地说，它是
更新该表以暂时更改search_path）。在我的debian开发系统上安装了

postgres 7.3（以及7.4），

这个工作正常。然而，在redhat 9上，我得到了一个pg_settings：

权限被拒绝我的触发器执行时出错。


当我尝试从命令行改变pg_settings表时，会发生同样的事情。 （当然，以超级用户身份连接时可行）。我
仔细检查了pg_settings视图上设置的权限，并且

从update-rule

for pg_settings，两者似乎都是正确的（和

debian机器上的相同）。


我需要得到运行的东西，我现在通过

解决了问题，让我的应用程序以超级用户身份连接的用户，但是我想要再次摆脱这个
。 ..


我可以在postgres.conf中查看更多权限，或者是否有一些

config-option我可以试试吗？


问候，Florian Pflug


PS：我也尝试移动postgres-data-dir，创建一个

新鲜一个用initdb - 但没有成功 - 仍然是pg_settings：

权限被拒绝

----------------- ----------（广播结束）---------------------------

提示9：计划者会忽略你的愿望如果您的

加入列的数据类型不匹配，请选择索引扫描

解决方案

Florian G .pflug写道：

我今天在红帽9上安装了一个postgres-application（这是在debian woody上开发的）
，使用redhad的postgres 7.3 rpms。
其中一个我的触发器使用pg_settings表（更准确地说，它更新该表以暂时更改search_path）。在我的debian开发系统上安装了
postgres 7.3（以及7.4），这很好用。然而，在redhat 9上，我得到了pg_settings：
权限被拒绝。我的触发器执行时出错。

我这里有红帽9，但很难猜出可能出现的问题

没有看到一些细节。你能发一个自包含的例子，

重新创造问题吗？


Joe


---- -----------------------（播出结束）---------------------- -----

提示2：您可以使用取消注册命令一次性取消所有列表

（发送取消注册YourEmailAddressHere到 ma ******* @ postgresql.org ）

2003年12月10日上午8:19，Joe Conway写道：

Florian G. Pflug写道：

我安装了一个postgres-application（这是在debian
woody上开发的）今天在红帽9上，使用redhad的postgres 7.3 rpms。
我的一个触发器使用了pg_settings表（更准确地说，它是
更新该表以临时更改search_path）。在我的debian开发系统上安装了
postgres 7.3（以及7.4），这很好用。然而，在redhat 9上，我得到了pg_settings：
权限被拒绝。执行我的触发器时出错。

我这里有Red Hat 9，但很难猜到可能出现什么问题
没有看到一些细节。你能发一个自包含的例子来重现这个问题吗？

这就是我所做的：

用户postgres （连接到template1）

..）创建用户testuser密码''pw''nocreatedb nocreateuser

..）create database testdb owner testuser encoding''utf-8' '


作为用户testuser（连接到testdb）：

..）更新pg_settings set setting =''public''其中name =''search_path' ';

这给出了pg_settings：权限被拒绝


..）select set_config（''search_path''，''public''，' 'f''）;

这是有效的，并将search_path设置为''public''


在debian（woody）上，带有木质-backport of postgresql-7.3安装

（sid重新编译为woody的软件包，并使用dpkg安装），

update pg_settings ...声明有效。


一旦我删除了nocreateuser来自创建用户

testuser ....行（或之后更改用户），它也适用于redhat

（但当然用户是超级用户......）


如果你需要进一步的信息，或者想让我测试一下，只需说

所以;-)


问候，Florian Pflug

- --------------------------（广播结束）------------------- --------

提示5：您是否检查过我们广泛的常见问题解答？

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

" Florian G. Pflug" < fg*@phlo.org>写道：

作为用户testuser（连接到testdb）：
。）更新pg_settings set setting =''public''其中name =''search_path'';
这给出pg_settings：permission denied

Hm。在这里工作正常。你从什么获得


select relacl，relacl从pg_class为空，其中relname =''pg_settings'';

问候，tom lane


---------------------------（广播结束）---------- -----------------

提示1：订阅和取消订阅命令转到 ma ******* @ postgresql.org

Hi

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.

The same thing happens when I try altering the pg_settings table from
the commandline. (But of course works, when connected as superuser). I
double-checked the permissions set on both the pg_settings view, and
the set_config(text, text, bool)-function called from the update-rule
for pg_settings, and both seem to be correct (and the same as on the
debian machine).

As I needed to get the thing running, I now solved the problem by
making the user that my app connects as a superuser, but I''d like to
get rid of this again...

Are there any more permission I could check, or perhaps some
config-option in postgres.conf that I could try?

greetings, Florian Pflug

PS: I also tried moving the postgres-data-dir away, and creating a
fresh one with initdb - but with no success - still "pg_settings:
permission denied"
---------------------------(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

解决方案

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?

Joe

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

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

"Florian G. Pflug" <fg*@phlo.org> writes:

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

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 ma*******@postgresql.org

这篇关于关于pg_settings的奇怪权限问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！