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

特権の付与と取り消し。

たとえば、3人のユーザーがいるとします。

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;