Mac에서 Postgresql 사용하기

Homebrew 설치 :

/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

---

Postgresql 17버전 설치

brew install postgresql@17

서버 시작

brew services start postgresql@17

PATH 등록

echo 'export PATH="/opt/homebrew/opt/postgresql@17/bin:$PATH"' >> ~/.zshrc
source ~/.zshrc

파일 경로 확인

brew --prefix postgresql@17

유저 생성

# 앱 전용 사용자 생성 (비밀번호 묻는 프롬프트 뜸)
createuser -P postgres
# 비밀번호 입력 프롬프트
Enter password for new role: 
Enter it again:

유저 생성 확인

# 접속
psql -h 127.0.0.1 -p 5432 -U $(whoami) -d postgres
# \du만 입력
postgres=# \du

                             List of roles
 Role name |                         Attributes                         
-----------+------------------------------------------------------------
 my_comp   | Superuser, Create role, Create DB, Replication, Bypass RLS
 postgres  | 
# 이런 식으로 출력

# \l만 입력
postgres=# \l

                                               List of databases
   Name    |  Owner  | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules |  Access privileges  
-----------+---------+----------+-----------------+---------+-------+--------+-----------+---------------------
 postgres  | my_comp | UTF8     | libc            | C       | C     |        |           | 
 template0 | my_comp | UTF8     | libc            | C       | C     |        |           | =c/my_comp         +
           |         |          |                 |         |       |        |           | my_comp=CTc/my_comp
 template1 | my_comp | UTF8     | libc            | C       | C     |        |           | =c/my_comp         +
           |         |          |                 |         |       |        |           | my_comp=CTc/my_comp

프로젝트마다 DB/사용자 나눠 쓰기

 

-- 1) 소유자(= 관리자 겸 마이그레이션 계정) 롤
CREATE ROLE sisy_owner WITH LOGIN PASSWORD 'owner_pw' CREATEDB;

-- 2) 앱에서 접속할 롤 (권한 제한)
CREATE ROLE sisy_app WITH LOGIN PASSWORD 'app_pw';

-- 3) 읽기전용 롤(선택)
CREATE ROLE sisy_reader WITH LOGIN PASSWORD 'reader_pw';

-- 4) 데이터베이스 생성 & 소유자 지정
CREATE DATABASE sisyphus OWNER sisy_owner TEMPLATE template0 ENCODING 'UTF8';

-- 5) 기본 권한 정리
REVOKE ALL ON DATABASE sisyphus FROM PUBLIC;

-- DB에 들어가서 스키마 권한 정리
\c sisyphus sisy_owner
REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT ALL ON SCHEMA public TO sisy_owner;

-- 6) 앱 계정 권한 부여(현재/미래 테이블까지)
GRANT CONNECT ON DATABASE sisyphus TO sisy_app;
GRANT USAGE ON SCHEMA public TO sisy_app;
GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA public TO sisy_app;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT,INSERT,UPDATE,DELETE ON TABLES TO sisy_app;

-- 7) 리더 권한(선택)
GRANT CONNECT ON DATABASE sisyphus TO sisy_reader;
GRANT USAGE ON SCHEMA public TO sisy_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO sisy_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO sisy_reader;

---

꼬였을 때 초기화

brew services stop postgresql@17

# 데이터 디렉터리 백업(선택)
mv /opt/homebrew/var/postgresql@17 /opt/homebrew/var/postgresql@16.bak.$(date +%s)

# 새로 초기화: 슈퍼유저 이름을 'postgres'로
initdb -D /opt/homebrew/var/postgresql@17 -U postgres

brew services start postgresql@17

# postgres 슈퍼유저로 접속 테스트
psql -h 127.0.0.1 -U postgres -d postgres