conditional logging based on client

pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed

conditional logging based on client
7+ messages / 5 participants
[nested] [flat]

* conditional logging based on client
@ 2008-07-22 12:17  Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Rajesh Kumar Mallah @ 2008-07-22 12:17 UTC (permalink / raw)
  To: pgsql-admin

Hi,

We want to log all SQLs that has been executed by using psql client.
we do not want to use .psql_history as it is distributed and may be
deleted by users .

The original objective is that we should be able to know what all hand made
SQLs have  been executed in past  (which can date back as long as 1 year even)

regds
Mallah.



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: conditional logging based on client
@ 2008-07-22 12:24  Achilleas Mantzios <achill@matrix.gatewaynet.com>
  parent: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
  0 siblings, 2 replies; 7+ messages in thread

From: Achilleas Mantzios @ 2008-07-22 12:24 UTC (permalink / raw)
  To: pgsql-admin; +Cc: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>

Στις Tuesday 22 July 2008 15:17:42 ο/η Rajesh Kumar Mallah έγραψε:
> Hi,
> 
> We want to log all SQLs that has been executed by using psql client.
> we do not want to use .psql_history as it is distributed and may be
> deleted by users .
> 
> The original objective is that we should be able to know what all hand made
> SQLs have  been executed in past  (which can date back as long as 1 year even)

You have to adjust log_statement in your postgresql.conf
however you will have to bear in mind the performance implications of this.
Normal production sites dont do this.
You can enable/disable this kind of logging any time by killing -HUP
> 
> regds
> Mallah.
> 



-- 
Achilleas Mantzios



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: conditional logging based on client
@ 2008-07-22 17:40  Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
  parent: Achilleas Mantzios <achill@matrix.gatewaynet.com>
  1 sibling, 2 replies; 7+ messages in thread

From: Rajesh Kumar Mallah @ 2008-07-22 17:40 UTC (permalink / raw)
  To: Achilleas Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-admin

On Tue, Jul 22, 2008 at 5:54 PM, Achilleas Mantzios
<achill@matrix.gatewaynet.com> wrote:
> Óôéò Tuesday 22 July 2008 15:17:42 ï/ç Rajesh Kumar Mallah Ýãñáøå:
>> Hi,
>>
>> We want to log all SQLs that has been executed by using psql client.
>> we do not want to use .psql_history as it is distributed and may be
>> deleted by users .
>>
>> The original objective is that we should be able to know what all hand made
>> SQLs have  been executed in past  (which can date back as long as 1 year even)
>
> You have to adjust log_statement in your postgresql.conf
> however you will have to bear in mind the performance implications of this.
> Normal production sites dont do this.
> You can enable/disable this kind of logging any time by killing -HUP

thanks for reply.
any hit on performance is not desired.
is it possible to alter logging behaviour just for the
psql session ?

>>
>> regds
>> Mallah.
>>
>
>
>
> --
> Achilleas Mantzios
>


^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: conditional logging based on client
@ 2008-07-22 18:47  Shane Ambler <pgsql@Sheeky.Biz>
  parent: Achilleas Mantzios <achill@matrix.gatewaynet.com>
  1 sibling, 0 replies; 7+ messages in thread

From: Shane Ambler @ 2008-07-22 18:47 UTC (permalink / raw)
  To: Achilleas Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-admin@postgresql.org, Rajesh Kumar Mallah <mallah.rajesh@gmail.com>

Achilleas Mantzios wrote:
> Î£Ï„Î¹Ï‚ Tuesday 22 July 2008 15:17:42 Î¿/Î· Rajesh Kumar Mallah
> ÎÎ³ÏÎ±ÏˆÎµ:
>> Hi,
>> 
>> We want to log all SQLs that has been executed by using psql
>> client. we do not want to use .psql_history as it is distributed
>> and may be deleted by users .
>> 
>> The original objective is that we should be able to know what all
>> hand made SQLs have  been executed in past  (which can date back as
>> long as 1 year even)
> 
> You have to adjust log_statement in your postgresql.conf however you
> will have to bear in mind the performance implications of this. 
> Normal production sites dont do this. You can enable/disable this
> kind of logging any time by killing -HUP

I am thinking that is a bit more than what you want. You can log all sql
statements run on a server but the server can't differentiate between
your custom-app sent sql and hand made sql sent through psql.

I know you can add things like remote address, username, dbname,
sessionid to the start of each log line but I don't know of getting the
client app name.

My thought would be to enable the statement logging and to modify your
client apps to have a set comment at the beginning and end of all sql
statements sent. You could then filter these sql statements out of the
logs leaving you with hand crafted sql.

-- 

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: conditional logging based on client
@ 2008-07-23 16:44  Shane Ambler <pgsql@Sheeky.Biz>
  parent: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
  1 sibling, 0 replies; 7+ messages in thread

