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
• Make our database accessible to Otterize Cloud
• Connect our cluster and database to Otterize Cloud
• 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. ngrok

We will be using it to create a proxy to connect our locally running database to Otterize Cloud, for the tutorial's purposes. Once you have a ngrok account, you’ll want to install it in your terminal using the instructions found here: ngrok install

Tutorial

Deploy tutorial services and request database credentials

This will set up the namespace we will use for our tutorial and deploy the client, server, and database.

Our server's Deployment spec will specify an annotation on the Pod, which requests that the credentials operator will provision a username and password for the server.

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

This specifies that the secret server-creds will have keys with the username and password to connect to the database. The secret will only be created once the database is integrated with Otterize Cloud.

kubectl create namespace otterize-tutorial-postgres
kubectl apply -n otterize-tutorial-postgres -f https://docs.otterize.com/code-examples/postgres/client-server-database.yaml

Make the database accessible to Otterize Cloud

We need to allow Otterize Cloud to access the database server so Otterize Cloud can configure on-demand credentials for our server’s access. This tutorial will expose our database port to our local environment and then proxy it to Otterize Cloud using ngrok. We will need both of these processes up and running during the rest of this tutorial.

In a new terminal window, run the following command to forward our database port from our cluster into your local environment:

kubectl port-forward svc/database 5432:5432 -n otterize-tutorial-postgres

Now that your database port is accessible to your local environment, we are using ngrok to make that available to Otterize Cloud. For production uses, this can be done through firewall configurations.

In a new terminal window, run:

ngrok tcp 5432

Once ngrok is running, make note of the Forwarding host and port. Will need this for our next step.

Integrate the database to Otterize Cloud

To add the database, we head over to the Integrations page

1. Click Add Integration
2. Select Integration Type: Database
3. Provide a name for the integration: otterize-tutorial-postgres
4. Leave the database type set to PostgreSQL
5. Copy your Forwarding host and port from ngrok in the Address Field. This will look something like 0.tcp.us-cal-1.ngrok.io:14192. Be sure to remove the tcp:// portion of the URL.
6. Username: otterize-tutorial, Password: jeffdog523
7. Note this is a superuser, which allows Otterize to create unique credentials for each service. For production, it is recommended to create a privileged user for Otterize’s exclusive use. This user should have the necessary permissions to GRANT access to any databases and tables you want it to manage.
8. Hit Test Connection, and you should see an “OK” status.
9. Hit the Add button to complete the integration

Integrate the cluster to Otterize Cloud

Create a Kubernetes integration on the Integrations page, and follow the instructions.

In the second step, after providing an integration name and environment, choose:

1. mTLS and Kafka Support: None
2. Enforcement mode: Enabled.
3. Copy and run the Helm upgrade command.
4. You should see the Connection status change.

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:

pq: password authentication failed for user "svc_9cigb2qemv_otterize_tutorial_postgres_server"

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 service the ability to insert 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: otterize-tutorial-postgres # Same name as our integration
      type: database
      databaseResources:
        - databaseName: otterize-tutorial
          table: public.example
          operations:
            - SELECT
            - INSERT

We can now apply our intents. Behind the scenes, Otterize Cloud runs CREATE USER and GRANT queries on the database, making our SELECT and INSERT errors disappear.

kubectl apply -f intents.yaml

Example log:

Successfully INSERTED into our table Successfully SELECTED, most recent value: 2024-01-22T18:48:43Z

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 namespace otterize-tutorial-postgres

End the ngrok and port forwarding processes by closing the terminal windows or Ctrl-C the processes.