Postgres Password

Learn how to work with the Postgres Operator in a Kubernetes (K8s) environment.

Connect to PostgreSQL database server using psql First, launch psql program and connect to the PostgreSQL Database Server using the postgres user by clicking the psql icon as shown below: Second, enter the necessary information such as Server, Database, Port, Username, and Password. After installing postgres follow following steps in order to setup password for default system account of Linux execute following in terminal: user:$ sudo -i -u postgres postgres@user:$ psql. After executing above two commands you will get into postgres shell. Execute this query in postgres shell.

Create a manifest for a new PostgreSQL cluster

Make sure you have set up the operator. Then you can create anew Postgres cluster by applying manifest like this minimal example:

Once you cloned the Postgres Operator repositoryyou can find this example also in the manifests folder:

Make sure, the spec section of the manifest contains at least a teamId, thenumberOfInstances and the postgresql object with the version specified.The minimum volume size to run the postgresql resource on Elastic BlockStorage (EBS) is 1Gi.

Note, that the name of the cluster must start with the teamId and -. AtZalando we use team IDs (nicknames) to lower the chance of duplicate clusternames and colliding entities. The team ID would also be used to query an API toget all members of a team and create database roles forthem. Besides, the maximum cluster name length is 53 characters.

Watch pods being created

Check if the database pods are coming up. Use the label application=spilo tofilter and list the label spilo-role to see when the master is promoted andreplicas get their labels.

The operator also emits K8s events to the Postgresql CRD which can be inspectedin the operator logs or with:

Connect to PostgreSQL

With a port-forward on one of the database pods (e.g. the master) you canconnect to the PostgreSQL database from your machine. Use labels to filter forthe master pod of our test cluster.

Open another CLI and connect to the database using e.g. the psql client.When connecting with the postgres user read its password from the K8s secretwhich was generated when creating the acid-minimal-cluster. As non-encryptedconnections are rejected by default set the SSL mode to require:

Defining database roles in the operator

Postgres Operator allows defining roles to be created in the resulting databasecluster. It covers three use-cases:

• manifest roles: create application roles specific to the cluster describedin the manifest.
• infrastructure roles: create application roles that should be automaticallycreated on every cluster managed by the operator.
• teams API roles: automatically create users for every member of the teamowning the database cluster.

In the next sections, we will cover those use cases in more details. Note, thatthe Postgres Operator can also create databases with pre-defined owner, readerand writer roles which saves you the manual setup. Read more in the nextchapter.

Manifest roles

Manifest roles are defined directly in the cluster manifest. Seeminimal postgres manifestfor an example of zalando role, defined with superuser and createdb flags.

Manifest roles are defined as a dictionary, with a role name as a key and alist of role options as a value. For a role without any options it is best tosupply the empty list []. It is also possible to leave this field empty as inour example manifests. In certain cases such empty field may be missing laterremoved by K8s due to the null value it gets(foobar_user: is equivalent to foobar_user: null).

The operator accepts the following options: superuser, inherit, login,nologin, createrole, createdb, replication, bypassrls.

By default, manifest roles are login roles (aka users), unless nologin isspecified explicitly.

The operator automatically generates a password for each manifest role andplaces it in the secret named{username}.{team}-{clustername}.credentials.postgresql.acid.zalan.do in thesame namespace as the cluster. This way, the application running in theK8s cluster and connecting to Postgres can obtain the password right from thesecret, without ever sharing it outside of the cluster.

At the moment it is not possible to define membership of the manifest role inother roles.

To define the secrets for the users in a different namespace than that of the cluster,one can set enable_cross_namespace_secret and declare the namespace for thesecrets in the manifest in the following manner,

Here, anything before the first dot is taken as the namespace and the text afterthe first dot is the username. Also, the postgres roles of these usernames wouldbe in the form of namespace.username.

For such usernames, the secret is created in the given namespace and its name isof the following form,{namespace}.{username}.{team}-{clustername}.credentials.postgresql.acid.zalan.do

Infrastructure roles

An infrastructure role is a role that should be present on every PostgreSQLcluster managed by the operator. An example of such a role is a monitoringuser. There are two ways to define them:

