Portal database

Set up a backing database for your portal.

This guide must be completed by a Portal admin.

About the database for Portal

To use the portal, you must configure a relational database management system (RDBMS) as backing storage for the portal server.

Types of data

The types of data that the portal server reads and writes include the following:

The teams, apps, and subscriptions that you create in the frontend portal.
The OIDC and API key authentication details that you create for APIs in the frontend portal. Note that client secrets are not stored in the database and can only be viewed once upon initial creation.
ApiProduct information such as versions and claim groups.

Database options

Review the following options to store data for your portal web server.

Default in-memory: By default, the portal server includes a SQLite in-memory database that is automatically set up when you deploy the server. Any time that you upgrade or restart the portal server, the information in this database is removed. For example, your Teams, Apps, credentials, and other information that you created through the frontend portal get deleted. Use this method only for quick testing and temporary development environments.
Postgres (preferred): Solo Enterprise for kgateway supports setting up Postgres as the RDBMS. The Postgres instance can be a deployment in the same cluster as the portal web server or in an external instance. If you use an external instance, make sure that your cluster can connect to the instance. For example, you might need to create the instance in the same virtual private cloud (VCP) and configure firewall, security groups, and other network and access control features.

Step 1: Deploy Postgres

Deploy a Postgres instance to the same cluster as the portal web server. The following steps also include an admin UI to help you visualize the data that is stored in the Postgres instance.

Create a Postgres deployment in the same namespace as the portal server. The following example creates a Postgres database named db with credentials of user and pass.

kubectl apply -f - <<EOF
apiVersion: apps/v1
kind: Deployment
metadata:
  name: postgres
  namespace: default
spec:
  replicas: 1
  selector:
    matchLabels:
      app: postgres
  template:
    metadata:
      labels:
        app: postgres
    spec:
      containers:
        - name: postgres
          image: postgres:13
          ports:
            - containerPort: 5432
          env:
            - name: POSTGRES_DB
              value: "db"
            - name: POSTGRES_USER
              value: "user"
            - name: POSTGRES_PASSWORD
              value: "pass"
          volumeMounts:
            - name: postgres-storage
              mountPath: /var/lib/postgresql/data
      volumes:
        - name: postgres-storage
          emptyDir: {}
---
apiVersion: v1
kind: Service
metadata:
  name: postgres
  namespace: default
spec:
  ports:
    - port: 5432
      targetPort: 5432
  selector:
    app: postgres   
EOF

Check that Postgres is successfully deployed.

kubectl rollout status deploy/postgres

Example output:

deployment "postgres" successfully rolled out

Optionally create an admin UI for Postgres. The following example creates a user@email.com admin user with the pass password, but you can update these values. If you updated the user credentials for the Postgres deployment in the previous step, make sure to update the same values in the ConfigMap.

kubectl apply -f - <<EOF
apiVersion: apps/v1
kind: Deployment
metadata:
  name: pgadmin
  namespace: default
spec:
  replicas: 1
  selector:
    matchLabels:
      app: pgadmin
  template:
    metadata:
      labels:
        app: pgadmin
    spec:
      containers:
        - name: pgadmin
          image: dpage/pgadmin4:latest
          ports:
            - containerPort: 80
          env:
            - name: PGADMIN_DEFAULT_EMAIL
              value: "user@email.com"
            - name: PGADMIN_DEFAULT_PASSWORD
              value: "pass"
          volumeMounts:
            - name: pgadmin-config-volume
              mountPath: /pgadmin4/servers.json
              subPath: servers.json
      volumes:
        - name: pgadmin-config-volume
          configMap:
            name: pgadmin-config
---
apiVersion: v1
kind: Service
metadata:
  name: pgadmin
  namespace: default
spec:
  type: ClusterIP
  ports:
    - port: 30002
      targetPort: 80
  selector:
    app: pgadmin
---
apiVersion: v1
kind: ConfigMap
metadata:
  name: pgadmin-config
  namespace: default
data:
  servers.json: |
    {
      "Servers": {
        "1": {
          "Name": "Portal DB",
          "Group": "Servers",
          "Host": "postgres.default.svc.cluster.local",
          "Port": 5432,
          "MaintenanceDB": "db",
          "Username": "user",
          "Password": "pass",
          "SSLMode": "disable",
          "Comment": "Automatically added server"
        }
      }
    }   
EOF

Check that the Postgres admin UI is successfully deployed.

kubectl rollout status deploy/pgadmin

Example output:

deployment "pgadmin" successfully rolled out

Step 2: Configure Portal to use Postgres

Now that Postgres is running, configure the portal server to store data in the Postgres instance.

Create a Kubernetes secret with the Postgres connection details. If you updated any of the values in the previous step such as the user credentials, update them in this command accordingly.

kubectl apply -f - <<EOF
apiVersion: v1
kind: Secret
metadata:
  name: portal-postgres-secret
  namespace: default
type: Opaque
stringData:
  host: "postgres.default.svc.cluster.local"
  port: "5432"
  username: "user"
  password: "pass"
  database: "db"
  sslmode: "disable"
EOF

Create a PortalParameters resource that references the secret that you created.

kubectl apply -f- <<EOF
apiVersion: portal.solo.io/v1alpha1
kind: PortalParameters
metadata:
  name: portal-params
  namespace: default
spec:
  store:
    postgres:
      secretRef:
        name: portal-postgres-secret
EOF

If you have not created a Portal yet, create one.

kubectl apply -f- <<EOF
apiVersion: portal.solo.io/v1alpha1
kind: Portal
metadata:
  name: my-portal
  namespace: default
spec:
  parametersRef:
    name: portal-params
  visibility:
   public: true
   apiProductRefs: []
EOF

Verify that the portal web server is up and running.
```
kubectl get pods | grep my-portal
```

Step 3: Verify that Portal stores data in Postgres

The more you use the portal web server, the more data is stored in Postgres. If you are setting up the portal for the first time, you might not have any data right away. However, you can come back to this topic after you generate data, such as by creating teams or deploying the frontend app.

Trigger the writing of data by following the guide to create Teams, apps, and subscriptions.

Enable port-forwarding for the Postgres admin UI.

kubectl port-forward svc/pgadmin -n default 8081:30002

In your browser, open the Postgres admin UI and log in with the user@email.com and pass credentials.
```
open localhost:8081
```
From the Object Explorer menu, expand Servers and click Portal DB. When prompted, enter the password for the user you configured when you deployed Postgres: pass.
If you get stuck in a login loop with an Errno 2 message, restart the pgadmin pod and try again.
From the Object Explorer menu’s Portal DB server section, expand Databases > db > Schemas > public > Tables.
Figure: Postgres Portal DB
Figure: Postgres Portal DB
To check the values in a table, right-click the table name and then click View/Edit Data > All Rows. The individual values are shown in the Data Output table.
Figure: Postgres data view
Figure: Postgres data view