Postgres¶

You can provision and configure Postgres through nais.yaml.

The database is provisioned into the teams own project in GCP. Here the team has full access to view logs, create and restore backups and other administrative database tasks.

Warning

If you change the postgreSQL version your data will be lost, as a new database will be created. In other words to upgrade the database version you will need to do a migration as described here: Upgrade GCP postgreSQL

When you deploy your application with database config, NAIS will ensure the database exists in a Google Cloud SQL instance with the specified Postgres version, and configure the application with means to connect to it.

Info

This feature is only available in GCP clusters. If you need on-prem databases, head over to navikt/database-iac.

Below is an example of the minimal configuration needed. See all configuration options in the nais.yaml reference.

...
kind: Application
metadata:
  name: myapp
spec:
  gcp:
    sqlInstances:
      - type: POSTGRES_12
        databases:
          - name: mydb

Configuration¶

To connect your application to the database, use information from the environment variables below.

The prefix NAIS_DATABASE_MYAPP_MYDB is automatically generated from the instance name myapp (defaults to application name) and mydb (from database spec). You can customize these environment variable names by setting .spec.gcp.sqlInstances[].databases[].envVarPrefix. For instance, setting this to DB will give you DB_HOST, DB_USERNAME, etc. Note that changing or adding envVarPrefix requires you to manually delete the google-sql-<MYAPP> secret and SQLUser with the same name as the application, see below.

key	environment variable	default
hostname	`NAIS_DATABASE_MYAPP_MYDB_HOST`	127.0.0.1
port	`NAIS_DATABASE_MYAPP_MYDB_PORT`	5432
database name	`NAIS_DATABASE_MYAPP_MYDB_DATABASE`	`.spec.gcp.sqlInstances[].databases[].name`
database user	`NAIS_DATABASE_MYAPP_MYDB_USERNAME`	`.spec.gcp.sqlInstances[].name`
database password	`NAIS_DATABASE_MYAPP_MYDB_PASSWORD`	(randomly generated)
database url with credentials	`NAIS_DATABASE_MYAPP_MYDB_URL`	`postgres://username:password@127.0.0.1:5432/mydb`

Info

The application is the only application that can access the database instance. Other applications can not connect. It is not, for instance, possible to have two applications (e.g. producer and consumer) connecting directly to the database.

Info

Note that if you change your application name, database name or envVarPrefix, and then change it later, you have to manually reset database credentials.

Query Insights¶

Query insights are now enabled by default in GCP. This feature provides query overview and analysis. The data is available in the Google cloud console.

For further reading see Google Cloud SQL Query Insights

Info

Data is available for seven days, increasing this will incur extra cost.

Maintenance window¶

Google will automatically perform upgrades, fix bugs and apply security patches to prevent exploits. Your application should be able to handle occasional downtime as this maintenance is performed. Read more on maintenance windows here. NAIS does not configure the maintenance window, but this can be set up in the application spec: nais.yaml. If you wish to be notified about upcoming maintenance, you can opt-in for this on the Communications page in the GCP console.

Sizing your database¶

By default, the database server has 1 vCPU, 614 MB RAM and 10GB of SSD storage with no automatic storage increase. If you need to change the defaults you can do this in nais.yaml.

Automated backup¶

The database is backed up nightly at 3 AM (GMT+1) by default, but can be overridden in nais.yaml by setting spec.gcp.sqlInstances[].autoBackupTime. By default, seven backups will be kept. More info here.

The backups can be found in the Google Cloud SQL instance dashboard.

Point-in-time recovery¶

Point-in-time recovery can be enabled by configuring this in the sql instance for your application spec. This feature allows you to recover your database to a specific point in time.

Info

This feature is not enabled by default. When enabled the Postgres instance will be restarted.

Warning

Use this feature with automatic storage increase enabled.

See application spec reference here For further reading see google Cloud SQL PIT recovery

Disaster backup¶

In case of catastrophic failure in GCP we are running a daily complete backup of the postgresql databases in GCP to an on-prem location. This backup currently runs at 5 am. This is in addition to the regular backups in GCP.

Cloud SQL credentials¶

