[메모] PostgreSQL 클라이언트 psql 기본 명령어

MySQL만 사용하던 사람들은 psql의 명령어가 생소할 수 있다. 이 글은 빠르게 참고할 수 있는 psql과 PostgreSQL 관련 기초 메모다.

psql 명령어 요약

\c database_name: MySQL의 USE database_name에 해당
\d: 테이블, 뷰, 시퀀스 등의 구조 보기
\dt: 현재 스키마의 테이블 목록 보기
\dt schema_name.*: 특정 스키마의 모든 테이블 나열 (예: \dt public.*)
\dn: 모든 스키마 목록 보기
\dn+: 스키마 목록 + 소유자, 권한, 설명 등 상세 정보 확인
\du: 모든 Role(사용자/그룹) 목록 보기
\x: 결과를 세로 리스트 형태로 토글 (긴 결과 보기 유용)
\setenv PAGER 'less -niS': 줄바꿈 없이 가로 스크롤 가능한 출력 설정 (psql 14 이상)
\q: psql 종료
\?: psql 명령어 전체 도움말 보기
SET search_path TO your_schema;: 현재 세션의 기본 스키마 설정
_{(지속 적용하려면 ALTER ROLE 또는 ALTER DATABASE 사용)}

모든 스키마 나열

SELECT schema_name FROM information_schema.schemata;

사용자(Roles)와 권한

PostgreSQL에서는 User라는 개념 대신 Role이 사용된다. Role은 로그인 가능한 사용자나 그룹으로 기능한다.

Role 및 권한 예시

-- 1. 개발자 그룹 생성
CREATE ROLE developers;

-- 2. 데이터베이스 생성 권한 부여
GRANT CREATEDB TO developers;

-- 3. 특정 스키마의 모든 테이블에 대해 SELECT 권한 부여
GRANT SELECT ON ALL TABLES IN SCHEMA public TO developers;

-- 4. 개발자 계정 생성 및 그룹에 추가
CREATE ROLE dev1 LOGIN PASSWORD 'password1';
CREATE ROLE dev2 LOGIN PASSWORD 'password2';
GRANT developers TO dev1, dev2;

사용자 권한 부여 (MySQL과의 차이)

MySQL은 다음처럼 간단하게 권한 부여가 가능하다:

GRANT ALL PRIVILEGES ON my_database.* TO 'user'@'host';

하지만 PostgreSQL은 데이터베이스 → 스키마 → 객체(테이블, 시퀀스, 함수) 구조로 되어 있어서, 더 세분화된 권한 부여가 필요하다.

PostgreSQL에서 권한 부여 절차

-- 1. 사용자 생성
CREATE ROLE myuser LOGIN PASSWORD 'mypassword';

-- 2. 데이터베이스 접속 권한
GRANT CONNECT ON DATABASE my_database TO myuser;

-- 3. public 스키마 사용 권한
GRANT USAGE ON SCHEMA public TO myuser;

-- 4. 스키마 내 모든 테이블 권한
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myuser;

-- 5. 스키마 내 모든 시퀀스 권한
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO myuser;

-- 6. 스키마 내 모든 함수 권한
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO myuser;

-- 7. 향후 생성될 객체에 대한 기본 권한 설정
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES TO myuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON SEQUENCES TO myuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON FUNCTIONS TO myuser;

💡 ALTER DEFAULT PRIVILEGES는 현재 세션의 role로 생성되는 객체에만 적용되므로, 이 권한 부여는 해당 user가 직접 객체를 생성하는 경우에만 유효함.