a9s PostgreSQL - Overview
This section describes the internals of a9s PostgreSQL.
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.