1

I try to read or write from/to an AWS RDS Proxy with a postgres RDS as the endpoint. The operation works with psql but fails on the same client with pg8000 or psycopg2 as client libraries in Python.

The operation works with with pg8000 and psycopg2 if I use the RDS directly as endpoint (without the RDS proxy).

sqlaclchemy/psycopg2 error message:

Feature not supported: RDS Proxy currently doesn’t support command-line options.

A minimal version of the code I use:

from sqlalchemy import create_engine

import os
from dotenv import load_dotenv
load_dotenv()

login_string = os.environ['login_string_proxy']
engine = create_engine(login_string, client_encoding="utf8", echo=True, connect_args={'options': '-csearch_path={}'.format("testing")})
engine.execute(f"INSERT INTO testing.mytable (product) VALUES ('123')")

pg8000: the place it stops / waits for something is in core.py:

def sock_read(b):
            try:
                return self._sock.read(b)
            except OSError as e:
                raise InterfaceError("network error on read") from e

A minimal version of the code I use:

import pg8000

import os
from dotenv import load_dotenv
load_dotenv()

db_connection = pg8000.connect(database=os.environ['database'], host=os.environ['host'], port=os.environ['port'], user=os.environ['user'], password=os.environ['password'])
db_connection.run(f"INSERT INTO mytable (data) VALUES ('data')")
db_connection.commit()
db_connection.close()

The logs in the RDS Proxy looks always normal for all the examples I mentioned - e.g.:

A new client connected from ...:60614.
Received Startup Message: [username="", database="", protocolMajorVersion=3, protocolMinorVersion=0, sslEnabled=false]
Proxy authentication with PostgreSQL native password authentication succeeded for user "" with TLS off.
A TCP connection was established from the proxy at ...:42795 to the database at ...:5432.
The new database connection successfully authenticated with TLS off.

I opened up all ports via security groups on the RDS and the RDS proxy and I used an EC2 inside the VPC.

I tried with autocommit on and off.

micha
  • 41
  • 1
  • 8

2 Answers2

2

The 'command-line option" being referred to is the -csearch_path={}.

Remove that, and then once the connection is established execute set search_path = whatever as your first query.

jjanes
  • 37,812
  • 5
  • 27
  • 34
  • Thanks, good find. It works now with sqlaclchemy/psycopg2. But the connection with the pg8000 module still does not work. So in the end probably a problem within pg8000. I may reach out to them on their GitHub. – micha Apr 26 '21 at 08:58
  • But what happens with pg8000? You get an error message? It just hangs forever? – jjanes Apr 26 '21 at 15:07
  • 1
    Hangs forever. I will try to report it to them within the next days. – micha Apr 28 '21 at 19:40
2

This is a known issue that pg8000 can't connect to AWS RDS proxy (postgres). I did a PR https://github.com/tlocke/pg8000/pull/72 let see if Tony Locke (the father of pg8000) approves the change. ( if not you have to change the lines of the core.py https://github.com/tlocke/pg8000/pull/72/files )

    self._write(FLUSH_MSG)
    if (code != PASSWORD):
        self._write(FLUSH_MSG)
grepit
  • 21,260
  • 6
  • 105
  • 81
  • 2
    The patch has indeed been accepted (thanks @grepit) and is in pg8000 version 1.19.4. – Tony Locke May 05 '21 at 20:33
  • Thanks Tony for the quick turnaround time on this. – grepit May 05 '21 at 21:19
  • Help wanted! I don't have access to AWS and I can't work out why @grepit's patch works and release 1.19.4 doesn't work. The discussion is at https://github.com/tlocke/pg8000/pull/72 if you have any ideas. Thanks! – Tony Locke May 11 '21 at 19:52
  • @TonyLocke I'll try to spend some time this weekend or next week to try this out and I'll update you on the CR – grepit May 11 '21 at 21:07