Security and Reliability in Managed Databases for PostgreSQL and MySQL

This page outlines key principles and best practices to help you ensure your applications’ security and reliability when using Managed Databases for PostgreSQL and MySQL.

Resilience

Resilience ensures the continuity and availability of your applications and data, even in the face of disruptions or failures. In Managed Databases for PostgreSQL and MySQL, you can promote resilience through the following pillars: availability, durability, backups and snapshots, failover, data restoration, and Read Replicas.

Availability and durability

Managed Databases for PostgreSQL and MySQL targets the following SLOs for their configuration types:

Important

The corresponding SLAs are only available for Production Optimized Database Instances.

Note

The detailed SLAs measurements and guarantees can be found on the Service Level Agreement for Managed Databases for PostgreSQL and MySQL page.

Backups and snapshots

You can configure your snapshots and/or backup settings to automate actions. When you define execution time, frequency, and expiration, you automate the creation and deletion of snapshots and backups. Creation happens at the defined frequency and deletion happens when resources reach their expiration date.

Refer to the How to enable Autobackup documentation page for more information.

Data restoration

You can restore your backups and snapshots anytime. The restoration is triggered by you, and Scaleway then automatically executes the restore operation.

Keep in mind that:

• If you restore a backup to a Database Instance already containing data, the existing data overwritten by the backup data will not be recoverable.
• Snapshot or backup restore operations can take some time, depending on the following factors:
  • Snapshots are disk “pictures” which contain all ready-to-use data structures for PostgreSQL and MySQL. Restoring a snapshot should usually take a few minutes.
  • Backups are logical backups of a database and only “raw” data from tables. Restoring a backup requires recreating objects such as indexes. Depending on the number of indexes, total data size, and database size, the restore operations can take from a few minutes to several hours.

Refer to the How to manage backups and the How to manage snapshots documentation pages for more information.

Failover

If you use High Availability as your Database Instance configuration type, whenever the primary node becomes unavailable, a failover will be performed automatically after the unavailability is detected.

Read Replicas

A Read Replica is a live copy of a Database Instance that only allows read-only connections. The replica mirrors the data of the primary Database node, and any changes made are replicated to the replica asynchronously.

Creating, deleting, and promoting a Read Replica are actions triggered by you. Scaleway then automatically creates, deletes or promotes the Read Replica.

Note

The time it takes to create the Read Replica depends on the volume of data being replicated.

When promoting a Read Replica to a main node, make sure that the main node has completed all ongoing operations and that the Read Replica lag is down to zero before promoting it.

If ongoing operations are not complete before the promote operation starts, some operations from the main node might not be contained in the newly promoted node.

Refer to the How to manage Read Replicas documentation page for more information.

Monitoring

Monitoring is an essential pillar to ensure the security and reliability of your services. The practice provides real-time insights into the performance, security, and resource allocation of your databases. Knowing the behaviors of databases in real-time allows you to proactively identify and resolve issues and optimize operational efficiency.

Metrics and logs

Database default logs and metrics are stored inside Cockpit.

This includes:

• Metrics: CPU, memory, disk usage, replication lag, and number of connections. Metrics are refreshed every minute.
• Logs:

  • PostgreSQL logs at a default level log_error_verbosity="VERBOSE" (see PostgreSQL documentation). Every request longer than 5 seconds is also logged.

    Note

    The log_min_duration_statement parameter is set at 5000 and cannot be changed.

    Tip

    For more granular log details, you can activate the pgaudit extension.

  • MySQL logs at a default level log_error_verbosity=2 (see MySQL documentation). Logging slow queries is not activated by default, but you can activate it using slow_query_log and long_query_time. 

Important

Since logs are also stored inside your Database Instance, any changes performed on the default log verbosity levels and threshold can lead to an increase in storage size, which might make your database unavailable. These potential impacts on the database hence remain your responsibility.

Disk full

Once full, a database will switch to the disk_full state and only accept read requests.

To troubleshoot the issue, you can either:

• Increase disk volume size, or
• Clear data from your database.

Refer to the Dealing with disk_full mode in a Database Instance documentation page for more information.

Note

You are responsible for monitoring disk size growth and performing any required disk size increases.