Cloud SQL uses ConfigConnector/CNRM to create and manage all relevant resources (sqldatabase, sqlinstance, sqluser, credentials) for postgreSQL. When creating an application via your nais.yaml the database in your google project, along with other necessary resources, are created. The creation of the database takes about ten minutes, and the credential settings will be updated after the database is ready for use.

Warning

If you delete and recreate your app new credentials will be created and a synchronization is needed. This process can take up to ten minutes. Using the workaround described below you can avoid this synchronization period.

Workaround for password synchronization issues¶

Retrieve the password from the secret google-sql- in your namespace (the password is base64 encoded).

kubectl get secret google-sql-<MYAPP> -o jsonpath="{ .data['<YOUR PASSWORD VARIABLE>'] }" | base64 -d

Through gcloud-cli¶

Give yourself the role of roles/cloudsql.admin (which includes the needed permission cloudsql.users.update).

gcloud projects add-iam-policy-binding <PROJECT_ID> \
    --member=user:<FIRSTNAME>.<LASTNAME>@nav.no \
    --role=roles/cloudsql.admin \
    --condition="expression=request.time < timestamp('$(date -v '+1H' -u +'%Y-%m-%dT%H:%M:%SZ')'),title=temp_access"

Then you can set the new password with the following command.

gcloud sql users set-password <USERNAME> --instance <DB_INSTANCE> --prompt-for-password

Through console.cloud.google.com¶

Log in to the Google Cloud Console and set the password manually for the application user in the sql instance: SQL -> -> Users -> -> Change password

Reset database credentials¶

To reset the database credentials for your application (if application name, database name or envVarPrefix has been changed):

$ kubectl delete secret google-sql-<MYAPP>
$ kubectl delete sqluser <MYAPP>

Cloud SQL Proxy¶

The application will connect to the database using Cloud SQL Proxy, ensuring that the database communication happens in secure tunnel, authenticated with automatically rotated credentials.

NAIS will add and configure the proxy client container as a sidecar in the pod, making it available on localhost for the application. The application will then connect to the proxy using standard database protocol just as if it was the actual database.

sqlproxy

For more detailed information, check out the Cloud SQL Proxy documentation

Additional user(s) database(s)¶

You can add users to your database by setting database configuration option: .spec.gcp.sqlInstances[].databases[].users[].name. Additional users needs to manually be given access to the database and table. Either directly or with Flyway or other database migration tools.

Names added must match regex: ^[_a-zA-Z][_a-zA-Z0-9]+$. Secrets is generated and mounted for each user.

With .spec.gcp.sqlInstances[].databases[].envVarPrefix set to DB and additional username to _user2 you will get environment variables in format DB_USER2_MYDB_USERNAME etc.

Details about environment variables is specified her: configuration

Info

If you've deployed your application with an additional users, and then change name or remove the user from configuration, you need to manually delete the google-sql-<MYAPP>-<USER> secret:

$ kubectl delete secret google-sql-<MYAPP>-<USER>

Personal database access¶

Databases should always be accessed using a personal account, and the access should ideally be temporary.

Prerequisites¶

Step 1. Install local binaries

This guide assumes that you have the following installed on your local machine:

Step 2. Allow your user to edit Cloud SQL resources for your project

Ensure that you have authenticated gcloud by running

gcloud auth login

To be able to perform the gcloud commands mentioned below you need a role with user edit permissions, e.g. roles/cloudsql.admin

To grant yourself this role for a given project, run the following command:

gcloud projects add-iam-policy-binding <project-id> \
    --member user:<your-email> \
    --role roles/cloudsql.admin \
    --condition="expression=request.time < timestamp('$(date -v '+1H' -u +'%Y-%m-%dT%H:%M:%SZ')'),title=temp_access"

where <project-id> can be found by running:

gcloud projects list \
    --filter=<team>

Step 3. One-time setup of privileges to SQL IAM users

This is only required once per database instance and should be done before DDL scripts are run in the database in order to ensure the objects have the right permissions.

Once the database instance is created, we need to grant the IAM users access to the public schema.

This can either be done by using the default application database user during database creation/migration with scripts (e.g. Flyway), or as a one-time setup by using the default postgres user.

Step 3a. Set password for postgres user

In order to use the postgres user, you have to set a password first:

gcloud sql users set-password postgres \
    --instance=<INSTANCE_NAME> \
    --prompt-for-password \
    --project <PROJECT_ID>

