Database Reference
In-Depth Information
There's more...
Was the change committed
It is possible to have some statements recorded in the log file, but not visible in the database
structure. Most DDL commands in PostgreSQL can be ROLLBACKed, so what is in the log, is
just a list of commands executed by postgresql, not what was actually committed. The log file
is not transactional, and also keeps commands that were rolled back.
Who made the change
To be able to know the database user who made the DDL changes, you have to make sure
that this info is logged as well.
In order to do so, you may have to change the
log_line_prefix
parameter to include
the
%u
format string.
A recommended minimal
log_line_prefix
format string for auditing DDL is '%t %u %d',
which tells postgresql to log timestamp, database user, and database name at the start of
every log line.
Can't I find out this information from the database
If you don't have logging enabled, or don't have all the logs, then you can get only very limited
information on who changed the database schema and when from system tables, and even
that is not reliable.
What you can can get is the "owner" of the database object (table, sequence, function, and so
on) but this may have been changed by "ALTER TABLE … SET OWNER to yyyy".
You may be able to guess the approximate time of object creation or of the latest modification
by looking up the transaction id in
xmin
system column in
pg_class
and
pg_attrib
system
tables. And then, try to find close
xmin
from some table which has automatic insert date
logging, maybe having
DEFAULT
CURRENT_TIMESTAMP
defined for some column.
Auditing data changes
This recipe provides different ways to collect changes to data contained in the tables for
auditing purposes.
Getting ready
Determine the following:
F
Do you need to audit all changes or only some?
F
What information about the changes do you need to collect: just the fact of change?
