The PostgreSQL Protocol

There are three versions of the PostgreSQL protocol. PostgreSQL 6.3 introduced protocol version numbers starting from 1.0. PostgreSQL 6.4 introduced protocol version 2.0 and PostgreSQL 7.4 introduced protocol version 3.0. The most recent version of the database, 8.0, uses protocol version 3.0.

Unlike the protocols that other DBMS use, the PostgreSQL protocol does not willingly surrender the database version number. During the connection handshake, the protocol version is the only information that is required to determine client-server compatibility. Once the handshake is complete, the client application may query the server version to determine whether specific SQL features are supported. This means that in order to fingerprint the server remotely and anonymously, the attacker must make inferences based on observing both the message flow and content:

• The server version can be inferred from the highest version number of the protocol that the postmaster supports.

• The server version can be inferred from error messages returned by sending malformed responses to certain messages.

• The server version can be inferred by studying responses to requests found in later versions of the protocol (SSL support was only introduced in version 7.1).

• The server version can be inferred by the presence of certain authentication types (md5 was only introduced in version 7.2).

The startup packet sent from the client typically contains a username, database name , and protocol version. The postmaster uses this information to examine the pg_hba.conf file for a match. If no match is found, an ErrorResponse is sent back to the client. If a partial match is found, depending on the user authenticating correctly, an authentication exchange begins. The postmaster sends back a message containing the type of authentication required. The client must then send credentials. If they are incorrect, an ErrorResponse is sent from the postmaster to the client. If a complete match is found, that is, no authentication is required or the user has correctly authenticated, an AuthenticationOK message is sent to the client.

This process is slightly different if the client wishes to communicate with the postmaster over SSL. Instead of sending a startup packet, the client will send an SSLRequest causing the postmaster to respond with either a single-byte packet containing a "Y" or "N", or an ErrorResponse if the postmaster version predates SSL support.

After receiving an AuthenticationOK message, the frontend must wait for a ReadyForQuery message from the backend before Query messages can be dispatched. Query messages cause the backend to respond with RowDescription messages (indicating that rows are about to be returned in response to a SELECT, FETCH, or other query) followed by DataRow messages (containing one of the set of rows returned by a SELECT, FETCH, or other query). If an error occurs, an ErrorResponse message is sent to the frontend. Finally, a CommandComplete message is dispatched to inform the client that the query succeeded.

You can find further information on the PostgreSQL protocol at:

Version 3.0: http://www.postgresql.org/docs/7.4/interactive/protocol.html

Version 2.0: http://www.postgresql.org/docs/7.3/interactive/protocol.html

Version 1.0: http://www.postgresql.org/docs/6.3/interactive/c50.html