PostgreSQL 스키마와 유저 권한 설정 및 DBeaver를 통한 DB 연결

다른 DB를 사용할 때는 잘 사용하지 않던 스키마 개념 때문에 굉장히 혼란스러웠지만 이틀 정도 매대기치다보니 정리가 되었다. 휴 @.@

---

 

# 사용자 권한 설정과 스키마 설정

## postgresql 접속

psql postgres

 

만일 postgreSQL 버전을 여러가지 사용중이라면 포트를 지정해서 접속!

psql -p [포트번호]

* 포트 번호는 postgresql.conf파일에서 확인 가능

 

### postgresql 명령어 참고 블로그

https://browndwarf.tistory.com/51

 

- PostgreSQL 구조를 생각해두면 이해하는데 좋음

PostgreSQL DBMS > Database > Schema > Table & View & Procedure

 

## 현재 세션 유저 확인

postgres=# SELECT session_user;

 

## 사용자 생성

- 데이터베이스에 접속할 수 있는 사용자 역할을 생성

- testuser 라는 유저명, password 설정하여 유저 생성

postgres=# CREATE ROLE testuser WITH LOGIN PASSWORD '비밀번호설정';

 

## 데이터베이스 생성

- 데이터베이스 자체를 생성하며, 해당 데이터베이스의 소유자를 지정

create database testdb with owner testuser;

 

 - 데이터베이스를 미리 만들어뒀다면 소유주가 postgres(기본사용자)이니 변경이 필요함

ALTER DATABASE policydb OWNER TO testuser;

## 데이터베이스 목록 확인

postgres=# \l+

 

## testuser 계정으로 testdb 접속

postgres=# \c testdb testuser
접속정보: 데이터베이스="testdb", 사용자="testuser".
testdb=>

=> 는 superuser가 아님을 뜻함.

 

## 스키마 생성

PostgreSQL을 제일 먼저 설치하면, 기본적으로 postgres라는 database가 존재하는데 Public이라는 Schema가 생성됨.

그 외 testuser 유저명과 같은 이름의 testuser라는 스키마를 생성 

(사내 규정으로 동일한 이름으로 생성했지만, 필요에 따라 스키마명은 변경하면 된다.)

 

create schema testuser authorization testuser;

 

## 스키마 확인

testdb=> \dn+
                                          스키마 목록
   이름   |      소유주       |              액세스 권한               |          설명
----------+-------------------+----------------------------------------+------------------------
 public   | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
          |                   | =U/pg_database_owner                   |
 testuser | testuser          |                                        |

 

## 검색 경로 설정 (필요에 따라 설정하는 듯 싶다. 알아보고 추후에 다시 업데이트 하겠다.)

스키마를 사용자명과 동일하게 설정하지 않았을 때는 검색 경로를 설정해서 해당 DB에서 어떤 스키마가 검색될지 경로를 설정해야 한다.

스키마명을 사용자명과 동일하게 설정했다면 이 단계는 필요치 않다.

testdb=> set search_path to "$user",testdb;
SET

여기서 사용된 $user는 PostgreSQL에서 제공하는 특별한 변수로, 현재 세션에서 로그인한 사용자의 이름을 나타내게 된다.

이 변수를 사용하면 각 사용자마다 다른 검색 경로를 설정할 수 있다.

 

"$user": 현재 세션의 사용자 이름을 검색 경로의 첫 번째로 지정.

예를 들어, 만약 현재 세션이 testuser로 로그인되어 있다면, 검색 경로의 첫 번째는 testuser이다.

 

testdb: 데이터베이스 testdb를 검색 경로의 두 번째로 지정.

이는 명시적으로 데이터베이스 이름을 사용하여 검색 경로를 설정.

 

이렇게 검색 경로를 설정해두면 현재 사용자의 스키마(사용자의 이름과 일치하는 스키마)를 먼저 검색하고, 그 다음에는 testdb 데이터베이스의 객체들을 검색할 수 있다.

 

검색 경로를 설정함으로써, 객체를 참조할 때 스키마 이름을 명시하지 않고도 직접 참조할 수 있다.

## 사용자 권한 부여

-- testuser 스키마에 대해 testuser 사용자에게 모든 권한 부여
-- USAGE 권한을 부여하여 스키마 내의 객체에 접근할 수 있도록 함
GRANT USAGE ON SCHEMA testuser TO testuser;

-- testuser 사용자에게 testuser 스키마에서 테이블/시퀀스를 생성할 수 있는 권한 부여
GRANT CREATE ON SCHEMA testuser TO testuser;

-- 스키마 내의 모든 테이블에 대해 SELECT, INSERT, UPDATE, DELETE 권한 부여
-- 필요에 따라 권한 조정
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA testuser TO testuser;

-- 스키마 내의 모든 시퀀스에 대해 USAGE 권한을 부여
GRANT USAGE ON ALL SEQUENCES IN SCHEMA testuser TO testuser;

-- 스키마 내의 모든 함수에 대해 EXECUTE 권한 부여
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA testuser TO testuser;

 

 

# DBeaver를 통해 스키마 확인됨

- 디비버를 통해 스키마를 확인하려면 스키마에 할당해논 유저명과 비밀번호를 통해 연결하면 확인 가능.

이 포스팅에서는 사용자 생성에서 만든 testuser 유저로 연결하면 된다.

host는 로컬에서 돌리면 localhost, 가상 머신 위에 해당 DB를 설치했다면 가상 머신의 IPv4 주소를 확인해보길 !

 

 

 

 

---

참고 : https://velog.io/@easbui/PostgreSQL-%EC%8A%A4%ED%82%A4%EB%A7%88%EC%99%80-%EA%B6%8C%ED%95%9C

https://kimdubi.github.io/postgresql/pg_schema/