Friday, January 13, 2012

PosgreSQL - monitoring. User auditing "on steroids"

I'm taking a deep look into the latest PostgreSQL added features (in 9.0 >), and I'm very happy with this one:

Instead of being able to set up configuration variables per database or per user, one can now set them up for a certain user in a certain database:

marc=# ALTER ROLE marc IN database marc set log_statement to 'all';
ALTER ROLE

To know who has which variables set-up in which user+database, there is a new psql command:

marc=# \drds
List of settings
role | database | settings
-----+----------+-----------------
marc | marc | log_statement=all
(1 row)

For instance, I've enabled log_statement = 'all' to my testing role 'rene' on a database called 'rene', even though, the general setting is established to 'none'.
And this is what the log (set as a foreign table) looks like when I execute something like a 'create table' statement.

-[ RECORD 1 ]----------+---------------------------------------------------------
log_time               | 2012-01-13 18:47:17.46-06
user_name              | rene
database_name          | rene
process_id             | 8584
connection_from        | [local]
session_id             | 4f10d072.2188
session_line_num       | 2
command_tag            | idle
session_start_time     | 2012-01-13 18:46:42-06
virtual_transaction_id | 2/89
transaction_id         | 0
error_severity         | LOG
sql_state_code         | 00000
message                | statement: create table test(a int primary key);
detail                 | 
hint                   | 
internal_query         | 
internal_query_pos     | 
context                | 
query                  | 
query_pos              | 
location               | 
application_name       | psql


To me, this level of customization of the auditing feature is just beautiful.

This is what the new \drds psql internal command does behind the scenes.

SELECT rolname AS role, datname AS database,pg_catalog.array_to_string(setconfig, E'\n') AS settings
                       FROM pg_db_role_setting AS s
                       LEFT JOIN pg_database ON pg_database.oid = setdatabase
                       LEFT JOIN pg_roles ON pg_roles.oid = setrole
                       WHERE pg_roles.rolname ~ '^(rene)$'
                       ORDER BY role, database;

Obviously I figured that out through the logs =-).

The novelty created in order to implement this feature is the pg_db_role_setting table, that internally has this structure:

postgres=# \d pg_catalog.pg_db_role_setting
Table "pg_catalog.pg_db_role_setting"
   Column    |  Type  | Modifiers 
-------------+--------+-----------
 setdatabase | oid    | not null
 setrole     | oid    | not null
 setconfig   | text[] | 
Indexes:
    "pg_db_role_setting_databaseid_rol_index" UNIQUE, btree (setdatabase, setrole), tablespace "pg_global"
Tablespace: "pg_global"

Reference:

http://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.0#Per_database.2Brole_configuration


And something as simple as that, is what it takes to make someone like me happy =-D.

No comments:

Post a Comment