Manually Creating a Supabase User via Dockerized PostgreSQL

When working with Supabase, there may be situations where you need to manually create a database user for administrative tasks or specific workflows. This guide walks through the process using a Dockerized PostgreSQL instance, as typically deployed with Supabase.

Prerequisites

A working Supabase instance deployed via Docker.
Docker and Docker Compose installed on your system.
Basic familiarity with PostgreSQL commands.

Step 1: Start Your Supabase Environment

Ensure your Supabase stack is up and running:

make a docker-compose.yml

services:
  db:
    image: supabase/postgres:15.6.1.143
    container_name: pflow_db
    environment:
      POSTGRES_PASSWORD: postgres
    ports:
      - "5432:5432"
    restart: on-failure

ubuntu@fake-ip-127-22-11-144:~/Workspace/supabase$ docker-compose up -d

This command starts all containers in detached mode, including the PostgreSQL instance that powers Supabase.

Step 2: Access the PostgreSQL Container

Connect to the PostgreSQL container:

ubuntu@fake-ip-127-22-11-144:~/Workspace/supabase$ docker-compose exec db bash

This command opens a shell inside the db container, where PostgreSQL is running.

Step 3: Switch to the `postgres` User

Inside the container, switch to the PostgreSQL user:

root@fake-ip-127-22-11-144:/# su - postgres

You are now operating as the postgres superuser within the container.

Step 4: Connect to PostgreSQL

Start a PostgreSQL session by connecting to the local server:

postgres@fake-ip-127-22-11-144:~$ psql -h localhost

This opens the PostgreSQL interactive terminal (psql).

Step 5: Switch to the Supabase Admin Database

Connect to the Supabase admin database:

postgres=> \c postgres supabase_admin

When prompted, enter the password for the supabase_admin user. This user typically manages database-level configurations within Supabase.

Step 6: Create a New User

Execute the following SQL command to create a new user with superuser privileges:

postgres=# create user myuser with superuser login password 'pflow';

This command creates a user named myuser with the password pflow and grants superuser privileges. Adjust the username and password to your needs.

Step 7: Test the New User

Switch to the new user to verify the account:

postgres=> \c postgres myuser

If successful, you will see a confirmation message:

psql (15.7 (Ubuntu 15.7-1.pgdg20.04+1), server 15.1 (Ubuntu 15.1-1.pgdg20.04+1))
You are now connected to database "postgres" as user "myuser".

This confirms that the user was created successfully and has the necessary privileges.

Recap

In this guide, you learned how to:

Launch a Dockerized Supabase instance.
Access and interact with the PostgreSQL container.
Create a new superuser in PostgreSQL and validate it.

Why This Matters

Manually creating users can be essential for advanced configurations, debugging, or implementing custom workflows. Always follow best practices to secure your database, especially when handling privileged accounts like superusers.

For more insights into managing Supabase and PostgreSQL, check out the Supabase Documentation.

stackdump/supabase_gain_admin.md