Обсуждение: how _not_ to log?
Hello there!
I've seen lots of people who have asked questions about how to log this or that, but I have the opposite question!
:-) I'm seeing this in my logs:
Jul 25 18:08:11 staging-db11 postgres[27050]: [10-2] STATEMENT: create role pguser encrypted password 'XXX';
Where XXX is the actual password. This happens every 30 minutes when my chef client kicks off and resets the
passwords. Here's everything that I have in postgres.conf related to logging:
log_destination = 'syslog' # Valid values are combinations of
# stderr, csvlog, syslog, and eventlog,
# depending on platform. csvlog
# requires logging_collector to be on.
logging_collector = on # Enable capturing of stderr and csvlog
# into log files. Required to be on for
# csvlogs.
log_directory = 'pg_log' # directory where log files are written,
log_filename = 'postgresql-%a.log' # log file name pattern,
log_truncate_on_rotation = on # If on, an existing log file with the
# same name as the new log file will be
log_rotation_age = 1d # Automatic rotation of logfiles will
log_rotation_size = 0 # Automatic rotation of logfiles will
# happen after that much log output.
# DO NOT USE without syslog or
# logging_collector
log_min_duration_statement = 2000 # 2 seconds
log_checkpoints = on
What I'd like to do is stop logging create role commands, as the logs end up full of passwords. Is there any way
todo this? Thanks, and have fun!
-tspencer
On 07/25/2013 03:59 PM, Tim Spencer wrote: > Hello there! > > I've seen lots of people who have asked questions about how to log this or that, but I have the opposite question! :-) I'm seeing this in my logs: > > Jul 25 18:08:11 staging-db11 postgres[27050]: [10-2] STATEMENT: create role pguser encrypted password 'XXX'; > > Where XXX is the actual password. This happens every 30 minutes when my chef client kicks off and resets the passwords. Here's everything that I have in postgres.conf related to logging: > > > What I'd like to do is stop logging create role commands, as the logs end up full of passwords. Is there any way todo this? Thanks, and have fun! The STATEMENT you show is from another process? I would except resetting of passwords to use ALTER ROLE. As an aside: http://www.postgresql.org/docs/9.2/interactive/sql-alterrole.html Caution must be exercised when specifying an unencrypted password with this command. The password will be transmitted to the server in cleartext, and it might also be logged in the client's command history or the server log. psql contains a command \password that can be used to change a role's password without exposing the cleartext password. As to how to make it stop, set log_statement = 'none' ? This will not log anything though, see below for details: http://www.postgresql.org/docs/9.2/interactive/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT log_statement (enum) Controls which SQL statements are logged. Valid values are none (off), ddl, mod, and all (all statements). ddl logs all data definition statements, such as CREATE, ALTER, and DROP statements. mod logs all ddl statements, plus data-modifying statements such as INSERT, UPDATE, DELETE, TRUNCATE, and COPY FROM. PREPARE, EXECUTE, and EXPLAIN ANALYZE statements are also logged if their contained command is of an appropriate type. For clients using extended query protocol, logging occurs when an Execute message is received, and values of the Bind parameters are included (with any embedded single-quote marks doubled). The default is none. Only superusers can change this setting. > > -tspencer > > > -- Adrian Klaver adrian.klaver@gmail.com
On Thursday, July 25, 2013, Tim Spencer wrote:
Hello there!
I've seen lots of people who have asked questions about how to log this or that, but I have the opposite question! :-) I'm seeing this in my logs:
Jul 25 18:08:11 staging-db11 postgres[27050]: [10-2] STATEMENT: create role pguser encrypted password 'XXX';
Where XXX is the actual password. This happens every 30 minutes when my chef client kicks off and resets the passwords. Here's everything that I have in postgres.conf related to logging:
log_destination = 'syslog' # Valid values are combinations of
# stderr, csvlog, syslog, and eventlog,
# depending on platform. csvlog
# requires logging_collector to be on.
logging_collector = on # Enable capturing of stderr and csvlog
# into log files. Required to be on for
# csvlogs.
log_directory = 'pg_log' # directory where log files are written,
log_filename = 'postgresql-%a.log' # log file name pattern,
log_truncate_on_rotation = on # If on, an existing log file with the
# same name as the new log file will be
log_rotation_age = 1d # Automatic rotation of logfiles will
log_rotation_size = 0 # Automatic rotation of logfiles will
# happen after that much log output.
# DO NOT USE without syslog or
# logging_collector
log_min_duration_statement = 2000 # 2 seconds
log_checkpoints = on
What I'd like to do is stop logging create role commands, as the logs end up full of passwords. Is there any way to do this? Thanks, and have fun!
Have chef supply the password in encrypted format.
It's not that well documented yet though, as far as I can tell. See this thread: http://www.postgresql.org/message-id/201110272054.p9RKsKs18362@momjian.us
Seems like that information should be in the CREATE ROLE docs.
-tspencer
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Jul 25, 2013, at 11:45 PM, Joe Van Dyk <joe@tanga.com> wrote:
> Have chef supply the password in encrypted format.
>
Interesting idea. I was hoping that somebody would be able to solve my logging issue instead of me having to
rejiggermy nice centralized password system. :-) Is there really no way to turn off the logging of those queries?
> It's not that well documented yet though, as far as I can tell. See this thread:
http://www.postgresql.org/message-id/201110272054.p9RKsKs18362@momjian.us
>
The fact that it's not well documented seems like it might be changed later on. Is this format set in stone?
Thanks, and have fun!
-tspencer
On Jul 26, 2013, at 1:42 PM, Tim Spencer wrote:
> On Jul 25, 2013, at 11:45 PM, Joe Van Dyk <joe@tanga.com> wrote:
>> Have chef supply the password in encrypted format.
>>
> I was hoping that somebody would be able to solve my logging issue instead of me having to rejigger my nice
centralizedpassword system.
Another "rejigger" idea would be to use psql meta-command:
\password fred
It logs a little more friendly:
2013-07-26 13:59:58 EDT,0,idle LOG: 00000: statement: ALTER USER fred PASSWORD
'md5af0d89ddc522353ffe41de823a94c0e1'
http://www.postgresql.org/docs/9.2/interactive/app-psql.html
On Thu, Jul 25, 2013 at 3:59 PM, Tim Spencer <tspencer@cloudpassage.com> wrote: > Hello there! > > I've seen lots of people who have asked questions about how to log this or that, but I have the opposite question! :-) I'm seeing this in my logs: > > Jul 25 18:08:11 staging-db11 postgres[27050]: [10-2] STATEMENT: create role pguser encrypted password 'XXX'; That does not look like the entire message. What was before and after it in the log? For example: ERROR: role "foobar" already exists STATEMENT: create role foobar encrypted password 'XXX'; If it were not for the ERROR, the STATEMENT would not be being logged, in my hands. > > Where XXX is the actual password. This happens every 30 minutes when my chef client kicks off and resets the passwords. Here's everything that I have in postgres.conf related to logging: > > log_destination = 'syslog' # Valid values are combinations of > # stderr, csvlog, syslog, and eventlog, > # depending on platform. csvlog > # requires logging_collector to be on. > logging_collector = on # Enable capturing of stderr and csvlog > # into log files. Required to be on for > # csvlogs. > log_directory = 'pg_log' # directory where log files are written, > log_filename = 'postgresql-%a.log' # log file name pattern, > log_truncate_on_rotation = on # If on, an existing log file with the > # same name as the new log file will be > log_rotation_age = 1d # Automatic rotation of logfiles will > log_rotation_size = 0 # Automatic rotation of logfiles will > # happen after that much log output. > # DO NOT USE without syslog or > # logging_collector > log_min_duration_statement = 2000 # 2 seconds > log_checkpoints = on What about log_min_error_statement ? > > What I'd like to do is stop logging create role commands, as the logs end up full of passwords. Is there any wayto do this? Thanks, and have fun! First you need to find out why they were getting logged. I don't think any of the setting you showed explain that. Also, I don't think anything you can do will render it acceptable to show your log files to unprivileged users, if that is what you are aiming for. Cheers, Jeff
On 07/26/2013 10:42 AM, Tim Spencer wrote: > On Jul 25, 2013, at 11:45 PM, Joe Van Dyk <joe@tanga.com> wrote: >> Have chef supply the password in encrypted format. >> > Interesting idea. I was hoping that somebody would be able to solve my logging issue instead of me having to rejiggermy nice centralized password system. :-) Is there really no way to turn off the logging of those queries? > >> It's not that well documented yet though, as far as I can tell. See this thread: http://www.postgresql.org/message-id/201110272054.p9RKsKs18362@momjian.us >> > The fact that it's not well documented seems like it might be changed later on. Is this format set in stone? Yes, if you consider the docs stone: http://www.postgresql.org/docs/9.2/static/protocol-flow.html AuthenticationMD5Password > > Thanks, and have fun! > > -tspencer > > > -- Adrian Klaver adrian.klaver@gmail.com
On Fri, Jul 26, 2013 at 2:54 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote: > http://www.postgresql.org/docs/9.2/interactive/sql-alterrole.html > > Caution must be exercised when specifying an unencrypted password > with this command. The password will be transmitted to the server in > cleartext, and it might also be logged in the client's command history or > the server log. psql contains a command \password that can be used to change > a role's password without exposing the cleartext password. Caution must be exercised with "encrypted" passwords too - they are cleartext-equivalent, which means you can use them to log in, without knowing the original password. And the "encryption" is single md5() so the actual password is relatively easy to crack too. So avoiding logging them is good idea. -- marko
On Jul 26, 2013, at 11:27 AM, Jeff Janes <jeff.janes@gmail.com> wrote:
> That does not look like the entire message. What was before and after
> it in the log?
>
> For example:
>
> ERROR: role "foobar" already exists
> STATEMENT: create role foobar encrypted password 'XXX';
>
> If it were not for the ERROR, the STATEMENT would not be being logged,
> in my hands.
>
Ah yes, that's it:
Jul 29 16:12:39 staging-db11 postgres[28849]: [34-1] ERROR: role "foobar" already exists
Jul 29 16:12:39 staging-db11 postgres[28849]: [34-2] STATEMENT: create role foobar with replication encrypted password
'XXX';
Interesting. I thought I was checking to see if the role existed in the chef recipe, but I guess that's somehow
failingand so it's trying to create the role. Somehow I spaced that it was doing a create role here instead of an
updateuntil you got me to dig into the log messages more. :-)
I guess I have some work on my end to do to make this work properly. Thanks, all, for your help, and have fun!
-tspencer