a9s PostgreSQL SPI Configuration

This document describes a9s PostgreSQL SPI configuration.

Archive Timeout

a9s PostgreSQL supports continuous archiving, making it possible to enable or disable this backup approach via custom parameters. 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. If this is permitted and it is used to creating databases, the owner must ensure that the owner of the database is cfuser (ALTER DATABASE <dbname> OWNER TO cfuser;) and that all objects in all databases must belong to cfuser.

• 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. This privilege must not be used to create other users and roles, the application developer must use cf service-bind/create-service-key instead.

• 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.

Name	Default value	Is modifiable
log_min_duration_statement	-1	true
metric_pg_database_size	300	true
metric_pg_replication	10	true
metric_pg_stat_all_indexes	0	true
metric_pg_stat_all_tables	0	true
metric_pg_statio_all_indexes	0	true
metric_pg_statio_all_tables	0	true
metric_pg_stat_archiver_table	0	true
metric_pg_stat_database	0	true
metric_pg_stat_database_conflicts	0	true
ssl_ciphers	HIGH:MEDIUM:+3DES:!aNULL	true
ssl_min_protocol_version	TLSv1	true
wal_level_logical	false	true
wal_writer_delay	200	true
max_connections	128	true
max_replication_slots	0	true
max_wal_senders	0	true
synchronous_commit	on	true
temp_file_limit	-1	true
continuous_archiving	disabled	true

log_min_duration_statement

The parameter log_min_duration_statement corresponds to the PostgreSQL configuration parameter log_min_duration_statement.

WARNINGS:

• Allowing the end user to set this value not equal to -1 can result in filling up the logs rather quickly. This disk might run out if disk space.
• Confidential data could be logged.
• Application developer do not have access to the log files directly, but only via the custom parameter syslog.

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.

wal_level_logical

The parameter wal_level_logical sets the PostgreSQL configuration parameter wal_level to the value logical. The default value depends on the topology of the instance. To know more about this, check the wal_level_logical Custom Parameter documentation.

Valid values are currently the boolean values: true, false.

wal_writer_delay

The parameter wal_writer_delay sets the PostgreSQL configuration parameter wal_writer_delay to the configured number.

Valid values are integers between 1 and 10_000.

max_connections

The parameter max_connections sets the PostgreSQL configuration parameter max_connections to the configured number.

caution

You cannot decrease max_connections as this will break clustering.

The default value for most service plans is 128.

We recommend to use a different approach for a9s-pg and configure this value manually, based on the usage of the clients. To configure this parameter, use the iaas.a9s_pg.max_connections property on your IaaS File.

IMPORTANT: It makes sense to configure this parameter per plan.

There is no silver bullet to calculate max_connections. The optimum value for this property depends on the configured value for work_mem and the total available memory as each sort operation allocates the specified work_mem. It is important to notice that it is possible that multiple sort operations can happen during a query, so each query can use an undefined amount of memory.

A reasonable value to assume is that in average, each connection uses 3 sort operations.

max_connections = (RAM - shared_buffers - memory left to the operating system) / (work_mem * 3)

By default, a9s PostgreSQL configures shared_buffers with 25% of the RAM and the default work_mem is 8MB. We also recommend leaving around 512MB for other process, this way for a 4GB RAM VM we have:

round((4096 - 1024 - 512) / ( 8 * 3)) = 107

max_replication_slots

The parameter max_replication_slots sets the PostgreSQL configuration parameter max_replication_slots to the configured number.

Note that this value will be added to necessary number for proper cluster replication. To know more about this, check the max_replication_slots Custom Parameter documentation.

Valid values are any integer bigger than 0.

max_wal_senders

The parameter max_wal_senders sets the PostgreSQL configuration parameter max_wal_senders to the configured number.

Note that this value will be added to necessary number for proper cluster replication. To know more about this, check the max_wal_senders Custom Parameter documentation.

Valid values are any integer bigger than 0.

synchronous_commit

The parameter synchronous_commit corresponds to the PostgreSQL configuration parameter synchronous_commit.

Specifies how much WAL processing must complete before the database server returns a “success” indication to the client.

Valid values are on, remote_write, remote_write, local and off.

temp_file_limit

The parameter temp_file_limit corresponds to the PostgreSQL configuration parameter temp_file_limit.

Specifies the maximum amount of disk space that a process can use for temporary files, such as sort and hash temporary files, or the storage file for a held cursor.

Valid value are any integer or a memory size.

continuous_archiving

The parameter continuous_archiving enables or disables Continuous Archiving and PITR backup approach for PostgreSQL.

If this parameter is disabled, a9s PostgreSQL uses the normal logical backups approach.

Valid values are enabled or disabled.

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

Per Plan Configuration

Take a look at Service Plans documentation if you want to configure the custom parameters per plan.

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.

postgresql-spi.protocols

For more details on this property, please refer to the a9s Framework TLS configuration documentation.