Postgres -拒绝更新pg_catalog.pg_cast

Question

我想从受限用户(在Postgres9.3上)更新pg_catalog.pg_cast。

但是，运行我需要的查询：

 update pg_cast set castcontext = 'i' where oid in ( select c.oid from pg_cast c inner join pg_type src on src.oid = c.castsource inner join pg_type tgt on tgt.oid = c.casttarget where src.typname like 'int%' and tgt.typname like 'bool%');

最后出现错误：

    ERROR:  permission denied for relation pg_cast

但是，权限似乎是正确设置的。请参阅我自DB和用户创建以来所做的步骤，直到查询：

psql -c "create database test1 WITH ENCODING 'UTF8' LC_COLLATE='en_GB.UTF8' LC_CTYPE='en_GB.UTF8' TEMPLATE=template0;" -U postgres
psql -U postgres test1;
test1=# CREATE USER test1 PASSWORD 'test1';
test1=# GRANT ALL ON SCHEMA public TO test1;
test1=# GRANT ALL ON ALL TABLES IN SCHEMA public TO test1;
test1=# GRANT SELECT ON TABLE pg_catalog.pg_cast TO test1; 
test1=# GRANT SELECT ON TABLE pg_catalog.pg_type TO test1; 
test1=# GRANT UPDATE ON TABLE pg_catalog.pg_cast TO test1; 
test1=# \q

sudo service postgresql-9.3 restart

PGPASSWORD=test1;psql -U test1 test1

test1=> \z pg_catalog.pg_cast
                  Access privileges
   Schema   |  Name   | Type  | Access privileges | Column access privileges 
------------+---------+-------+-------------------+--------------------------
 pg_catalog | pg_cast | table | =r/postgres      +| 
        |         |       | test1=rw/postgres | 
(1 row)

test1=> \z pg_catalog.pg_type
                  Access privileges
   Schema   |  Name   | Type  | Access privileges | Column access privileges 
------------+---------+-------+-------------------+--------------------------
 pg_catalog | pg_type | table | =r/postgres      +| 
        |         |       | test1=r/postgres  | 
(1 row)

test1=> SELECT grantee, privilege_type FROM information_schema.role_table_grants WHERE table_name='pg_cast';
 grantee | privilege_type 
---------+----------------
 test1   | SELECT
 test1   | UPDATE
(2 rows)

test1=> update pg_cast set castcontext = 'i' where oid in ( select c.oid from pg_cast c inner join pg_type src on src.oid = c.castsource inner join pg_type tgt on tgt.oid = c.casttarget where src.typname like 'int%' and tgt.typname like 'bool%');
ERROR:  permission denied for relation pg_cast

我还应该做什么，以启用对test1用户的查询执行？谢谢。

Answer 1

您确实不应该直接更新系统目录。“拒绝许可”错误是Postgres试图保护您不被射中脚的错误。

如果你真的想要的话(如果你弄坏了什么东西，你可以把这两块都留下.)从这里开始：https://serverfault.com/questions/300123/how-to-edit-system-catalogs-in-postgresql-8-1

Answer 2

我也有过类似的问题，更新格林梅系统目录，提示是：

错误:权限被拒绝："pg_filespace_entry“是系统目录，

在尝试修改系统表之前，该解决方案使用了以下命令：

set allow_system_table_mods='dml';