• With the infrastructure roles secret only
• With both the the secret and the infrastructure role ConfigMap.

Infrastructure roles secret

Infrastructure roles can be specified by the infrastructure_roles_secretsparameter where you can reference multiple existing secrets. Prior to v1.6.0the operator could only reference one secret with theinfrastructure_roles_secret_name option. However, this secret could containmultiple roles using the same set of keys plus incrementing index.

The block above describes the infrastructure role 'dbuser' with password'secret' that is a member of the 'operator' role. The resulting role willautomatically be a login role.

With the new option users can configure the names of secret keys that containthe user name, password etc. The secret itself is referenced by thesecretname key. If the secret uses a template for multiple roles as describedabove list them separately.

Note, only the CRD-based configuration allows for referencing multiple secrets.As of now, the ConfigMap is restricted to either one or the existing templateoption with infrastructure_roles_secret_name. Please, refer to the examplemanifests to understand how infrastructure_roles_secrets has to be configuredfor the configmap or CRD configuration.

If both infrastructure_roles_secret_name and infrastructure_roles_secretsare defined the operator will create roles for both of them. So make sure,they do not collide. Note also, that with definitions that solely use theinfrastructure roles secret there is no way to specify role options (likesuperuser or nologin) or role memberships. This is where the additionalConfigMap comes into play.

Secret plus ConfigMap

A ConfigMapallows for defining more details regarding the infrastructure roles. Therefore,one should use the new style that specifies infrastructure roles using both thesecret and a ConfigMap. The ConfigMap must have the same name as the secret.The secret should contain an entry with 'rolename:rolepassword' for each role.

And the role description for that user should be specified in the ConfigMap.

One can allow membership in multiple roles via the inrole array parameter,define role flags via the user_flags list and supply per-role options throughthe db_parameters dictionary. All those parameters are optional.

Both definitions can be mixed in the infrastructure role secret, as long asyour new-style definition can be clearly distinguished from the old-style one(for instance, do not name new-style roles userN).

Since an infrastructure role is created uniformly on all clusters managed bythe operator, it makes no sense to define it without the password. Suchdefinitions will be ignored with a prior warning.

See infrastructure roles secretand infrastructure roles configmapfor the examples.

Teams API roles

These roles are meant for database activity of human users. It's possible toconfigure the operator to automatically create database roles for lets say allemployees of one team. They are not listed in the manifest and there are no K8ssecrets created for them. Instead they would use an OAuth2 token to connect. Toget all members of the team the operator queries a defined API endpoint thatreturns usernames. A minimal Teams API should work like this:

A 'fake' Teams API deployment is providedin the manifests folder to set up a basic API around whatever services is usedfor user management. The Teams API's URL is set in the operator'sconfigurationand enable_teams_api must be set to true. There are more settings availableto choose superusers, group roles, PAM configurationetc. An OAuth2 token can be passed to the Teams API via a secret. The name forthis secret is configurable with the oauth_token_secret_name parameter.

Additional teams and members per cluster

Postgres clusters are associated with one team by providing the teamID inthe manifest. Additional superuser teams can be configured as mentioned inthe previous paragraph. However, this is a global setting. To assignadditional teams, superuser teams and single users to clusters of a giventeam, use the PostgresTeam CRD.

Note, by default the PostgresTeam support is disabled in the configuration.Switch enable_postgres_team_crd flag to true and the operator will start towatch for this CRD. Make sure, the cluster role is up to date and contains asection for PostgresTeam.

Additional teams

To assign additional teams and single users to clusters of a given team,define a mapping with the PostgresTeam Kubernetes resource. The PostgresOperator will read such team mappings each time it syncs all Postgres clusters.

With the example above the operator will create login roles for all membersof b-team in every cluster owned by a-team. It's possible to do vice versafor clusters of b-team in one manifest:

You see, the PostgresTeam CRD is a global team mapping and independent fromthe Postgres manifests. It is possible to define multiple mappings, even withredundant content - the Postgres operator will create one internal cache fromit. Additional teams are resolved transitively, meaning you will also addusers for their additionalTeams, e.g.:

