Reg : Logging

From: Technical Doubts <online(dot)technicaldoubts(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Reg : Logging
Date: 2013-11-25 06:32:06
Message-ID: CAJyuQsHEV+6iUVd8rjf3WAZ-GTiUQEux8aEi1s9ejVsFX2ALaA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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.

Thanks in advance.

-
John

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Raghu Ram 2013-11-25 07:39:33 Re: Reg : Logging
Previous Message Stephen Frost 2013-11-25 02:38:05 Re: GSSAPI authentication with AD