0

I am on Linux platform with PostgreSQL 5.5. I am trying to monitor all traffic related to PostgreSQL between Master and Slave. To that end, I used Wireshark to monitor the traffic. Then, I started PostgreSQL and ran various queries. During queries, I ran Wireshark on Master just to capture the traffic between Master and Slave.

But there is one problems with PostgreSQL traffic captured using Wireshark. All the traffic is sent/received in TCP packets and that traffic is in coded form. I can't read that data. Please see the image below: WireShark File's Image for PostgreSQL DB.

I want to find out exact queries from Wirehsark that I inserted in PostgreSQL database. What is the best way to go about finding queries of PostgreSQL?

On the other hand, I ran same queries on MySQL database and repeated above mentioned experiment. I can easily read all those three queries in wireshark dump because they are not in coded form. Please see the image below: WireShark File's Image for MySQL DB At the end of the image, it is shown exact query that I inserted in MySQL. But I can' read the same query in PostgreSQL case (Ref. 1st image) I need to find out above query from Wirehsark file.

About File:

  • 192.168.50.11 is the source machine from where I inserted queries to remote PostgreSQL's Master server
  • 192.168.50.12 is the IP of Master's server
  • 192.168.50.13 is the slave's IP address

Queries were executed from .11 and inserted into .12 and then replicated to .13 using Master-Slave approach. Pointers will be very welcome.

Muhammad Karam Shehzad
  • 49
  • 1
  • 7
  • 1
    Wireshark supports PostgreSQL dissection (See: https://wiki.wireshark.org/PostgresProtocol). It's difficult to analyze screenshots; perhaps you could post a capture file somewhere, such as on cloudshark, pastebin, ... – Christopher Maynard Apr 26 '17 at 14:26
  • This looks to me like a repost from your other question [How to find (decode) PostgreSQL query from Wireshark File?](http://stackoverflow.com/questions/43603225/how-to-find-decode-postgresql-query-from-wireshark-file) - "decode" and "decrypt" mean the same thing, IMHO. – Funk Forty Niner May 02 '17 at 13:44

1 Answers1

0

Solution to my own problem:

I got the solution of my question.

I used Python code to insert queries into remote PostgreSQL database. I used following line in PostgreSQL to connect with database. con = psycopg2.connect(host="192.168.50.12", database="postgres", user="postgres", password="faban") If you use above approach then all the data will be sent in encrypted form. If you use the approach given below in python code then all the data will be sent in decrypted form. You can easily read all queries in Wireshark. con = psycopg2.connect("host=192.168.50.12 dbname=postgres user=postgres password=faban sslmode=disable") Same is the case in C-Code as well. Decrypted data sprintf(conninfo, "dbname=postgres hostaddr=192.168.50.12 user=postgres password=faban sslmode=disable"); Encrypted Data sprintf(conninfo, "dbname=postgres hostaddr=192.168.50.12 user=postgres password=faban");

Muhammad Karam Shehzad
  • 49
  • 1
  • 7