Frontend/Backend Protocol
Written by Phil Thompson (phil@river-bank.demon.co.uk).
Updates for protocol 2.0 by Tom Lane (tgl@sss.pgh.pa.us).
PostgreSQL uses a message-based protocol
for communication between frontends and backends. The protocol is
implemented over TCP/IP and also on Unix domain
sockets. PostgreSQL 6.3 introduced
version numbers into the protocol. This was done in such a way as
to still allow connections from earlier versions of frontends, but
this document does not cover the protocol used by those earlier
versions.
This document describes version 2.0 of the protocol, implemented in
PostgreSQL 6.4 and later.
Higher level features built on this protocol (for example, how
libpq passes certain environment
variables after the connection is established) are covered
elsewhere.
Overview
A frontend opens a connection to the server and sends a start-up
packet. This includes the names of the user and the database the
user wants to connect to. The server then uses this, and the
information in the pg_hba.conf file to
determine what further authentication information it requires the
frontend to send (if any) and responds to the frontend accordingly.
The frontend then sends any required authentication information.
Once the server validates this it responds to the frontend that it
is authenticated and sends a message indicating successful start-up
(normal case) or failure (for example, an invalid database name).
In order to serve multiple clients efficiently, the server would
normally create a new child process to handle each incoming
connection. However, this is not required. In the current
implementation, a new child process is created immediately after an
incoming connection is detected. In earlier versions of PostgreSQL
(7.1 and earlier), the child process was created after sending the
authentication confirmation message.
When the frontend wishes to disconnect it sends an appropriate packet and
closes the connection without waiting for a response for the backend.
Packets are sent as a data stream. The first byte determines what
should be expected in the rest of the packet. The exceptions are
packets sent as part of the startup and authentication exchange,
which comprise a packet length followed by the packet itself. The
difference is historical.
Protocol
This section describes the message flow. There are four different
types of flows depending on the state of the connection: start-up,
query, function call, and termination. There are also special
provisions for notification responses and command cancellation,
which can occur at any time after the start-up phase.
Start-up
Initially, the frontend sends a StartupPacket. The server uses
this info and the contents of the pg_hba.conf
file to determine what authentication method the frontend must
use. The server then responds with one of the following messages:
ErrorResponse
The server then immediately closes the connection.
AuthenticationOk
The authentication exchange is completed.
AuthenticationKerberosV4
The frontend must then take part in a Kerberos V4
authentication dialog (not described here, part of the
Kerberos specification) with the server. If this is
successful, the server responds with an AuthenticationOk,
otherwise it responds with an ErrorResponse.
AuthenticationKerberosV5
The frontend must then take part in a Kerberos V5
authentication dialog (not described here, part of the
Kerberos specification) with the server. If this is
successful, the server responds with an AuthenticationOk,
otherwise it responds with an ErrorResponse.
AuthenticationUnencryptedPassword
The frontend must then send an UnencryptedPasswordPacket. If
this is the correct password, the server responds with an
AuthenticationOk, otherwise it responds with an ErrorResponse.
AuthenticationEncryptedPassword
The frontend must then send an EncryptedPasswordPacket. If
this is the correct password, the server responds with an
AuthenticationOk, otherwise it responds with an ErrorResponse.
If the frontend does not support the authentication method
requested by the server, then it should immediately close the
connection.
After having received AuthenticationOk, the frontend should wait
for further messages from the server. The possible messages from
the backend in this phase are:
BackendKeyData
This message provides secret-key data that the frontend must
save if it wants to be able to issue cancel requests later.
The frontend should not respond to this message, but should
continue listening for a ReadyForQuery message.
ReadyForQuery
Start-up is completed. The frontend may now issue query or
function call messages.
ErrorResponse
Start-up failed. The connection is closed after sending this
message.
NoticeResponse
A warning message has been issued. The frontend should
display the message but continue listening for ReadyForQuery
or ErrorResponse.
The ReadyForQuery message is the same one that the backend will
issue after each query cycle. Depending on the coding needs of
the frontend, it is reasonable to consider ReadyForQuery as
starting a query cycle (and then BackendKeyData indicates
successful conclusion of the start-up phase), or to consider
ReadyForQuery as ending the start-up phase and each subsequent
query cycle.
Query
A Query cycle is initiated by the frontend sending a Query message
to the backend. The backend then sends one or more response
messages depending on the contents of the query command string,
and finally a ReadyForQuery response message. ReadyForQuery
informs the frontend that it may safely send a new query or
function call.
The possible response messages from the backend are:
CompletedResponse
An SQL command completed normally.
CopyInResponse
The backend is ready to copy data from the frontend to a
table. The frontend should then send a CopyDataRows message.
The backend will then respond with a CompletedResponse message
with a tag of COPY.
CopyOutResponse
The backend is ready to copy data from a table to the
frontend. It then sends a CopyDataRows message, and then a
CompletedResponse message with a tag of COPY.
CursorResponse
The query was either an INSERT,
UPDATE, DELETE,
FETCH, or a SELECT
command. If the transaction has been aborted then the backend
sends a CompletedResponse message with a tag of *ABORT
STATE*. Otherwise the following responses are sent.
For an INSERT command, the backend then
sends a CompletedResponse message with a tag of
INSERT oid
rows, where
rows is the number of rows
inserted, and oid is the object ID
of the inserted row if rows is 1,
otherwise oid is 0.
For a DELETE command, the backend then
sends a CompletedResponse message with a tag of DELETE
rows where
rows is the number of rows deleted.
For an UPDATE command, the backend then
sends a CompletedResponse message with a tag of UPDATE
rows where
rows is the number of rows affected
by the update.
For a FETCH or SELECT
command, the backend sends a RowDescription message. This is
then followed by an AsciiRow or BinaryRow message (depending
on whether a binary cursor was specified) for each row being
returned to the frontend. Finally, the backend sends a
CompletedResponse message with a tag of SELECT.
EmptyQueryResponse
An empty query string was recognized. (The need to specially
distinguish this case is historical.)
ErrorResponse
An error has occurred.
ReadyForQuery
Processing of the query string is complete. A separate
message is sent to indicate this because the query string may
contain multiple SQL commands. (CompletedResponse marks the
end of processing one SQL command, not the whole string.)
ReadyForQuery will always be sent, whether processing
terminates successfully or with an error.
NoticeResponse
A warning message has been issued in relation to the query.
Notices are in addition to other responses, i.e., the backend
will continue processing the command.
A frontend must be prepared to accept ErrorResponse and
NoticeResponse messages whenever it is expecting any other type of
message.
Actually, it is possible for NoticeResponse to arrive even when
the frontend is not expecting any kind of message, that is, the
backend is nominally idle. (In particular, the backend can be
commanded to terminate by its parent process. In that case it will
send a NoticeResponse before closing the connection.) It is
recommended that the frontend check for such asynchronous notices
just before issuing any new command.
Also, if the frontend issues any LISTEN
commands then it must be prepared to accept NotificationResponse
messages at any time; see below.
Function Call
A Function Call cycle is initiated by the frontend sending a
FunctionCall message to the backend. The backend then sends one
or more response messages depending on the results of the function
call, and finally a ReadyForQuery response message. ReadyForQuery
informs the frontend that it may safely send a new query or
function call.
The possible response messages from the backend are:
ErrorResponse
An error has occurred.
FunctionResultResponse
The function call was executed and returned a result.
FunctionVoidResponse
The function call was executed and returned no result.
ReadyForQuery
Processing of the function call is complete. ReadyForQuery
will always be sent, whether processing terminates
successfully or with an error.
NoticeResponse
A warning message has been issued in relation to the function
call. Notices are in addition to other responses, i.e., the
backend will continue processing the command.
A frontend must be prepared to accept ErrorResponse and
NoticeResponse messages whenever it is expecting any other type of
message. Also, if it issues any LISTEN
commands then it must be prepared to accept NotificationResponse
messages at any time; see below.
Notification Responses
If a frontend issues a LISTEN command, then the
backend will send a NotificationResponse message (not to be
confused with NoticeResponse!) whenever a
NOTIFY command is executed for the same
notification name.
Notification responses are permitted at any point in the protocol
(after start-up), except within another backend message. Thus,
the frontend must be prepared to recognize a NotificationResponse
message whenever it is expecting any message. Indeed, it should
be able to handle NotificationResponse messages even when it is
not engaged in a query.
NotificationResponse
A NOTIFY command has been executed for a
name for which a previous LISTEN command
was executed. Notifications may be sent at any time.
It may be worth pointing out that the names used in listen and
notify commands need not have anything to do with names of
relations (tables) in the SQL database. Notification names are
simply arbitrarily chosen condition names.
Cancelling Requests in Progress
During the processing of a query, the frontend may request
cancellation of the query. The cancel request is not sent
directly on the open connection to the backend for reasons of
implementation efficiency: we don't want to have the backend
constantly checking for new input from the frontend during query
processing. Cancel requests should be relatively infrequent, so
we make them slightly cumbersome in order to avoid a penalty in
the normal case.
To issue a cancel request, the frontend opens a new connection to
the server and sends a CancelRequest message, rather than the
StartupPacket message that would ordinarily be sent across a new
connection. The server will process this request and then close
the connection. For security reasons, no direct reply is made to
the cancel request message.
A CancelRequest message will be ignored unless it contains the
same key data (PID and secret key) passed to the frontend during
connection start-up. If the request matches the PID and secret
key for a currently executing backend, the processing of the
current query is aborted. (In the existing implemenation, this is
done by sending a special signal to the backend process that is
processing the query.)
The cancellation signal may or may not have any effect --- for
example, if it arrives after the backend has finished processing
the query, then it will have no effect. If the cancellation is
effective, it results in the current command being terminated
early with an error message.
The upshot of all this is that for reasons of both security and
efficiency, the frontend has no direct way to tell whether a
cancel request has succeeded. It must continue to wait for the
backend to respond to the query. Issuing a cancel simply improves
the odds that the current query will finish soon, and improves the
odds that it will fail with an error message instead of
succeeding.
Since the cancel request is sent across a new connection to the
server and not across the regular frontend/backend communication
link, it is possible for the cancel request to be issued by any
process, not just the frontend whose query is to be canceled.
This may have some benefits of flexibility in building
multiple-process applications. It also introduces a security
risk, in that unauthorized persons might try to cancel queries.
The security risk is addressed by requiring a dynamically
generated secret key to be supplied in cancel requests.
Termination
The normal, graceful termination procedure is that the frontend
sends a Terminate message and immediately closes the connection.
On receipt of the message, the backend immediately closes the
connection and terminates.
An ungraceful termination may occur due to software failure (i.e.,
core dump) at either end. If either frontend or backend sees an
unexpected closure of the connection, it should clean up and
terminate. The frontend has the option of launching a new backend
by recontacting the server if it doesn't want to terminate
itself.
Message Data Types
This section describes the base data types used in messages.
Intn(i)
An n bit integer in network byte order.
If i is specified it
is the literal value. Eg. Int16, Int32(42).
LimStringn(s)
A character array of exactly n bytes interpreted as a '\0'
terminated string. The '\0' is omitted if there is
insufficient room. If s is specified it is the literal value.
Eg. LimString32, LimString64("user").
String(s)
A conventional C '\0' terminated string with no length
limitation.
If s is specified it is the literal value.
Eg. String, String("user").
There is no predefined limit on the length of a string
that can be returned by the backend. Good coding strategy for a frontend
is to use an expandable buffer so that anything that fits in memory can be
accepted. If that's not feasible, read the full string and discard trailing
characters that don't fit into your fixed-size buffer.
Byten(c)
Exactly n bytes. If c is specified it is the literal
value. Eg. Byte, Byte1('\n').
Message Formats
This section describes the detailed format of each message. Each can be sent
by either a frontend (F), a backend (B), or both (F & B).
AsciiRow (B)
Byte1('D')
Identifies the message as an ASCII data row.
(A prior RowDescription message defines the number of
fields in the row and their data types.)
Byten
A bit map with one bit for each field in the row. The 1st
field corresponds to bit 7 (MSB) of the 1st byte, the 2nd
field corresponds to bit 6 of the 1st byte, the 8th field
corresponds to bit 0 (LSB) of the 1st byte, the 9th field
corresponds to bit 7 of the 2nd byte, and so on. Each bit
is set if the value of the corresponding field is not NULL.
If the number of fields is not a multiple of 8, the remainder
of the last byte in the bit map is wasted.
Then, for each field with a non-NULL value, there is the following:
Int32
Specifies the size of the value of the field, including
this size.
Byten
Specifies the value of the field itself in ASCII
characters. n is the above
size minus 4.
There is no trailing '\0' in the field data; the front
end must add one if it wants one.
AuthenticationOk (B)
Byte1('R')
Identifies the message as an authentication request.
Int32(0)
Specifies that the authentication was successful.
AuthenticationKerberosV4 (B)
Byte1('R')
Identifies the message as an authentication request.
Int32(1)
Specifies that Kerberos V4 authentication is required.
AuthenticationKerberosV5 (B)
Byte1('R')
Identifies the message as an authentication request.
Int32(2)
Specifies that Kerberos V5 authentication is required.
AuthenticationUnencryptedPassword (B)
Byte1('R')
Identifies the message as an authentication request.
Int32(3)
Specifies that an unencrypted password is required.
AuthenticationEncryptedPassword (B)
Byte1('R')
Identifies the message as an authentication request.
Int32(4)
Specifies that an encrypted password is required.
Byte2
The salt to use when encrypting the password.
BackendKeyData (B)
Byte1('K')
Identifies the message as cancellation key data.
The frontend must save these values if it wishes to be
able to issue CancelRequest messages later.
Int32
The process ID of this backend.
Int32
The secret key of this backend.
BinaryRow (B)
Byte1('B')
Identifies the message as a binary data row.
(A prior RowDescription message defines the number of
fields in the row and their data types.)
Byten
A bit map with one bit for each field in the row. The 1st
field corresponds to bit 7 (MSB) of the 1st byte, the 2nd
field corresponds to bit 6 of the 1st byte, the 8th field
corresponds to bit 0 (LSB) of the 1st byte, the 9th field
corresponds to bit 7 of the 2nd byte, and so on. Each bit
is set if the value of the corresponding field is not NULL.
If the number of fields is not a multiple of 8, the remainder
of the last byte in the bit map is wasted.
Then, for each field with a non-NULL value, there is the following:
Int32
Specifies the size of the value of the field, excluding
this size.
Byten
Specifies the value of the field itself in binary
format. n is the above size.
CancelRequest (F)
Int32(16)
The size of the packet in bytes.
Int32(80877102)
The cancel request code. The value is chosen to contain
"1234" in the most significant 16 bits, and "5678" in the
least 16 significant bits. (To avoid confusion, this code
must not be the same as any protocol version number.)
Int32
The process ID of the target backend.
Int32
The secret key for the target backend.
CompletedResponse (B)
Byte1('C')
Identifies the message as a completed response.
String
The command tag. This is usually (but not always) a single
word that identifies which SQL command was completed.
CopyDataRows (B & F)
This is a stream of rows where each row is terminated by a Byte1('\n').
This is then followed by the sequence Byte1('\\'), Byte1('.'),
Byte1('\n').
CopyInResponse (B)
Byte1('G')
Identifies the message as a Start Copy In response.
The frontend must now send a CopyDataRows message.
CopyOutResponse (B)
Byte1('H')
Identifies the message as a Start Copy Out response.
This message will be followed by a CopyDataRows message.
CursorResponse (B)
Byte1('P')
Identifies the message as a cursor response.
String
The name of the cursor. This will be "blank" if the cursor is
implicit.
EmptyQueryResponse (B)
Byte1('I')
Identifies the message as a response to an empty query string.
String("")
Unused.
EncryptedPasswordPacket (F)
Int32
The size of the packet in bytes.
String
The encrypted (using MD5 or crypt()) password.
ErrorResponse (B)
Byte1('E')
Identifies the message as an error.
String
The error message itself.
FunctionCall (F)
Byte1('F')
Identifies the message as a function call.
String("")
Unused.
Int32
Specifies the object ID of the function to call.
Int32
Specifies the number of arguments being supplied to the
function.
Then, for each argument, there is the following:
Int32
Specifies the size of the value of the argument,
excluding this size.
Byten
Specifies the value of the field itself in binary
format. n is the above size.
FunctionResultResponse (B)
Byte1('V')
Identifies the message as a function call result.
Byte1('G')
Specifies that a nonempty result was returned.
Int32
Specifies the size of the value of the result, excluding this
size.
Byten
Specifies the value of the result itself in binary format.
n is the above size.
Byte1('0')
Unused. (Strictly speaking, FunctionResultResponse and
FunctionVoidResponse are the same thing but with some optional
parts to the message.)
FunctionVoidResponse (B)
Byte1('V')
Identifies the message as a function call result.
Byte1('0')
Specifies that an empty result was returned.
NoticeResponse (B)
Byte1('N')
Identifies the message as a notice.
String
The notice message itself.
NotificationResponse (B)
Byte1('A')
Identifies the message as a notification response.
Int32
The process ID of the notifying backend process.
String
The name of the condition that the notify has been raised on.
Query (F)
Byte1('Q')
Identifies the message as a query.
String
The query string itself.
ReadyForQuery (B)
Byte1('Z')
Identifies the message type. ReadyForQuery is sent
whenever the backend is ready for a new query cycle.
RowDescription (B)
Byte1('T')
Identifies the message as a row description.
Int16
Specifies the number of fields in a row (may be zero).
Then, for each field, there is the following:
String
Specifies the field name.
Int32
Specifies the object ID of the field type.
Int16
Specifies the type size.
Int32
Specifies the type modifier.
StartupPacket (F)
Int32(296)
The size of the packet in bytes.
Int32
The protocol version number. The most significant 16 bits are
the major version number. The least 16 significant bits are
the minor version number.
LimString64
The database name, defaults to the user name if empty.
LimString32
The user name.
LimString64
Any additional command line arguments to be passed to the
backend child process by the server.
LimString64
Unused.
LimString64
The optional tty the backend should use for debugging messages.
Terminate (F)
Byte1('X')
Identifies the message as a termination.
UnencryptedPasswordPacket (F)
Int32
The size of the packet in bytes.
String
The unencrypted password.