From: Shane Ambler @ 2008-07-23 16:44 UTC (permalink / raw)
  To: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>; +Cc: Achilleas Mantzios <achill@matrix.gatewaynet.com>; pgsql-admin

Rajesh Kumar Mallah wrote:
> On Tue, Jul 22, 2008 at 5:54 PM, Achilleas Mantzios 

>>> The original objective is that we should be able to know what all
>>> hand made SQLs have  been executed in past  (which can date back
>>> as long as 1 year even)
>> You have to adjust log_statement in your postgresql.conf however
>> you will have to bear in mind the performance implications of this.
>>  Normal production sites dont do this. You can enable/disable this
>> kind of logging any time by killing -HUP
> 
> thanks for reply. any hit on performance is not desired. is it
> possible to alter logging behaviour just for the psql session ?
> 

Not quite but the opposite could be a path to look down.

The config specifies logging statements that is applied to all sessions.
Your app could then be setup to alter this at run time.

I haven't tried this - it is only a theory - I would think that you
could create a function to turn off logging but you would also need to
look into restricting access to only your app (your app provides some
sort of code maybe).

The biggest problem here will be preventing users from performing this 
command by hand in psql. This is where the function would allow a point 
that must be 'authenticated' by your app.

http://www.postgresql.org/docs/8.3/interactive/functions-admin.html
and
http://www.postgresql.org/docs/8.3/interactive/view-pg-settings.html

as well as SECURITY DEFINER which is the reason for the function - it
must be run as a superuser.
http://www.postgresql.org/docs/8.3/interactive/sql-createfunction.html

Another option would be to have them use a different user name when 
using psql. Probably not an option unless the app runs with it's own 
username.

-- 

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: conditional logging based on client
@ 2008-07-23 17:22  Scott Marlowe <scott.marlowe@gmail.com>
  parent: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
  1 sibling, 1 reply; 7+ messages in thread

From: Scott Marlowe @ 2008-07-23 17:22 UTC (permalink / raw)
  To: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>; +Cc: Achilleas Mantzios <achill@matrix.gatewaynet.com>; pgsql-admin

2008/7/22 Rajesh Kumar Mallah <mallah.rajesh@gmail.com>:
> On Tue, Jul 22, 2008 at 5:54 PM, Achilleas Mantzios
> <achill@matrix.gatewaynet.com> wrote:
>> Óôéò Tuesday 22 July 2008 15:17:42 ï/ç Rajesh Kumar Mallah Ýãñáøå:
>>> Hi,
>>>
>>> We want to log all SQLs that has been executed by using psql client.
>>> we do not want to use .psql_history as it is distributed and may be
>>> deleted by users .
>>>
>>> The original objective is that we should be able to know what all hand made
>>> SQLs have  been executed in past  (which can date back as long as 1 year even)
>>
>> You have to adjust log_statement in your postgresql.conf
>> however you will have to bear in mind the performance implications of this.
>> Normal production sites dont do this.
>> You can enable/disable this kind of logging any time by killing -HUP
>
> thanks for reply.
> any hit on performance is not desired.
> is it possible to alter logging behaviour just for the
> psql session ?

Set log_statement='ddl' in your postgresql.conf and pgsql will log all
ddl that hits the database.  It won't log other stuff, just the ddl.


^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: conditional logging based on client
@ 2008-07-23 19:12  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Scott Marlowe <scott.marlowe@gmail.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Tom Lane @ 2008-07-23 19:12 UTC (permalink / raw)
  To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>; Achilleas Mantzios <achill@matrix.gatewaynet.com>; pgsql-admin

"Scott Marlowe" <scott.marlowe@gmail.com> writes:
> 2008/7/22 Rajesh Kumar Mallah <mallah.rajesh@gmail.com>:
>> any hit on performance is not desired.
>> is it possible to alter logging behaviour just for the
>> psql session ?

> Set log_statement='ddl' in your postgresql.conf and pgsql will log all
> ddl that hits the database.  It won't log other stuff, just the ddl.

I think the OP didn't want *all* sessions doing this but just selected
sessions; so ALTER USER ... SET log_statement, or possibly
ALTER DATABASE ... SET, would perhaps serve better.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

end of thread, other threads:[~2008-07-23 19:12 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-07-22 12:17 conditional logging based on client Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
2008-07-22 12:24 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2008-07-22 17:40   ` Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
2008-07-23 16:44     ` Shane Ambler <pgsql@Sheeky.Biz>
2008-07-23 17:22     ` Scott Marlowe <scott.marlowe@gmail.com>
2008-07-23 19:12       ` Tom Lane <tgl@sss.pgh.pa.us>
2008-07-22 18:47   ` Shane Ambler <pgsql@Sheeky.Biz>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox