Postgresql – How to edit system catalogs in PostgreSQL 8.1

postgresql

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?

Best Answer

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';
 rolcatupdate 
--------------
 t
(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.

Related Topic