PostgreSQL – Is sslmode=verify-full Enough for Secure Connection?

postgresql

I'm using Amazon's postgresql RDS service to run a few shared development/testing databases. I'm connecting to it via something like:

 psql " dbname=postgres user=myrootuser sslrootcert=rds-combined-ca-bundle.pem sslmode=verify-full host=myhostname.asdfqwer.us-west-2.rds.amazonaws.com"

The password is in a pgpass file which no one else should be able to read.

Is this enough to connect securely to the database? Or are there vulnerabilities that can, say, downgrade the connection to plain text or something that I should be aware of?

(I know it would probably be wise to put the DB behind a VPC/firewall not make it globally accessible, but that's not practical for us at the moment).

Best Answer

If done correctly, it is secure.

The biggest vulnerability would be someone tricking you into removing the sslmode=verify-full parameters from your connect string, or some similar social engineering attack.

Related Solutions

Postgresql – How to verify postgresql db restore from pg_dump file

What is the exact problem you are trying to solve? pg_dump and pg_restore are official supported PostgreSQL tools, so you should just assume they are correctly implemented. You have a few options on checking databases are identical though, depending on how certain you need to be they are identical.

Your most basic check would be to check that both databases have the same tables. You can find a list of tables via the information schema. For a loose check that the data is identical, you could look at the amount rows in these tables, and the upper and lower bound of primary keys (assuming you have primary keys where ordering makes sense).

If you really need to guarantee the data is identical, then you will need to produce some sort of hash and compare that, and that's essentially what you are doing with sorted dumps. It's worth to remember though that the database is more than just tables, you need to check sequences too.

PostgreSQL Full Text Search – Implementation Guide

This is not really a use case for full text search because full text relies on stemming the text and parsing the chunks into tokens. As you can see from keywords, '580h' is parsed as its own word because there's no language in which '580' is a "stem" of '580h'. You'd probably be better off with regular expression matching.

Here's a query that I worked up for you:

SELECT id, title 
  FROM stickers WHERE
    (title ~* '580')
      AND
    (title ~* 'case')
ORDER BY id

Best Answer

Related Solutions

Postgresql – How to verify postgresql db restore from pg_dump file

PostgreSQL Full Text Search – Implementation Guide

Related Question