PostgreSQL : 사용 방법(기초)

■ 기본 연결 및 접속
```sql
      ▷ PostgreSQL 서버 접속
      psql -h localhost -U username -d database_name

      ▷ 로컬 기본 접속
      psql -U postgres

      ▷ 특정 포트로 접속
      psql -h localhost -p 5432 -U username -d database_name
```

■ 데이터베이스 관리
```sql
      ▷ 데이터베이스 생성
      CREATE DATABASE mydb;

      ▷ 데이터베이스 목록 보기
      \l

      ▷ 데이터베이스 삭제
      DROP DATABASE mydb;

      ▷ 데이터베이스 선택/연결
      \c database_name
```

■ 사용자(Role) : 생성과 삭제
```sql
      ▷ 기본 사용자 생성
      CREATE USER username WITH PASSWORD 'password';

      ▷ 또는 CREATE ROLE 사용 (더 일반적)
      CREATE ROLE username WITH LOGIN PASSWORD 'password';

      ▷ 다양한 옵션으로 사용자 생성
      CREATE ROLE myuser WITH
          LOGIN                    ▷ 로그인 가능
          PASSWORD 'mypassword'          ▷ 비밀번호 설정
          CREATEDB                 ▷ 데이터베이스 생성 권한
          CREATEROLE              ▷ 역할 생성 권한
          SUPERUSER               ▷ 슈퍼유저 권한
          VALID UNTIL '2025-12-31';       ▷ 만료일 설정

      ▷ 사용자 삭제
      DROP USER username;
      ▷ 또는
      DROP ROLE username;

      ▷ 사용자가 소유한 객체가 있을 때 강제 삭제
      DROP OWNED BY username;
      DROP ROLE username;
```

■ 사용자(Role) : 목록 및 정보 보기
```sql
      ▷ 사용자 목록 보기
      \du

      ▷ 또는 SQL로
      SELECT rolname, rolsuper, rolcreatedb, rolcreaterole, rolcanlogin FROM pg_roles;

      ▷ 더 자세한 사용자 정보
      SELECT
          rolname as "사용자명",
          CASE WHEN rolsuper THEN '예' ELSE '아니오' END as "슈퍼유저",
          CASE WHEN rolcreatedb THEN '예' ELSE '아니오' END as "DB생성가능",
          CASE WHEN rolcreaterole THEN '예' ELSE '아니오' END as "역할생성가능",
          CASE WHEN rolcanlogin THEN '예' ELSE '아니오' END as "로그인가능"

      FROM pg_roles
      ORDER BY rolname;

      ▷ 현재 접속한 사용자 확인
      SELECT current_user, session_user;
```

■ 사용자(Role) : 정보 수정
```sql
      ▷ 비밀번호 변경
      ALTER USER username WITH PASSWORD 'newpassword';

      ▷ 권한 추가/제거
      ALTER USER username WITH CREATEDB;
      ALTER USER username WITH NOCREATEDB;

      ▷ 사용자명 변경
      ALTER USER old_username RENAME TO new_username;

      ▷ 계정 만료일 설정
      ALTER USER username VALID UNTIL '2025-12-31';

      ▷ 로그인 비활성화/활성화
      ALTER USER username WITH NOLOGIN;
      ALTER USER username WITH LOGIN;
```

■ 사용자(Role) :데이터베이스 레별 권한
```sql
      ▷ 데이터베이스 소유권 부여
      ALTER DATABASE mydb OWNER TO username;

      ▷ 데이터베이스 접근 권한 부여
      GRANT CONNECT ON DATABASE mydb TO username;

      ▷ 데이터베이스 접근 권한 제거
      REVOKE CONNECT ON DATABASE mydb FROM username;

      ▷ 모든 권한 부여
      GRANT ALL PRIVILEGES ON DATABASE mydb TO username;

      ▷ 특정 데이터베이스의 권한 확인
      \l
      ▷ 또는
      SELECT datname, datacl FROM pg_database WHERE datname = 'mydb';
```

