코드노예

PostgreSQL에서는 Role을 정의해서 DB 이용에 대한 권한을 부여합니다. 다른 DB의 user와 비슷한 개념이지만 좀 더 포괄적으로 사용할 수 있습니다.

여기서는 testuser 라는 role을 생성할 예정이고, 해당 role에게 testdb에 접속해서 public schema 에 있는 테이블을 사용하는 권한을 부여하는 방법에 대해서 알아보도록 하겠습니다.

ROLE 생성하기

Role 생성은 CREATE ROLE statement를 사용합니다.

CREATE ROLE testuser;

cf. role 이름은 '소문자'만 허용이 되는 것 같습니다. 생성시에 이름을 대문자로 입력해도 '소문자'로 생성이 되네요..

ROLE 확인하기

생성된 Role은 다음의 statement로 확인이 가능합니다.

SELECT rolname FROM pg_roles;

혹은 psql 에서는 아래 command를 이용할 수도 있습니다.

\du

ROLE 에 속성 추가/변경 하기

위에서 생성된 role은 아무런 권한이 부여되지 않았기 때문에, 아무것도 할 수 없습니다. 로그인 조차 할 수 없기 때문에, 필요한 속성을 추가해 줘야 합니다. ALTER ROLE statement 를 이용하여 지정한 password를 이용해서 로그인을 할 수 있는 권한을 부여합니다.

ALTER ROLE testuser [WITH] LOGIN PASSWORD 'password';

WITH는 LOGIN, PASSWORD 같은 옵션 앞에 붙인다고 되어 있는데, 생략해도 되는 것 같습니다. ( PostgreSQL 16 기준)

추가할 수 있는 속성에 대해서는 PostgreSQL manual 페이지를 참조해 주세요.

https://www.postgresql.org/docs/current/sql-alterrole.html

참고로 PostgreSQL에서, CREATE USER는 CREATE ROLE 의 alias 로 쓰이고 있어서, 아래 구문은 위와 같습니다.

ALTER USER testuser [WITH] LOGIN PASSWORD 'password';

참고로 LOGIN 속성을 제거 하고 싶다면, NOLOGIN 속성을 부여하면 됩니다.

Password 변경은 ALTER ROLE 혹은 ALTER USER statement로 PASSWORD 를 다시 지정해 주면 됩니다.

ALTER ROLE testuser PASSWORD 'new_password';

DB 연결 권한 추가하기

Role은 전체 PostgresSQL 시스템에서 유저를 관리하는 개념이었고, 개별 database에 접근하는 권한은 각 database 별도로 따로 지정해 줘야 합니다. 먼저 DB 연결 권한을 부여합니다.

GRANT CONNECT ON DATABASE testdb TO testuser;

DB 사용 권한 추가하기

다음으로 DB 사용 권한을 부여해 줍니다. DB 사용 권한은 schema 별로 지정해 줘야 합니다. 여기서는 public schema에 대한 권한을 부여해 주겠습니다. DB 사용 권한 부여시에는 해당 DB에 접속한 상태에서 구문을 입력합니다.

GRANT USAGE ON SCHEMA public TO testuser;

이번에는 select 구문 사용 권한을 추가해 봅니다.

GRANT SELECT ON ALL TABLES IN SCHEMA public TO testuser;

select 이외에도 insert, update, delete 등의 권한을 추가해 줄 수 있습니다.

그 밖에 자세한 권한에 대한 정보는 아래 링크를 참고하시면 됩니다.

https://www.postgresql.org/docs/current/sql-grant.html

위의 구문으로 DB 테이블에 대한 권한을 부여하면, 권한을 줄 때 당시에 있던 테이블에 대해서만 권한이 적용되며, 나중에 추가한 테이블에 대한 권한은 자동으로 추가되지 않습니다. 모든 새로 생성된 테이블에 대한 권한을 자동으로 추가해 주려면 아래와 같은 구문을 실행해 줍니다.

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO testuser;

DB 사용 권한 제거하기

위에서 생성한 DB 권한은 Revoke statement로 제거할 수 있습니다.

REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM testuser;

cf.  테이블 별 사용 권한 확인

\dp <tablename>

ROLE 제거하기

위에서 생성했던 Role은 DROP ROLE statement로 제거할 수 있습니다. 하지만 그 전에 부여했던 모든 권한을 DB별로 모두 제거해 줘야 합니다.

권한 제거는 DROP OWNED statement를 이용합니다. 아래와 같이 사용하면 됩니다.

DROP OWNED BY testuser CASCADE;

그리고 나서 Role을 제거합니다.

DROP ROLE testuser;

아래 URL 내용 참고.

https://stackoverflow.com/questions/3023583/how-to-quickly-drop-a-user-with-existing-privileges