Service Instance Access

This page describes usage specific to a9s PostgreSQL. It builds upon the information provided in Getting Started - Accessing a Service Instance.

Accessing an a9s PostgreSQL Service Instance

In order to access an a9s PostgreSQL Service Instance, the Service Instance's credentials stored in the environment variables of the application bound to it. This is further explanined in Lifecycle of a Service Instance - Obtain Credentials for Accessing a Service Instance.

The resulting environment variables for an a9s PostgreSQL application can be seen below:

cf env a9s-postgresql-app

Output

Getting env variables for app a9s-postgresql-app in org test / space test as admin...
OK

System-Provided:
{
 "VCAP_SERVICES": {
  "a9s-postgresql": [
   {
    "credentials": {
     "host": "EXAMPLE-HOST",
     "hosts": ["EXAMPLE-HOST"],
     "name": "d92e2bd",
     "password": "EXAMPLE-PASSWORD",
     "port": 5432,
     "uri": "EXAMPLE-URI",
     "username": "EXAMPLE-USERNAME"
    },
    "label": "a9s-postgresql",
    "name": "my-postgresql-service",
    "plan": "postgresql-cluster-small",
    "tags": [
     "sql",
     "database"
    ]
   }
  ]
 }
}
...

The host, username and password values can be used to connect to the database with a PostgreSQL client. The database is specified as name, and this is the database that should be used to store the data.

caution

The postgres database is reserved for administrative use only and no data should be stored there.

Create a Tunnel to The Service

With the cf ssh command a ssh forward tunnel to the management dashboard can be created. Use port 5432 to connect to the a9s PostgreSQL Instance.

$ cf ssh a9s-postgresql-app -L 5432:d67901c-psql-master-alias.node.dc1.consul:5432
vcap@956aaf4e-6da9-4f69-4b1d-8e631a403312:~$

When the ssh tunnel is open you can access the instance over the address localhost:5432.

note

Don't forget to close the session with exit.

Internal Workings of a9s PostgreSQL.

info

{/ TODO: This can be removed once a9s PostgreSQL 13 is no longer supported. /}

Starting from 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.