Connecting Heroku Lumen App to Amazon RDS MySQL 5.7.19

0
votes

I have granted access for the Amazon RDS MySQL instance (5.7.19) for 0.0.0.0/0 as suggested by Heroku. This makes the connection work successfully. When I force SSL for the mysql-user (ALTER USER 'user'@'%' REQUIRE SSL;) the connection breaks. I have followed these instructions from Heroku.

My Heroku DATABASE_URL config variable: mysql://username:password@AMAZONRDSMYSQLURL/DATABASE?sslca=config/amazon-rds-ca-cert.pem

The certificate is stored under /config/amazon-rds-ca-cert.pem

From my localhost terminal I can connect via SSL to the Amazon RDS instance (with the same certificate from above) using this command (works also without --ssl-mode=VERIFY_IDENTITY): mysql -h AMAZONRDSMYSQLURL --ssl-ca=/Users/Documents/amazon-rds-ca-cert.pem --ssl-mode=VERIFY_IDENTITY -u USERNAME -p

My database configuration in Lumen (/config/database.php):

<?php

$url = parse_url(getenv("DATABASE_URL"));
$host = $url["host"];
$username = $url["user"];
$password = $url["pass"];
$database = substr($url["path"], 1);

return [

    'default' => 'mysql',

    'connections' => [

        'mysql' => [
            'driver' => 'mysql',
            'port' => '3306',
            'host'      => $host,
            'database'  => $database,
            'username'  => $username,
            'password'  => $password,
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'strict' => true,
            'engine' => null,
        ],

Any idea whats going wrong here? Thanks!

2
mysqllaravelherokuamazon-rdslumen

2 Answers

0
votes

You haven't told your Database file to use SSL.

<?php

$url = parse_url(getenv("DATABASE_URL"));
$host = $url["host"];
$username = $url["user"];
$password = $url["pass"];
$database = substr($url["path"], 1);

return [

'default' => 'mysql',

'connections' => [

    'mysql' => [
        'driver' => 'mysql',
        'port' => '3306',
        'host'      => $host,
        'database'  => $database,
        'username'  => $username,
        'password'  => $password,
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'strict' => true,
        'engine' => null,
        'options'  => array(
                    "sslmode" => "require",
                    "sslrootcert" => "config/amazon-rds-ca-cert.pem"
                )
    ],
0
votes

I finally made it work with:

'connections' => [

'sqlite' => [
    'driver' => 'sqlite',
    'database' => env('DB_DATABASE', database_path('database.sqlite')),
    'prefix' => '',
],

'mysql' => [
    'driver' => 'mysql',
    'port' => '3306',
    'host'      => $host,
    'database'  => $database,
    'username'  => $username,
    'password'  => $password,
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
    'strict' => true,
    'engine' => null,
    'options'  => array(
        PDO::MYSQL_ATTR_SSL_CA => '../config/amazon-rds-ca-cert.pem'
    )
],