a9s PostgreSQL - Overview

This section describes the internals of a9s PostgreSQL.

info

In PostgreSQL 15, the privilege for regular users to create objects in the 'public' schema has been revoked, rendering normal users incapable of creating tables or other items in the 'public' schema. To preserve the previous user functionality, a new schema named 'shared' has been introduced in a9s PostgreSQL 15. As of now, the default schema for a9s PostgreSQL versions 15 and above is 'shared'.

Credentials

The a9s PostgreSQL service instance has a special user called cfuser. Every user (e.g., created with cf bind-service or cf create-service-key) inherits its privileges and capabilities from the cfuser, which means that every user has access to two roles: its own and the cfuser. The default role used when connecting is the cfuser.

All objects in the default database must belong to the cfuser. Otherwise, other users are not able to access them. When changing the user role using SET ROLE or ALTER ROLE, one must be careful about the ownership and accessibility of tables, sequences, views, and other objects. When deleting a credential, all objects belonging to the user are being deleted and the ownership is transferred to cfuser.

It is possible to configure the privileges for cfuser and the users who inherits from cfuser via custom parameters during instance creation (cf create-service and cf update-service) and the user that inherits from cfuser during credentials creation (cf bind-service or cf create-service-key), check the documentation to know how.

Replication Slots Cleanup

A PostgreSQL user configured with the REPLICATION role privilege can create replication slots to replicate directly from a node.

When an application is connected and streaming using a replication slot, this slot is marked as active, and every WAL file that is replicated by all replication slots are recycled. When a replication slot is marked as inactive, the WAL files are kept and not recycled until that all slots streams the changes from that file. This means that when a slot is inactive, WAL files can consume all the available storage in the persistent disk and break the cluster.

a9s PostgreSQL ships the Replication Slot Reaper routine, which periodically drops inactive replication slots if they are inactive for too long or if it is inactive and the persistent disk usage has hit a threshold. To know these configuration values, consult the platform operator.