Server plugin: DataStore "sql"

The sql plugin implements SQL based data storage for the SPIRE server using SQLite, PostgreSQL or MySQL databases.

Configuration	Description
database_type	database type
connection_string	connection string
ro_connection_string	Read Only connection
root_ca_path	Path to Root CA bundle (MySQL only)
client_cert_path	Path to client certificate (MySQL only)
client_key_path	Path to private key for client certificate (MySQL only)
max_open_conns	The maximum number of open db connections (default: 100)
max_idle_conns	The maximum number of idle connections in the pool (default: 100)
conn_max_lifetime	The maximum amount of time a connection may be reused (default: unlimited)
disable_migration	True to disable auto-migration functionality. Use of this flag allows finer control over when datastore migrations occur and coordination of the migration of a datastore shared with a SPIRE Server cluster. Only available for databases from SPIRE Code version 0.9.0 or later.

For more information on the max_open_conns, max_idle_conns, and conn_max_lifetime, refer to the documentation for the Go database/sql package.

Note: The SQL plugin uses an internal default setting of 30 seconds for the maximum idle time per connection (ConnMaxIdleTime). This setting is not configurable through the plugin configuration.

Database configurations

database_type = "sqlite3"

Save database in file:

connection_string="DATABASE_FILE.db"

Save database in memory:

connection_string="file:memdb?mode=memory&cache=shared"

If you are compiling SPIRE from source, please see SQLite and CGO for additional information.

Sample configuration

    DataStore "sql" {
        plugin_data {
            database_type = "sqlite3"
            connection_string = "./.data/datastore.sqlite3"
        }
    }

database_type = "postgres"

The connection_string for the PostgreSQL database connection consists of the number of configuration options separated by spaces.

For example:

connection_string="dbname=postgres user=postgres password=password host=localhost sslmode=disable"

Consult the lib/pq driver documentation for more connection_string options.

Supported PostgreSQL versions

We use lib/pq driver so we currently support the versions of PostgreSQL that the library supports. This usually means the current set of supported maintained versions. We run integration tests against a number of versions. Older versions may still work, but they are not tested.

Configuration Options

• dbname - The name of the database to connect to
• user - The user to sign in as
• password - The user's password
• host - The host to connect to. Values that start with / are for unix domain sockets. (default is localhost)
• port - The port to bind to. (default is 5432)
• sslmode - whether to use SSL (default is require, this is not the default for libpq)
• fallback_application_name - An application_name to fall back to if one isn't provided.
• connect_timeout - Maximum wait for connection, in seconds. Zero or not specified means wait indefinitely.
• sslcert - Cert file location. The file must contain PEM encoded data.
• sslkey - Key file location. The file must contain PEM encoded data.
• sslrootcert - The location of the root certificate file. The file must contain PEM encoded data.

Valid sslmode configurations

• disable - No SSL
• require - Always SSL (skip verification)
• verify-ca - Always SSL (verify that the certificate presented by the server was signed by a trusted CA)
• verify-full - Always SSL (verify that the certification presented by the server was signed by a trusted CA and the server host name matches the one in the certificate)

Sample configuration

    DataStore "sql" {
        plugin_data {
            database_type = "postgres"
            connection_string = "dbname=spire_development user=spire host=127.0.0.1 sslmode=disable"
        }
    }

Google Cloud SQL Proxy Support

The PostgreSQL datastore supports connecting to Google Cloud SQL instances via the Cloud SQL Proxy. This support is enabled automatically when using a Cloud SQL connection string. The plugin includes the Cloud SQL Proxy PostgreSQL dialer, which handles secure connections to Cloud SQL PostgreSQL instances transparently.

When connecting to a Cloud SQL PostgreSQL instance, use a connection string with the Cloud SQL instance connection name format. The Cloud SQL Proxy will automatically manage authentication and encryption.

database_type = "mysql"

The connection_string for the MySQL database connection consists of the number of configuration options (optional parts marked by square brackets):

username[:password]@][protocol[(address)]]/dbname[?param1=value1&...&paramN=valueN]

For example:

connection_string="username:password@tcp(localhost:3306)/dbname?parseTime=true"

Consult the MySQL driver repository for more connection_string options.

