Basic Command

명령어	설명
psql -U username -P	접속
\l	모든 데이터베이스 표시
\c db_name	특정 데이터베이스 연결
\dt	특정 데이터베이스의 모든 테이블을 표시
\d table_name	특정 테이블을 세부정보 표시
\di table_name	특정 테이블의 모든 인덱스를 표시
\dn	모든 스키마 표시
\dn	모든 스키마 표시
\du	현재 데이터베이스에 클러스터의 모든 역할(사용자) 표시
\dp	현재 데이터베이스에 있는 개체의 액세스 권한을 표시
\q, exit	종료
select current_user, current_database();	현재 사용자 및 데이터베이스를 표시
\conninfo;	현재 데이터베이스 표시
select version();	현재 PostgreSQL 버전을 표시
SET search_path TO 스키마명, public;	접속 후 기본 스키마 지정

접속하기

psql -U [username] -d [database_name] -h [hostname] -p [port_number]
psql -U myuser -d mydatabase -h localhost -p 5432

각 값의 기본 값

• username: postgres

• database_name: postgres

• hostname: localhost

• port_number: 5432

유저 리스트

\du
\du+

SELECT rolname FROM pg_roles;

SELECT
    rolname AS role_name,
    rolsuper AS superuser,
    rolcreaterole AS create_role,
    rolcreatedb AS create_db,
    rolcanlogin AS can_login,
    rolreplication AS replication
FROM
    pg_roles
WHERE
    rolname NOT LIKE 'pg\_%' ESCAPE '\'
ORDER BY
    rolname
;

SELECT usename AS role_name,
  CASE
     WHEN usesuper AND usecreatedb THEN
	   CAST('superuser, create database' AS pg_catalog.text)
     WHEN usesuper THEN
	    CAST('superuser' AS pg_catalog.text)
     WHEN usecreatedb THEN
	    CAST('create database' AS pg_catalog.text)
     ELSE
	    CAST('' AS pg_catalog.text)
  END role_attributes
FROM pg_catalog.pg_user
ORDER BY role_name desc;

CreateRole

PostgreSQL은 역할이라는 개념을 사용하여 사용자 계정을 나타낸다. 다른 데이터베이스 시스템처럼 사용자 개념을 사용하지 않는다. 일반적으로 PostgreSQL 서버에 로그인할 수 있는 역할을 로그인 역할이라고 한다 이는 다른 데이터베이스 시스템의 사용자 계정과 동일하다. 역할에 다른 역할이 포함된 경우 이를 그룹 역할이라고 한다.

SELECT rolname FROM pg_roles;
CREATE ROLE name WITH option; -- option: SUPERUSER, CREATEDB, CREATEROLE

CREATE ROLE username WITH LOGIN PASSWORD 'password';
CREATE ROLE username WITH LOGIN PASSWORD 'password' SUPERUSER;
CREATE ROLE alice LOGIN PASSWORD 'securePass1';
CREATE ROLE john SUPERUSER LOGIN PASSWORD 'securePass1';
CREATE ROLE john CREATEDB LOGIN PASSWORD 'securePass1';
CREATE ROLE dev_api WITH LOGIN PASSWORD 'securePass1' VALID UNTIL '2050-01-01';
CREATE ROLE api LOGIN PASSWORD 'securePass1' CONNECTION LIMIT 1000;

• 이름이 pg_로 시작하는 역할은 시스템 역할이다

• postgres는 PostgreSQL 설치 관리자가 만든 수퍼유저 역할입니다.

• psql에서 \du 명령을 사용하면 현재 PostgreSQL 서버에서 postgres 역할을 포함하여 생성한 모든 역할을 표시할 수 있다.

Grant Privilage

GRANT privilege_list | ALL
ON  table_name
TO  role_name;

• privilege_list: SELECT, INSERT, UPDATE, DELETE, TRUNCATE, ALL, etc

-- after creating table candidates
GRANT SELECT ON candidates TO joe;
GRANT INSERT, UPDATE, DELETE ON candidates TO joe;
GRANT ALL ON candidates TO joe;
GRANT ALL ON ALL TABLES IN SCHEMA "public" TO joe;
GRANT SELECT ON ALL TABLES IN SCHEMA "public" TO reader;

Revoke Privilage

REVOKE privilege | ALL
ON TABLE table_name |  ALL TABLES IN SCHEMA schema_name
FROM role_name;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Alter Role

ALTER ROLE role_name [WITH] option;

create role calf login password 'securePwd1';
alter role calf superuser;
\du calf

Show tables

SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';