Question 114861 in PostgreSQL Question posted in
The official documentation can be found here.

SQL Parameters missing when using tshark against PostgreSQL v12 traffic

I’m attempting to spy on non-SSL PostgreSQL traffic using tshark using the following command:

# tshark -f 'tcp dst port 5432' -O PGSQL 
   -d 'tcp.port==5432,pgsql' -T fields -e pgsql.query

I am able to see SQL queries, but all the actual values/parameters are missing (instead replaced with placeholders $1, $2, $3 etc). Example output is as follows:

...
INSERT INTO mdl_logstore_standard_log
(eventname,component,action,target,objecttable,objectid,crud,edulevel,
contextid,contextlevel,contextinstanceid,userid,courseid,relateduserid,
anonymous,other,timecreated,origin,ip,realuserid)
VALUES
($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20),
($21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$32,$33,$34,$35,$36,$37,$38,$39,$40)
INSERT INTO mdl_backup_files_temp (contextid,component,filearea,itemid,info,backupid)
 VALUES($1,$2,$3,$4,$5,$6) RETURNING id RELEASE SAVEPOINT
moodle_pg_savepoint; SAVEPOINT moodle_pg_savepoint SELECT * FROM mdl_backup_ids_temp
WHERE backupid = $1 AND itemname = $2 AND itemid = $3 INSERT INTO
mdl_backup_files_temp (contextid,component,filearea,itemid,info,backupid)
VALUES($1,$2,$3,$4,$5,$6) RETURNING id RELEASE SAVEPOINT moodle_pg_savepoint;
SAVEPOINT moodle_pg_savepoint
...

What am I missing here – and how can I view the values/parameters as well ?

Tags:

2

Answers


  1. 0

    In order to see the values passed in to a prepared statement, you’ll need to set log_statement to either mod or all. log_statement prints the statement that is about to be executed, and includes the parameters/arguments used.

    I think the easiest way to turn it on is by doing:

    psql -c "ALTER SYSTEM SET log_statement TO 'all'"
psql -c "SELECT pg_reload_conf()"

    From there, you should be able to view the parameters.

    Bear in mind, this has the potential to generate a lot of traffic, so you’ll sent to set it back to the previous value once you’re done (you can get the current value by calling psql -c "SHOW log_statement" before you do the two commands above).

    Login or Signup to reply.
  2. 0

    In order to see the parameters, you would give tshark another field to print:

    -e pgsql.query -e pgsql.val.data

    But this is going to be mess, especially if you use prepared statements. You should really just figure out what you are doing wrong with log_statement='all', it will log all statements not just a sample of them. Maybe you have that setting countermanded per user, per database, or per connection.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search