a9s PostgreSQL SPI Configuration

This document describes a9s PostgreSQL SPI configuration.

Summary

Archive Timeout

a9s PostgreSQL supports continuous archiving, making possible to configure its value via custom parameter. This means that the user can configure the value for archive_timeout during instance creation.

It is possible for the operator to specify the default value for archive_timeout, as well as minimum and maximum possible values for this property. We describe these properties below.

Properties

1. Default Archive Timeout

Configurable through the property postgresql-spi.continuous_archiving.default_archive_timeout, receives an integer for the default timeout in seconds. Default value is 1h (3600s).

2. Minimum Archive Timeout

Configurable through the property postgresql-spi.continuous_archiving.min_archive_timeout, receives an integer bigger than 0 for the minimum possible value a user can configure archive_timeout during instance creation. It is always possible to set the archive timeout to 0, which mean no timeout. Default value is 10 minutes (600s).

3. Maximum Archive Timeout

Configurable through the property postgresql-spi.continuous_archiving.max_archive_timeout, receives an integer bigger than 0 for the maximum possible value a user can configure archive_timeout during instance creation. Default value is 2h (7200s).

Role Privileges

a9s PostgreSQL supports role privileges configuration, making it possible for a user to specify which privileges the user has by default or on a per credentials basis. However, privileges must be permitted according to the platform operator's decision.

Possible configurable privilege values are CREATEDB, CREATEROLE, and REPLICATION. The operator must keep in mind that permitting and enabling privileges might expose the service, cause data loss, and even impact on the service replication. There is a small and not exhaustive list of considerations below. Before permitting any privilege, read the official PostgreSQL documentation:

  • CREATEDB: Gives the user permission to create and drop new databases. NOCREATEDB is the default. A user with this privilege can cause data loss and even impact on the automation of your cluster. For example, deleting the postgres database can cause backup and restore to fail, and deleting the current default database causes the user to lose the data and not being able to access the instance.

  • CREATEROLE: Gives the user permission to create, delete, and alter the attributes of a role. NOCREATEROLE is the default. A role with this privilege can create new users with a weak password, therefore exposing the service, it is also able to create new roles with different privileges (except SUPERUSER). For example, a user could create another user with CREATEDB privileges.

  • REPLICATION: Gives the role permission to create and drop replication slots and connect to the service in replication mode. NOREPLICATION is the default. Misusing a user with this privilege can cause problems to replication, continuous archiving, and internal storage usage, which might cause a9s Parachute to stop the service.

Properties

1. Default Privileges

Configurable through the property postgresql-spi.service_instance.privileges.default, it receives an array of possible permitted roles. For example:

postgresql-spi:
  service_instance:
    privileges:
      default:
      - CREATEROLE
      - CREATEDB

The specified default value is validated according to the permitted privileges when creating the instance.

Default is empty.

2. Permitted Privileges

Configurable through the property postgresql-spi.service_instance.privileges.permitted, it receives an array of permitted privileges. A user can only configure the role privilege if it is permitted under this section. For example:

postgresql-spi:
  service_instance:
    privileges:
      permitted:
      - CREATEROLE
      - CREATEDB

By default, no privilege configuration is permitted.

Custom Parameter Configuration

The a9s PostgreSQL SPI handles custom parameters for all service instances. It does configuration and validation of custom parameters.

The a9s PostgreSQL SPI defines default values for certain custom parameters. Also, it defines whether a custom parameter can be set by the application developer.

The platform operator can overwrite those choices by changing the a9s PostgreSQL SPI configuration. The platform operator has to configure the property postgresql-spi.service_instance.custom_param_config. The property value must be a hash. Every key in that hash stands for a custom parameter and the value for every key contains the configuration for that particular parameter. The configuration for a particular param is a hash consisting of the following optional keys: default_value and modifiable. The key default_value specifies the default value for that parameter and modifiable specifies whether the application developer is allowed to set that custom parameter. For example:

postgresql-spi:
  service_instance:
    custom_param_config:
      metric_pg_database_size:
        default_value: 600
        modifiable: false

Default Custom Parameter Configuration

The following table contains custom parameters that are configurable by the platform operator. The meaning of those custom parameters can be read in the application developer documentation for a9s PostgreSQL.

NameDefault valueIs modifiable
metric_pg_database_size300true
metric_pg_replication10true
metric_pg_stat_all_indexes0true
metric_pg_stat_all_tables0true
metric_pg_statio_all_indexes0true
metric_pg_statio_all_tables0true
metric_pg_stat_archiver_table0true
metric_pg_stat_database0true
metric_pg_stat_database_conflicts0true
ssl_ciphersHIGH:MEDIUM:+3DES:!aNULLtrue
ssl_min_protocol_versionTLSv1true

ssl_ciphers

The parameter ssl_ciphers corresponds to the PostgreSQL configuration parameter ssl_ciphers.

a9s PostgreSQL SPI does no validation for the ssl_ciphers value except that it must be of type string or null (use default value).

ssl_min_protocol_version

The parameter ssl_min_protocol_version corresponds to the PostgreSQL configuration parameter ssl_min_protocol_version. Valid values are currently: TLSv1, TLSv1.1, TLSv1.2, TLSv1.3.

The ssl_min_protocol_version has currently no effect. PostgreSQL > 11 introduces this option to set the minimum SSL/TLS protocol version to use.

Example Configuration

Let's build an imaginary example.

The default value for the metric_pg_replication custom parameter should be 600. The custom parameter metric_pg_database_size should also have a default value of 600 and the application developer should not be allowed to change it.

Then the configuration must look the following way:

postgresql-spi:
  service_instance:
    custom_param_config:
      metric_pg_replication:
        default_value: 600
        modifiable: true
      metric_pg_database_size:
        default_value: 600
        modifiable: false

Notes

Additional custom parameters will be made available over time. We do not make all available custom parameters the SPIs knows about available to the platform operator from the beginning.

During the SPI startup, the configuration the platform operator made will be checked. When it's invalid, the SPI will not start up.