■ 사용자(Role) :스키마 레별 권한
```sql
      ▷ 스키마 사용 권한 부여
      GRANT USAGE ON SCHEMA public TO username;

      ▷ 스키마의 모든 권한 부여
      GRANT ALL ON SCHEMA public TO username;

      ▷ 스키마에서 테이블 생성 권한
      GRANT CREATE ON SCHEMA public TO username;

      ▷ 스키마 권한 확인
      \dn+
```

■ 사용자(Role) : 테이블 레벨 권한
```sql
      ▷ 테이블 조회 권한
      GRANT SELECT ON table_name TO username;

      ▷ 테이블 수정 권한
      GRANT INSERT, UPDATE, DELETE ON table_name TO username;

      ▷ 테이블의 모든 권한
      GRANT ALL PRIVILEGES ON table_name TO username;

      ▷ 여러 테이블에 한번에 권한 부여
      GRANT SELECT ON ALL TABLES IN SCHEMA public TO username;

      ▷ 앞으로 생성될 테이블에도 자동으로 권한 부여
      ALTER DEFAULT PRIVILEGES IN SCHEMA public
      GRANT SELECT ON TABLES TO username;

      ▷ 특정 컬럼에만 권한 부여
      GRANT SELECT (id, name) ON users TO username;
      GRANT UPDATE (email) ON users TO username;

      ▷ 권한 제거
      REVOKE SELECT ON table_name FROM username;
      REVOKE ALL PRIVILEGES ON table_name FROM username;
```

■ 사용자(Role) : 시퀀스 권한
```sql
      ▷ 시퀀스 사용 권한 (SERIAL 컬럼 사용시 필요)
      GRANT USAGE, SELECT ON SEQUENCE table_name_id_seq TO username;

      ▷ 모든 시퀀스에 권한 부여
      GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO username;

      ▷ 기본 시퀀스 권한 설정
      ALTER DEFAULT PRIVILEGES IN SCHEMA public
      GRANT USAGE, SELECT ON SEQUENCES TO username;
      ```

■ 사용자(Role) : 그룹 관리 권한
```sql
      ▷ 그룹 역할 생성
      CREATE ROLE developers;
      CREATE ROLE managers;

      ▷ 그룹에 권한 부여
      GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO developers;
      GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO managers;

      ▷ 사용자를 그룹에 추가
      GRANT developers TO username;
      GRANT managers TO username;

      ▷ 사용자를 그룹에서 제거
      REVOKE developers FROM username;

      ▷ 그룹 멤버십 확인
      SELECT
          r.rolname as "그룹",
          m.rolname as "멤버"
      FROM pg_roles r
      JOIN pg_auth_members am ON r.oid = am.roleid
      JOIN pg_roles m ON am.member = m.oid
      WHERE r.rolname IN ('developers', 'managers')
      ORDER BY r.rolname, m.rolname;
```

■ 사용자(Role) : 권한 확인 방법
```sql
      ▷ 테이블 권한 확인
      \dp table_name
      ▷ 또는
      \z table_name

      ▷ 모든 테이블 권한 확인
      \dp

      ▷ SQL로 권한 확인
      SELECT
          schemaname,
          tablename,
          tableowner,
          tablespace,
          hasindexes,
          hasrules,
          hastriggers
      FROM pg_tables
      WHERE schemaname = 'public';

      ▷ 특정 사용자의 권한 확인
      SELECT
          table_name,
          privilege_type,
          is_grantable
      FROM information_schema.table_privileges
      WHERE grantee = 'username';
```

■ 사용자(Role) : 실용적인 권한 관리 예시
```sql
      ▷ 1. 읽기 전용 사용자 생성
      CREATE ROLE readonly_user WITH LOGIN PASSWORD 'password';
      GRANT CONNECT ON DATABASE mydb TO readonly_user;
      GRANT USAGE ON SCHEMA public TO readonly_user;
      GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
      ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user;

      ▷ 2. 개발자용 사용자 생성
      CREATE ROLE developer WITH LOGIN PASSWORD 'devpass';
      GRANT CONNECT ON DATABASE mydb TO developer;
      GRANT USAGE, CREATE ON SCHEMA public TO developer;
      GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO developer;
      GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO developer;

      ▷ 3. 관리자용 사용자 생성
      CREATE ROLE admin_user WITH LOGIN PASSWORD 'adminpass' CREATEDB;
      GRANT ALL PRIVILEGES ON DATABASE mydb TO admin_user;
```

