PostgreSQL Security
for Application Developers

PostgresOpen SV 2017

September 8th, 2017
Silicon Valley, CA

DB Security
vs
User Security
vs
App Security

DB Security

Config + Authen + Authz +
Upgrades + Backups + More

User Security

Team Accessing DB
Authen, Authz, Audit ...

(Check out JackDB)

App Security

Clients talk to App
App talks to DB

(Subject of this talk)

About Me

• Sehrope Sarkuni
• Founder of JackDB (https://www.jackdb.com/)
• Contributor to pgjdbc (Java driver)
• Contributor to node-postgres (Node.js driver)
• Occasionally gives (great) advice on #postgresql

Good security is
like an onion

... it will make you cry

... it has layers

Defense in Depth

The more the merrier

(We're assuming there are crocodiles in the water too)

Principle of Least Privilege

If you shouldn't be using it

... you shouldn't be access to use it

App ⇔ Database

Types of Interactions

1. Read Data
2. Change Data
3. Change Structure

App ⇔ Database

Real World Account Separation

Level 1

One account for everything

App ⇔ Database

Real World Account Separation

Level 2

One account for read only

One account for everything else

App ⇔ Database

Real World Account Separation

Level 3

One account for read only

One account for read / write

One account for everything else

("Sweet spot")

App ⇔ Database

Real World Account Separation

Level 4+

Multiple accounts for read only

Multiple accounts for read / write

Multiple accounts for other actions

Pretty Good™ Setup

Breakdown by DB Privilege

Create Users and the App Database

-- Create some users
CREATE USER app_read WITH PASSWORD '1badPassword';
CREATE USER app_dml WITH PASSWORD 'n0tRea11yS3cret';
CREATE USER app WITH PASSWORD 'pl3aseChang3me!';

-- Create our database
CREATE DATABASE app WITH OWNER app;

Brief aside about passwords

Aren't those passwords great?

They've got a letter and a number!

One of them even uses special characters!

Guidelines for Application Database Passwords

• Cryptographically random
• Length = strength
• Humans don't need to remember them
• Humans should not need to know them

Rule of thumb:
If it's easy enough to remember, you're doing it wrong.

Generating Good
App Passwords

$ tr -dc A-Za-z0-9 < /dev/urandom | head -c 32; echo

Examples

• 2WVOlvMEQKLZaRhuqUou5KcY8Fek1JSd
• Eejho56k7aqr5uP85JCq547DHhjXmjGk
• la9cUwhYV8UO59d71m38adojvazWgtqV

Back to permissions...

Reset Default Permissions

-- Otherwise everybody can do everything in public
REVOKE ALL ON SCHEMA public FROM public;

-- ... but make sure everybody can list out objects in public
GRANT USAGE ON SCHEMA public TO public;

GRANT Permissions to app_read

-- Let our READ user read from anything
GRANT SELECT
  ON ALL TABLES IN SCHEMA public
  TO app_read;

-- ... and make sure it can for anything we create later too
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES
  TO app_read;

GRANT Permissions to app_dml

-- Let our DML user read and write everything
GRANT SELECT, INSERT, UPDATE, DELETE
  ON ALL TABLES IN SCHEMA public
  TO app_dml;

-- ... make sure it can for anything we create later too
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES
  TO app_dml;

Nothing special to do for user app

Owner of the database can do almost everything.

... but make sure the user is NOT a super user.

Provide DB Credentials
Via ENV Variables

# Use to connect as app_read
READ_DATABASE_URL="postgresql://app_read:1badPassword@..."

# Use to connect as app_dml
DML_DATABASE_URL="postgresql://app_dml:n0tRea11yS3cret@..."

# Use to connect as app
DDL_DATABASE_URL="postgresql://app_ddl:pl3aseChang3me@..."

But Provide Only
What You Need

# Use to connect as app_read
READ_DATABASE_URL="postgresql://app_read:1badPassword@..."

# Use to connect as app_dml
DML_DATABASE_URL="postgresql://app_dml:n0tRea11yS3cret@..."

Our app doesn't need the app user

... so it should not have its credentials

WWPPD

What Would Peter Parker Do?

Bonus!
Security ⇔ Scale

• Start with single cluster but easy scaling later
• Access patterns match usage patterns
• Direct read-only queries to replicas
• Lower load on primary (only writes)

Scale Out Work Effort

Connecting to your database

Using a database driver

Driver talks to DB server

Handles authentication

Presents a language-specific interface

Types of Postgres Drivers

Some Postgres Drivers

PSA: Update Your Drivers

Regularly update your drivers

• New Client Features
• New Server Features
• Performance Improvments
• Security Fixes

PSA: Signup for Updates

Get notified of new updates

• Postgres mailing list (pgsql-announce)
• Driver specific lists (ex: pgsql-jdbc)
• Driver specific GitHub notifications

Keeping up to date with updates and security notifications requires hypervigilance is table stakes.

Who here uses
Node.js +
node-postgres?

(The pg NPM module)

So Node.js users saw this right?

Connecting to our DB

Transport Layer Security

Encrypting the wire between app and database

Also referred to as SSL

Why SSL?

Eavesdropping
&
Man-in-the-middle

Eavesdropping

Ability to listen in on conversation

Alice:"Hey Bob what's your ATM pin?"

Bob:"1234!"

Eve is in the room and overhears everything

MITM

Ability to replace the conversation

Alice:"Hey Eve, ask Bob if he needs anything."

Bob:"Hey Eve, tell Alice I don't need anything."

Eve:"Hey Alice, Bob said to give Eve $20."

Eve controls the room and replaces the conversation.

Should I use SSL?

(Full flow)

Should I use SSL?

(Simpler Flow)

Should I use SSL?

(Even Simpler Flow)

libpq SSL Modes

Real World SSL Modes

iCA = Internal Certificate Authority

How do I enable SSL
from _________?

• Java
• Python
• Node.js
• Ruby
• ...

PGJDBC (Java) SSL

SSL Connection Parameters

PGJDBC (Java) SSL

With Default JVM Certificate Validation

String url = "jdbc:postgresql://" + host + ":" + port + "/";
Properties props = new Properties();
props.setProperty("user", username);
props.setProperty("password", password);
props.setProperty("databaseName", database);    
// Enable SSL
props.setProperty("ssl", "true");    
try (Connection conn = DriverManager.getConnection(url, props)) {
  // ...
}

• Eavesdropping Protection
• MITM Protection

... but never works out of the box

PGJDBC (Java) SSL

Skipping Certificate Validation

// Enable SSL
props.setProperty("ssl", "true");

// Override socket factory
props.setProperty(
    "sslfactory",
    "org.postgresql.ssl.NonValidatingFactory");

• Eavesdropping Protection
• MITM Protection

If you trust the network this is reasonably okay

PGJDBC (Java) SSL

With Certificate Pinning

// Enable SSL
props.setProperty("ssl", "true");

// Override socket factory
props.setProperty(
  "sslfactory",
  "org.postgresql.ssl.SingleCertValidatingFactory");
props.setProperty(
  "sslfactoryarg",
  "classpath:path/to/server.crt");

• Eavesdropping Protection
• MITM Protection

... and no JVM wide config required!

Certificate Pinning

• Client validates the server is the expected server
• Pinned cert can be server's or any step of chain
• Easier to use a root certificate / chain
• Insulates from server certificate updates

Ex: AWS provides a chain for all RDS databases.

(http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.SSL.html)

SCRAM Auth

Salted Challenge
Response Authentiation

• Replaces MD5
• Better than MD5
• Server validation (in PostgreSQL 11?)

What's wrong with this?

app.get('/stuff/:id/name', function(req, res, next) {
  const id = req.params.id;
  const sql = "SELECT name FROM stuff WHERE id = '" + id + "'";
  const row = (await pool.query(sql)).rows[0];
  return res.send(row.name);
});

(There's actually a couple of things wrong but one big one...)

SQL Injection

A malicious request:

GET /stuff/'%0AUNION%20ALL%0ASELECT%20secret%0AFROM%20other_table%0AWHERE%20''%20%3D%20'/name

Leads to executing arbitrary SQL:

SELECT name FROM stuff WHERE id = ''
UNION ALL
SELECT secret
FROM other_table
WHERE '' = ''

SQL Injection

Usually caused by generating SQL using user input

Damage ranges from bad to catastrophic

... But it's easily avoided!

SQL Injection

How to avoid

1. Use parameters
2. Never compose SQL as strings with user input
3. ... but if you do make sure to properly escape it

Literals
vs
Identifiers

Literals

Values and constants

Examples:

Can be parameterized

(Except in DO blocks...)

Identifiers

References to objects

Examples:

Cannot be parameterized

Parameters Can Be Used
In Place Of Literals

This works:

SELECT *
FROM some_table
WHERE id = $1

This does not work:

SELECT *
FROM $1

Using Numbered
Parameters From Node.js
(Using node-postgres)

Reference in SQL with $1, $2, ...

Specify as an array of values when executing SQL

const username = req.query.username;
const sql = 'SELECT * FROM person WHERE username = $1';
const row = (await pool.query(sql, [username])).rows[0];
// Do stuff with row

Numbered
Parameters From Java
(Using pgjdbc)

Reference in SQL with ? placeholders

Set values via PreparedStatement.setXYZ(...)

String sql = "SELECT * FROM account WHERE id = ?";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setString(1, id);
ResultSet rs = stmt.executeQuery();
// Do stuff with result set

Using Named Parameters from Java
(Using Spring JDBC)

NamedParameterJdbcTemplate namedParamTemplate = ...
String sql =
  "INSERT INTO conferences_ive_spoke_at " +
  "(name, year) " +
  "VALUES " +
  "(:name, :year)";
MapSqlParameterSource params = new MapSqlParameterSource();
params.addValue("name", "Postgres Open SV");
params.addValue("year", 2017);
namedParamTemplate.update(sql, params);

Escaping Identifiers
in Java

Using PGConnection.escapeIdentifier(...)

String tableName = ...
PGConnection pgConn = (PGConnection) conn;
String sql =
  "SELECT * " +
  "FROM " + pgConn.escapeIdentifier(tableName);
ResultSet rs = stmt.executeQuery(sql);

There's also PGConnection.escapeLiteral(...)
... but you probably don't need it. Use parameters!

Escaping Identifiers
(and more) in Node.js

Using the pg-format module

const format = require('pg-format');
const sql = format(
  'SELECT *\nFROM %I\nWHERE my_col = %L\n%s',
  'my_table',
  34,
  'LIMIT 10');

Generates:

SELECT *
FROM my_table
WHERE my_col = '34'
LIMIT 10

https://github.com/datalanche/node-pg-format

SQL Strings in Source Code

• Lots of ways to avoid via ORMs and libraries
• ... but eventually you need write custom SQL

Rule of thumb:
If there's more than one column, relation, or WHERE clause predicate then externalize it

SQL Strings in Source Code

Okay to inline:

SELECT f.name FROM foo f WHERE f.id = :id

Not okay to inline

SELECT
  f.foo,
  f.baz,
  f.bam
FROM foo f
WHERE f.id = :id
  AND f.baz BETWEEN :start AND :end

SQL Strings in Source Code

Okay to inline:

String sql = "SELECT f.name FROM foo f WHERE f.id = :id";

Not okay to inline

String sql =
  "SELECT\n" +
  "  f.foo,\n" +
  "  f.baz,\n" +
  "  f.bam\n" +
  "FROM foo f\n" +
  "WHERE f.id = :id\n" +
  "  AND f.baz BETWEEN :start AND :end\n"

Externalize With
A SQL Repo

• Directory of .sql files
• Each file is a single SQL statement
• Can be a SELECT, INSERT, UPDATE, ...
• Reference in app code via relative path

sehrope@skynet:~/model-t101$ find sql -type f
sql/person/person-find-base.sql
sql/person/person-find-by-id.sql
sql/person/person-find-by-name.sql
sql/person/person-insert.sql
sql/person/person-update-email.sql
sql/widget/widget-find-by-id.sql
sql/widget/widget-find-by-person-id.sql
sql/report/crazy-tps-report.sql

Externalize With
A SQL Repo

Load a .sql file from Java:

String sql = sqlRepo.load("foo/foo-find-by-id");    

Or from Node.js:

const sql = await sqlRepo.load("foo/foo-find-by-id");

Or any other language ... it's literally just a file!

Externalize With
A SQL Repo

• Works best with named parameters
• Proper SQL syntax highlighting
• Polyglot friendly (same file in Node.js, Java, ...)
• Much prettier git diffs
• Allows for static analysis of SQL

What could go
wrong with this?

sql/foo/my-func.sql

PERFORM foo_baz(:name)

Foo.java

String name = ... // some user input

NamedParameterJdbcTemplate namedParamTemplate = ...
String sql = sqlRepo.load('foo/my-func');
MapSqlParameterSource params = new MapSqlParameterSource();
params.addValue("name", name);
namedParamTemplate.update(sql, params);

(Hint: You can't see it...)

We don't know!

All depends on what foo_baz does

Summary

• Minimize Privileges
• Update Drivers
• Use SSL
• (Named) Parameters
• Externalize SQL

Q&A

Sehrope Sarkuni

sehrope@jackdb.com

Thank You!