jeroenrinzema/psql-wire

PostgreSQL server wire protocol. Build your own server and start serving connections.

PSQL wire protocol 🔌

A pure Go PostgreSQL server wire protocol implementation. Build your own PostgreSQL server within a few lines of code. This project attempts to make it as straight forward as possible to set-up and configure your own PSQL server. Feel free to check out the examples directory for various ways on how to configure/set-up your own server.

🚧 This project does not include a PSQL parser. Please check out other projects such as auxten/postgresql-parser to parse PSQL SQL queries.

package main

import (
	"context"
	"fmt"

	wire "github.com/jeroenrinzema/psql-wire"
)

func main() {
	wire.ListenAndServe("127.0.0.1:5432", handler)
}

func handler(ctx context.Context, query string) (wire.PreparedStatements, error) {
	return wire.Prepared(wire.NewStatement(func(ctx context.Context, writer wire.DataWriter, parameters []wire.Parameter) error {
		fmt.Println(query)
		return writer.Complete("OK")
	})), nil
}

---

🚧 When wanting to debug issues and or inspect the PostgreSQL wire protocol please check out the psql-proxy cli

Sponsor

Sponsored by Cloud Proud. A single access point for all your data. Query all your data sources as one large virtual database using the PostgreSQL protocol and SQL.

$ docker run -p 5432:5432 registry.cloudproud.nl/lab/kit
$ # The web interface is up and running at: http://localhost:5432
$ # You could login using the default username and password kit:kitpw

Support

Feel free to join the Cloud Proud Slack workspace to discuss feature requests or issues.

Contributing

Thank you for your interest in contributing to psql-wire! Check out the open projects and/or issues and feel free to join any ongoing discussion. Feel free to checkout the open TODO's within the project.

Everyone is welcome to contribute, whether it's in the form of code, documentation, bug reports, feature requests, or anything else. We encourage you to experiment with the project and make contributions to help evolve it to meet your needs!

See the contributing guide for more details.

psql-wire's Issues

support GSS encryption

psql-wire currently does not support GSS encrypted connections. The GSS
authentication API is supported inside the PostgreSQL wire protocol and
API's should be made available to support these type of connections.
https://www.postgresql.org/docs/current/gssapi-auth.html
https://www.postgresql.org/docs/current/protocol-flow.html#id-1.10.6.7.13

Line: 27

psql-wire//handshake.go

Lines 24 to 34 in 2ae2aca

	return conn, version, reader, nil
	}
	// TODO: support GSS encryption
	//
	// `psql-wire` currently does not support GSS encrypted connections. The GSS
	// authentication API is supported inside the PostgreSQL wire protocol and
	// API's should be made available to support these type of connections.
	// https://www.postgresql.org/docs/current/gssapi-auth.html
	// https://www.postgresql.org/docs/current/protocol-flow.html#id-1.10.6.7.13

Replace with appropriate checks (e.g. spell checking).

Line: 27
https://github.com/jeroenrinzema/psql-wire/blob/0a717252f0facb3327f43ac44d52785663e4d20d//.git/hooks/sendemail-validate.sample#L24-L34

EOF while connecting to the server

I received the following error in the error log emitted by this library while connecting with a server instance created using this

Error

{"level":"error","timestamp":"2022-10-10T16:47:51.717162+05:30","caller":"[email protected]/wire.go:105","msg":"an unexpected error got returned while serving a client connection","error":"EOF","stacktrace":"github.com/jeroenrinzema/psql-wire.(*Server).Serve.func2\n\t/Users/kishanb/go/pkg/mod/github.com/jeroenrinzema/[email protected]/wire.go:105"}

Simulation steps

• Main program

import (
	"context"
	"crypto/tls"
	"fmt"
	wire "github.com/jeroenrinzema/psql-wire"
	"go.uber.org/zap"
)

func main(){
   RunPostgresRedshiftProxy()
}

func RunPostgresRedshiftProxy() error {
	server, err := wire.NewServer(wire.Logger(constructLogger()), wire.SimpleQuery(queryHandler), wire.ClientAuth(tls.NoClientCert))
	if err != nil {
		return fmt.Errorf("error while instantiating server: %w", err)
	}
	err = server.ListenAndServe("127.0.0.1:25432")
	if err != nil {
		return fmt.Errorf("error while listening: %w", err)
	}
	return nil
}

func queryHandler(ctx context.Context, query string, writer wire.DataWriter) error {
	fmt.Println(query)
	return writer.Complete("OK")
}

