Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2009

    Question Unanswered: Modifying pg_catalog

    Hi Forum,

    I want to add two triggers to pg_authid and pg_auth_members to clone these tables to my Security schema [Links Login names with users details eg. Name].

    Im using Postgres 9.0

    Chapter 45 [pg. 1442] states that
    PostgreSQL’s system catalogs are regular tables. You can drop and recreate the tables, add columns, insert and update values, and severely mess up your system that way.
    However, I cant find the setting to allow changes to the system catalog. Can someone please tell me what setting / SQL I need to run to enable, and then disable system catalog changes.

    EDIT: Answer: allow_system_table_mods in postgresql.conf. See pg 432 of manual for details.

    Last edited by Wedgetail; 12-02-10 at 04:25. Reason: Summarise answer

  2. #2
    Join Date
    Nov 2003
    Provided Answers: 23
    You should never directly manipulate the system catalogs.

    If you are asking which statements do change the catalogs, then the answer is: nearly every DDL statement. For pg_authid and pg_auth_menbers I'd assume these are the CREATE ROLE, ALTER ROLE and GRANT statements.

  3. #3
    Join Date
    Feb 2009
    Thanks Shammat for your reply.

    You should never directly manipulate the system catalogs.
    I am aware it is not advised to change the system catalogs. What I was seeking to do was to add an "After Insert Trigger" on pg_authid to clone the inserted Role to my user database to link information about the login role to the user database [eg. Person name, contact details etc].

    As far as I am aware there are two ways of achieving this:
    1) To add a Trigger to the pg_authid table to clone any inserted or updated rows to the user database.

    2) Create a function to insert the new role, which also adds the details to the User Security table.

    Neither is ideal, but the first more closely simulates the referential integrity of having all users in the User Security table.

    I also found the answer to the question. The variable is "allow_system_table_mods" in the postgresql.conf file. You must restart the server for the change to take effect. See Page 432 of the Postgres manual.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts