logging to PostgreSQL database
Hi, I am a student of Computer Science at University of Ljubljana. Recently I started working on some project which includes logging into PostgreSQL database. I came across some problem which I can't resolve myself. No matter what I do, syslog-ng refuses to log into local PostgreSQL database. Here is my current configuration: using syslog-ng 3.2 server contents of syslog-ng.conf ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ @version: 3.2 #Default configuration file for syslog-ng. # # For a description of syslog-ng configuration file directives, please read # the syslog-ng Administrator's guide at: # # http://www.balabit.com/dl/html/syslog-ng-admin-guide_en.html/bk01-toc.html # options { time_reap(30); mark_freq(10); keep_hostname(yes); }; ###### # sources source s_local { # message generated by Syslog-NG internal(); # standard Linux log source (this is the default place for the syslog() # function to send logs to) unix-stream("/dev/log"); # messages from the kernel file("/proc/kmsg" program_override("kernel")); }; # source s_syslog { syslog(ip(127.0.0.1) port(1999) transport("tcp")); }; ######################## # Filters ######################## # Here's come the filter options. With this rules, we can set which # message go where. # messages for eneraptor should have priority level LOG_MAIL and contain keyword "eneraptor" filter f_mail_eneraptor { facility(mail) and match("eneraptor"); }; ###### # destinations destination d_messages { file("/var/log/messages"); }; destination filtered_messages { file("/var/log/messages_filtered"); }; # # SQL logging support # destination d_pgsql { sql(type(pgsql) host("localhost") username("eneraptor") password("eneraptor") database("eneraptordb") table("logs") columns("datetime varchar(16)", "host varchar(32)", "program varchar(8)", "pid varchar(8)", "message varchar(200)") values("$R_DATE", "$HOST", "$PROGRAM", "$PID", "$MSG") indexes("datetime", "host", "program", "pid", "message")); }; log { source(s_local); destination(d_messages); # destination(d_pgsql); }; log { source(s_local); filter(f_mail_eneraptor); destination(filtered_messages); destination(d_pgsql); }; ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ using PostgreSQL 8.4 contents of pg_hba.conf: ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ... # # Database administrative login by UNIX sockets local all all trust # TYPE DATABASE USER CIDR-ADDRESS METHOD # "local" is for Unix domain socket connections only # local all all trust # IPv4 local connections: # host all all 127.0.0.1/32 md5 # IPv6 local connections: host all all ::1/128 md5 # syslog-ng logger # host eneraptordb eneraptor 127.0.0.1/32 trust ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ As far as I understand this configuration file, any user should have full access to any database on local machine. At this point, I am not concerned about security issues as I plan to address them after I resolve logging problem. postgresql.conf: Below are included only lines I uncommented or changed. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ... listen_addresses = 'localhost' # what IP address(es) to listen on; ... password_encryption = on ... ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ This is the error I get, logged by syslog-ng: *Jan 5 22:00:38 computerName syslog-ng[11080]: Error establishing SQL connection; type='pgsql', host='localhost', port='', username='eneraptor', database='eneraptordb', error='could not connect to server: Connection refused\x0a\x09Is the server running on host "localhost" and accepting\x0a\x09TCP/IP connections on port 0?\x0a'* * * Database has also been created, properly named and has the right user. Message I am trying to log is being sent by test program written in C, line that sends message is (every few seconds): *syslog(LOG_MAIL, "eneraptor test: %d", counter);*
From what I could gather is that syslog-ng probably can't connect to database, but I am running out of options on how to resolve this issue. I would be very grateful if you could help me resolve this issue.
Best regards, Janez Barbic
On Wed, Jan 05, 2011 at 10:24:17PM +0100, Janez Barbič wrote:
Hi, I am a student of Computer Science at University of Ljubljana. Recently I started working on some project which includes logging into PostgreSQL database.
Welcome to the list.
This is the error I get, logged by syslog-ng: *Jan 5 22:00:38 computerName syslog-ng[11080]: Error establishing SQL connection; type='pgsql', host='localhost', port='', username='eneraptor', database='eneraptordb', error='could not connect to server: Connection refused\x0a\x09Is the server running on host "localhost" and accepting\x0a\x09TCP/IP connections on port 0?\x0a'
Are you setting the port right? Here it seems to be zeroed out, which is probably bad, there have also been some bugs in syslog-ng and libdbi which led to zeroed out or ignored port values before. Try using tcpdump or Wireshark or tshark on the lo interface to monitor the connection attempt to see if something looks wrong there. You can compare with what happens on a successful connect from the CLI client to spot the difference.
Database has also been created, properly named and has the right user.
What happens when you try to query and add values to the table with the CLI client?
From what I could gather is that syslog-ng probably can't connect to database, but I am running out of options on how to resolve this issue. I would be very grateful if you could help me resolve this issue.
We can probably help you figure it out but you'll have to do some debugging work as well to figure out the specifics.
Best regards, Janez Barbic
Good Luck, Matthew Hall.
Hi Matthew, I solved it! :) Thank you for pointing out the obvious, I had issues with port. Even though I noticed zeroed out port I paid no attention to it because I assumed syslog-ng used default Postgres port. Postgres is listening to port 5432 (default), but syslog-ng was sending packets to port 0 (again, thanks for Wireshark idea). So I just pointed syslog-ng to the correct port and it started to work. I must also say that I am positively surprised by really fast response :) Best regards, Janez Barbic On Wed, Jan 5, 2011 at 10:31 PM, Matthew Hall <mhall@mhcomputing.net> wrote:
On Wed, Jan 05, 2011 at 10:24:17PM +0100, Janez Barbič wrote:
Hi, I am a student of Computer Science at University of Ljubljana. Recently I started working on some project which includes logging into PostgreSQL database.
Welcome to the list.
This is the error I get, logged by syslog-ng: *Jan 5 22:00:38 computerName syslog-ng[11080]: Error establishing SQL connection; type='pgsql', host='localhost', port='', username='eneraptor', database='eneraptordb', error='could not connect to server: Connection refused\x0a\x09Is the server running on host "localhost" and accepting\x0a\x09TCP/IP connections on port 0?\x0a'
Are you setting the port right? Here it seems to be zeroed out, which is probably bad, there have also been some bugs in syslog-ng and libdbi which led to zeroed out or ignored port values before. Try using tcpdump or Wireshark or tshark on the lo interface to monitor the connection attempt to see if something looks wrong there. You can compare with what happens on a successful connect from the CLI client to spot the difference.
Database has also been created, properly named and has the right user.
What happens when you try to query and add values to the table with the CLI client?
From what I could gather is that syslog-ng probably can't connect to database, but I am running out of options on how to resolve this issue. I would be very grateful if you could help me resolve this issue.
We can probably help you figure it out but you'll have to do some debugging work as well to figure out the specifics.
Best regards, Janez Barbic
Good Luck, Matthew Hall.
______________________________________________________________________________ Member info: https://lists.balabit.hu/mailman/listinfo/syslog-ng Documentation: http://www.balabit.com/support/documentation/?product=syslog-ng FAQ: http://www.campin.net/syslog-ng/faq.html
On Thu, Jan 06, 2011 at 12:18:12AM +0100, Janez Barbič wrote:
Hi Matthew,
I solved it! :)
Wonderful.
Even though I noticed zeroed out port I paid no attention to it because I assumed syslog-ng used default Postgres port. Postgres is listening to port 5432 (default), but syslog-ng was sending packets to port 0
Normally something like this would not happen, but there have been a series of odd bugs in the behavior of DB ports for different DBs due to various syslog-ng vs. DBI interactions that have gone wrong.
(again, thanks for Wireshark idea).
I used to create network anomaly detection software, so I never believe anything about socket programming unless I have packet captures or detailed debug logs. Preferably both, because firewalls and applications can reject traffic at L3-L7 after the packet capture gets the traffic at L2.
So I just pointed syslog-ng to the correct port and it started to work.
Good thing it did. Otherwise we would have had to track down another port bug. Although it's a bug it defaults to port 0 which nobody uses instead of defaulting to the Postgres port. Maybe you could put this into Bugzilla?
I must also say that I am positively surprised by really fast response :)
There's a rule of open source that if you want a prompt response on a mailing list, you should provide prompt responses to everyone else. I really try to follow this.
Best regards, Janez Barbic
Regards, Matthew Hall.
On Wed, 2011-01-05 at 22:24 +0100, Janez Barbič wrote:
Hi, I am a student of Computer Science at University of Ljubljana. Recently I started working on some project which includes logging into PostgreSQL database. I came across some problem which I can't resolve myself. No matter what I do, syslog-ng refuses to log into local PostgreSQL database. Here is my current configuration:
This is the error I get, logged by syslog-ng: Jan 5 22:00:38 computerName syslog-ng[11080]: Error establishing SQL connection; type='pgsql', host='localhost', port='', username='eneraptor', database='eneraptordb', error='could not connect to server: Connection refused\x0a\x09Is the server running on host "localhost" and accepting\x0a\x09TCP/IP connections on port 0?\x0a'
Just a quick guess, before I check my own PostgreSQL setup: judging by the error message you received, would it help if you explicitly specified which port Postgre is listening on? -- |8]
participants (3)
-
Gergely Nagy
-
Janez Barbič
-
Matthew Hall