func constructLogger() *zap.Logger {
	productionConfig := zap.NewProductionConfig()
	productionConfig.EncoderConfig.TimeKey = "timestamp"
	productionConfig.EncoderConfig.EncodeTime = zapcore.RFC3339NanoTimeEncoder
	logger, _ := productionConfig.Build()
	return logger
}

• Test program

import (
	"context"
	"fmt"
	"github.com/jackc/pgx/v4"
	"github.com/stretchr/testify/require"
	"go.uber.org/zap"
	"go.uber.org/zap/zapcore"
	"testing"
)

func Test1(t *testing.T) {
	databaseUrl := "postgres://postgres:mypassword@localhost:25432/postgres"
	conn, err := pgx.Connect(context.Background(), databaseUrl)
	require.NoError(t, err)

	t.Run("test query execution", func(t *testing.T) {
		var name string
		var weight int64
		err = conn.QueryRow(context.Background(), "select name, weight from widgets limit 1").Scan(&name, &weight)
		require.NoError(t, err)
		fmt.Println(name, weight)
	})
}

• Start the server using go run
• Run the above test using go test and observe the logs from the server

Server should return the column types that will be

returned for the given portal or statement.

The Describe message (portal variant) specifies the name of an
existing portal (or an empty string for the unnamed portal). The
response is a RowDescription message describing the rows that will be
returned by executing the portal; or a NoData message if the portal
does not contain a query that will return rows; or ErrorResponse if
there is no such portal.

The Describe message (statement variant) specifies the name of an
existing prepared statement (or an empty string for the unnamed
prepared statement). The response is a ParameterDescription message
describing the parameters needed by the statement, followed by a
RowDescription message describing the rows that will be returned when
the statement is eventually executed (or a NoData message if the
statement will not return rows). ErrorResponse is issued if there is
no such prepared statement. Note that since Bind has not yet been
issued, the formats to be used for returned columns are not yet known
to the backend; the format code fields in the RowDescription message
will be zeroes in this case.
https://www.postgresql.org/docs/current/protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY

Line: 141

psql-wire//command.go

Lines 138 to 148 in 3dbc832

	case types.ClientParse:
	return srv.handleParse(ctx, reader, writer)
	case types.ClientDescribe:
	// TODO: Server should return the column types that will be
	// returned for the given portal or statement.
	//
	// The Describe message (portal variant) specifies the name of an
	// existing portal (or an empty string for the unnamed portal). The
	// response is a RowDescription message describing the rows that will be
	// returned by executing the portal; or a NoData message if the portal
	// does not contain a query that will return rows; or ErrorResponse if

Receiving error "got no result from the query"

Coping the content from #40 (comment) here for better visibility

I am getting the error got no result from the query even though the query actually wrote a result set (screenshot below)

Should i raise a separate issue or should this be reopened?

Screenshot 1

One interesting thing here is that if i fire the same query very fast i actually get the result "at times" (Screenshot below)

Screenshot 2

Something wierd is going on.

Logs when the error happens

{"level":"debug","timestamp":"2022-11-19T17:25:48.402488+05:30","caller":"[email protected]/command.go:67","msg":"incoming command","length":944,"type":"P"}
{"level":"debug","timestamp":"2022-11-19T17:25:48.402723+05:30","caller":"[email protected]/command.go:276","msg":"incoming extended query","query":"SELECT n.nspname as \"Schema\",\n                    c.relname as \"Name\",\n                    CASE c.relkind\n                      WHEN 'r' THEN 'table' WHEN 'v' THEN 'view'\n                      WHEN 'p' THEN 'partitioned table'\n                      WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index'\n                      WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special'\n                      WHEN 'f' THEN 'foreign table' END\n                    as \"Type\",\n                    pg_catalog.pg_get_userbyid(c.relowner) as \"Owner\"\n          \n            FROM    pg_catalog.pg_class c\n                    LEFT JOIN pg_catalog.pg_namespace n\n                      ON n.oid = c.relnamespace\n            WHERE   c.relkind = ANY($1) \n            AND n.nspname <> 'pg_catalog'\n            AND n.nspname <> 'information_schema'\n            AND n.nspname !~ '^pg_toast'\n            AND pg_catalog.pg_table_is_visible(c.oid)  ORDER BY 1, 2","name":"","parameters":1}
{"level":"debug","timestamp":"2022-11-19T17:25:48.402844+05:30","caller":"[email protected]/command.go:67","msg":"incoming command","length":36,"type":"B"}
{"level":"debug","timestamp":"2022-11-19T17:25:48.40287+05:30","caller":"[email protected]/command.go:344","msg":"reading parameters format codes","length":1}
{"level":"debug","timestamp":"2022-11-19T17:25:48.402902+05:30","caller":"[email protected]/command.go:372","msg":"reading parameters values","length":1}
{"level":"debug","timestamp":"2022-11-19T17:25:48.402913+05:30","caller":"[email protected]/command.go:386","msg":"incoming parameter","value":"{r,p,v,m,S,f,\"\"}"}
{"level":"debug","timestamp":"2022-11-19T17:25:48.402923+05:30","caller":"[email protected]/command.go:397","msg":"reading result-column format codes","length":1}
{"level":"debug","timestamp":"2022-11-19T17:25:48.402958+05:30","caller":"[email protected]/command.go:67","msg":"incoming command","length":6,"type":"D"}
{"level":"debug","timestamp":"2022-11-19T17:25:48.402977+05:30","caller":"[email protected]/command.go:67","msg":"incoming command","length":9,"type":"E"}
{"level":"debug","timestamp":"2022-11-19T17:25:48.402986+05:30","caller":"[email protected]/command.go:435","msg":"executing","name":"","limit":0}
{"level":"debug","timestamp":"2022-11-19T17:25:49.791743+05:30","caller":"[email protected]/command.go:67","msg":"incoming command","length":4,"type":"S"}