Supported MySQL versions

We support all currently maintainted versions of MySQL and run integration tests against LTS releases. Older versions of may still work, but are not tested.

Configuration Options

• dbname - The name of the database to connect to
• username - The user to sign in as
• password - The user's password
• address - The host to connect to. Values that start with / are for unix domain sockets. (default is localhost)

If you need to use custom Root CA, just specify root_ca_path in the plugin config. Similarly, if you need to use client certificates, specify client_key_path and client_cert_path. Other options can be configured via tls params in the connection_string options.

Sample configuration

    DataStore "sql" {
        plugin_data {
            database_type = "mysql"
            connection_string = "spire:@tcp(127.0.0.1)/spire_development?parseTime=true"
        }
    }

Google Cloud SQL Proxy Support

The MySQL datastore supports connecting to Google Cloud SQL instances via the Cloud SQL Proxy. This support is enabled automatically when using a Cloud SQL connection string. The plugin includes the Cloud SQL Proxy MySQL dialer, which handles secure connections to Cloud SQL MySQL instances transparently.

When connecting to a Cloud SQL MySQL instance, use a connection string with the Cloud SQL instance connection name format. The Cloud SQL Proxy will automatically manage authentication and encryption.

IAM Authentication

Identity and Access Management (IAM) authentication allows for secure authentication to databases hosted on cloud services. Unlike traditional methods, it uses an authentication token instead of a password. When using IAM authentication, it is required to exclude the password from the connection string.

The database_type configuration allows specifying the type of database with IAM authentication support. The configuration always follows this structure:

    database_type "dbtype-with-iam-support" {
        setting_1 = "value-1"
        setting_2 = "value-2"
        ...
    }

Note: Replace dbtype-with-iam-support with the specific database type that supports IAM authentication.

Supported IAM authentication database types include:

"aws_postgres"

For PostgreSQL databases on AWS RDS using IAM authentication. The region setting is mandatory, specifying the AWS service region.

This is the complete list of configuration options under the database_type setting when aws_postgres is set:

Configuration	Description	Required	Default
access_key_id	AWS access key id.	Required only if AWS_ACCESS_KEY_ID environment variable is not set.	Value of AWS_ACCESS_KEY_ID environment variable.
secret_access_key	AWS secret access key.	Required only if AWS_SECRET_ACCESSKEY environment variable is not set.	Value of AWS_SECRET_ACCESSKEY environment variable.
region	AWS region of the database.	Yes.

Settings of the postgres database type also apply here.

Sample configuration

    DataStore "sql" {
        plugin_data {
            database_type "aws_postgres" {
                region = "us-east-2"
            }
            connection_string = "dbname=spire user=test_user host=spire-test.example.us-east-2.rds.amazonaws.com port=5432 sslmode=require"
        }
   }

"aws_mysql"

For MySQL databases on AWS RDS using IAM authentication. The region setting is required.

This is the complete list of configuration options under the database_type setting when aws_mysql is set:

Configuration	Description	Required	Default
access_key_id	AWS access key id.	Required only if AWS_ACCESS_KEY_ID environment variable is not set.	Value of AWS_ACCESS_KEY_ID environment variable.
secret_access_key	AWS secret access key.	Required only if AWS_SECRET_ACCESSKEY environment variable is not set.	Value of AWS_SECRET_ACCESSKEY environment variable.
region	AWS region of the database.	Yes.

Settings of the mysql database type also apply here.

Sample configuration

    DataStore "sql" {
        plugin_data {
            database_type "aws_mysql" {
                region = "us-east-2"
            }
            connection_string="test_user:@tcp(spire-test.example.us-east-2.rds.amazonaws.com:3306)/spire?parseTime=true&allowCleartextPasswords=1&tls=true"
        }
    }

Read Only connection

Read Only connection will be used when the optional ro_connection_string is set. The formatted string takes the same form as connection_string. This option is not applicable for SQLite3.

SQLite and CGO

SQLite support requires the use of CGO. This is not a concern for users downloading SPIRE or using the official SPIRE container images. However, if you are building SPIRE from the source code, please note that compiling SPIRE without CGO (e.g. CGO_ENABLED=0) will disable SQLite support.