Me: Jehan-Guillaume (ioguix) de Rorthais

Activities:

• phpPgAdmin dev & admin
• PgSQL extension to control pgPool: pgpool_adm
• full-time PostgreSQL lover at Dalibo

IRC: ioguix

Contact: [email protected]

“Extract and mess with PostgreSQL messages from network dump”

Initially based on PDML output from tshark (Wireshark project)

⇒ tshark + PgSQL == pgShark

Original goal:

• upgrade from PgSQL 8.1 ⇒ 8.4
• lotta bad implicit conversions all over the place
• devs need help to find those queries
• high loaded production server
  ⇒ can't afford logging all queries

Solution: extract SQL from network dump and test them.

New goals:

• generic PostgreSQL messages dissector
• mess with everything, not only queries
• having fun

Ancestor (early 2010):

• initially a PHP script parsing PDML output from tshark
• Pros: quick dirty PHP hack
• Cons:
  • very slow
  • hard to extend
  • doesn't cover the whole protocol
  • PHP

Current (early 2011):

• Perl + libpcap
• fast, complete, portable
• from your perl scripts:

  use pgShark::Core

my $shark = pgShark::Core->new({
  'procs' => {
    'Query' => \&Query,
    'Execute' => \&Execute,
  },
  'host' => $args{'host'}, 'port' => $args{'port'}
});
 
sub Query {
  my $pg_msg = shift;
  printf "QUERY query=%s\n", $pg_msg->{'query'};
}
 
sub Execute {
  my $pg_msg = shift;
  printf "EXECUTE name='%s', nb_rows=%d\n", 
    $pg_msg->{'name'}, $pg_msg->{'nb_rows'};
}

# message informations hash
  my $pg_msg = {
    'sess_hash' => $session_hash, # basically IP+src port
    'timestamp' => $timestamp, # timestamps of the message
    'type' => $type, # one-char type from pg proto
    'data' => $data, # the message data w/o type and length
    ## other fields specifics to each messages are added bellow
  };

pgShark::Core can bind a function to every available messages from the PostgreSQL protocol:

Authentication* BackendKeyData Bind BindComplete CancelRequest
Close CloseComplete CommandComplete CopyData CopyDone CopyFail
CopyInResponse CopyOutResponse DataRow Describe 
EmptyQueryResponse ErrorResponse Execute Flush NoData NoticeResponse
NotificationResponse ParameterDescription ParameterStatus Parse 
ParseComplete PasswordMessage PortalSuspended Query ReadyForQuery
RowDescription SSLAnswer+ SSLRequest+ StartupMessage Sync Terminate

Some alpha/beta code distributed with pgShark:

• Debug
• SQL / Normalize
• Fouine

• split the project:
  • pgShark::Core
  • tools using pgShark
• next tool base on pgShark: Replay?
• bugfixes