Security in Managed Service for Sharded PostgreSQL

• How is data security ensured?

• Can my credentials leak through the router?

• How do I set up backups?

• How do I ensure high availability?

• What happens in case of overloads?

• What happens if a query is canceled?

• Are there risks of query duplication is using a load balancer upstream of the routers?

• How do I restrict access to the administrative console?

How is data security ensured?How is data security ensured?

Sharded PostgreSQL stores only data location metadata. Data security is ensured by Managed Service for PostgreSQL by providing:

• TLS 1.3 traffic encryption for all connections (client ↔ router ↔ shard).
• Audit, with access logs stored in Yandex Audit Trails for 30 days. Learn more about viewing logs in a Managed Service for Sharded PostgreSQL cluster.

Can my credentials leak through the router?Can my credentials leak through the router?

No, your credentials are secure. The risks are comparable to using a connection pooler, e.g., Odyssey. Your data does go through Sharded PostgreSQL, but the security practices are as per Yandex Cloud standards.

How do I set up backups?How do I set up backups?

Backups are run automatically for all deployed clusters. Optionally, you can specify the start time and select the retention period for your backups. Learn more about setting up backups in Managed Service for Sharded PostgreSQL.

How do I ensure high availability?How do I ensure high availability?

To ensure high availability of your Managed Service for Sharded PostgreSQL cluster:

• Create shards so that each shard, i.e., Managed Service for PostgreSQL cluster, includes at least three hosts (master and replicas) located in different availability zones.
• For Managed Service for Sharded PostgreSQL clusters with standard sharding, create at least three INFRA hosts in different availability zones.
• For Managed Service for Sharded PostgreSQL clusters with advanced sharding, create at least three COORDINATOR hosts and three ROUTER hosts in different availability zones.

What happens in case of overloads?What happens in case of overloads?

An overloaded replica becomes unavailable, and the cluster stops sending requests to it until it recovers.

Let's assume your cluster receives 95% of write requests and 5% of read requests. If your router configuration is default_target_session_attrs = read-only, read requests are evenly distributed among replicas. If a replica becomes unavailable, e.g., SELECT pg_is_in_recovery(); times out, the service stops sending requests to that replica and proceeds to check its health. As soon as the replica responds, requests to it are resumed.

What happens if a query is canceled?What happens if a query is canceled?

The application terminates the current router connection, establishes a new one, and sends a cancel message with the query ID to the router. The router receives the cancel message and forwards it to the shard the query is addressed to.

Are there risks of query duplication is using a load balancer upstream of the routers?Are there risks of query duplication is using a load balancer upstream of the routers?

Yes. If a client terminates the connection and the load balancer retries the request, Sharded PostgreSQL will treat it as a new request, which may result in duplication, e.g., for INSERT. We recommend using idempotent operations or implementing deduplication logic at the application layer.

How do I restrict access to the administrative console?How do I restrict access to the administrative console?

Access to the administrative console is restricted by default. You can connect to the console only via TLS using a password.

You set a password for access to the administrative console when creating a cluster. If you need to, you can change the password on an active cluster.