Home / server / Questions / 272703
In Process
Mike Thomsen
Asked: 2011-05-24 04:25:16 +0800 CST

Can PostgreSQL be configured to log user creation, deletion and modification?

  • 772

Is it possible to make PostgreSQL log user creation, user deletion and change in user permissions?

postgresql

1 Answers

  1. Yes, add in your postgresql.conf and restart (or just reload) db server:

    log_statement = 'ddl'

    That works with createuser, dropuser wrappers, CREATE/DROP ROLE/USER and ALTER ROLE/USER statements as I checked:

    2011-05-23 14:40:45 CEST STATEMENT:  DROP ROLE thomas;
2011-05-23 14:40:52 CEST LOG:  statement: CREATE ROLE greg LOGIN CREATEDB;
2011-05-23 14:42:36 CEST LOG:  statement: ALTER ROLE greg NOCREATEDB;

    log_statement (enum) - Controls which SQL statements are logged. Valid values are none (off), ddl, mod, and all (all statements). ddl logs all data definition statements, such as CREATE, ALTER, and DROP statements.

    http://www.postgresql.org/docs/current/static/runtime-config-logging.html#GUC-LOG-STATEMENT

    There is one drawback, that you have another DDL statements logged as well.

    • 4