This creates roles for members of the c-team team not only in all clustersowned by a-team, but as well in cluster owned by b-team, as a-team isan additionalTeam to b-team

Not, you can also define additionalSuperuserTeams in the PostgresTeammanifest. By default, this option is disabled and must be configured withenable_postgres_team_crd_superusers to make it work.

Virtual teams

There can be 'virtual teams' that do not exist in the Teams API. It can makeit easier to map a group of teams to many other teams:

This example would create roles for members of c-team and d-team plusadditional virtual-team members in clusters owned by a-team or b-team.

Teams changing their names

With PostgresTeams it is also easy to cover team name changes. Just addthe mapping between old and new team name and the rest can stay the same.E.g. if team a-team's name would change to f-team in the teams API itcould be reflected in a PostgresTeam mapping with just two lines:

This is helpful, because Postgres cluster names are immutable and can notbe changed. Only via cloning it could get a different name starting with thenew teamID.

Additional members

Single members might be excluded from teams although they continue to workwith the same people. However, the teams API would not reflect this anymore.To still add a database role for former team members list their role underthe additionalMembers section of the PostgresTeam resource:

This will create the login role tia in every cluster owned by a-team.The user can connect to databases like the other team members.

The additionalMembers map can also be used to define users of virtualteams, e.g. for virtual-team we used above:

Removed members

The Postgres Operator does not delete database roles when users are removedfrom manifests. But, using the PostgresTeam custom resource or Teams API itis very easy to add roles to many clusters. Manually reverting such a changeis cumbersome. Therefore, if members are removed from a PostgresTeam or theTeams API the operator can rename roles appending a configured suffix to thename (see role_deletion_suffix option) and revoke the LOGIN privilege.The suffix makes it easy then for a cleanup script to remove those deprecatedroles completely. Switch enable_team_member_deprecation to true to enablethis behavior.

When a role is re-added to a PostgresTeam manifest (or to the source behindthe Teams API) the operator will check for roles with the configured suffixand if found, rename the role back to the original name and grant LOGINagain.

Prepared databases with roles and default privileges

The users section in the manifests only allows for creating database roleswith global privileges. Fine-grained data access control or role membership cannot be defined and must be set up by the user in the database. But, the PostgresOperator offers a separate section to specify preparedDatabases that will becreated with pre-defined owner, reader and writer roles for each individualdatabase and, optionally, for each database schema, too. preparedDatabasesalso enable users to specify PostgreSQL extensions that shall be created in agiven database schema.

Default database and schema

A prepared database is already created by adding an empty preparedDatabasessection to the manifest. The database will then be called like the Postgrescluster manifest (- are replaced with _) and will also contain a schemacalled data.

Default NOLOGIN roles

Given an example with a specified database and schema:

Postgres Operator will create the following NOLOGIN roles:

The <dbname>_owner role is the database owner and should be used when creatingnew database objects. All members of the admin role, e.g. teams API roles, canbecome the owner with the SET ROLE command. Default privilegesare configured for the owner role so that the <dbname>_reader roleautomatically gets read-access (SELECT) to new tables and sequences and the<dbname>_writer receives write-access (INSERT, UPDATE, DELETE on tables,USAGE and UPDATE on sequences). Both get USAGE on types and EXECUTE onfunctions.

The same principle applies for database schemas which are owned by the<dbname>_<schema>_owner role. <dbname>_<schema>_reader is read-only,<dbname>_<schema>_writer has write access and inherit reading from the readerrole. Note, that the <dbname>_* roles have access incl. default privileges onall schemas, too. If you don't need the dedicated schema roles - i.e. you onlyuse one schema - you can disable the creation like this:

Then, the schemas are owned by the database owner, too.

Default LOGIN roles

The roles described in the previous paragraph can be granted to LOGIN roles fromthe users section in the manifest. Optionally, the Postgres Operator can alsocreate default LOGIN roles for the database an each schema individually. Theseroles will get the _user suffix and they inherit all rights from their NOLOGINcounterparts. Therefore, you cannot have defaultRoles set to false and enabledefaultUsers at the same time.

These default users are enabled in the manifest with the defaultUsers flag:

