SQL Parameters missing when using tshark against PostgreSQL v12 traffic-CodePudding

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 ?

CodePudding user response：

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).

CodePudding user response：

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.