Originally posted by @kishaningithub in #40 (comment)

deallocate statement is currentlly being sent to the simple query handler

As of now when i run the following query using prepared statment of database/sql using pgx library the first request coming into the handler contains the query then the subsequent one contains [email protected]/command.go:216 incoming simple query {"query": "deallocate \"pgx_0\""}

During prepared statment calls can just the sql alone be sent?

      select * 
      from (
		  select 1 id
		  union all
		  select 2 id
		  union all
		  select 3 id
      )
      where id > $1

Support Extended Query

Discussed in #13

^{Originally posted by trietphm July 28, 2022}
Do you have any plan to support the Extended Query?
https://www.postgresql.org/docs/current/protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY

I can try implementing it but I don't know the detailed specs to start with.

Query not received when querying via pgx

I am using the latest version v0.3.0 of this, when i try to connect to a proxy with this test case the query never gets received

Test code using pgx connection

t.Run("test query execution", func(t *testing.T) {
	var name string
	var weight int64
	err = conn.QueryRow(context.Background(), "select name, weight from widgets limit 1").Scan(&name, &weight)
	require.NoError(t, err)
	fmt.Println(name, weight)
})

Debug logs

{"level":"debug","timestamp":"2022-10-19T18:42:12.915693+05:30","caller":"[email protected]/command.go:61","msg":"incoming command","length":58,"type":"P"}
{"level":"debug","timestamp":"2022-10-19T18:42:12.915705+05:30","caller":"[email protected]/command.go:61","msg":"incoming command","length":16,"type":"D"}
{"level":"debug","timestamp":"2022-10-19T18:42:12.915711+05:30","caller":"[email protected]/command.go:61","msg":"incoming command","length":4,"type":"S"}
{"level":"debug","timestamp":"2022-10-19T18:42:12.915755+05:30","caller":"[email protected]/command.go:61","msg":"incoming command","length":22,"type":"B"}
{"level":"debug","timestamp":"2022-10-19T18:42:12.915763+05:30","caller":"[email protected]/command.go:61","msg":"incoming command","length":6,"type":"D"}
{"level":"debug","timestamp":"2022-10-19T18:42:12.915768+05:30","caller":"[email protected]/command.go:61","msg":"incoming command","length":9,"type":"E"}
{"level":"debug","timestamp":"2022-10-19T18:42:12.915775+05:30","caller":"[email protected]/command.go:61","msg":"incoming command","length":4,"type":"S"}

The full test code can be found here in my rdapp repo where i am trying to use this in order to create a postgres proxy for AWS redshift data API

Numeric types are not supported properly

If a column is defined with type oid.T_numeric (1700) the result is not properly converted:

• SELECT 0.99::numeric' is returned as 99e-2

To add the proper support the type need to be registered manually:

connInfo.RegisterDataType(pgtype.DataType{
			Value: &shopspring.Numeric{},
			Name:  "numeric",
			OID:   pgtype.NumericOID,
		})

Working Example

package main

