SSL Yii connection to postgresql Google cloud database

Question

I’m newbie user of Yii and I need to connect my Yii2 application to a Google cloud database (postgres). I need to made the connection using the SSL certificates I already have. I have all the credentials (host, user, 3x ssl, etc).

The thing is I’m able to connect to this remote google database using both PgAdmin client and the Google SDK console (Google Shell), but I can’t connect my Yii2 application to this database. I read a lot of documentation and found nothing.

This is what I have in my common/config/main-local.php (Yii2 advanced configuration), I added the field 'attributes', just in case it works, but as I thought is not working:

'db' => [
    'class' => 'yii\db\Connection',       
    'dsn' => 'pgsql:host=my-host;dbname=my-db-name',
    'attributes' => [
        PDO::PGSQL_ATTR_SSL_KEY => 'path/to/my/client-key.pem',
        PDO::PGSQL_ATTR_SSL_CERT => 'path/to/my/client-cert.pem',
        PDO::PGSQL_ATTR_SSL_CA => 'path/to/my/server-ca.pem',
    ],
    'username' => 'myusername',
    'password' => 'mypassword',
    'charset' => 'utf8',
],

This is how I connect through Google Shell, this works:

psql "sslmode=verify-ca sslrootcert=path/to/my/server-ca.pem sslcert=path/to/my/client-cert.pem sslkey=path/to/my/client-key.pem hostaddr=google-instance-host user=david.tamarit dbname=google-db-name"`

Then, I put my password and I'm connected to my database in Google shell.

In PgAdmin, I pass my credentials and I connect to the database, this works too.

How can I connect to my google cloud database using Yii?

Thanks in advance!

Please show us what is your connection config so we can fix it if needed (remember to remove credentials) — Bizley
With the field 'attributes' I get this: Fatal error: Uncaught Error: Undefined class constant 'PGSQL_ATTR_SSL_KEY' in C:\xampp\htdocs\myt-migracion\common\config\main-local.php:10 Stack trace: #0 C:\xampp\htdocs\myt-migracion\frontend\web\index.php(12): require() #1 {main} thrown in C:\xampp\htdocs\myt-migracion\common\config\main-local.php on line 10 — dtamab
If I comment the 'attributes' field, I get this Yii exception: Database Exception – yii\db\Exception SQLSTATE[08006] [7] FATAL: connection requires a valid client certificate FATAL: pg_hba.conf rejects connection for host "xxx.xx.xx.xx", user "myusername", database "mydbname", SSL off — dtamab

Bizley Bizley · Accepted Answer · 2019-07-11T12:19:52

Since the DB is not on your machine there is no need for you to have PostgreSQL installed and this is the reason (I think) that these constants are not recognised. Try this:

'attributes' => [
    'sslmode' => 'verify-ca',
    'sslkey' => 'path/to/my/client-key.pem',
    'sslcert' => 'path/to/my/client-cert.pem',
    'sslrootcert' => 'path/to/my/server-ca.pem',
],

SSL Yii connection to postgresql Google cloud database

1 Answers