■ 사용자(Role) : 보안 권장 사항
```sql
      ▷ 1. 기본 public 스키마 권한 제한
      REVOKE CREATE ON SCHEMA public FROM PUBLIC;
      REVOKE ALL ON DATABASE mydb FROM PUBLIC;

      ▷ 2. 비밀번호 정책 설정 (postgresql.conf)
      ▷ password_encryption = 'scram-sha-256'

      ▷ 3. 연결 제한 설정
      ALTER ROLE username CONNECTION LIMIT 10;

      ▷ 4. 특정 IP에서만 접속 허용 (pg_hba.conf 파일에서 설정)
      ▷ host    mydb    username    192.168.1.0/24    md5

      ▷ 5. 사용하지 않는 기본 권한 제거
      REVOKE ALL ON SCHEMA public FROM PUBLIC;
```

■ 사용자(Role) : 권한 문제 해결
```sql
      ▷ 권한 부족 오류 시 확인사항
      ▷ 1. 데이터베이스 연결 권한
      SELECT has_database_privilege('username', 'mydb', 'CONNECT');

      ▷ 2. 테이블 권한 확인
      SELECT has_table_privilege('username', 'table_name', 'SELECT');

      ▷ 3. 스키마 권한 확인
      SELECT has_schema_privilege('username', 'public', 'USAGE');

      ▷ 4. 모든 필요한 권한 한번에 부여
      GRANT CONNECT ON DATABASE mydb TO username;
      GRANT USAGE ON SCHEMA public TO username;
      GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO username;
      GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO username;
```

■ 테이블 관리
```sql
      ▷ 테이블 생성
      CREATE TABLE users (
          id SERIAL PRIMARY KEY,
          name VARCHAR(100) NOT NULL,
          email VARCHAR(255) UNIQUE,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      );

      ▷ 테이블 목록 보기
      \dt
      \dt *.*
      \dt+    ▷ 자세히
      \dt+ public.*       ▷ 테이블 정보를 한눈에 보기

      ▷ 현재 작업 상태 확인 등
      \echo :LAST_ERROR_MESSAGE         ▷ 최근 실행한 쿼리 확인 (psql 에서)
      SELECT current_database();        ▷ 현재 연결된 데이터베이스 확인
      SELECT current_schema();          ▷ 현재 스키마 확인
      SELECT current_user();        ▷ 현재 사용자 확인

      ▷ 테이블명 패턴의 검색
      \dt user*   ▷ user로 시작하는 테이블 검색
      \dt *log*   ▷ log가 포함된 테이블 검색

      ▷ 테이블 구조 보기
      \d table_name
      \d+ table_name  ▷ 테이블 구조를 자세히

      ▷ 테이블 선택하는 방법들(Mysql USE table_name 명령어 없다)
      SELECT * FROM table_name;
      SELECT * FROM public.table_name       ▷ 스키마 지정

      ▷ 테이블 삭제
      DROP TABLE table_name;

      ▷ 컬럼 추가
      ALTER TABLE users ADD COLUMN age INTEGER;

      ▷ 컬럼 삭제
      ALTER TABLE users DROP COLUMN age;
```

■ 스키마 관리
```sql
      ▷ 현재 스키마 경로 보기
      SHOW search_path;

      ▷ 스키마 경로 변경(임시)
      SET search_path TO schema_name, public;

      ▷ 스키마 생성
      CREATE SCHEMA my_schema;

      ▷ 스키마별 테이블 목록
      \dn       ▷ 스키마 목록보기

```

■ 데이터 조작 (CRUD)
```sql
      ▷ 데이터 삽입
      INSERT INTO users (name, email) VALUES ('김철수', 'kim@example.com');

      ▷ 여러 행 삽입
      INSERT INTO users (name, email) VALUES
          ('이영희', 'lee@example.com'),
          ('박민수', 'park@example.com');

      ▷ 데이터 조회
      SELECT * FROM users;
      SELECT name, email FROM users WHERE id = 1;

      ▷ 조건부 조회
      SELECT * FROM users WHERE name LIKE '김%';
      SELECT * FROM users WHERE created_at >= '2024-01-01';

      ▷ 정렬
      SELECT * FROM users ORDER BY name ASC;
      SELECT * FROM users ORDER BY created_at DESC;

      ▷ 제한
      SELECT * FROM users LIMIT 10;
      SELECT * FROM users LIMIT 10 OFFSET 20;

      ▷ 데이터 수정
      UPDATE users SET email = 'newemail@example.com' WHERE id = 1;

      ▷ 데이터 삭제
      DELETE FROM users WHERE id = 1;
```

