Re: Reg : Logging

On Mon, Nov 25, 2013 at 12:02 PM, Technical Doubts <
online(dot)technicaldoubts(at)gmail(dot)com> 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