Обсуждение: conditional logging based on client
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.
Στις 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
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
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 >
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
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.
"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