How to Create a New PostgreSQL User for an Existing Database (Without Using postgres)

1️⃣ Enter the PostgreSQL container

docker exec -it postgres_pgvector psql -U postgres

---

2️⃣ Create a new user (role)

Choose a strong password 👇

CREATE USER ai_user WITH PASSWORD 'ai_user_password';

---

3️⃣ Grant access to the database

GRANT CONNECT ON DATABASE vectordb TO ai_user;

---

4️⃣ Switch to the database

\c vectordb

---

5️⃣ Grant schema & table permissions

This is IMPORTANT (most people miss this 👀)

GRANT USAGE ON SCHEMA public TO ai_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO ai_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO ai_user;

---

6️⃣ Future tables auto-permission (best practice)

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO ai_user;

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

---

7️⃣ Update your .env

AI_DB_CONNECTION=postgresql_ai
AI_DB_HOST=206.189.131.217
AI_DB_PORT=5432
AI_DB_DATABASE=vectordb
AI_DB_USERNAME=ai_user
AI_DB_PASSWORD=ai_user_password

---

8️⃣ Test connection

psql -h 206.189.131.217 -p 5432 -U ai_user -d vectordb

---

🔐 Recommended (production-safe)

If this is production:

REVOKE ALL ON DATABASE vectordb FROM PUBLIC;

And never use postgres in apps.

---