ApsaraDB RDS:Praktik terbaik untuk pengelolaan izin di ApsaraDB RDS for PostgreSQL

Langkah 1: Buat pemilik dan role

-- Buat pemilik. Ganti password dengan password yang kuat.
CREATE USER rdspg_owner WITH LOGIN PASSWORD 'asdfy181BASDfadasdbfas';

-- Buat role baca-tulis dan read-only (tanpa login).
CREATE ROLE rdspg_role_readwrite;
CREATE ROLE rdspg_role_readonly;

-- Berikan izin DQL + DML pada tabel yang dibuat oleh rdspg_owner kepada rdspg_role_readwrite.
ALTER DEFAULT PRIVILEGES FOR ROLE rdspg_owner GRANT ALL ON TABLES TO rdspg_role_readwrite;

-- Berikan izin DQL + DML pada sequence yang dibuat oleh rdspg_owner kepada rdspg_role_readwrite.
ALTER DEFAULT PRIVILEGES FOR ROLE rdspg_owner GRANT ALL ON SEQUENCES TO rdspg_role_readwrite;

-- Berikan izin hanya DQL pada tabel yang dibuat oleh rdspg_owner kepada rdspg_role_readonly.
ALTER DEFAULT PRIVILEGES FOR ROLE rdspg_owner GRANT SELECT ON TABLES TO rdspg_role_readonly;

ALTER DEFAULT PRIVILEGES berlaku untuk semua objek yang akan dibuat oleh rdspg_owner di masa depan. Objek yang sudah ada memerlukan perintah GRANT terpisah.

Langkah 2: Buat user aplikasi

-- User baca-tulis: mewarisi izin DQL + DML dari rdspg_role_readwrite.
CREATE USER rdspg_readwrite WITH LOGIN PASSWORD 'dfandfnapSDhf23hbEfabf';
GRANT rdspg_role_readwrite TO rdspg_readwrite;

-- User read-only: mewarisi izin hanya DQL dari rdspg_role_readonly.
CREATE USER rdspg_readonly WITH LOGIN PASSWORD 'F89h912badSHfadsd01zlk';
GRANT rdspg_role_readonly TO rdspg_readonly;

Langkah 3: Buat skema dan berikan akses

-- Tetapkan rdspg_owner sebagai pemilik skema rdspg.
CREATE SCHEMA rdspg AUTHORIZATION rdspg_owner;

-- Berikan akses skema ke kedua role.
GRANT USAGE ON SCHEMA rdspg TO rdspg_role_readwrite;
GRANT USAGE ON SCHEMA rdspg TO rdspg_role_readonly;

rdspg_readwrite dan rdspg_readonly mewarisi akses skema melalui role mereka. Tidak diperlukan pemberian izin langsung ke user ini.

Tambahkan lebih banyak skema

Untuk menambahkan skema kedua (rdspg_1) ke proyek yang sama:

CREATE SCHEMA rdspg_1 AUTHORIZATION rdspg_owner;

-- Berikan izin akses pada skema rdspg_1 kepada role.
-- Berikan izin untuk melakukan operasi DDL CREATE, DROP, dan ALTER pada tabel di skema rdspg_1.
GRANT USAGE ON SCHEMA rdspg_1 TO rdspg_role_readwrite;
GRANT USAGE ON SCHEMA rdspg_1 TO rdspg_role_readonly;

Semua user yang ditugaskan ke role ini secara otomatis mewarisi akses ke skema baru tersebut.

Berikan akses lintas proyek

Untuk memberikan akses baca ke employee_readwrite (user di proyek employee) terhadap proyek rdspg:

-- Berikan izin rdspg_role_readonly kepada employee_readwrite.
GRANT rdspg_role_readonly TO employee_readwrite;

Menggunakan \du

Hubungkan ke instans Anda dan jalankan \du untuk menampilkan daftar role dan keanggotaan role:

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}

Kolom Member of untuk employee_readwrite menampilkan rdspg_role_readonly dan employee_role_readwrite, yang mengonfirmasi bahwa akses baca lintas proyek aktif.

Menggunakan SQL

Untuk audit lengkap semua role dan keanggotaannya:

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;

Operasi DDL oleh rdspg_owner

rdspg_owner memiliki izin DDL penuh di skema mana pun yang dimilikinya:

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

DML dan DQL oleh rdspg_readwrite

rdspg_readwrite dapat membaca dan menulis data tetapi tidak dapat memodifikasi skema:

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

-- DDL diblokir:
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

Hanya DQL oleh rdspg_readonly

rdspg_readonly dapat melakukan kueri tetapi tidak dapat memodifikasi data:

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)