Default access privileges are also defined for LOGIN roles on database andschema creation. This means they are currently not set when defaultUsers(or defaultRoles for schemas) are enabled at a later point in time.

Schema search_path for default roles

The schema search_pathfor each role will include the role name and the schemas, this role should haveaccess to. So foo_bar_writer does not have to schema-qualify tables fromschemas foo_bar_writer, bar, while foo_writer can look up foo_writer andany schema listed under schemas. To register the default public schema inthe search_path (because some extensions are installed there) one has to addthe following (assuming no extra roles are desired only for the public schema):

Database extensions

Prepared databases also allow for creating Postgres extensions. They will becreated by the database owner in the specified schema.

Some extensions require SUPERUSER rights on creation unless they are notwhitelisted by the pgextwlistextension, that is shipped with the Spilo image. To see which extensions areon the list check the extwlist.extension parameter in the postgresql.conffile.

Make sure that pgextlist is also listed under shared_preload_libraries inthe PostgreSQL configuration. Then the database owner should be able to createthe extension specified in the manifest.

From databases to preparedDatabases

If you wish to create the role setup described above for databases listed underthe databases key, you have to make sure that the owner role follows the<dbname>_owner naming convention of preparedDatabases. As roles are syncedfirst, this can be done with one edit:

Adding existing database schemas to the manifest to create roles for them aswell is up the user and not done by the operator. Remember that if you don'tspecify any schema a new database schema called data will be created. Wheneverything got synced (roles, schemas, extensions), you are free to remove thedatabase from the databases section. Note, that the operator does not deletedatabase objects or revoke privileges when removed from the manifest.

Resource definition

The compute resources to be used for the Postgres containers in the pods can bespecified in the postgresql cluster manifest.

Postgres Password File

The minimum limits to properly run the postgresql resource are configured to250m for cpu and 250Mi for memory. If a lower value is set in themanifest the operator will raise the limits to the configured minimum values.If no resources are defined in the manifest they will be obtained from theconfigured default requests.

Use taints, tolerations and node affinity for dedicated PostgreSQL nodes

To ensure Postgres pods are running on nodes without any other application pods,you can use taints and tolerationsand configure the required toleration in the manifest.

If you need the pods to be scheduled on specific nodes you may use node affinityto specify a set of label(s), of which a prospective host node must have at least one. This could be used toplace nodes with certain hardware capabilities (e.g. SSD drives) in certain environments or network segments,e.g. for PCI compliance.

In-place major version upgrade

Starting with Spilo 13, operator supports in-place major version upgrade to ahigher major version (e.g. from PG 10 to PG 12). To trigger the upgrade,simply increase the version in the manifest. It is your responsibility to testyour applications against the new version before the upgrade; downgrading isnot supported. The easiest way to do so is to try the upgrade on the clonedcluster first (see next chapter). More details can be found in theadmin docs.

How to clone an existing PostgreSQL cluster

You can spin up a new cluster as a clone of the existing one, using a clonesection in the spec. There are two options here:

• Clone from an S3 bucket (recommended)
• Clone directly from a source cluster

Note, that cloning can also be used for major version upgradesof PostgreSQL.

Clone from S3

Cloning from S3 has the advantage that there is no impact on your productiondatabase. A new Postgres cluster is created by restoring the data of anothersource cluster. If you create it in the same Kubernetes environment, use adifferent name.

Here cluster is a name of a source cluster that is going to be cloned. A newcluster will be cloned from S3, using the latest backup before the timestamp.Note, that a time zone is required for timestamp in the format of +00:00 whichis UTC. The uid field is also mandatory. The operator will use it to find acorrect key inside an S3 bucket. You can find this field in the metadata of thesource cluster:

For non AWS S3 following settings can be set to support cloning from other S3implementations:

Clone directly

Another way to get a fresh copy of your source DB cluster is viapg_basebackup. Touse this feature simply leave out the timestamp field from the clone section.The operator will connect to the service of the source cluster by name. If thecluster is called test, then the connection string will look like host=testport=5432), which means that you can clone only from clusters within the samenamespace.

Be aware that on a busy source database this can result in an elevated load!

Setting up a standby cluster

