PostgreSQL에서 읽기 전용 사용자 생성

1. 단일 테이블에 Select/Usage 부여
2. PostgreSQL 버전 9.0+에 대한 다중 테이블/보기
3. 읽기 전용 사용자 생성
4. Postgres를 Redash에 연결

읽기 전용 사용자 데이터베이스는 데이터베이스와 해당 테이블 및 필드에 대한 읽기 전용 액세스가 제한되어 있으므로 보고에 유용합니다.

Postgres 준비 전용 사용자는 사용자가 데이터베이스에서 정보를 읽을 수만 있고 쓸 수는 없음을 의미합니다. 먼저 Postgres 읽기 전용 사용자를 만드는 방법을 알아보겠습니다.

단일 테이블에 Select/Usage 부여

Postgres 사용자에 대한 이력이 있는 경우 GRANT 및 REVOKE 명령을 본 적이 있을 것입니다. 이는 Postgres에서 액세스 제어의 기본 기반임이 입증되었습니다.

다루기가 혼란스러울 때 Postgres는 신중하게 탐색할 수 있도록 다양한 액세스 제어 설정을 제공합니다. 이러한 명령에 대해 주목해야 할 또 다른 사항은 데이터베이스 내에 도입된 테이블과 같은 새 개체에 적용되지 않는다는 것입니다.

문제가 되는 기본 권한이 있으며 이러한 권한은 새 개체가 생성될 때 Postgres에 의해 역할에 적용됩니다. 그러나 ALTER DEFAULT PRIVILEGES 명령을 사용하여 이러한 기본 권한을 수정할 수 있습니다.

따라서 데이터베이스에 CONNECT 권한만 부여하면 사용자는 데이터베이스에 연결할 수 있지만 다른 권한은 없습니다. 여기에서 추가 마일을 이동하여 네임스페이스/스키마에서 USAGE를 부여하고 테이블 및 뷰에서 개별적으로 SELECT를 부여해야 합니다.

다음과 같이 할 수 있습니다.

GRANT CONNECT ON DATABASE databasename TO user; -- databasename = your database name
GRANT USAGE ON SCHEMA public TO user;
GRANT SELECT ON tablename TO user;

PostgreSQL 버전 9.0+에 대한 다중 테이블/보기

최신 버전의 PostgreSQL을 다운로드했다고 가정합니다. 이 경우 다음과 같이 하나씩 입력하는 대신 단일 명령을 사용하여 테이블/뷰 등과 같은 스키마의 개체에 대한 권한을 쉽게 부여할 수 있습니다.

GRANT SELECT ON ALL TABLES IN SCHEMA public TO user;

이 라인은 이미 생성된 테이블에만 영향을 미칩니다. PostgreSQL은 또한 다음과 같이 향후 새 개체에 자동으로 기본 역할을 할당할 수 있는 기능을 제공합니다.

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

처음에 이 명령을 실행한 사용자만이 이러한 기본 역할을 개체 및 테이블에 할당할 수 있다는 점에 유의해야 합니다.

그러나 발급 사용자가 구성원인 모든 역할에 설정할 수 있습니다. 동시에 새 개체를 생성할 때 자신이 구성원인 모든 역할의 기본 권한을 사용하지 않습니다.

데이터베이스에 소유 역할이 있는 접근 방식을 채택할 수 있으므로 특정 소유 역할에서 스키마 변경이 수행되므로 해당 소유 역할에 기본 권한을 할당할 수 있습니다.

9.0 이전의 PostgreSQL 버전에 대한 다중 테이블/보기

이전 버전의 PostgreSQL에서는 이 기능을 쉽게 사용할 수 없지만 Postgres 읽기 전용 사용자를 생성하는 것은 여전히 가능합니다.

길이 다중 테이블 변경 시, 오류를 방지하기 위해 자동 프로세스를 사용하여 각 뷰/테이블에 필요한 GRANT SELECT를 생성하는 것이 좋습니다.

다음과 같은 방식으로 수행됩니다.

SELECT 'GRANT SELECT ON ' || relname || 'TO user; '
FROM pg_class JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
WHERE nspname = 'public' AND relkind IN ('r' ,'v' , 'S');

이 명령줄은 실행될 때 관련 GRANT 명령을 GRANT SELECT에 출력해야 합니다. 공용의 모든 시퀀스, 테이블 및 보기에서 그리고 복사 및 붙여넣기를 위해. 그러나 이미 생성된 테이블에만 적용한다는 점에 유의해야 합니다.

읽기 전용 사용자 생성

다음 코드를 실행하여 읽기 전용 사용자를 만듭니다.

CREATE ROLE Read_Only_User WITH LOGIN PASSWORD 'pass321.'
SUPERUSER INHERIT
NOCREATEDB NOCREATEROLE NOREPLICATION
VALID UNTIL
'infinity';

방금 생성한 Postgres 읽기 전용 사용자에게 권한을 부여할 수 있습니다.

GRANT CONNECTION ON DATABASE databasename TO Read_Only_User;
GRANT USAGE ON SCHEMA public TO Read_Only_User;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO Read_Only_User;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO Read_Only_User;

그러나 이러한 명령은 데이터베이스에 이미 생성된 테이블에서만 작동합니다. 향후 생성되는 모든 새 테이블에 권한을 할당하려면:

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

테이블에 대한 권한을 부여하는 GRANT USAGE 이후의 지침은 원하는 데이터베이스에서 실행되어야 한다는 점에 유의해야 합니다. 그렇지 않으면 권한이 작동하지 않습니다.

이렇게 하려면 \connect databasename;을 입력하면 됩니다. 데이터베이스를 연결합니다.

Postgres를 Redash에 연결

GRANT/USAGE 명령이 없으면 권한이 작동하지 않습니다. 여기서 USAGE는 개체의 권한 요구 사항이 충족되는 한 특정 스키마에 포함된 개체 액세스를 허용합니다. 먼저 읽기 전용 역할을 생성합니다.

CREATE ROLE myapp_readonly; 
GRANT CONNECT ON DATABASE database default TO myapp_readonly;
GRANT USAGE ON SCHEMA myapp TO myapp_readonly;

그런 다음 새로 생성된 역할에 대한 권한을 부여하십시오. 마지막으로 redash를 사용하여 사용자를 생성하고 새로운 읽기 전용 사용자로 Postgres 데이터베이스에 연결한 후 읽기 전용 역할을 할당하십시오.

Postgres 읽기 전용 사용자를 생성하면 데이터베이스를 처리할 때 큰 자산이 됩니다. 그러나 설치된 버전에 따라 작업의 난이도가 다를 수 있습니다.

이 문서에서는 Postgres 읽기 전용 사용자를 제대로 생성할 수 있도록 다양한 버전의 Postgres에 대한 모든 기반을 다룹니다. 이렇게 하면 데이터를 읽을 수 있는 액세스 권한이 있는 사용자를 만들 수 있을 뿐만 아니라 사용자가 편집을 수행할 수 없습니다.