1

Python Pandas has both a to_sql and read_sql methods which can be used to communicate with a database.

I am connecting to a Postgres database. Pandas seems to use SQL Alchemy and psycopg2 to perform the communication.

When using create_engine('mysql+mysqlconnector://user:password@amazon_endpoint:port/database_name',connect_args={'sslmode':'require'}, echo=True) I received an AttributeError: Unsupported argument 'sslmode' error.

I then saw the following posts: 1, 2 and amended the code to be create_engine('mysql+mysqlconnector://user:password@amazon_endpoint:port/database_name?ssl_ca=/home/user/Downloads/rds-combined-ca-bundle.pem'). The connection now works and I am able to connect to the database. Does this means that the connection is now secure? Is there a way I can confirm this?

Community
  • 1
  • 1
Sofia
  • 35
  • 5

1 Answers1

1

I don't believe the connection is secure by default, unless your Postgres instance has been configured to require secure connections. First, your instance needs to have been built with SSL support, and then it's a simple matter of telling the client to connect securely. From the documentation:

PostgreSQL has native support for using SSL connections to encrypt client/server communications for increased security. This requires that OpenSSL is installed on both client and server systems and that support in PostgreSQL is enabled at build time (see Chapter 15).

With SSL support compiled in, the PostgreSQL server can be started with SSL enabled by setting the parameter ssl to on in postgresql.conf. The server will listen for both normal and SSL connections on the same TCP port, and will negotiate with any connecting client on whether to use SSL. By default, this is at the client's option; see Section 19.1 about how to set up the server to require use of SSL for some or all connections.

When you use to_sql or read_sql, pass it a SQLAlchemy connection that's been set up with sslmode=require. This question provides the full snippet for setting up your connection:

db = create_engine(
  'postgresql+pg8000://user:pass@hostname/dbname',
  connect_args={'sslmode':'require'},
  echo=True
).connect()
Community
  • 1
  • 1
Kristján
  • 18,165
  • 5
  • 50
  • 62
  • Sorry about the accidental response edit. Your response helped me get an outcome (I updated the ticket to reflect this). Is there a way I can confirm that the connection is secure? – Sofia Sep 10 '15 at 06:10
  • It's sort of odd that you're specifying a MySQL protocol if you're connecting to Postgres. The only ways I can think to confirm are by finding some verbose connection output or sniffing your network traffic. – Kristján Sep 10 '15 at 06:19
  • 1
    There is a MySQL and Postgres instance which I connect to on Amazon. When checking the connection I checked both and copied the mysql connector code :P. Thanks for your help :) – Sofia Sep 10 '15 at 06:22