CODEWITHSUNDEEP
pythonpostgresqlpandassqlalchemy
Sofia
Sofia

Reputation: 35

How to secure pandas methods when communicating with a Postgres database?

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?

Upvotes: 1

Views: 1503

Answers (1)

Kristján
Kristján

Reputation: 18803

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()

Upvotes: 1

Related Questions