[Bug]: AWS MySQL RDS connectivity issue when force TLS is enabled

[vasiliyplotnikov]

Snipe-IT Version

v8.3.6

PHP Version

8.3.6

Composer Version

2.9.2

MySQL/MariaDB version

8.4.7

How did you install Snipe-IT?

Docker

Is this a fresh install or an upgrade?

Upgrade

What happened?

I have an AWS RDS MySQL (8.4.7) database instance. The RDS parameter group has the option require_secure_transport enabled. With this option, only TLS connections are allowed.

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/mysql-ssl-connections.require-ssl.html

I can connect to the RDS with the following command without any issues:

mysql -h host.name -P 3306 -u host.username -p --ssl-mode=REQUIRED

However, snipeit throws the following error:

SQLSTATE[HY000] [2002] Cannot connect to MySQL using SSL (Connection: mysql, SQL: select exists (select 1 from information_schema.tables where table_schema = 'snipeit' and table_name = 'migrations' and table_type in ('BASE TABLE', 'SYSTEM VERSIONED')) as `exists`) 

I believe there is a bug in the MySQL driver configuration. Laravel's PAAS SSL configuration is missing the PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT attribute

snipe-it/config/database.php

Line 103 in 20bacfe

PDO::MYSQL_ATTR_SSL_CA => env('DB_SSL_CA_PATH'), // /path/to/ca.pem

You can reproduce this by executing the following in a container from the snipeit docker image

php -r '
try {
  $pdo = new PDO(
    "mysql:host=host.name;dbname=dbname",
    "dbuser",
    "dbpassword",
    [
      PDO::MYSQL_ATTR_SSL_CA => "/var/lib/snipeit/rds-global-bundle.pem",
    ]
  );
  echo "SSL PDO connection successful\n";
} catch (PDOException $e) {
  echo $e->getMessage() . "\n";
}
'

Returns SQLSTATE[HY000] [2002] Cannot connect to MySQL using SSL

php -r '
try {
  $pdo = new PDO(
    "mysql:host=host.name;dbname=dbname",
    "dbuser",
    "dbpassword",
    [
      PDO::MYSQL_ATTR_SSL_CA => "/var/lib/snipeit/rds-global-bundle.pem",
      PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false
    ]
  );
  echo "SSL PDO connection successful\n";
} catch (PDOException $e) {
  echo $e->getMessage() . "\n";
}
'

Returns SSL PDO connection successful

Or you can just add the parameter in the database.php, restart the service and verify DB connectivity with

php artisan tinker
DB::connection()->getPdo();

What browsers are you seeing the problem on?

No response

Can you reproduce this on the public demo?

N/A

Do you have full multiple company support enabled?

No

If you have full multiple company support enabled, do you have location scoping to company enabled?

I do not have full multiple company support enabled

Application log output

SQLSTATE[HY000] [2002] Cannot connect to MySQL using SSL (Connection: mysql, SQL: select exists (select 1 from information_schema.tables where table_schema = 'snipeit' and table_name = 'migrations' and table_type in ('BASE TABLE', 'SYSTEM VERSIONED')) as `exists`) 

Browser console output

Common Issues

• I have searched this repo for existing issues related to my issue (including closed issues)
• My APP_URL is set correctly in my .env file (including http or https and no trailing slash)
• I have searched the official Snipe-IT documentation and have checked the Common Issues documentation (where applicable)
• I have run database migrations (where applicable).
• I have attached screenshots and/or videos of the issue (where applicable)

Code of Conduct

• I agree to follow this project's Code of Conduct