■ 자주 사용하는 함수들
```sql
      ▷ 개수 세기
      SELECT COUNT(*) FROM users;
      SELECT COUNT(DISTINCT email) FROM users;

      ▷ 집계 함수
      SELECT AVG(age), MAX(age), MIN(age) FROM users;

      ▷ 문자열 함수
      SELECT UPPER(name), LOWER(email) FROM users;
      SELECT CONCAT(name, ' - ', email) FROM users;

      ▷ 날짜 함수
      SELECT NOW(), CURRENT_DATE, CURRENT_TIME;
ㅍSELECT DATE_PART('year', created_at) FROM users;
```

■ JOIN 연산
```sql
      ▷ INNER JOIN
      SELECT u.name, p.title
      FROM users u
      INNER JOIN posts p ON u.id = p.user_id;

      ▷ LEFT JOIN
      SELECT u.name, p.title
      FROM users u
      LEFT JOIN posts p ON u.id = p.user_id;

      ▷ 여러 테이블 JOIN
      SELECT u.name, p.title, c.content
      FROM users u
      JOIN posts p ON u.id = p.user_id
      JOIN comments c ON p.id = c.post_id;

      ▷ 외래 키 관계 보기
      SELECT
          tc.table_name,
          kcu.column_name,
          ccu.table_name AS foreign_table_name,
          ccu.column_name AS foreign_column_name
      FROM information_schema.table_constraints AS tc
      JOIN information_schema.key_column_usage AS kcu
          ON tc.constraint_name = kcu.constraint_name
      JOIN information_schema.constraint_column_usage AS ccu
          ON ccu.constraint_name = tc.constraint_name
      WHERE tc.constraint_type = 'FOREIGN KEY'
          AND tc.table_name = 'your_table_name';
```

■ 인덱스 관리
```sql
      ▷ 인덱스 생성
      CREATE INDEX idx_users_email ON users(email);
      CREATE INDEX idx_users_name_email ON users(name, email);

      ▷ 인덱스 목록 보기
\di

      ▷ 인덱스 삭제
      DROP INDEX idx_users_email;
```

■ 유용한 psql 명령어
```sql
      ▷ 도움말
      \h                    ▷ SQL 명령어 도움말
      \?                    ▷ psql 명령어 도움말

      ▷ 정보 보기
      \l                    ▷ 데이터베이스 목록
      \dt                   ▷ 테이블 목록
      \dv                   ▷ 뷰 목록
      \df                   ▷ 함수 목록
      \du                   ▷ 사용자 목록

      ▷ 설정
      \timing on            ▷ 쿼리 실행 시간 표시
      \x                    ▷ 확장 표시 모드 토글
      \x auto               ▷ 자동 확장 표시
      ▷ 파일 작업
      \i filename.sql       ▷ SQL 파일 실행
      \o output.txt         ▷ 출력을 파일로 저장

      ▷ 종료
      \q                    ▷ psql 종료
```

■ 트랜잭션
```sql
      ▷ 트랜잭션 시작
      BEGIN;

      ▷ 작업 수행
      INSERT INTO users (name, email) VALUES ('test', 'test@example.com');
      UPDATE users SET name = 'updated' WHERE id = 1;

      ▷ 커밋 (변경사항 저장)
      COMMIT;

      ▷ 또는 롤백 (변경사항 취소)
      ▷ ROLLBACK;
```

■ 백업과 복원
```bash
      # 데이터베이스 백업
      pg_dump -U username database_name > backup.sql

      # 데이터베이스 복원
      psql -U username database_name < backup.sql

      # 압축 백업
      pg_dump -U username -Fc database_name > backup.dump

      # 압축 백업 복원
      pg_restore -U username -d database_name backup.dump
```