Skip to main content

Just-in-time PostgreSQL access

Overview

This tutorial will deploy an example cluster to highlight Otterize's PostgreSQL capabilities. Within that cluster is a client service that hits an endpoint on a server, which then connects to a database. The server runs two different database operations:

  1. An INSERT operation to append a table within the database
  2. A SELECT operation to validate the updates.

The server needs appropriate permissions to access the database. You could use one admin user for all services, which is insecure and is the cause for many security breaches. With Otterize, you can specify required access, and have Otterize create users and perform correctly scoped SQL GRANTs just in time, as the service spins up and down.

In this tutorial, we will:

  • Deploy an example cluster
  • Deploy Otterize in our cluster and give it access to our database instance
  • Declare a ClientIntents resource for the server, specifying required access
  • See that the required access has been granted

Prerequisites

1. Minikube Cluster​

Prepare a Kubernetes cluster with Minikube

For this tutorial you'll need a local Kubernetes cluster. Having a cluster with a CNI that supports NetworkPolicies isn't required for this tutorial, but is recommended so that your cluster works with other tutorials.

If you don't have the Minikube CLI, first install it.

Then start your Minikube cluster with Calico, in order to enforce network policies.

minikube start --cpus=4 --memory 4096 --disk-size 32g --cni=calico

2. Deploy Otterize​

To deploy Otterize, head over to Otterize Cloud and associate a Kubernetes cluster on the Integrations page, and follow the instructions. If you already have a Kubernetes cluster connected, skip this step.

Tutorial

Deploy tutorial services and request database credentials​

Next, set up the namespace used for our tutorial and deploy the client, server & database services in it:

kubectl create namespace otterize-tutorial-postgres
kubectl apply -n otterize-tutorial-postgres -f https://docs.otterize.com/code-examples/postgres/client-server-database.yaml
Expand to see the deployment YAML
apiVersion: apps/v1
kind: Deployment
metadata:
  name: server
spec:
  replicas: 1
  selector:
    matchLabels:
      app: server
  template:
    metadata:
      annotations:
        credentials-operator.otterize.com/user-password-secret-name: server-creds
      labels:
        app: server
    spec:
      serviceAccountName: server
      containers:
        - name: server
          imagePullPolicy: Always
          image: 'otterize/postgres-tutorial-server'
          ports:
            - containerPort: 80
          env:
            - name: DB_SERVER_USER
              valueFrom:
                secretKeyRef:
                  name: server-creds
                  key: username
            - name: DB_SERVER_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: server-creds
                  key: password
---
apiVersion: v1
kind: Service
metadata:
  name: server
spec:
  type: ClusterIP
  selector:
    app: server
  ports:
    - name: http
      port: 80
      targetPort: 80
---
apiVersion: v1
kind: ServiceAccount
metadata:
  name: server
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: client
spec:
  replicas: 1
  selector:
    matchLabels:
      app: client
  template:
    metadata:
      labels:
        app: client
    spec:
      containers:
        - name: client
          imagePullPolicy: Always
          image: 'otterize/postgres-tutorial-client'
          ports:
            - containerPort: 80
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: database
spec:
  replicas: 1
  selector:
    matchLabels:
      app: database
  template:
    metadata:
      labels:
        app: database
    spec:
      containers:
        - name: database
          imagePullPolicy: Always
          image: 'otterize/postgres-tutorial-database'
          ports:
            - containerPort: 5432
---
apiVersion: v1
kind: Service
metadata:
  name: database
spec:
  selector:
    app: database
  ports:
    - protocol: TCP
      port: 5432
      targetPort: 5432

Our server's Deployment spec specifies an annotation on its Pod, which requests that the Otterize operator provision a username and password for it:

  template:
    metadata:
      annotations:
        credentials-operator.otterize.com/user-password-secret-name: server-creds

