postgreSQL 데이터베이스, 스키마, 사용자, 롤 관리를 위한 쿼리

postgreSQL 13.3 버전 기준으로 작성된 글입니다.

 

psql 콘솔 접근

sudo su - postgres
psql

혹은

psql -U USER_ID -d DB_NAME

 

psql console 에서 사용할 수 있는 커맨드

-- psql console cmd
\du -- 사용자별 Role 조회


\l -- db 정보 조회
\dn -- 스키마 조회
\dt -- 테이블 조회

 

데이터베이스, 스키마, 사용자, 롤 관리 쿼리

-- 데이터베이스 관련 (스키마보다 상위 범주의 개념)
CREATE DATABASE db_name OWNER user_id; -- 데이터베이스 생성
DROP DATABASE db_name; -- 데이터베이스 삭제
SELECT * FROM pg_database; -- 전체 데이터베이스 조회


-- 스키마 관련
SELECT * FROM pg_catalog.pg_namespace; -- 전체 스키마 조회
CREATE SCHEMA schema_name AUTHORIZATION user_name  -- 스키마 생성
DROP SCHEMA schema_name CASCADE;	 -- 스키마 삭제


-- USER 관련
SELECT * FROM pg_user; -- 사용자 조회
SELECT * FROM pg_shadow; -- 사용자 조회

-- admin과 동일한 권한으로 생성
CREATE USER user_name PASSWORD 'password' superuser CREATEDB createrole replication bypassrls; 

CREATE USER user_name PASSWORD 'password'; -- 사용자 생성(DB 또는 schema 생성시에 owner 로 지정 가능)
DROP USER user_name; -- 사용자 삭제


-- Role 관련
ALTER ROLE user_name CREATEDB replication; -- 사용자의 Role 변경 (추가)