Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I found some solution by adding

allow_system_table_mods = on

to PostgreSQL configuration file.

But on PostgreSQL this doesen't work. I can't find any other method who give me access to edit this catalogs.

Have someone any idea?

share|improve this question
Editing the system catalog by hand is usually a BAD idea. Perhaps if you explain why you want to do such a thing someone can give you safer suggestions? –  voretaq7 Aug 11 '11 at 14:48
I know about how BAD idea is it, but this is a one way to solve my problem. –  Svisstack Aug 11 '11 at 19:09
Realy i need this, i will wait for some answers. –  Svisstack Aug 11 '11 at 19:10
Another allow_system_table_mods = on motivation: enabled/disable triggers which is currently not elegantly possible without admin rights (even if I create a function in some admins schema and grant execute rights to some non-admin, it does not work): stackoverflow.com/a/6152343/1915920 –  Andreas Dietrich Sep 29 at 11:54

1 Answer 1

up vote 1 down vote accepted

I just installed PostgresSQL 8.1 from source code and have no issue with editing system catalogs as superuser. It's determined by rolcatupdate property in pg_roles system catalog:

Role may update system catalogs directly. (Even a superuser may not do this unless this column is true.)

You can check it by:

SELECT rolcatupdate FROM pg_roles WHERE rolname LIKE 'postgres';
(1 row)

For other roles you have false value, so you could (probably you shouldn't doing this at all, so be careful) set to true:

UPDATE pg_roles SET rolcatupdate = true WHERE rolname LIKE 'roleName'

Second way (let's say you don't have any superuser accout after some accident) is to run PostgreSQL server in single-user mode:

When running a stand-alone server, the session user will be set to the user with ID 1. This user does not actually have to exist, so a stand-alone server can be used to manually recover from certain kinds of accidental damage to the system catalogs. Implicit superuser powers are granted to the user with ID 1 in stand-alone mode.

share|improve this answer
postgres=# SELECT rolcatupdate FROM pg_roles WHERE rolname LIKE 'postgres'; rolcatupdate -------------- t (1 row) postgres=# postgres=# alter table pg_catalog.pg_database rename to pg_database_catalog; ERROR: permission denied: "pg_database" is a system catalog –  Svisstack Aug 13 '11 at 12:13
sorry this dont work ;-/ –  Svisstack Aug 13 '11 at 12:14
@Svisstack: pg_database is internal PostgreSQL catalog. You can modify/update some values like datacl, but you can't change its name. –  Grzegorz Szpetkowski Aug 13 '11 at 12:25
ok, how i can grant full access to update postgresql catalog with change its name on version 8.1? this is possible? i must migrate to newest version? –  Svisstack Aug 13 '11 at 12:31
AFAIK there is no such grant and you really shouldn't touch catalogs in such way. I'll check if that is possible "manually". Why do you need that ? –  Grzegorz Szpetkowski Aug 13 '11 at 12:39

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.