Standby cluster is a Patroni featurethat first clones a database, and keeps replicating changes afterwards. As thereplication is happening by the means of archived WAL files (stored on S3 orthe equivalent of other cloud providers), the standby cluster can exist in adifferent location than its source database. Unlike cloning, the PostgreSQLversion between source and target cluster has to be the same.

Forgot My Postgres Password

To start a cluster as standby, add the following standby section in the YAMLfile and specify the S3 bucket path. An empty path will result in an error andno statefulset will be created.

At the moment, the operator only allows to stream from the WAL archive of themaster. Thus, it is recommended to deploy standby clusters with only one pod.You can raise the instance count when detaching. Note, that the same pod rolelabels like for normal clusters are used: The standby leader is labeled asmaster.

Providing credentials of source cluster

A standby cluster is replicating the data (including users and passwords) fromthe source database and is read-only. The system and application users (likestandby, postgres etc.) all have a password that does not match the credentialsstored in secrets which are created by the operator. One solution is to createsecrets beforehand and paste in the credentials of the source cluster.Otherwise, you will see errors in the Postgres logs saying users cannot log inand the operator logs will complain about not being able to sync resources.

When you only run a standby leader, you can safely ignore this, as it will besorted out once the cluster is detached from the source. It is also harmless ifyou don’t plan it. But, when you created a standby replica, too, fix thecredentials right away. WAL files will pile up on the standby leader if noconnection can be established between standby replica(s). You can also edit thesecrets after their creation. Find them by:

Promote the standby

One big advantage of standby clusters is that they can be promoted to a properdatabase cluster. This means it will stop replicating changes from the source,and start accept writes itself. This mechanism makes it possible to movedatabases from one place to another with minimal downtime. Currently, theoperator does not support promoting a standby cluster. It has to be donemanually using patronictl edit-config inside the postgres container of thestandby leader pod. Remove the following lines from the YAML structure and theleader promotion happens immediately. Before doing so, make sure that thestandby is not behind the source database.

Finally, remove the standby section from the postgres cluster manifest.

Turn a normal cluster into a standby

There is no way to transform a non-standby cluster to a standby cluster throughthe operator. Adding the standby section to the manifest of a runningPostgres cluster will have no effect. But, as explained in the previousparagraph it can be done manually through patronictl edit-config. This time,by adding the standby_cluster section to the Patroni configuration. However,the transformed standby cluster will not be doing any streaming. It will be instandby mode and allow read-only transactions only.

Sidecar Support

Each cluster can specify arbitrary sidecars to run. These containers could beused for log aggregation, monitoring, backups or other tasks. A sidecar can bespecified like this:

In addition to any environment variables you specify, the following environmentvariables are always passed to sidecars:

• POD_NAME - field reference to metadata.name
• POD_NAMESPACE - field reference to metadata.namespace
• POSTGRES_USER - the superuser that can be used to connect to the database
• POSTGRES_PASSWORD - the password for the superuser

The PostgreSQL volume is shared with sidecars and is mounted at/home/postgres/pgdata.

Note: The operator will not create a cluster if sidecar containers arespecified but globally disabled in the configuration. The enable_sidecarsoption must be set to true.

If you want to add a sidecar to every cluster managed by the operator, you can specify it in the operator configuration instead.

InitContainers Support

Each cluster can specify arbitrary init containers to run. These containers canbe used to run custom actions before any normal and sidecar containers start.An init container can be specified like this:

initContainers accepts full v1.Container definition.

Note: The operator will not create a cluster if initContainers arespecified but globally disabled in the configuration. Theenable_init_containers option must be set to true.

Increase volume size

Postgres operator supports statefulset volume resize if you're using theoperator on top of AWS. For that you need to change the size field of thevolume description in the cluster manifest and apply the change:

The operator compares the new value of the size field with the previous one andacts on differences.

You can only enlarge the volume with the process described above, shrinking isnot supported and will emit a warning. After this update all the new volumes inthe statefulset are allocated according to the new size. To enlarge persistentvolumes attached to the running pods, the operator performs the followingactions:

• call AWS API to change the volume size

• connect to pod using kubectl exec and resize filesystem with resize2fs.