This specifies that the secret server-creds will be populated with keys containing the username and password used by this pod to connect to the database. The secret will only be created by the Otterize operator after it is integrated with your database by applying a PostgreSQLServerConfig resources.

View logs for the server​

After the client, server, and database are up and running, we can see that the server does not have the appropriate access to the database by inspecting the logs with the following command.

kubectl logs -f -n otterize-tutorial-postgres deploy/server

Example log:

Unable to perform INSERT operation
Unable to perform SELECT operation

Create an Otterize database integration and deploy a PostgreSQLServerConfig to allow Otterize DB access​

To create a database integration, head over to the Otterize Cloud and navigate to the Integrations page. Click on the "Add Integration" button and select the "Database" option. Fill in the required fields and click "Create" to create your integration.

Next, apply a PostgreSQLServerConfig so Otterize will know how to access our database instance:

  • Create a Kuberentes secret containing the database credentials:

    kubectl create secret generic postgres-tutorial-db-credentials -n otterize-tutorial-postgres --from-literal=username='otterize-tutorial' --from-literal=password='jeffdog523'

    In this tutorial, the PostgreSQL database comes with the predefined username & password, but for future uses a role will have to be created in the database to grant Otterize access as well as the ability to configure other users.

  • Apply the PostgreSQLServerConfig to the cluster:

    kubectl apply -n otterize-tutorial-postgres -f https://docs.otterize.com/code-examples/postgres/postgresqlserverconfig.yaml

This PostgreSQLServerConfig tells Otterize how to access a database instance named postgres-tutorial-db, meaning that when intents are applied requesting access permissions to postgres-tutorial-db, the Otterize operator will be able to configure them:

apiVersion: k8s.otterize.com/v1alpha3
kind: PostgreSQLServerConfig
metadata:
  name: postgres-tutorial-db
spec:
  address: database.otterize-tutorial-postgres.svc.cluster.local:5432
  credentials:
    secretRef:
      name: postgres-tutorial-db-credentials

Define your ClientIntents​

ClientIntents are Otterize’s way of defining access through unique relationships, which lead to perfectly scoped access. In this example, we provide our server workload the ability to insert and select records to allow it to access the database.

Below is our intents.yaml file. As you can see, it is scoped to our database named otterize-tutorial and our public.example table. We also have limited the access to just SELECT and INSERT operations. We could add more databases, tables, or operations if our service required more access.

Specifying the table and operations is optional. If you don't specify the table, access will be granted to all tables in the specified database. If you don't specify the operations, all operations will be allowed.

apiVersion: k8s.otterize.com/v1alpha3
kind: ClientIntents
metadata:
  name: client-intents-for-server
  namespace: otterize-tutorial-postgres
spec:
  service:
    name: server
  calls:
    - name: postgres-tutorial-db # Same name as our PostgreSQLServerConfig metadata.name
      type: database
      databaseResources:
        - databaseName: otterize-tutorial
          table: public.example
          operations:
            - SELECT
            - INSERT

We can now apply our intents. Behind the scenes, the Otterize operator created the user for our server workload and executed GRANT queries on the database, making our SELECT and INSERT errors disappear.

kubectl apply -n otterize-tutorial-postgres -f https://docs.otterize.com/code-examples/postgres/clientintents.yaml

View logs for the server​

We can now view the server logs once again. This time, we should see that the server has the appropriate access to the database:

kubectl logs -f -n otterize-tutorial-postgres deploy/server

Example log:

Successfully INSERTED into our table

Successfully SELECTED, most recent value: 2024-04-30T13:20:46Z

That’s it! If your service’s functionality changes, adding or removing access is as simple as updating your ClientIntents definitions. For fun, try altering the operations to just SELECT or INSERT.

Teardown

To remove the deployed examples, run:

kubectl delete clientintents.k8s.otterize.com -n otterize-tutorial-postgres client-intents-for-server && \
kubectl delete namespace otterize-tutorial-postgres