STEP 1 — Masuk sebagai postgres (superuser)

sudo -u postgres psql

STEP 2 — Buat Database

CREATE DATABASE ms_auth_db;

STEP 3 — Buat User Khusus Microservice

CREATE USER ms_auth_user WITH ENCRYPTED PASSWORD 'StrongPassword123!';

STEP 4 — Beri Akses Connect ke Database

GRANT CONNECT ON DATABASE ms_auth_db TO ms_auth_user;

STEP 5 — Masuk ke Database

\c ms_auth_db

STEP 6 — Buat Schema Khusus untuk Microservice

CREATE SCHEMA ms_auth AUTHORIZATION ms_auth_user;

STEP 7 — Beri Hak Pakai Schema

GRANT USAGE ON SCHEMA ms_auth TO ms_auth_user;
GRANT CREATE ON SCHEMA ms_auth TO ms_auth_user;

STEP 8 — Set Default Schema untuk User

ALTER ROLE ms_auth_user SET search_path TO ms_auth;

STEP 9 — Atur Default Privileges (Penting!)

ALTER DEFAULT PRIVILEGES IN SCHEMA ms_auth
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO ms_auth_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA ms_auth
GRANT USAGE ON SEQUENCES TO ms_auth_user;

STEP 10 — Set CREATEDB Privileges (Penting!)

ALTER USER pramuka_gamification_user CREATEDB;

---

SHORTCUT

CREATE DATABASE pramuka_gamification_db_dev;
CREATE USER pramuka_gamification_user WITH ENCRYPTED PASSWORD 'XXXXXXXX';
GRANT CONNECT ON DATABASE pramuka_gamification_db_dev TO pramuka_gamification_user;
\c pramuka_gamification_db_dev
CREATE SCHEMA pramuka_gamification_db_dev AUTHORIZATION pramuka_gamification_user;
ALTER ROLE pramuka_gamification_user SET search_path TO pramuka_gamification_db_dev;
ALTER SCHEMA pramuka_gamification_db_dev OWNER TO pramuka_gamification_user;

CHANGE AS OWNER

root@56580ac3e1d7:/# su - postgres
postgres@56580ac3e1d7:~$ psql
psql (15.15 (Debian 15.15-1.pgdg13+1))
Type "help" for help.

postgres=# \c pramuka_gamification_db_dev
You are now connected to database "pramuka_gamification_db_dev" as user "postgres".
pramuka_gamification_db_dev=# ALTER DATABASE pramuka_gamification_db_dev OWNER TO pramuka_gamification_user;
ALTER DATABASE
pramuka_gamification_db_dev=#

READ ONLY USER

CREATE USER ferdi WITH PASSWORD 'password';

GRANT CONNECT ON DATABASE pramuka_gamification_db_prod TO ferdi;
\c pramuka_gamification_db_prod

GRANT USAGE ON SCHEMA pramuka_gamification_db_prod TO ferdi;
GRANT SELECT, UPDATE ON ALL TABLES IN SCHEMA pramuka_gamification_db_prod TO ferdi

ALTER DEFAULT PRIVILEGES IN SCHEMA pramuka_gamification_db_prod

---

SELECT DB & CHECK SCHEMA

\c pramuka_gamification_db_prod
\dn

CHECK PRIVILEGE

SELECT grantee, table_schema, table_name, privilege_type
FROM information_schema.table_privileges
WHERE grantee = 'ferdi';

---

FOR DEPLOYMENT

-- 1. Create database
CREATE DATABASE pramuka_gamification_db_dev;

-- 2. Create user
CREATE USER pramuka_gamification_user WITH ENCRYPTED PASSWORD 'XXXXXXXX';

-- 3. Grant basic access ke database
GRANT CONNECT ON DATABASE pramuka_gamification_db_dev TO pramuka_gamification_user;

-- (optional tapi bagus untuk fleksibilitas Prisma)
GRANT CREATE ON DATABASE pramuka_gamification_db_dev TO pramuka_gamification_user;

-- 4. Masuk ke database
\c pramuka_gamification_db_dev

-- 5. Create schema khusus
CREATE SCHEMA pramuka_gamification_db_dev AUTHORIZATION pramuka_gamification_user;

-- 6. Pastikan user bisa pakai & create object di schema
GRANT USAGE, CREATE ON SCHEMA pramuka_gamification_db_dev TO pramuka_gamification_user;

-- 7. Set default schema (biar gak fallback ke public)
ALTER ROLE pramuka_gamification_user SET search_path TO pramuka_gamification_db_dev;

-- 8. Pastikan ownership (redundan tapi aman)
ALTER SCHEMA pramuka_gamification_db_dev OWNER TO pramuka_gamification_user;

-- 9. Default privileges (PENTING untuk migration berikutnya)
ALTER DEFAULT PRIVILEGES IN SCHEMA pramuka_gamification_db_dev
GRANT ALL ON TABLES TO pramuka_gamification_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA pramuka_gamification_db_dev
GRANT ALL ON SEQUENCES TO pramuka_gamification_user;

---

POSTGRE COMMAND IN DOCKER

CONSOLE

docker exec -it db-postgres psql -U postgres

IMPORT

docker exec -i db-postgres psql -U postgres -d spam_db < ./spam_db_2026-03-27.sql