Creating a New Database in an Existing PostgreSQL Instance

A quick-reference guide for creating a new database inside an existing PostgreSQL instance — whether it runs natively on a host, inside a Docker container, or as part of a CloudNativePG cluster in Kubernetes. Covers getting a shell, creating a role, provisioning the database, and granting the privileges the new owner needs.

🧭Prerequisites

A running PostgreSQL instance you can reach.
Superuser credentials (the default postgres role, or equivalent).
For Kubernetes / CNPG: kubectl access to the namespace and the name of the primary pod.
For Docker: docker CLI access and the container name or ID.

🐚Step 1: Get a Shell on the Instance

The first thing you need is an interactive psql session connected as the superuser. How you get there depends on your deployment model.

1
Kubernetes (CloudNativePG / any Pod)
Exec into the primary pod and launch psql:
bash
```
kubectl exec -it <primary-pod> -n <namespace> -- psql -U postgres
```
For CNPG the primary is usually the pod whose name ends in -1. You can confirm with kubectl cnpg status <cluster> -n <namespace>.

Docker

bash

docker exec -it <container-name> psql -U postgres

3
Native / Local Install
If Postgres is running directly on the host:
bash
```
sudo -u postgres psql
```

💡

In all three cases you should land at a postgres=# prompt. The # (rather than >) confirms you are connected as a superuser.

👤Step 2: Create a Dedicated Role

Best practice is to give every database its own login role rather than sharing postgres. Create the role from the superuser session:

sql

CREATE ROLE myapp_user WITH LOGIN PASSWORD 'a-strong-password';

ℹ️

Replace myapp_user and a-strong-password with your actual values. For production, feed the password through a secret manager (Kubernetes Secret, Vault, etc.) rather than hard-coding it.

Common optional flags you can tack on:

CREATEDB — lets the role create additional databases later.
CONNECTION LIMIT 50 — caps concurrent connections.
VALID UNTIL '2027-01-01' — auto-expires the credentials.

🗄️Step 3: Create the Database

Still in the same superuser psql session, create the database and assign ownership to the new role:

sql

CREATE DATABASE myapp_db OWNER myapp_user;

Verify the database was created:

sql

\l

You should see myapp_db in the listing with myapp_user as its owner.

🔑Step 4: Grant Privileges

Connect to the new database and grant the role everything it needs.

1
Switch into the new database
sql
```
\c myapp_db
```
The prompt changes to myapp_db=#.

Grant all privileges on the database

sql

GRANT ALL PRIVILEGES ON DATABASE myapp_db TO myapp_user;

Grant usage and create on the public schema

sql

GRANT USAGE ON SCHEMA public TO myapp_user;
GRANT CREATE ON SCHEMA public TO myapp_user;

Grant privileges on all existing tables (if any)

sql

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myapp_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO myapp_user;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO myapp_user;

Set default privileges for future objects

So tables/sequences/functions created later by postgres are automatically accessible to the app role:

sql

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT ALL ON TABLES TO myapp_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT ALL ON SEQUENCES TO myapp_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT ALL ON FUNCTIONS TO myapp_user;

⚠️

On PostgreSQL 15+ the public schema is no longer writable by all roles by default. The GRANT CREATE ON SCHEMA public step above is essential — without it your app will get permission denied for schema public errors when it tries to run migrations.

✅Step 5: Verify the Setup

Confirm the new role can connect and create objects.

Connect as the new role

Exit the current session (\\q) and reconnect as the new user:

bash

# Kubernetes
kubectl exec -it <primary-pod> -n <namespace> -- \
  psql -U myapp_user -d myapp_db

# Docker
docker exec -it <container-name> \
  psql -U myapp_user -d myapp_db

# Native
psql -U myapp_user -d myapp_db

2
Create a throwaway table
sql
```
CREATE TABLE _test (id serial PRIMARY KEY);
DROP TABLE _test;
```
If both statements succeed, the role has the correct permissions.

📋Complete Script

Copy-pasteable block that combines every step above. Run this from a postgres=# superuser prompt:

sql

-- 1. Create the role
CREATE ROLE myapp_user WITH LOGIN PASSWORD 'a-strong-password';

-- 2. Create the database
CREATE DATABASE myapp_db OWNER myapp_user;

-- 3. Connect to the new database
\c myapp_db

-- 4. Grant privileges
GRANT ALL PRIVILEGES ON DATABASE myapp_db TO myapp_user;
GRANT USAGE  ON SCHEMA public TO myapp_user;
GRANT CREATE ON SCHEMA public TO myapp_user;

-- 5. Grant on existing objects (safe even if there are none yet)
GRANT ALL PRIVILEGES ON ALL TABLES    IN SCHEMA public TO myapp_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO myapp_user;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO myapp_user;

-- 6. Ensure future objects are accessible too
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT ALL ON TABLES    TO myapp_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT ALL ON SEQUENCES TO myapp_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT ALL ON FUNCTIONS TO myapp_user;

☸️CNPG-Specific Notes

If your PostgreSQL instance is managed by the CloudNativePG operator, keep the following in mind:

Declarative databases: CNPG can manage databases and roles via the Cluster manifest. If you prefer GitOps, define them in spec.bootstrap.initdb or via spec.managed.roles / spec.managed.databases instead of running SQL by hand.
Failover: manual SQL changes on the primary are replicated to standbys, but the operator won't know about them. Document any imperative changes so they can be reconciled later.
Secrets: CNPG auto-creates Kubernetes Secrets for roles defined in the manifest. For manually created roles, create a matching Secret yourself so your apps can reference it.

References: