memo: postgresqlのroleを使って見たmemo

DBMS の role を試したかったんだけどmysqlがイマイチらしいのでpostgreって見た

目標の Roles

Role	Patients Table	Appointments Table
Administrator	Read, Insert, Update, Delete	Read, Insert, Update, Delete
Medical Staff	Read, Insert, Update	Read, Insert, Update
Administrative Staff	Read	Read, Insert, Update

Install PostgresSQL

brew install postgresql
brew services start postgresql

# postgresのCLIを開く
psql postgres

DB, table作成

CREATE DATABASE clinic;
-- これが use clinic と同じもの。コネクション繋ぐ的な感じらしい
\c clinic;

CREATE TABLE Patients (
  PatientID SERIAL PRIMARY KEY,
  Name VARCHAR(64) NOT NULL,
  ContactDetails VARCHAR(100),
  MedicalHistory TEXT
);

CREATE TABLE Staff (
  StaffID SERIAL PRIMARY KEY,
  Name VARCHAR(64)
);

CREATE TABLE Appointments (
  AppointmentID SERIAL PRIMARY KEY,
  PatientID INT REFERENCES Patients(PatientID),
  StaffID INT REFERENCES Staff(StaffID),
  AppointmentDateTime TIMESTAMP WITH TIME ZONE,
  Details TEXT
);


INSERT INTO Patients (Name, ContactDetails, MedicalHistory)
VALUES
  ('Bob Lead', '123 street A', '...'),
  ('John Black', '456 street B', '...'),
  ('Michael Clapton', '789 street C', '...');


INSERT INTO Staff (Name)
VALUES
  ('Dr. Paul McCartney'),
  ('Dr. George Harrison'),
  ('Nurse John Doe');



INSERT INTO Appointments (PatientID, StaffID, AppointmentDateTime, Details)
VALUES
  (1, 1, '2024-08-04 12:00:00', 'treatment'),
  (2, 2, '2024-08-05 11:30:00', 'treatment'),
  (3, 1, '2024-08-06 13:00:00', 'treatment');

ユーザ作成

CREATE USER db_user_administrator WITH PASSWORD 'password';
CREATE USER db_user_staff WITH PASSWORD 'password';
CREATE USER db_user_administrative_staff WITH PASSWORD 'password';

それぞれのユーザで接続するコマンド

psql -U db_user_administrator -d clinic
psql -U db_user_staff -d clinic
psql -U db_user_administrative_staff -d clinic

ロール作成。

CREATE ROLE role_administrator;
CREATE ROLE role_staff;
CREATE ROLE role_administrative_staff;

ロールをユーザにアタッチする。以後、全ての権限操作はRoleに対して行う

GRANT role_administrator TO db_user_administrator;
GRANT role_staff TO db_user_staff;
GRANT role_administrative_staff TO db_user_administrative_staff;

各ユーザでログインしてみると、何も見れない

SELECT * FROM Patients;
SELECT * FROM Appointments;

テーブルに権限をつけると、見れる。ここでDELETE権限の有無や、SELECTのみの権限を与えている。今回の肝。注意すべきは、権限を与えているのがdbuserではなくroleだということ。

-- Table priviledge
-- role_administrator
GRANT ALL ON public.Patients TO role_administrator;
GRANT ALL ON public.Appointments TO role_administrator;
-- role_staff
GRANT SELECT, INSERT, UPDATE ON public.Patients TO role_staff;
GRANT SELECT, INSERT, UPDATE ON public.Appointments TO role_staff;
-- role_administrative_staff
GRANT SELECT ON public.Patients TO role_administrative_staff;
GRANT SELECT, INSERT, UPDATE ON public.Appointments TO role_administrative_staff;

これはpostgres特有のGRANT. INSERTするときにauto-incrementの次の値 or 今の値にアクセスする的な権限が必要なんだってさ。

-- postgresql 
This sequence is automatically created by PostgreSQL to manage the PatientID values for the Patients table.
GRANT USAGE ON SEQUENCE patients_patientid_seq TO role_administrator;
GRANT USAGE ON SEQUENCE patients_patientid_seq TO role_staff;
GRANT USAGE ON SEQUENCE appointments_appointmentid_seq TO role_administrator;
GRANT USAGE ON SEQUENCE appointments_appointmentid_seq TO role_staff;
GRANT USAGE ON SEQUENCE appointments_appointmentid_seq TO db_user_administrative_staff;

PatientsにINSERTすると、administrative_staffだけは失敗する

INSERT INTO Patients (Name, ContactDetails, MedicalHistory)
VALUES
  ('New Patient1', '111 street D', '...');
INSERT INTO Patients (Name, ContactDetails, MedicalHistory)
VALUES
  ('New Patient2', '222 street D', '...');
INSERT INTO Patients (Name, ContactDetails, MedicalHistory)
VALUES
  ('New Patient3', '333 street D', '...');

一方apointmentsは全部成功する

INSERT INTO Appointments (PatientID, StaffID, AppointmentDateTime, Details)
VALUES
  (4, 1, '2024-08-07 11:00:00', 'treatment');

INSERT INTO Appointments (PatientID, StaffID, AppointmentDateTime, Details)
VALUES
  (4, 2, '2024-08-08 11:00:00', 'treatment');

INSERT INTO Appointments (PatientID, StaffID, AppointmentDateTime, Details)
VALUES
  (4, 3, '2024-08-09 11:00:00', 'treatment');

UPDATEも同様

UPDATE Patients SET ContactDetails = 'Updated 1' WHERE PatientID = 4;
UPDATE Patients SET ContactDetails = 'Updated 2' WHERE PatientID = 5;
UPDATE Patients SET ContactDetails = 'Updated 3' WHERE PatientID = 6;
UPDATE Appointments SET Details = 'Updated 1' WHERE AppointmentID = 4;
UPDATE Appointments SET Details = 'Updated 2' WHERE AppointmentID = 5;
UPDATE Appointments SET Details = 'Updated 3' WHERE AppointmentID = 6;

DELETEはadministratorしか成功しない。

DELETE FROM Patients WHERE PatientID = 6;
DELETE FROM Appointments WHERE AppointmentID = 1;

これはやり直す時用のメモSQL

DROP ROLE role_administrator;
DROP ROLE role_staff;
DROP ROLE role_administrative_staff;

DROP USER db_user_administrator;
DROP USER db_user_staff;
DROP USER db_user_administrative_staff;

 REVOKE ALL ON public.patients FROM role_administrator;
 REVOKE ALL ON public.appointments FROM role_administrator;
 REVOKE ALL ON public.patients FROM role_staff;
 REVOKE ALL ON public.appointments FROM role_staff;
 REVOKE ALL ON public.patients FROM role_administrative_staff;
 REVOKE ALL ON public.appointments FROM role_administrative_staff;