postgresql
역할 관리

통사론

• CREATE ROLE name [ [ WITH ] option [ ... ] ]

• CREATE USER name [ [ WITH ] option [ ... ] ]

• where option can be: SUPERUSER | NOSUPERUSER | CREATEDB | NOCREATEDB | CREATEROLE | NOCREATEROLE | CREATEUSER | NOCREATEUSER | INHERIT | NOINHERIT | LOGIN | NOLOGIN | CONNECTION LIMIT connlimit | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' | VALID UNTIL 'timestamp' | IN ROLE role_name [, ...] | IN GROUP role_name [, ...] | ROLE role_name [, ...] | ADMIN role_name [, ...] | USER role_name [, ...] | SYSID uid

비밀번호가있는 사용자 만들기

일반적으로 응용 프로그램에서 기본 데이터베이스 역할 (종종 postgres )을 사용하지 않아야합니다. 대신 낮은 수준의 권한을 가진 사용자를 생성해야합니다. 여기서 우리는 niceusername 이라는 이름을 niceusername 암호에 very-strong-password 암호를 부여합니다.

CREATE ROLE niceusername with PASSWORD 'very-strong-password' LOGIN;

문제는 psql 콘솔에 입력 된 쿼리가 사용자의 홈 디렉토리에있는 .psql_history 기록 파일에 저장되고 PostgreSQL 데이터베이스 서버 로그에 기록되어 비밀번호가 노출 될 수 있다는 것입니다.

이를 피하려면 \password 명령을 사용하여 사용자 암호를 설정하십시오. 명령을 실행하는 사용자가 수퍼 유저 인 경우 현재 암호는 묻지 않습니다. 수퍼 유저의 비밀번호를 변경하려면 수퍼 유저 여야합니다.

CREATE ROLE niceusername with LOGIN;
\password niceusername

역할 및 일치하는 데이터베이스 만들기

주어진 애플리케이션을 지원하기 위해 종종 새로운 역할과 데이터베이스를 만들어 일치시킵니다.

실행할 쉘 명령은 다음과 같습니다.

$ createuser -P blogger
Enter password for the new role: ********
Enter it again: ********

$ createdb -O blogger blogger

이것은 pg_hba.conf 가 올바르게 설정되었다고 가정합니다. 아마도 다음과 같습니다 :

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    sameuser        all             localhost               md5
local   sameuser        all                                     md5

권한 부여 및 취소.

다음 세 사용자가 있다고 가정 해 보겠습니다.

1. 데이터베이스 관리자> 관리자
2. 데이터에 대한 전체 액세스 권한이있는 애플리케이션> read_write
3. 읽기 전용 액세스> read_only

--ACCESS DB
REVOKE CONNECT ON DATABASE nova FROM PUBLIC;
GRANT  CONNECT ON DATABASE nova  TO user;

위의 쿼리를 사용하면 신뢰할 수없는 사용자가 더 이상 데이터베이스에 연결할 수 없습니다.

--ACCESS SCHEMA
REVOKE ALL     ON SCHEMA public FROM PUBLIC;
GRANT  USAGE   ON SCHEMA public  TO user;

다음 쿼리 집합은 인증되지 않은 사용자로부터 모든 권한을 취소하고 read_write 사용자에 대해 제한된 권한 집합을 제공합니다.

--ACCESS TABLES
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC ;
GRANT SELECT                         ON ALL TABLES IN SCHEMA public TO read_only ;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO read_write ;
GRANT ALL                            ON ALL TABLES IN SCHEMA public TO admin ;

--ACCESS SEQUENCES
REVOKE ALL   ON ALL SEQUENCES IN SCHEMA public FROM PUBLIC;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO read_only; -- allows the use of CURRVAL
GRANT UPDATE ON ALL SEQUENCES IN SCHEMA public TO read_write; -- allows the use of NEXTVAL and SETVAL
GRANT USAGE  ON ALL SEQUENCES IN SCHEMA public TO read_write; -- allows the use of CURRVAL and NEXTVAL
GRANT ALL    ON ALL SEQUENCES IN SCHEMA public TO admin;

사용자의 기본 search_path 변경

아래 명령을 사용하여 사용자의 기본 search_path를 설정할 수 있습니다.

1. 기본 스키마를 설정하기 전에 검색 경로를 확인하십시오.

postgres=# \c postgres user1
You are now connected to database "postgres" as user "user1".
postgres=> show search_path;
  search_path   
----------------
 "$user",public
(1 row)

2. alter user 명령으로 search_path 를 설정하여 새 스키마 my_schema 를 추가하십시오.

postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# alter user user1 set search_path='my_schema, "$user", public';
ALTER ROLE

3. 실행 후 결과를 확인하십시오.

postgres=# \c postgres user1
Password for user user1: 
You are now connected to database "postgres" as user "user1".
postgres=> show search_path;
 search_path 
-------------
 my_schema, "$user", public
(1 row)

대안 :

postgres=# set role user1;
postgres=# show search_path;
 search_path 
-------------
 my_schema, "$user", public
(1 row)

나중에 생성되는 객체에 대한 액세스 권한을 부여합니다.

다음 three users 가 있다고 가정 해 보겠습니다.

1. 데이터베이스 admin > admin
2. 데이터에 대한 전체 액세스 권한이있는 애플리케이션> read_write
3. 읽기 전용 액세스> read_only

아래 질의를 사용하면 지정된 스키마에서 앞으로 생성 된 객체에 대한 액세스 권한을 설정할 수 있습니다.

ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT                      ON TABLES TO read_only;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT,INSERT,DELETE,UPDATE ON TABLES TO read_write;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT ALL                         ON TABLES TO admin;

또는 지정된 사용자가 나중에 작성한 오브젝트에 대한 액세스 권한을 설정할 수 있습니다.

ALTER DEFAULT PRIVILEGES FOR ROLE admin GRANT SELECT  ON TABLES TO read_only;

읽기 전용 사용자 만들기

CREATE USER readonly WITH ENCRYPTED PASSWORD 'yourpassword';
GRANT CONNECT ON DATABASE <database_name> to readonly;

GRANT USAGE ON SCHEMA public to readonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;