How to Delete a User from the Astronomer Database

When managing Astronomer Software, there may be instances where you need to remove a specific user from the PostgreSQL database. This guide walks you through the process of deleting a user from the astronomer_houston database using Kubernetes and PostgreSQL. The steps involve running a temporary PostgreSQL pod, connecting to the relevant database, identifying the user, and finally deleting them.

Prerequisites

Before we begin, make sure you have:

• Access to your Kubernetes cluster.
• Namespace where Astronomer is installed (in this case, we'll assume it's astronomer).
• kubectl CLI set up and authenticated.
• PostgreSQL credentials stored as Kubernetes secrets.

Step 1: Run a PostgreSQL Pod in Your Kubernetes Namespace

The first step is to run a temporary PostgreSQL pod within your Kubernetes namespace. This pod will use the bitnami/postgresql image and automatically connect to the astronomer_houston database.

Here’s how to run it:

kubectl run psql --rm -it --restart=Never --namespace astronomer --image bitnami/postgresql --command -- psql $(kubectl get secret -n astronomer astronomer-houston-backend --template='{{.data.connection | base64decode }}' | sed 's/?.*//g')

Once the command runs, you'll enter the PostgreSQL interactive terminal (psql).

If you don’t see the command prompt, try pressing Enter to initiate the session.

Step 2: Check the List of Databases

Now that you’re inside the psql shell, you can list all available databases using the following command:

\l

This will output something like:

astronomer_houston=> \l
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules | Access privileges
--------------------------------------+----------+----------+-----------------+-------------+-------------+--------+-----------+-----------------------
astronomer_grafana | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | |
astronomer_houston | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | |
extraterrestrial_rocket_3050_airflow | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | |
main | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | |
newtonian_wormhole_1269_airflow | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | |
postgres | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | |
rdsadmin | rdsadmin | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | rdsadmin=CTc/rdsadmin
stellar_sky_7765_airflow | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | |
template0 | rdsadmin | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | =c/rdsadmin +
| | | | | | | | rdsadmin=CTc/rdsadmin
template1 | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
traditional_declination_0216_airflow | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | |
(11 rows)

Look for the astronomer_houston database. If your release name is different, replace astronomer with your actual release name.

Step 3: Switch to the astronomer_houston Database

Since you’ll be working in the astronomer_houston database, switch to it by running the following command:

\c astronomer_houston

If successful, you'll see a message like this:

astronomer_houston=> \c astronomer_houston
psql (17.0, server 16.4)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off, ALPN: none)
You are now connected to database "astronomer_houston" as user "postgres".

Step 4: Set the Search Path

Next, ensure that the correct schema is being used by setting the search path. In Astronomer, the default schema is often named houston$default. To set this, run:

set search_path="houston$default";

This ensures that your queries target the right schema.

astronomer_houston=> set search_path="houston$default";
SET

Step 5: Find the User by Email

Before deleting the user, you need to find their unique user ID in the system. Suppose the user you want to delete has the email john.doe@example.com. You can retrieve their ID with this query:

SELECT id FROM "User" WHERE username = 'john.doe@example.com';

SELECT id FROM "User" where username = 'kalyanreddy.kasireddy@astronomer.io';
id
----------------------------
cm2a96zyk3098117cs95o1k5z4
(1 row)

This query will return the user's ID, which is required for the next step. For example:

Note the id that’s returned.

Step 6: Delete the User

Now that you have the user’s ID, you can proceed with deleting the user from the database.

Run the following DELETE command:

astronomer_houston=> DELETE FROM "User" WHERE id = 'cm2a96zyk3098117cs95o1k5z4';
DELETE 1

This will remove the user with the corresponding ID from the database. You should see confirmation of the deletion:

Step 7: Exit the PostgreSQL Pod

Once you’ve finished, you can exit the PostgreSQL pod by typing:

This will close the pod session and delete the temporary pod since we used the --rm flag when running the pod initially.

Alternative setup: No access to Bitnami image

If you are unable to access the Bitnami image to create the Postgres Pod, you can replace Step 2 from the core steps with the following setup and proceed to Step 3.

1. Run the following command to retrieve the database access string. Copy the output for later in this setup.

kubectl get secret astronomer-bootstrap -o jsonpath='{.data.connection}' | base64 -d

2. Create a local file called new_pod.yaml and copy the following YAML configuration into the file:

apiVersion: v1
kind: Pod
metadata:
name: postgres-client
labels:
app: postgres-client
spec:
containers:
- image: postgres:latest
command:
- sleep
- "3600"
imagePullPolicy: IfNotPresent
name: postgres-client
restartPolicy: Always

3. Run the following command to apply the YAML file and create a new Pod in the Astronomer namespace:

kubectl apply -f path/new_pod.yml

4. Run the following commands to access the PostgreSQL client within the newly created pod:

kubectl exec -it postgres-client -- sh
apt update && apt install postgresql-client
psql <database-access-string>
\l

➜ Another recommendation was to remove the user from all teams first. After that, you should be able to delete the user from the UI or Houston API query.

To find the user ID. You can do this by running a users query or using the command astro workspace user list.

mutation removeUser { 
  removeUser(userUuid: "cl74qcu6821035814fg6tavm6iz") { 
    username 
  } 
}