Database performance

Scaleway creates your Database Instance with a default configuration fine-tuned to optimize database performance for standard workloads and monitor and solve issues caused by hardware, operating systems, or file system level failures.

You are responsible for:

• Any impacts on performance incurred from SQL queries or database-specific configurations you set up, such as creating indexes, triggering backups, or changing advanced settings (e.g. max_connections).
• Sizing the Database Instance appropriately for your workload.

Configuration and version management

Configuration and version management are critical for maintaining consistency, reliability, and scalability across your services, and to ensure that deployments are accurately configured, tracked, and updated.

Database instance configuration

Scaleway creates your Database Instance with a default configuration proven for standard use cases. Certain options are restricted to ensure reliability.

You can modify some advanced settings, such as effective_cache_size or max_connections in PostgreSQL, but you are responsible for any impacts on your database availability and performance.

Version management

Two types of engine version upgrades are available for your databases:

Database extensions

For security reasons, we support a defined set of databases extensions for PostgreSQL.

For security and reliability reasons, we only support a specific version of each extension for each PostgreSQL major version.

This means we will only update the available versions of an extension when any new PostgreSQL major versions are released.

As extensions are usually less trusted components, we put in place several security measures to prevent privilege escalations from an extension.

However, the extensions are provided “as is”. When installing and using an extension, you remain responsible for its usage, compatibility, and potential side effects on database objects or existing users and queries.

Data protection

Data protection allows you to safeguard sensitive information from unauthorized access to prevent breaches and data loss. Implementing robust encryption, access controls, and backup strategies ensures data integrity and compliance with regulatory standards.

Encryption in transit

Managed Databases for PostgreSQL and MySQL supports SSL connections and allows you to download TLS certificates from Managed Databases.

You remain responsible for configuring encryption for the SQL clients connecting to the database, by carrying out actions such as activating SSL in client configuration options or renewing TLS certificates.

When used by SQL Clients, SSL connections will be performed end-to-end, i.e. from your SQL client up to the Database Instance executing SQL queries.

Note

Any intermediary Scaleway components, such as Load Balancers, do not decrypt Managed Databases’ traffic.

Scaleway access

In order to perform maintenance operations and guarantee the reliability of your database, or comply with local regulations, we reserve the right to access your database with administrative access.

Most of these accesses are automatic actions, in cases where Scaleway needs to set up replication or upgrade minor versions.

Manual interventions might be required occasionally. All Scaleway accesses are authenticated and traced following industry security standards.

Identity and access management

Identity and access management allows you to enable granular control over user permissions and to mitigate the risk of unauthorized access or data breaches.

Scaleway IAM

Managed Databases for PostgreSQL provides IAM permissions sets that allow or restrict specific actions a user or application can perform, such as creating or deleting Database Instances. These are:

• RelationalDatabasesReadOnly and
• RelationalDatabasesFullAccess

You are responsible for attributing these permissions to the relevant users or applications and reviewing these accesses frequently.

Refer to the How to create a policy documentation page to set up a policy for Managed Databases for PostgreSQL and MySQL.

Database access management

Managed Databases for PostgreSQL and MySQL allow you to manage access to user accounts inside your database.

These typically correspond to PostgreSQL or MySQL users inside your Database Instance who can connect to your database and perform SQL queries.

To ensure your Database Instance security and reliability, we limit the set of SQL actions you can perform or give to other users, such as CREATE PUBLICATION or CREATE USER user SUPERUSER in PostgreSQL.

You are responsible for giving access to the relevant users or applications and reviewing these accesses frequently.

Moreover, these accesses are stored inside your Database Instance only and are independent of Scaleway IAM, which means you are responsible for ensuring they are properly backed up or removed when required.

Refer to the How to manage user permissions documentation page for more information.

Security best practices

For optimal security, we recommend that you:

1. Activate connection encryption and certificate validation in clients connecting to the database

   • For PostgreSQL clients: set the ssl-mode option to verify-full
   • For MySQL clients: enable the VERIFY_IDENTITY option

2. Restrict external access to databases by limiting IPs able to connect to the database.

Note

You can use ACLs to restrict access. Refer to the How to manage allowed IPs documentation page for more information.