0

I am on Linux 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 those 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 the exact queries from Wireshark that I inserted in the 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 the 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, the exact query that I inserted in MySQL is shown. But I can't read the same query in PostgreSQL case (refer to the first image).

I need to find out above query from Wireshark file.

About the 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 the master-slave approach. Pointers will be very welcome.

Matthias Braun
  • 32,039
  • 22
  • 142
  • 171
Muhammad Karam Shehzad
  • 57
  • 1
  • 8
  • 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
  • See also [this question](https://stackoverflow.com/questions/18456934/how-to-view-encrypted-application-data-in-wireshark): "How to view Encrypted Application Data in Wireshark" – Matthias Braun Dec 26 '22 at 19:35

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
  • 57
  • 1
  • 8