Step 3b. Log in to the database with the postgres user

Set up the cloudsql-proxy:

CONNECTION_NAME=$(gcloud sql instances describe <INSTANCE_NAME> \
    --format="get(connectionName)" \
    --project <PROJECT_ID>);

cloud_sql_proxy -instances=${CONNECTION_NAME}=tcp:5432

Log in to the database (you will be prompted for the password you set in the previous step):

psql -U postgres -h localhost <DATABASE_NAME> -W

If you are using Cloud SQL Auth proxy v1.21.0 or newer you can get the token in the cloud_sql_proxy command so you can run the psql-command without the -W parameter:

cloud_sql_proxy -enable_iam_login -instances=${CONNECTION_NAME}=tcp:5432

Step 3c. Enable privileges for user(s) in database

This can be enabled for all cloudsqliamusers (all IAM users are assigned the role cloudsqliamuser) with the following command:

alter default privileges in schema public grant all on tables to cloudsqliamuser;

Or for a specific user (the given IAM user must exist in the database):

alter default privileges in schema public grant all on tables to 'user@nav.no';

If your application created the tables before you were able to run these commands, then the owner of the tables is set to the application's user.

Thus, your application must run the following command either through your chosen database migration tool (e.g. Flyway) or manually with the application user's credentials:

grant all on all tables in schema public to cloudsqliamuser;

Granting temporary personal access¶

Step 1. Create database IAM user

This is required once per user and requires that you have create user permission in IAM in your project, e.g. Cloud SQL Admin.

To grant yourself this role for a given project, run the following command:

gcloud projects add-iam-policy-binding <project-id> \
    --member user:<your-email> \
    --role roles/cloudsql.admin \
    --condition="expression=request.time < timestamp('$(date -v '+1H' -u +'%Y-%m-%dT%H:%M:%SZ')'),title=temp_access"

Then, to create the database IAM user:

gcloud beta sql users create <FIRSTNAME>.<LASTNAME>@nav.no \
    --instance=<INSTANCE_NAME> \
    --type=cloud_iam_user \
    --project <PROJECT_ID>

Step 2. Create a temporary IAM binding for 1 hour

Generally, you should try to keep your personal database access time-limited.

The following command grants your user permission to log into the database for 1 hour.

If your system has GNU utilities installed:

gcloud projects add-iam-policy-binding <PROJECT_ID> \
    --member=user:<FIRSTNAME>.<LASTNAME>@nav.no \
    --role=roles/cloudsql.instanceUser \
    --condition="expression=request.time < timestamp('$(date --iso-8601=seconds -d '+1 hours')'),title=temp_access"

Otherwise (e.g. MacOS users):

gcloud projects add-iam-policy-binding <PROJECT_ID> \
    --member=user:<FIRSTNAME>.<LASTNAME>@nav.no \
    --role=roles/cloudsql.instanceUser \
    --condition="expression=request.time < timestamp('$(date -v '+1H' -u +'%Y-%m-%dT%H:%M:%SZ')'),title=temp_access"

Step 3. Log in with personal user

Ensure that the cloudsql-proxy is up and running, if not then:

CONNECTION_NAME=$(gcloud sql instances describe <INSTANCE_NAME> \
  --format="get(connectionName)" \
  --project <PROJECT_ID>);

cloud_sql_proxy -instances=${CONNECTION_NAME}=tcp:5432

Then, connect to the database:

export PGPASSWORD=$(gcloud auth print-access-token)

psql -U <FIRSTNAME>.<LASTNAME>@nav.no -h localhost <DATABASE_NAME>

Deleting the database¶

The database is not automatically removed when deleting your NAIS application. Remove unused databases to avoid incurring unnecessary costs. This is done by setting cascadingDelete in your nais.yaml-specification.

Danger

When you delete an Cloud SQL instance, you cannot reuse the name of the deleted instance until one week from the deletion date.

Debugging¶

Check the events on the Config Connector resources

$ kubectl describe sqlinstance <myapp>
$ kubectl describe sqldatabase <mydb>
$ kubectl describe sqluser <myapp>

Check the logs of the Cloud SQL Proxy

$ kubectl logs <pod> -c cloudsql-proxy

Example with all configuration options¶

See full example.