Re: Reg : Logging

[Raghu Ram <raghuchennuru@xxxxxxxxx>]

On Mon, Nov 25, 2013 at 12:02 PM, Technical Doubts <online.technicaldoubts@xxxxxxxxx> wrote:

Dear Team,

We are using PostgreSQL 9.2

We have approx 10 users with individual user names to access db through pg Admin.

Also our requirement is like, we have to log all the queries executed by the user invcluding select queries.

For the same we set the log_statement for all users as 'ALL'. It's perfectly working and all the queries are being logged.

The problem is, while the user executes the queries from pg Admin, back end queries are also getting logged as below for user developer1 for even a select count(*) query.

developer1 2013-11-23 14:21:25 IST 193.45.21.20 LOG:  statement: SELECT CASE WHEN nspname LIKE E'pg\\_temp\\_%' THEN 1
                WHEN (nspname LIKE E'pg\\_%') THEN 0
                ELSE 3 END AS nsptyp,
           nsp.nspname, nsp.oid, pg_get_userbyid(nspowner) AS namespaceowner, nspacl, description,       has_schema_privilege(nsp.oid, 'CREATE') as cancreate,
    (SELECT array_agg(label) FROM pg_seclabels sl1 WHERE sl1.objoid=nsp.oid) AS labels,
    (SELECT array_agg(provider) FROM pg_seclabels sl2 WHERE sl2.objoid=nsp.oid) AS providers
      FROM pg_namespace nsp
      LEFT OUTER JOIN pg_description des ON des.objoid=nsp.oid
     WHERE NOT ((nspname = 'pg_catalog' AND EXISTS (SELECT 1 FROM pg_class WHERE relname = 'pg_class' AND relnamespace = nsp.oid LIMIT 1)) OR
    (nspname = 'pgagent' AND EXISTS (SELECT 1 FROM pg_class WHERE relname = 'pga_job' AND relnamespace = nsp.oid LIMIT 1)) OR
    (nspname = 'information_schema' AND EXISTS (SELECT 1 FROM pg_class WHERE relname = 'tables' AND relnamespace = nsp.oid LIMIT 1)) OR
    (nspname LIKE '_%' AND EXISTS (SELECT 1 FROM pg_proc WHERE proname='slonyversion' AND pronamespace = nsp.oid LIMIT 1))
    )  AND nspname NOT LIKE E'pg\\_temp\\_%'AND nspname NOT LIKE E'pg\\_toast_temp\\_%' ORDER BY 1, nspname
developer1 2013-11-23 14:21:25 IST 193.45.21.20 LOG:  statement: SELECT defaclacl FROM pg_catalog.pg_default_acl dacl WHERE dacl.defaclnamespace = 16394::oid AND defaclobjtype='r'
developer1 2013-11-23 14:21:25 IST 193.45.21.20 LOG:  statement: SELECT defaclacl FROM pg_catalog.pg_default_acl dacl WHERE dacl.defaclnamespace = 16394::oid AND defaclobjtype='S'
developer1 2013-11-23 14:21:25 IST 193.45.21.20 LOG:  statement: SELECT defaclacl FROM pg_catalog.pg_default_acl dacl WHERE dacl.defaclnamespace = 16394::oid AND defaclobjtype='f'
developer1 2013-11-23 14:21:25 IST 193.45.21.20 LOG:  statement: SELECT defaclacl FROM pg_catalog.pg_default_acl dacl WHERE dacl.defaclnamespace =

Kindly suggest how to avoid logging the back end queries and capture the exact query executed by user.

Requesting you to modify below parameters in "postgresql.conf" file to log all the queries executed by the user including select queries:

log_statement='mod'

log_line_prefix = '%t [%p]: [%l-1] db=%d,user=%u client=%r application_name=%a '

log_min_duration_statement = 0 

Thanks & Regards

Raghu Ram