Fist step has a limitation, AWS rate-limits this operation to no more than onceevery 6 hours. Note, that if the statefulset is scaled down before resizing thenew size is only applied to the volumes attached to the running pods. Thesize of volumes that correspond to the previously running pods is not changed.

Logical backups

You can enable logical backups (SQL dumps) from the cluster manifest by addingthe following parameter in the spec section:

The operator will create and sync a K8s cron job to do periodic logical backupsof this particular Postgres cluster. Due to the limitation of K8s cron jobsit is highly advisable to set up additional monitoring for this feature; suchmonitoring is outside the scope of operator responsibilities. Seeconfiguration reference andadministrator documentation for details on how backups areexecuted.

Connection pooler

The operator can create a database side connection pooler for those applicationswhere an application side pooler is not feasible, but a number of connections ishigh. To create a connection pooler together with a database, modify themanifest:

This will tell the operator to create a connection pooler with defaultconfiguration, through which one can access the master via a separate service{cluster-name}-pooler. With the first option, connection pooler for master serviceis created and with the second option, connection pooler for replica is created.Note that both of these flags are independent of each other and user can set orunset any of them as per their requirements without any effect on the other.

In most of the cases thedefault configurationshould be good enough. To configure a new connection pooler individually foreach Postgres cluster, specify:

The enableConnectionPooler flag is not required when the connectionPoolersection is present in the manifest. But, it can be used to disable/remove thepooler while keeping its configuration.

Postgres Password On Command Line

By default, PgBouncer is used as connection pooler.To find out about pool modes read the PgBouncerdocs(but it should be the general approach between different implementation).

Note, that using PgBouncer a meaningful resource CPU limit should be 1 coreor less (there is a way to utilize more than one, but in K8s it's easier just tospin up more instances).

Custom TLS certificates

By default, the Spilo image generates its own TLS certificate during startup.However, this certificate cannot be verified and thus doesn't protect fromactive MITM attacks. In this section we show how to specify a custom TLScertificate which is mounted in the database pods via a K8s Secret.

Before applying these changes, in k8s the operator must also be configured withthe spilo_fsgroup set to the GID matching the postgres user group. If youdon't know the value, use 103 which is the GID from the default Spilo image(spilo_fsgroup=103 in the cluster request spec).

Postgres Password Not Working

OpenShift allocates the users and groups dynamically (based on scc), and theirrange is different in every namespace. Due to this dynamic behaviour, it's nottrivial to know at deploy time the uid/gid of the user in the cluster.Therefore, instead of using a global spilo_fsgroup setting, use thespiloFSGroup field per Postgres cluster.

Upload the cert as a kubernetes secret:

When doing client auth, CA can come optionally from the same secret:

Then configure the postgres resource with the TLS secret:

Optionally, the CA can be provided by a different secret:

Then configure the postgres resource with the TLS secret:

Alternatively, it is also possible to usecert-manager to generate these secrets.

Certificate rotation is handled in the Spilo image which checks every 5minutes if the certificates have changed and reloads postgres accordingly.

The file .pgpass in a user's home directory or the file referenced by PGPASSFILE can contain passwords to be used if the connection requires a password (and no password has been specified otherwise). On Microsoft Windows the file is named %APPDATA%postgresqlpgpass.conf (where %APPDATA% refers to the Application Data subdirectory in the user's profile).

This file should contain lines of the following format:

(You can add a reminder comment to the file by copying the line above and preceding it with #.) Each of the first four fields can be a literal value, or *, which matches anything. The password field from the first line that matches the current connection parameters will be used. (Therefore, put more-specific entries first when you are using wildcards.) If an entry needs to contain : or , escape this character with . A host name of localhost matches both TCP (host name localhost) and Unix domain socket (pghost empty or the default socket directory) connections coming from the local machine. In a standby server, a database name of replication matches streaming replication connections made to the master server. The database field is of limited usefulness because users have the same password for all databases in the same cluster.

On Unix systems, the permissions on .pgpass must disallow any access to world or group; achieve this by the command chmod 0600 ~/.pgpass. If the permissions are less strict than this, the file will be ignored. On Microsoft Windows, it is assumed that the file is stored in a directory that is secure, so no special permissions check is made.