Database as a Service: Managed PostgreSQL Onboarding

Overview

This guide walks through the process of creating a new managed PostgreSQL database instance for a user, including user creation and initial setup.

Prerequisites

  • Administrative access to the target cluster, specifically command line and k9s

Steps

1. Connect to Kubernetes Cluster

Obtain the hostname/IP of the target Kubernetes cluster or appropriate jump server, and switch local kubectl context as needed.

2. Launch K9s CLI Tool

  1. Run the following command.

k9s

  1. Navigate to the appropriate namespace containing the PostgreSQL cluster as needed.

3. Open a second terminal (recommended)

Follow steps 1 and 2 again. You will need to reference randomly generated credentials, and copy/paste is easier.

4. Get the external postgres cluster superuser credentials

  1. In k9s, switch to secrets

  2. Scroll/search for df-pg-external-superuser, and press x to decode.

5. Connect to PostgreSQL Cluster

  1. In the second terminal - with k9s, go to services

  2. Shell into df-pg-external-rw

  3. Run the following command, replace the placeholder value with the username from the secret.

psql -h df-pg-external-rw -U postgres -d postgres

  1. When prompted, enter the password from the secret.

6. Generate random initial password for new user

Still in the psql cli session, run the following command.

SELECT string_agg(substr('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789', (random() * 61)::int + 1, 1), '') AS secure_password
FROM generate_series(1, 24);

7. Create New User Role

  1. Run the following command. Replace <username> with the designated username (e.g., vendor_a), and the password with the previously generated string.

CREATE ROLE <username> WITH
    LOGIN
    PASSWORD '<generated_secure_password>'
    NOSUPERUSER
    NOCREATEDB
    INHERIT;

8. Create New Database

Replace <database_name> with the requested database name, and username with the previously created user

CREATE DATABASE <database_name>
    OWNER <username>
    ENCODING 'UTF8';

9. Send Welcome Email

Send two separate emails to the requestor:

Email 1: Welcome and Access Details

Subject: Your Raft Managed PostgreSQL Database is Ready

Hello [Requestor Name],

Your new managed PostgreSQL database has been created with the following details:

Host: postgres.dbaas.<RDP deployed FQDN>
Port: 5432
Database: <database_name>
Username: <username>

You will receive a separate encrypted email containing your initial database password.

Please confirm that you can successfully connect to your database at your earliest convenience.

If you experience any issues, please contact our support team.

Best regards,
<your name>

Email 2: Encrypted Password

Send a separate encrypted email containing only the initial password.

Security Notes

  • Always use strong, randomly generated passwords

  • Send password information only via encrypted channels

  • Encourage requestors to change their password upon first login

Next Steps

  • Monitor for email confirmation of successful access