Sunday, January 1, 2012

Postgresql - Security : Sniffing PostgreSQL communications

This is the kind of info that wireshark can give you about any particular unencrypted network message using the pgsql protocol:

Field name Type Description Versions
pgsql.authtype Signed 32-bit integer Authentication type 1.0.0 to 1.6.4
pgsql.code String Code 1.0.0 to 1.6.4
pgsql.col.index Unsigned 32-bit integer Column index 1.0.0 to 1.6.4
pgsql.col.name String Column name 1.0.0 to 1.6.4
pgsql.col.typemod Signed 32-bit integer Type modifier 1.0.0 to 1.6.4
pgsql.condition String Condition 1.0.0 to 1.6.4
pgsql.copydata Byte array Copy data 1.0.0 to 1.6.4
pgsql.detail String Detail 1.0.0 to 1.6.4
pgsql.error String Error 1.0.0 to 1.6.4
pgsql.file String File 1.0.0 to 1.6.4
pgsql.format Unsigned 16-bit integer Format 1.0.0 to 1.6.4
pgsql.frontend Boolean Frontend 1.0.0 to 1.6.4
pgsql.hint String Hint 1.0.0 to 1.6.4
pgsql.key Unsigned 32-bit integer Key 1.0.0 to 1.6.4
pgsql.length Unsigned 32-bit integer Length 1.0.0 to 1.6.4
pgsql.line String Line 1.0.0 to 1.6.4
pgsql.message String Message 1.0.0 to 1.6.4
pgsql.oid Unsigned 32-bit integer OID 1.0.0 to 1.6.4
pgsql.oid.table Unsigned 32-bit integer Table OID 1.0.0 to 1.6.4
pgsql.oid.type Unsigned 32-bit integer Type OID 1.0.0 to 1.6.4
pgsql.parameter_name String Parameter name 1.0.0 to 1.6.4
pgsql.parameter_value String Parameter value 1.0.0 to 1.6.4
pgsql.password String Password 1.0.0 to 1.6.4
pgsql.pid Unsigned 32-bit integer PID 1.0.0 to 1.6.4
pgsql.portal String Portal 1.0.0 to 1.6.4
pgsql.position String Position 1.0.0 to 1.6.4
pgsql.query String Query 1.0.0 to 1.6.4
pgsql.routine String Routine 1.0.0 to 1.6.4
pgsql.salt Byte array Salt value 1.0.0 to 1.6.4
pgsql.severity String Severity 1.0.0 to 1.6.4
pgsql.statement String Statement 1.0.0 to 1.6.4
pgsql.status Unsigned 8-bit integer Status 1.0.0 to 1.6.4
pgsql.tag String Tag 1.0.0 to 1.6.4
pgsql.text String Text 1.0.0 to 1.6.4
pgsql.type String Type 1.0.0 to 1.6.4
pgsql.val.data Byte array Data 1.0.0 to 1.6.4
pgsql.val.length Signed 32-bit integer Column length 1.0.0 to 1.6.4
pgsql.where String Context 1.0.0 to 1.6.4

http://www.wireshark.org/docs/dfref/p/pgsql.html

This is for example one very innocent looking capture result of executing psql internal command \l (list databases):




Extended descriptions:


+ Well, not everything regarding sniffing communications is bad, I guess you can troubleshoot a variety of problems with its help.

But I'm a lazy kind of guy in the sense that I'd rather capture automatically any communication related to PostgreSQL and have it saved in a txt file. How to accomplish such thing?

One option is using tshark:
http://blog.timstoop.nl/2008/11/03/sniffing-postgresql-queries-with-tsharkwireshark/


Conclusions:
+ Added another tool for troubleshooting postgresql issues.
+ Of course it must be documented somewhere in the web, but using this technique you get a glance at what psql internal commands do.
+ Only very lame postgresql administrators don't encrypt postgresql messages for sensitive communications.

No comments:

Post a Comment