import (
	"context"
	"log"

	"github.com/jackc/pgtype"
	shopspring "github.com/jackc/pgtype/ext/shopspring-numeric"
	wire "github.com/jeroenrinzema/psql-wire"
	"github.com/lib/pq/oid"
)

func main() {
	log.Println("PostgreSQL server is up and running at [127.0.0.1:5432]")
	wire.ListenAndServe("127.0.0.1:5432", handle)
	select {}
}

var table = wire.Columns{
	{
		Table:  0,
		Name:   "name",
		Oid:    oid.T_text,
		Width:  256,
		Format: wire.TextFormat,
	},
	{
		Table:  0,
		Name:   "member",
		Oid:    oid.T_bool,
		Width:  1,
		Format: wire.TextFormat,
	},
	{
		Table:  0,
		Name:   "age",
		Oid:    oid.T_int4,
		Width:  1,
		Format: wire.TextFormat,
	},
	{
		Table:  0,
		Name:   "amount",
		Oid:    oid.T_numeric,
		Width:  1,
		Format: wire.TextFormat,
	},
}

func handle(ctx context.Context, query string, writer wire.DataWriter) error {
	log.Println("incoming SQL query:", query)
	connInfo := wire.TypeInfo(ctx)
	if connInfo != nil {
		connInfo.RegisterDataType(pgtype.DataType{
			Value: &shopspring.Numeric{},
			Name:  "numeric",
			OID:   pgtype.NumericOID,
		})
	}

	writer.Define(table)
	writer.Row([]any{"John", true, 29, 0.99})
	writer.Row([]any{"Marry", false, 21, 0.99})
	return writer.Complete("OK")
}

Reference: https://github.com/jackc/pgx/wiki/Numeric-and-decimal-support

Replace with appropriate checks for this patch

(e.g. checkpatch.pl).

Line: 35
https://github.com/jeroenrinzema/psql-wire/blob/0a717252f0facb3327f43ac44d52785663e4d20d//.git/hooks/sendemail-validate.sample#L32-L42

Flush all remaining rows inside connection buffer if

any are remaining.

The Flush message does not cause any specific
output to be generated, but forces the backend to deliver any data
pending in its output buffers. A Flush must be sent after any
extended-query command except Sync, if the frontend wishes to examine
the results of that command before issuing more commands. Without
Flush, messages returned by the backend will be combined into the
minimum possible number of packets to minimize network overhead.
https://www.postgresql.org/docs/current/protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY

Line: 166

psql-wire//command.go

Lines 163 to 173 in 3dbc832

	case types.ClientBind:
	return srv.handleBind(ctx, reader, writer)
	case types.ClientFlush:
	// TODO: Flush all remaining rows inside connection buffer if
	// any are remaining.
	//
	// The Flush message does not cause any specific
	// output to be generated, but forces the backend to deliver any data
	// pending in its output buffers. A Flush must be sent after any
	// extended-query command except Sync, if the frontend wishes to examine
	// the results of that command before issuing more commands. Without

Include a value to identify unique connections

include a identification value inside the context that
could be used to identify connections at a later stage.

Line: 41

psql-wire//command.go

