CODEWITHSUNDEEP
phppdophp-pgsql
Jared Dunham
Jared Dunham

Reputation: 1527

Connecting to PGSQL over SSL via PHP PDO

Where would I be able to pass my client certificates to connect to a PostgreSQL database? Do I have to pass these certificates in the dsn or options parameters in the PDO constructor? I'm unable to find any documentation online.

I am using PHP 7.0.22 on a Ubuntu 16.04.1. I have SSL support enabled for the pgsql driver. I did find these constants in the PDO class: PDO::MYSQL_ATTR_SSL_CA, PDO::MYSQL_ATTR_SSL_KEY and a few others, but these are obviously for mySQL and not PGSQL.

EDIT

Here is a working secure implementation based on the answer below:

$dbh = new PDO('pgsql:localhost=host;port=26257;dbname=bank;sslmode=require;sslcert=[path]/client.maxroach.crt;sslkey=[path]/client.maxroach.key;sslrootcert=[path]/ca.crt;',
    'maxroach', null, array(
      PDO::ATTR_ERRMODE          => PDO::ERRMODE_EXCEPTION,
      PDO::ATTR_EMULATE_PREPARES => true,
  ));

Upvotes: 5

Views: 11032

Answers (1)

IMSoP
IMSoP

Reputation: 97718

According to comments on the PDO Postgres connection string manual, the full DSN string is passed directly to the underlying library function PQconnectdb. Consequently, you should be able to use all the parameters specified in the PostgreSQL documentation for that string.

Relevant quotes from that page:

  • sslmode: This option determines whether or with what priority a secure SSL TCP/IP connection will be negotiated with the server.
  • sslcert: This parameter specifies the file name of the client SSL certificate.
  • sslkey: This parameter specifies the location for the secret key used for the client certificate.

All three parameters have more details on the page linked.

Note that although provided by PostgreSQL, this code is all running on the same server as PHP, so the paths will all be loaded from that server, and need to be readable by the PHP host process.

Upvotes: 5

Related Questions