ApsaraDB RDS:ApsaraDB RDS for PostgreSQL の権限管理のベストプラクティス

ステップ 1: 所有者とロールの作成

-- Create the owner. Replace the password with a strong password.
CREATE USER rdspg_owner WITH LOGIN PASSWORD 'asdfy181BASDfadasdbfas';

-- Create the read-write and read-only roles (no login).
CREATE ROLE rdspg_role_readwrite;
CREATE ROLE rdspg_role_readonly;

-- Grant DQL + DML permissions on tables created by rdspg_owner to rdspg_role_readwrite.
ALTER DEFAULT PRIVILEGES FOR ROLE rdspg_owner GRANT ALL ON TABLES TO rdspg_role_readwrite;

-- Grant DQL + DML permissions on sequences created by rdspg_owner to rdspg_role_readwrite.
ALTER DEFAULT PRIVILEGES FOR ROLE rdspg_owner GRANT ALL ON SEQUENCES TO rdspg_role_readwrite;

-- Grant DQL-only permission on tables created by rdspg_owner to rdspg_role_readonly.
ALTER DEFAULT PRIVILEGES FOR ROLE rdspg_owner GRANT SELECT ON TABLES TO rdspg_role_readonly;

ALTER DEFAULT PRIVILEGES は、rdspg_owner が将来作成するすべてのオブジェクトに適用されます。既存のオブジェクトには、個別の GRANT が必要です。

ステップ 2: アプリケーションユーザーの作成

-- Read-write user: inherits DQL + DML permissions from rdspg_role_readwrite.
CREATE USER rdspg_readwrite WITH LOGIN PASSWORD 'dfandfnapSDhf23hbEfabf';
GRANT rdspg_role_readwrite TO rdspg_readwrite;

-- Read-only user: inherits DQL-only permission from rdspg_role_readonly.
CREATE USER rdspg_readonly WITH LOGIN PASSWORD 'F89h912badSHfadsd01zlk';
GRANT rdspg_role_readonly TO rdspg_readonly;

ステップ 3: スキーマの作成とアクセス権の付与

-- Set rdspg_owner as the owner of the rdspg schema.
CREATE SCHEMA rdspg AUTHORIZATION rdspg_owner;

-- Grant schema access to both roles.
GRANT USAGE ON SCHEMA rdspg TO rdspg_role_readwrite;
GRANT USAGE ON SCHEMA rdspg TO rdspg_role_readonly;

rdspg_readwrite と rdspg_readonly は、それぞれのロールを通じてスキーマアクセスを継承します。これらのユーザーへの直接の付与は不要です。

スキーマの追加

同じプロジェクトに2番目のスキーマ (rdspg_1) を追加するには:

CREATE SCHEMA rdspg_1 AUTHORIZATION rdspg_owner;

-- Grant the access permission on the rdspg_2 schema to roles.
-- Grant the permissions to perform DDL CREATE, DROP, and ALTER operations on tables in the rdspg_1 schema.
GRANT USAGE ON SCHEMA rdspg_1 TO rdspg_role_readwrite;
GRANT USAGE ON SCHEMA rdspg_1 TO rdspg_role_readonly;

これらのロールに割り当てられたすべてのユーザーは、新しいスキーマアクセスを自動的に継承します。

プロジェクト間のアクセス権の付与

employee_readwrite ( employee プロジェクトのユーザー) に rdspg プロジェクトへの読み取りアクセス権を付与するには:

-- Grant rdspg_role_readonly permissions to employee_readwrite.
GRANT rdspg_role_readonly TO employee_readwrite;

\du の使用

インスタンスに接続し、\du を実行してロールとロールメンバーシップを一覧表示します。

postgres=> \du
                         List of roles
 Role name              | Attributes    | Member of
------------------------+---------------+----------------------------------------------
 rdspg_owner            | ...           | {}
 rdspg_role_readwrite   | Cannot login  | {}
 rdspg_role_readonly    | Cannot login  | {}
 rdspg_readwrite        | ...           | {rdspg_role_readwrite}
 rdspg_readonly         | ...           | {rdspg_role_readonly}
 employee_readwrite      | ...           | {rdspg_role_readonly,employee_role_readwrite}

employee_readwrite の [所属] 列には rdspg_role_readonly と employee_role_readwrite の両方が表示され、クロスプロジェクトの読み取りアクセスがアクティブであることを確認できます。

SQL の使用

すべてのロールおよびそれらのメンバーシップを対象とした完全な監査を行うには：

SELECT r.rolname, r.rolsuper, r.rolinherit,
  r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
  r.rolconnlimit, r.rolvaliduntil,
  ARRAY(SELECT b.rolname
        FROM pg_catalog.pg_auth_members m
        JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
        WHERE m.member = r.oid) as memberof
, r.rolreplication
, r.rolbypassrls
FROM pg_catalog.pg_roles r
WHERE r.rolname !~ '^pg_'
ORDER BY 1;

rdspg_owner による DDL 操作

rdspg_owner は、所有する任意のスキーマで完全な DDL 権限を保持します。

CREATE TABLE rdspg.test(id bigserial primary key, name text);
CREATE INDEX idx_test_name on rdspg.test(name);

rdspg_readwrite による DML および DQL

rdspg_readwrite はデータの読み書きはできますが、スキーマを変更することはできません。

INSERT INTO rdspg.test (name) VALUES('name0'),('name1');
SELECT id,name FROM rdspg.test LIMIT 1;

-- DDL is blocked:
CREATE TABLE rdspg.test2(id int);
ERROR:  permission denied for schema rdspg

DROP TABLE rdspg.test;
ERROR:  must be owner of table test

ALTER TABLE rdspg.test ADD id2 int;
ERROR:  must be owner of table test

CREATE INDEX idx_test_name on rdspg.test(name);
ERROR:  must be owner of table test

rdspg_readonly による DQL のみ

rdspg_readonly はデータをクエリできますが、変更することはできません。

INSERT INTO rdspg.test (name) VALUES('name0'),('name1');
ERROR:  permission denied for table test

SELECT id,name FROM rdspg.test LIMIT 1;
 id | name
----+-------
  1 | name0
(1 row)