Lines 38 to 48 in 3dbc832

	func (srv *Server) consumeCommands(ctx context.Context, conn net.Conn, reader *buffer.Reader, writer *buffer.Writer) (err error) {
	srv.logger.Debug("ready for query... starting to consume commands")
	// TODO: Include a value to identify unique connections
	//
	// include a identification value inside the context that
	// could be used to identify connections at a later stage.
	for {
	err = readyForQuery(writer, types.ServerIdle)
	if err != nil {

Remote Address

Is there a way to get the remote connecting address? It doesn't look like the net.Conn is exposed anywhere one of the Option functions could access it

Include the ability to catch sync messages in order to

close the current transaction.

At completion of each series of extended-query messages, the frontend
should issue a Sync message. This parameterless message causes the
backend to close the current transaction if it's not inside a
BEGIN/COMMIT transaction block (“close” meaning to commit if no
error, or roll back if error). Then a ReadyForQuery response is
issued. The purpose of Sync is to provide a resynchronization point
for error recovery. When an error is detected while processing any
extended-query message, the backend issues ErrorResponse, then reads
and discards messages until a Sync is reached, then issues
ReadyForQuery and returns to normal message processing. (But note
that no skipping occurs if an error is detected while processing Sync
— this ensures that there is one and only one ReadyForQuery sent for
each Sync.)
https://www.postgresql.org/docs/current/protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY

Line: 117

psql-wire//command.go

Lines 114 to 124 in 3dbc832

	switch t {
	case types.ClientSync:
	// TODO: Include the ability to catch sync messages in order to
	// close the current transaction.
	//
	// At completion of each series of extended-query messages, the frontend
	// should issue a Sync message. This parameterless message causes the
	// backend to close the current transaction if it's not inside a
	// BEGIN/COMMIT transaction block (“close” meaning to commit if no
	// error, or roll back if error). Then a ReadyForQuery response is

Handle incoming result-column format codes

Incoming format codes are currently ignored and should be handled in
the future. The result-column format codes. Each must presently be
zero (text) or one (binary). These format codes should be returned
and handled by the parent function to return the proper column formats.
https://www.postgresql.org/docs/current/protocol-message-formats.html

Line: 400

psql-wire//command.go

Lines 397 to 407 in 3dbc832

	srv.logger.Debug("reading result-column format codes", zap.Uint16("length", length))
	for i := uint16(0); i < length; i++ {
	// TODO: Handle incoming result-column format codes
	//
	// Incoming format codes are currently ignored and should be handled in
	// the future. The result-column format codes. Each must presently be
	// zero (text) or one (binary). These format codes should be returned
	// and handled by the parent function to return the proper column formats.
	// https://www.postgresql.org/docs/current/protocol-message-formats.html
	_, err := reader.GetUint16()

Enable the jackc/pgx error code tests

We have disabled the pgx tests for now until jackc/pgx#1466 has been resolved.

Line: 41

psql-wire//error_test.go

Lines 38 to 48 in 1676ea5

	})
	t.Run("jackc/pgx", func(t *testing.T) {
	// TODO: Enable the jackc/pgx error code tests
	// We have disabled the pgx tests for now until https://github.com/jackc/pgx/discussions/1466 has been resolved.
	t.Skip()
	ctx := context.Background()
	connstr := fmt.Sprintf("postgres://%s:%d", address.IP, address.Port)
	conn, err := pgx.Connect(ctx, connstr)
	assert.NoError(t, err)

Limit the maximum number of records to be returned.

Maximum number of limit to return, if portal contains a
query that returns limit (ignored otherwise). Zero denotes “no limit”.

Line: 426

psql-wire//command.go

Lines 423 to 433 in 3dbc832

	return err
	}
	// TODO: Limit the maximum number of records to be returned.
	//
	// Maximum number of limit to return, if portal contains a
	// query that returns limit (ignored otherwise). Zero denotes “no limit”.
	limit, err := reader.GetUint32()
	if err != nil {
	return err
	}

Handling of "prepared statement already exists" error

At times the client of the psql server gets the error "prepared statement already exists". I dont have a exact simulation steps as such because the issue is intermittent.

During the times this happens, i see the server returns the above error first and then the SimpleQueryHandler gets called.

Screenshot from pgcli client

Support type for type modifiers

Some types could be overridden using the type modifier field within a RowDescription.
Type modifier (see pg_attribute.atttypmod). The meaning of the
modifier is type-specific.
Atttypmod records type-specific data supplied at table creation time (for
example, the maximum length of a varchar column). It is passed to
type-specific input functions and length coercion functions. The value
will generally be -1 for types that do not need atttypmod.

https://www.postgresql.org/docs/current/protocol-message-formats.html
https://www.postgresql.org/docs/current/catalog-pg-attribute.html

Line: 73

psql-wire//row.go

Lines 70 to 80 in 3dbc832

	writer.AddInt16(column.AttrNo)
	writer.AddInt32(int32(column.Oid))
	writer.AddInt16(column.Width)
	// TODO: Support type for type modifiers
	//
	// Some types could be overridden using the type modifier field within a RowDescription.
	// Type modifier (see pg_attribute.atttypmod). The meaning of the
	// modifier is type-specific.
	// Atttypmod records type-specific data supplied at table creation time (for
	// example, the maximum length of a varchar column). It is passed to
	// type-specific input functions and length coercion functions. The value

Replace with appropriate checks for the whole series

(e.g. quick build, coding style checks, etc.).

Line: 41
https://github.com/jeroenrinzema/psql-wire/blob/0a717252f0facb3327f43ac44d52785663e4d20d//.git/hooks/sendemail-validate.sample#L38-L48

Avoiding the dependency on libpq

As you might already know lib/pq is now in maintenance mode and psql-wire has a direct dependency on it.

From what is see the library is mainly being used for its Oid type can this be extracted out by someway?

Handle multiple parameter format codes.

We are currently only supporting string parameters. We have to
include support for binary parameters in the future.
https://www.postgresql.org/docs/14/protocol-message-formats.html

Line: 358

psql-wire//command.go

Lines 355 to 365 in 3dbc832

	return nil, errors.New("unsupported binary parameter format, only text formatted parameter types are currently supported")
	}
	// TODO: Handle multiple parameter format codes.
	//
	// We are currently only supporting string parameters. We have to
	// include support for binary parameters in the future.
	// https://www.postgresql.org/docs/14/protocol-message-formats.html
	}
	// NOTE: read the total amount of parameter values that will be send

Running sql scripts from graphical clients sends all sql statements as a single query string

Context

When i run sql scripts through vscode sql tools. The received query string in SimpleQueryFn is of the form

select * from employee limit 10;\nselect count(*) from student;

What i tried

In the SimpleQueryFn implementation that i wrote, i tried splitting the sql with ; character and running it separately and this results in the error columns have already been defined

What i expect

I expect psql wire to do the split and send queries one by one inside the SimpleQueryFn

What are your thoughts?

handle `copy from stdin` query

Hi, and first of all: thank you for your work! 🙂

I am trying to use pgx's CopyFrom method on a server created with psql-wire.

Reading the spec here https://www.postgresql.org/docs/15/protocol-flow.html#PROTOCOL-COPY, the server is supposed to send a CopyInResponse message before receiving the data via multiple CopyData messages, ending with a CopyDone message.

I don't see a way to handle that currently inside a ParseFn, but maybe I missed something?

The query reaches my parser, and the wire.PreparedStatementFn is called just after, but then we can't properly process the whole operation.

Calling writer.Complete("CopyInResponse") makes the pgx client return without error, but with 0 rows inserted (and we never see the data to be copied server-side).

I'm not sure how this should be handled.

Maybe a different handler? That would make psql-wire responsible for parsing and recognizing a copy from query, I'm not sure that's great.

Or change the wire.DataWriter to be able to send the CopyInResponse and read the next messages? That may be better.

This may be a "won't fix", but I wanted to at least have a discussion about it.

Even if this hurdle falls, I know pgx forces the binary format on copy from so I won't be out of the woods yet 😄

Support Extended Query

Do you have any plan to support the Extended Query?
https://www.postgresql.org/docs/current/protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY

I can try implementing it but I don't know the detailed specs to start with.

Specifies the object ID of the parameter data type

Specifies the object ID of the parameter data type. Placing a zero here
is equivalent to leaving the type unspecified.
reader.GetUint32()

Line: 264

psql-wire//command.go

Lines 261 to 271 in 3dbc832

	}
	for i := uint16(0); i < parameters; i++ {
	// TODO: Specifies the object ID of the parameter data type
	//
	// Specifies the object ID of the parameter data type. Placing a zero here
	// is equivalent to leaving the type unspecified.
	// `reader.GetUint32()`
	}
	statement, descriptions, err := srv.Parse(ctx, query)

Return a negative parameter description if no parameters have been described while parsing

After debugging the issue (#49) reported by @kishaningithub. I noticed that it is possible to not define the parameters by simply sending a out of bound/negative integer when describing a prepared message. This could probably be embedded by updating the writeParameterDescriptions to return a negative integer when no parameters are defined and to update the simple query option to simply not return any parameters.

psql-wire/command.go

Lines 294 to 303 in 8f291e8

	func (srv *Server) writeParameterDescriptions(writer *buffer.Writer, parameters []oid.Oid) error {
	writer.Start(types.ServerParameterDescription)
	writer.AddInt16(int16(len(parameters)))
	for _, parameter := range parameters {
	writer.AddInt32(int32(parameter))
	}
	return writer.End()
	}

psql-wire/options.go

Lines 71 to 92 in 8f291e8

	// NOTE: we have to lookup all parameters within the given query.
	// Parameters could represent positional parameters or anonymous
	// parameters. We return a zero parameter oid for each parameter
	// indicating that the given parameters could contain any type. We
	// could safely ignore the err check while converting given
	// parameters since ony matches are returned by the positional
	// parameter regex.
	matches := QueryParameters.FindAllStringSubmatch(query, -1)
	parameters := make([]oid.Oid, 0, len(matches))
	for _, match := range matches {
	// NOTE: we have to check whether the returned match is a
	// positional parameter or an un-positional parameter.
	// SELECT * FROM users WHERE id = ?
	if match[1] == "" {
	parameters = append(parameters, 0)
	}
	position, _ := strconv.Atoi(match[1]) //nolint:errcheck
	if position > len(parameters) {
	parameters = parameters[:position]
	}
	}

Supporting different forms of prepared statement query params

As of now if i give a prepared statement of the below form and i pass in the $1 argument, it works as expected

    select * 
    from (
		select 1 id
		union all
		select 2 id
		union all
		select 3 id
    )
    where id > $1

but if i send the below query I get the error sql: expected 0 arguments, got 1 from the client side (notice i am using ? for params)

    select * 
    from (
		select 1 id
		union all
		select 2 id
		union all
		select 3 id
    )
    where id > ?

Logs from psql-wire

2022-12-12T11:16:45.662+0530	INFO	[email protected]/wire.go:88	serving incoming connections	{"addr": "[::]:35432"}
2022-12-12T11:16:45.665+0530	DEBUG	[email protected]/wire.go:126	serving a new client connection
2022-12-12T11:16:45.666+0530	DEBUG	[email protected]/handshake.go:137	attempting to upgrade the client to a TLS connection
2022-12-12T11:16:45.666+0530	DEBUG	[email protected]/handshake.go:140	no TLS certificates available continuing with a insecure connection
2022-12-12T11:16:45.666+0530	ERROR	[email protected]/wire.go:116	an unexpected error got returned while serving a client connection	{"error": "EOF"}
github.com/jeroenrinzema/psql-wire.(*Server).Serve.func2
	/Users/kishanb/go/pkg/mod/github.com/jeroenrinzema/[email protected]/wire.go:116
2022-12-12T11:16:45.666+0530	DEBUG	[email protected]/wire.go:126	serving a new client connection
2022-12-12T11:16:45.666+0530	DEBUG	[email protected]/handshake.go:137	attempting to upgrade the client to a TLS connection
2022-12-12T11:16:45.667+0530	DEBUG	[email protected]/handshake.go:140	no TLS certificates available continuing with a insecure connection
2022-12-12T11:16:45.667+0530	ERROR	[email protected]/wire.go:116	an unexpected error got returned while serving a client connection	{"error": "EOF"}
github.com/jeroenrinzema/psql-wire.(*Server).Serve.func2
	/Users/kishanb/go/pkg/mod/github.com/jeroenrinzema/[email protected]/wire.go:116
2022-12-12T11:16:45.667+0530	DEBUG	[email protected]/wire.go:126	serving a new client connection
2022-12-12T11:16:45.668+0530	DEBUG	[email protected]/wire.go:137	handshake successfull, validating authentication
2022-12-12T11:16:45.668+0530	DEBUG	[email protected]/handshake.go:68	reading client parameters
2022-12-12T11:16:45.668+0530	DEBUG	[email protected]/handshake.go:86	client parameter	{"key": "user", "value": "postgres"}
2022-12-12T11:16:45.668+0530	DEBUG	[email protected]/handshake.go:86	client parameter	{"key": "database", "value": "postgres"}
2022-12-12T11:16:45.668+0530	DEBUG	[email protected]/auth.go:33	authenticating client connection
2022-12-12T11:16:45.668+0530	DEBUG	[email protected]/wire.go:150	connection authenticated, writing server parameters
2022-12-12T11:16:45.668+0530	DEBUG	[email protected]/handshake.go:102	writing server parameters
2022-12-12T11:16:45.668+0530	DEBUG	[email protected]/handshake.go:113	server parameter	{"key": "is_superuser", "value": "off"}
2022-12-12T11:16:45.668+0530	DEBUG	[email protected]/handshake.go:113	server parameter	{"key": "session_authorization", "value": "postgres"}
2022-12-12T11:16:45.668+0530	DEBUG	[email protected]/handshake.go:113	server parameter	{"key": "server_encoding", "value": "UTF8"}
2022-12-12T11:16:45.668+0530	DEBUG	[email protected]/handshake.go:113	server parameter	{"key": "client_encoding", "value": "UTF8"}
2022-12-12T11:16:45.668+0530	DEBUG	[email protected]/command.go:39	ready for query... starting to consume commands
2022-12-12T11:16:45.668+0530	DEBUG	[email protected]/command.go:65	incoming command	{"length": 149, "type": "P"}
2022-12-12T11:16:45.668+0530	DEBUG	[email protected]/command.go:270	incoming extended query	{"query": "\n      select * \n      from (\n\t\t  select 1 id\n\t\t  union all\n\t\t  select 2 id\n\t\t  union all\n\t\t  select 3 id\n      )\n      where id > ?\n   ", "name": "pgx_0", "parameters": 0}
2022-12-12T11:16:45.668+0530	DEBUG	[email protected]/command.go:65	incoming command	{"length": 11, "type": "D"}
2022-12-12T11:16:45.668+0530	DEBUG	[email protected]/command.go:65	incoming command	{"length": 4, "type": "S"}
    rdapp_test.go:94: 
        	Error Trace:	/Users/kishanb/Programming/Personal/rdapp/componenttest/rdapp_test.go:94
        	Error:      	Received unexpected error:
        	            	sql: expected 0 arguments, got 1
        	Test:       	TestRedshiftDataAPIProxyTestSuite/TestPreparedStatementQueryExecution
--- FAIL: TestRedshiftDataAPIProxyTestSuite (0.01s)
    --- FAIL: TestRedshiftDataAPIProxyTestSuite/TestPreparedStatementQueryExecution (0.01s)


FAIL
2022-12-12T11:16:45.669+0530	DEBUG	[email protected]/command.go:65	incoming command	{"length": 23, "type": "Q"}
2022-12-12T11:16:45.669+0530	DEBUG	[email protected]/command.go:216	incoming simple query	{"query": "deallocate \"pgx_0\""}

If you notice the log line incoming extended query it says parameters as 0

This can be simulated by using the example code from the stdlib database/sql package

Link to code: https://pkg.go.dev/database/sql#DB.Prepare (screenshot below for easy access)

close the statement or portal

Line: 189

psql-wire//command.go

Lines 186 to 196 in b289207

	// https://github.com/postgres/postgres/blob/6e1dd2773eb60a6ab87b27b8d9391b756e904ac3/src/backend/tcop/postgres.c#L4295
	return readyForQuery(writer, types.ServerIdle)
	case types.ClientClose:
	// TODO: close the statement or portal
	writer.Start(types.ServerCloseComplete) //nolint:errcheck
	writer.End() //nolint:errcheck
	return readyForQuery(writer, types.ServerIdle)
	case types.ClientTerminate:
	err = srv.handleConnTerminate(ctx)
	if err != nil {
	return err

we have disabled the pgx tests for now until

jackc/pgx#1466 has been resolved

Line: 41

psql-wire//error_test.go

Lines 38 to 48 in 532a768

	})
	t.Run("jackc/pgx", func(t *testing.T) {
	// TODO: we have disabled the pgx tests for now until
	// https://github.com/jackc/pgx/discussions/1466 has been resolved
	t.Skip()
	ctx := context.Background()
	connstr := fmt.Sprintf("postgres://%s:%d", address.IP, address.Port)
	conn, err := pgx.Connect(ctx, connstr)
	assert.NoError(t, err)

Updation of pre-releases as releases in github

As you can see in the below screenshot and link the releases are tagged as "pre-release". Can you kindly publish them as a release?

Link: https://github.com/jeroenrinzema/psql-wire/releases

Screenshot

Fishy behaviours when handling request from multiple connections

Way to simulate

• Add a test which starts the psql wire server
• Add mulltiple sub tests which sends sql queries
• Example: https://github.com/kishaningithub/rdapp/blob/refactor-interaction-service/componenttest/rdapp_test.go

Observed behavior

• When running the sub test individually the tests pass
• When run as a whole (i.e. run the parent test) then we see wierdness where the following classes of errors happen
  • The psql wire server returns response to the client before the simple query handler finishes its work
  • Argument mismatch error

Support for GSS encryption

psql-wire currently does not support GSS encrypted connections. The GSS
authentication API is supported inside the PostgreSQL wire protocol and
API's should be made available to support these type of connections.
https://www.postgresql.org/docs/current/gssapi-auth.html
https://www.postgresql.org/docs/current/protocol-flow.html#id-1.10.6.7.13

Line: 27

psql-wire//handshake.go

Lines 24 to 34 in 3dbc832

	return conn, version, reader, nil
	}
	// TODO: support GSS encryption
	//
	// `psql-wire` currently does not support GSS encrypted connections. The GSS
	// authentication API is supported inside the PostgreSQL wire protocol and
	// API's should be made available to support these type of connections.
	// https://www.postgresql.org/docs/current/gssapi-auth.html
	// https://www.postgresql.org/docs/current/protocol-flow.html#id-1.10.6.7.13