[postgresql] PostgreSQL에서 읽기 전용 사용자를 어떻게 만듭니 까?

단일 테이블에 사용량 부여 / 선택

데이터베이스에 CONNECT 만 부여하면 사용자는 연결할 수 있지만 다른 권한은 없습니다. 다음과 같이 네임 스페이스 (스키마)에 USAGE를 부여하고 테이블과 뷰에 대해 SELECT를 개별적으로 부여해야합니다.

GRANT CONNECT ON DATABASE mydb TO xxx;
-- This assumes you're actually connected to mydb..
GRANT USAGE ON SCHEMA public TO xxx;
GRANT SELECT ON mytable TO xxx;

여러 테이블 / 뷰 (PostgreSQL 9.0+)

최신 버전의 PostgreSQL에서는 하나씩 입력하지 않고 단일 명령을 사용하여 스키마의 모든 테이블 / 뷰 / 등에 대한 권한을 부여 할 수 있습니다.

GRANT SELECT ON ALL TABLES IN SCHEMA public TO xxx;

이것은 이미 작성된 테이블에만 영향을줍니다. 보다 강력하게 는 나중에 새 객체 에 기본 역할을 자동으로 할당 할 수 있습니다.

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

기본적으로 이것은이 명령을 발행 한 사용자가 작성한 오브젝트 (테이블)에만 영향을 미칩니다. 발행하는 사용자가 속한 모든 역할에서도 설정할 수 있습니다. 그러나 새 객체를 만들 때 구성원으로 속한 모든 역할에 대한 기본 권한을 선택하지는 않습니다. 데이터베이스에 소유 역할이있는 접근 방식을 채택하고 해당 소유 역할로 스키마 변경이 수행되는 경우 해당 소유 역할에 기본 권한을 지정해야합니다. IMHO 이것은 약간 혼란스럽고 기능적인 워크 플로를 만들기 위해 실험해야 할 수도 있습니다.

여러 테이블 / 뷰 (9.0 이전의 PostgreSQL 버전)

길고 다중 테이블 변경에서 오류가 발생하지 않도록하려면 다음 ‘자동’프로세스를 사용하여 GRANT SELECT각 테이블 / 뷰에 필요한 항목 을 생성하는 것이 좋습니다 .

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

copy-n-paste 방식으로 모든 테이블, 뷰 및 시퀀스에서 관련 GRANT 명령을 GRANT SELECT에 출력해야합니다. 당연히 이것은 이미 작성된 테이블에만 적용됩니다.

답변

test=> GRANT SELECT ON ALL TABLES IN SCHEMA public TO joeuser;

CREATE ROLE Read_Only_User WITH LOGIN PASSWORD 'Test1234'
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL 'infinity';

GRANT CONNECT ON DATABASE YourDatabaseName 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;

$ sudo -upostgres psql postgres
postgres=# CREATE ROLE readonly WITH LOGIN ENCRYPTED PASSWORD '<USE_A_NICE_STRONG_PASSWORD_PLEASE';
postgres=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

$ echo "hostssl <PUT_DBNAME_HERE> <PUT_READONLY_USERNAME_HERE> 0.0.0.0/0 md5" | sudo tee -a /etc/postgresql/9.2/main/pg_hba.conf
$ sudo service postgresql reload

# This will prevent default users from creating tables
REVOKE CREATE ON SCHEMA public FROM public;

# If you want to grant a write user permission to create tables
# note that superusers will always be able to create tables anyway
GRANT CREATE ON SCHEMA public to writeuser;

# Now create the read-only user
CREATE ROLE readonlyuser WITH LOGIN ENCRYPTED PASSWORD 'strongpassword';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonlyuser;

# You can either grant USAGE to everyone
GRANT USAGE ON SCHEMA public TO public;

# Or grant it just to your read only user
GRANT USAGE ON SCHEMA public TO readonlyuser;

user@server:~$ sudo su - postgres

postgres@server:~$ createuser --interactive 
Enter name of role to add: readonly
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n

postgres@server:~$ psql
psql (10.6 (Ubuntu 10.6-0ubuntu0.18.04.1), server 9.5.14)
Type "help" for help.

postgres=# alter user readonly with password 'readonly';
ALTER ROLE

postgres=# \c target_database
psql (10.6 (Ubuntu 10.6-0ubuntu0.18.04.1), server 9.5.14)
You are now connected to database "target_database" as user "postgres".

target_database=# GRANT CONNECT ON DATABASE target_database TO readonly;
GRANT

target_database=# GRANT USAGE ON SCHEMA public TO readonly ;
GRANT

target_database=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly ;
GRANT

target_database=# ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;
ALTER DEFAULT PRIVILEGES