[PostgreSQL] PostgreSQL 사용자 생성, 수정, 삭제 그리고 권한 부여와 회수

1. 사용자 생성, 수정, 삭제

(1) 사용자 생성

• 기본 생성

CREATE ROLE username WITH LOGIN PASSWORD 'password';

• 권한을 포함한 생성

CREATE ROLE app_user
WITH LOGIN PASSWORD 'pass123'
CREATEDB
CREATEROLE;

LOGIN → 로그인 가능

CREATEDB → DB 생성 가능

CREATEROLE → 사용자 생성 가능

SUPERUSER → 관리자

NOSUPERUSER → 일반유저(기본값)

(2) 사용자 수정

• 비밀번호 변경

ALTER ROLE username WITH PASSWORD 'newpassword';

• 권한 변경

ALTER ROLE username CREATEDB;
ALTER ROLE username NOSUPERUSER;

• 이름 변경

ALTER ROLE oldname RENAME TO newname;

(3) 사용자 삭제

DROP ROLE username;

※ 해당 사용자가 소유한 객체가 있으면 삭제 불가

(4) 사용자 조회

\du

또는

SELECT rolname FROM pg_roles;

2. 권한 부여와 회수

(1) 권한 부여

• DB 접속 권한

GRANT CONNECT ON DATABASE mydb TO username;

• 스키마 사용 권한

GRANT USAGE ON SCHEMA public TO username;

• 모든 테이블의 모든 권한

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO username;

• 특정 테이블의 특정 권한

GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE users TO username;

• 시퀀스 권한

GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public TO username;

• 실행 권한

GRANT EXECUTE ON FUNCTION my_function() TO username;

(2) 권한 회수

• 특정 권한 회수

REVOKE SELECT ON users FROM username;

• 모든 권한 회수

REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM username;

(3) 기본 권한 설정

• 앞으로 생성될 테이블에도 자동 권한

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

(4) 권한 조회

• 테이블 권한

\z users

• 사용자 권한

\du

3. 권한 구조의 핵심 이해하기

User와 Role의 관계

• User(사용자) = 로그인이 가능한 Role
• Role = 사용자 + 그룹 개념
• 권한은 Role에 부여

CREATE USER user1 PASSWORD 'pass';
-- 위 구문은 아래와 동일  
CREATE ROLE user1 WITH LOGIN PASSWORD 'pass';

Role을 다른 Role에 할당하기

• Role을 다른 Role에 할당 가능

GRANT readonly TO username;

4. 실수 포인트

1) 스키마 권한 빠짐

GRANT USAGE ON SCHEMA public TO username;

→ 없으면 테이블 접근 불가

2) 시퀀스 권한 없음 (INSERT 실패)

GRANT USAGE, SELECT ON SEQUENCES

3) 미래 테이블 권한 누락

ALTER DEFAULT PRIVILEGES ...