Connecting to Cloud SQL postgres instance with SSL in python

Question

I am trying to connect to a postgres instance I have in cloud sql. I have everything set up and am able to connect to it if ssl encryption is turned off. But now that I have it on I am trying to connect but running into some error.

  def run():
    connector = Connector()
    def getconn():
        conn = connector.connect(
            os.getenv("CONNECTION_NAME"),
            "pg8000",
            user = os.getenv('DB_USERNAME'),
            password = os.getenv("DB_PASSWORD"),
            db=os.getenv('DB_NAME'),
            ip_type= IPTypes.PRIVATE
        )
        return conn
    pool = sqlalchemy.create_engine(
        "postgresql+pg8000://",
        creator=getconn,
   pool.execute("CREATE TABLE........;")

All the certs are stored in secret manager as strings so I am using env variables to grab them, which is why I used cadata for example. But running into this error cadata does not contain a certificate why is this error coming up?

What is 'secret manager'? I know of several things that could go by that description, and I'm sure there are more i don't know of. — jjanes
– jjanes, Commented Aug 21, 2022 at 21:20
@jjanes gcp secret manager it’s where you can store API keys, passwords, access keys etc — user12230466
– user12230466, Commented Aug 21, 2022 at 21:45
On which line of code are you getting this error? Remember that Cloud SQL does not provide you with a public certificate. Verification of the certificate is performed by SQL Server and not your code. The CN does not even match. In your case, the client certificate is used for authentication of the client and not verification of the host. Therefore, do not use host based code. — John Hanley
– John Hanley, Commented Aug 21, 2022 at 21:51
@JohnHanley the error occurs here ssl_context.load_verify_locations(cadata=server_ca). I was attempting to follow GCPs example of connecting to Postgres instance via ssl on their examples. I can connect fine with ssl encryption being off but with it being on I need to connect with those certs? — user12230466
– user12230466, Commented Aug 21, 2022 at 21:54
The APIs that you are using require specifying files. Write the contents of the certificates to a local file that the driver can read from. — John Hanley
– John Hanley, Commented Aug 22, 2022 at 2:38

Jack Wotherspoon · Accepted Answer · 2022-08-22 14:54:07Z

3

I'd recommend using the Cloud SQL Python Connector to connect to Cloud SQL from Python as it will generate the SSL context for you, meaning no need to manage SSL certificates! It also has additional benefits of not needing to authorize networks etc.

You can find a code sample for the Python Connector similar to the one you are using for establishing a TCP connection.

There is also an interactive getting started Colab Notebook that will walk you through using the Python Connector without you needing to change a single line of code!

It makes connecting to Cloud SQL both easy and secure.

answered Aug 22, 2022 at 14:54

Jack Wotherspoon

2,0591 gold badge5 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

user12230466 Over a year ago

I was using this example cloud.google.com/sql/docs/postgres/connect-run#private-ip_1

user12230466 Over a year ago

Does this example require ssl^?

Jack Wotherspoon Over a year ago

If you use the Python Connector you won't need to manage the SSL certs yourself. In that link you shared the import ssl is most likely a typo.

user12230466 Over a year ago

It looks like this example is not using the python connector right? As it doesnt intialize connection with connector = Connector()?

Jack Wotherspoon Over a year ago

Correct, the Python Connector is still technically in "beta" for a few more weeks before it will be going GA. Until then the docs will show TCP/Unix. The Python Connector is more secure and easy to use, the doc you linked in the near future will be replaced with Python Connector code usage.

|

Collectives™ on Stack Overflow

Connecting to Cloud SQL postgres instance with SSL in python

1 Answer 1

12 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related