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

```sh
brew install postgresql
brew services start postgresql

# postgresのCLIを開く
psql postgres
```

DB, table作成
```sql
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');

```

ユーザ作成
```sql
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';
```

それぞれのユーザで接続するコマンド
```rb
psql -U db_user_administrator -d clinic
psql -U db_user_staff -d clinic
psql -U db_user_administrative_staff -d clinic
```


ロール作成。
```sql
CREATE ROLE role_administrator;
CREATE ROLE role_staff;
CREATE ROLE role_administrative_staff;
```
ロールをユーザにアタッチする。以後、全ての権限操作はRoleに対して行う
```sql
GRANT role_administrator TO db_user_administrator;
GRANT role_staff TO db_user_staff;
GRANT role_administrative_staff TO db_user_administrative_staff;
```
各ユーザでログインしてみると、何も見れない
```sql
SELECT * FROM Patients;
SELECT * FROM Appointments;
```

テーブルに権限をつけると、見れる。ここでDELETE権限の有無や、SELECTのみの権限を与えている。今回の肝。注意すべきは、権限を与えているのがdbuserではなくroleだということ。
```sql
-- 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 今の値にアクセスする的な権限が必要なんだってさ。
```sql
-- 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だけは失敗する

```sql
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は全部成功する
```sql
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も同様
```sql
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しか成功しない。

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



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

```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;
```