Redshift의 보안 관리
개요
Redshift 보안관리에 대하여 정리한다. 권한에 대한 관리이기 때문에 superuse 권한이 있는 계정에서 확인하는 것이 좋다. 해당 내용은 Redshfit document를 참조하여 작성되었다.
- 데이터베이스 보안 관리 - Amazon Redshift
- CREATE USER - Amazon Redshift
- ALTER USER - Amazon Redshift
- DROP USER - Amazon Redshift
- create group - Amazon Redshift
- ALTER GROUP - Amazon Redshift
- DROP GROUP - Amazon Redshift
- GRANT - Amazon Redshift
- REVOKE - Amazon Redshift
- ALTER DEFAULT PRIVILEGES - Amazon Redshift
pg_catalog
Redshift는 PostgreSQL을 커스터마이징 하였기때문에 postgreSQL의 pg_catalog정보를 검색하여 해당테이블의 내용을 파악하고 관리하는데 도움이 된다. Redshift공식 문서에 따르면 일부 상이한 내용이 존재할 수 있다고 표시하고 있으나 테이블/권한 등의 체크를 위한 pg_catalog 테이블들은 거의 비슷하게 사용하는 것을 볼 수 있다.
Redshfit접속 후 버전을 확인하면 베이스로 하고 있는 PostgreSQL 버전을 확인할 수 있고 해당 버전의 pg_catalog에 대한 내용은 Redshift 문서보다는 System Catalogs 에서 보다 상세한 내용을 볼 수 있다.
usesuper
해당 권한을 가진 계정은 다른 DBMS의 관리자 계정과 유사하다. 다만 해당 권한을 가지더라도 다음과 같은 동작을 하지는 못한다.
- 스키마보다 상위 개념인 Database를 만들 수 있는 권한(usecreatedb)
- 시스템 카탈로그를 업데이트할 수 있는 권한(usecatupd)
그 밖에 모든 작업을 할 수 있다. 해당 권한이 있는가 없는가는 pg_catalog.pg_user 테이블을 이용하여 확인 할 수 있다.
SELECT
usename,
usecreatedb, -- 데이터베이스 생성권한
usesuper, -- 슈퍼유저
usecatupd -- 카탈로그 업데이트 권한
FROM bi.pg_catalog.pg_user
ORDER BY 4 DESC, 2 DESC, 3 DESC, 1 DESC
usesuper 권한 부여
슈퍼유저는 계정을 생성하거나 생성된 계정을 수정하면서 createuser 권한을 부여해서 만든다. 아래 예제는 adminuser 를 계정이라고 간주한 샘플 쿼리이다.
-- 계정 생성 시 슈퍼유저 부여
create user adminuser createuser password '1234Admin';
-- 이미 만들어진 계정에 슈퍼유저 부여
alter user adminuser createuser;
usesuper
권한 회수는 createuser 권한을 회수함으로 진행한다.
alter user adminuser with nocreateuser;
테이블에 접근하기 위한 최소한의 권한
Redshift에서 계정을 생성 시 테이블을 접근하기 위해서는 아래와 같은 권한이 필요하다.
- 스키마접근 - USAGE
- 테이블 - SELECT
다른 DBMS와는 달리 Redshift의 경우 특정 테이블에 대하여 SELECT 권한을 계정에 부여하여도 스키마에 접근가능한 USAGE권한을 부여하지 않는다면 해당 테이블에 대해서 접근할 수 없다.
따라서 USAGE/SELECT가 모두 있어야만 테이블에 접근할 수 있다. Redshift의 베이스가되는 PostgreSQL의 도큐먼트를 보면 GRANT USAGE에 대하여 아래와 같이 설명하고 있다.
Essentially this allows the grantee to “look up” objects within the schema.
본질적으로 이를 통해 허가자는 스키마 내에서 객체를 “조회”할 수 있습니다.
이러한 권한이 계정에 부여되어있는지는 아래와 같은 SQL을 통하여 확인 가능하다.
SELECT
*
FROM
(
SELECT
schemaname
,usename
,HAS_SCHEMA_PRIVILEGE(usrs.usename, schemaname, 'create') AS cre -- 해당 스키마에서 객체를 생성할 수 있는 권한
,HAS_SCHEMA_PRIVILEGE(usrs.usename, schemaname, 'usage') AS usg -- 해당 스키마에서 객체를 조회할 수 있는 권한
,usrs.usesuper
FROM
(SELECT nspname AS schemaname FROM pg_namespace) AS objs
INNER JOIN
(SELECT * FROM pg_user) AS usrs
ON 1 = 1
ORDER BY schemaname
)
WHERE (cre = true OR usg = true) -- 권한이 하나라도 있어야함
-- and usesuper = false -- 슈퍼 권한 여부 체크
and schemaname = 'schema' -- 스키마
유저와 그룹
Redshift는 개인마다 계정을 발급하더라도 공통으로 권한을 상속받을 수 있는 Group의 개념이 있다.
- 유저는 말 그대로 계정을 의미한다.
- 그룹은 각 유저를 묶는 개념으로, 그룹으로 권한을 부여할 때 사용한다.
- AWS의 Role과 같은 개념이라고 보면 된다.
- 즉, 그룹을 적극 활용할 경우 새로운 유저가 추가 되거나 삭제, 또 권한을 변경해야할 때 그룹에서 빼고, 넣고와 같은 간단한 작업만 하면 되기에 적극 활용하도록 한다.
그룹이 할당받고 있는 권한을 확인하기 위해서는 아래 SQL을 사용할 수 있다.
-- GROUP PRIVILEGES CHECK SQL
SELECT namespace as schema,
item as tab,
case when charindex('r',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then 'Y' else 'N' end as "select",
case when charindex('w',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then 'Y' else 'N' end as "update",
case when charindex('a',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then 'Y' else 'N' end as "insert",
case when charindex('d',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then 'Y' else 'N' end as "delete",
case when charindex('R',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then 'Y' else 'N' end as "rule",
case when charindex('x',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then 'Y' else 'N' end as "references",
case when charindex('t',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then 'Y' else 'N' end as "trigger",
case when charindex('X',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then 'Y' else 'N' end as "execute",
case when charindex('U',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then 'Y' else 'N' end as "usage",
case when charindex('C',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then 'Y' else 'N' end as "create",
case when charindex('T',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then 'Y' else 'N' end as "temporary",
relacl,
'grant ' || substring(
case when charindex('r',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',select ' else '' end
||case when charindex('w',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',update ' else '' end
||case when charindex('a',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',insert ' else '' end
||case when charindex('d',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',delete ' else '' end
||case when charindex('R',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',rule ' else '' end
||case when charindex('x',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',references ' else '' end
||case when charindex('t',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',trigger ' else '' end
||case when charindex('X',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',execute ' else '' end
||case when charindex('U',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',usage ' else '' end
||case when charindex('C',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',create ' else '' end
||case when charindex('T',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',temporary ' else '' end
, 2,10000) || ' on '||namespace||'.'||item ||' to "'||pu.groname||'";' as grantsql
FROM (
SELECT use.usename as subject,
nsp.nspname as namespace,
c.relname as item,
c.relkind as type,
use2.usename as owner,
c.relacl
FROM pg_user use
cross join pg_class c
left join pg_namespace nsp on (c.relnamespace = nsp.oid)
left join pg_user use2 on (c.relowner = use2.usesysid)
WHERE c.relowner = use.usesysid
and nsp.nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema')
ORDER BY subject, namespace, item
) join pg_group pu on array_to_string(relacl, '|') like '%'||pu.groname||'%'
WHERE relacl is not null
and pu.groname='schema_readonly_group' -- group name
-- and schema = 'schema_name' -- schema
-- and tab = 'table_name' -- table name
ORDER BY schema, "tab"
스키마별 권한 설정
- 테이블 생성 시 기본적으로 테이블에 대한 Owner는 테이블을 생성한 계정이 부여받는다.
- 스키마별로 기본 권한 설정을 세팅할 수 있으며, 해당 스키마에 테이블이 생성 시 어떻게 권한을 자동으로 부여하느냐 와 같은 설정을 진행한다.
SELECT * FROM pg_catalog.pg_default_acl해당 테이블을 확인하여 설정을 볼 수 있다- 만약 해당 테이블이 0건인 경우 기본 설정이 없는 것이다. 이경우 테이블 생성 시 마다 유저별로 GRANT를 진행하여야만 볼 수 있게 된다.
테이블을 만들었는데 왜 못보니🤬 - 만약 테이블이 존재하는 스키마에 해당 기본 권한을 부여 시 이미 만들어진 객체에 대해서는 적용되지 않기 때문에, 전체에 대한을 한번 정리하기 위해서는 GRANT/REVOKE와 같은 행위를 한번 진행하여야 한다.
- ,보다 자세한 권한 설정에 대한 구문은 ALTER DEFAULT PRIVILEGES - Amazon Redshift 를 참조하자.
결론 및 테스트
보다 편하게 스키마/테이블에 대한 관리를 위하여 아래와 같은 시나리오를 적용하는 것이 좋을 것으로 보여진다.
- Superuser는 모든 권한이 있기에 별도로 작업할 필요성이 없다.
- 다만 Superuser 계정에 대해서는 해당 계정을 왜 사용하는가와 리스트에 대하여 관리가 필요하다.
- 권한별로 GROUP을 생성한다. 종류는 아래 3가지 용도로 적절하게-
- SELECT ONLY
- SELECT + DML
- SELECT + DML + DDL (단, DROP과 같은 기존 객체는 Owner만 가능하도록)
- 스키마에 기본 권한 설정을 한다.
- 이미 생성된 객체에 대해서는 권한이 부여되지 않기에 GRANT/REVOKE는 별도로 수행한다.
- 신규 테이블을 생성하여서 그룹 권한이 정상적으로 부여되는가 확인한다.
- 신규 계정 생성 후 권한이 없는 상태로 접근 불가능함을 테스트 한다.
- 해당 계정을 권한이 있는 그룹으로 할당 후 접근 가능함을 테스트한다.
댓글남기기