1. 行級安全性 (RLS) 概述
行級安全性 (RLS) 允許 PostgreSQL 控制使用者可以查看或操作表中的哪些行。這是醫療保健的關鍵功能，因為：
- A醫生只看他的病人
- 內科護理師只看內科患者
- 醫院管理員查看該醫院的所有患者
- 患者只能看到自己的記錄
1.1。 RLS 與應用程式層級過濾
應用程式級過濾（不安全）：
SELECT * FROM patients WHERE doctor_id = :currentDoctor- 開發人員忘記了 WHERE 子句 → 資料外洩
- SQL注入繞過WHERE子句
- 直接資料庫存取繞過應用程式邏輯
- 報告工具繞過應用程式
行級安全性（SAFETY）：
SELECT * FROM patients;— 僅傳回允許的行- 資料庫可執行，無法繞過
- 對應用程式透明
- 適用於所有工具（psql、報表、BI）
- 縱深防禦
1.2。醫療保健 RLS 架構
請求流程：
- Quarkus App — 提取 JWT 聲明（user_id、角色、部門、hospital_id）
- SET LOCAL 會話變數 (
app.current_user_id,app.current_role,app.current_dept,app.hospital_id） - 執行查詢 —
SELECT * FROM patients; - PostgreSQL RLS 策略 評估
current_setting('app.current_role'),current_setting('app.current_dept') - 僅傳回允許的行 — 按 RLS 過濾的結果
2. 基本 RLS 設定
2.1。醫療保健資料庫架構
-- =====================================================
-- HEALTHCARE DATABASE SCHEMA VỚI RLS SUPPORT
-- =====================================================
-- Bảng hospitals
CREATE TABLE patient_schema.hospitals (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name VARCHAR(255) NOT NULL,
code VARCHAR(20) UNIQUE NOT NULL,
address TEXT,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Bảng departments
CREATE TABLE patient_schema.departments (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
hospital_id UUID NOT NULL REFERENCES patient_schema.hospitals(id),
name VARCHAR(100) NOT NULL,
code VARCHAR(20) NOT NULL,
UNIQUE(hospital_id, code)
);
-- Bảng staff (bác sĩ, y tá, admin)
CREATE TABLE patient_schema.staff (
id UUID PRIMARY KEY, -- Maps to Keycloak user ID
hospital_id UUID NOT NULL REFERENCES patient_schema.hospitals(id),
department_id UUID REFERENCES patient_schema.departments(id),
full_name VARCHAR(255) NOT NULL,
role VARCHAR(50) NOT NULL, -- 'doctor', 'nurse', 'admin', 'lab_tech'
license_number VARCHAR(50),
is_active BOOLEAN DEFAULT true,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Bảng patients
CREATE TABLE patient_schema.patients (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
hospital_id UUID NOT NULL REFERENCES patient_schema.hospitals(id),
department_id UUID REFERENCES patient_schema.departments(id),
-- PHI fields (sẽ encrypt ở section sau)
full_name_encrypted BYTEA NOT NULL,
date_of_birth_encrypted BYTEA,
national_id_encrypted BYTEA,
phone_encrypted BYTEA,
-- Non-PHI metadata
patient_code VARCHAR(20) NOT NULL,
admission_date DATE,
discharge_date DATE,
status VARCHAR(20) DEFAULT 'active',
-- Search hashes
national_id_hash TEXT UNIQUE,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
-- Bảng doctor-patient relationship
CREATE TABLE patient_schema.doctor_patient (
doctor_id UUID NOT NULL REFERENCES patient_schema.staff(id),
patient_id UUID NOT NULL REFERENCES patient_schema.patients(id),
relationship_type VARCHAR(30) NOT NULL, -- 'primary', 'consulting', 'referred'
start_date DATE NOT NULL DEFAULT CURRENT_DATE,
end_date DATE,
is_active BOOLEAN DEFAULT true,
PRIMARY KEY (doctor_id, patient_id)
);
-- Bảng medical records
CREATE TABLE patient_schema.medical_records (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
patient_id UUID NOT NULL REFERENCES patient_schema.patients(id),
doctor_id UUID NOT NULL REFERENCES patient_schema.staff(id),
department_id UUID REFERENCES patient_schema.departments(id),
hospital_id UUID NOT NULL REFERENCES patient_schema.hospitals(id),
record_type VARCHAR(50) NOT NULL, -- 'examination', 'lab_order', 'prescription'
-- Encrypted content
content_encrypted BYTEA NOT NULL,
encryption_key_id TEXT NOT NULL,
-- Metadata
record_date TIMESTAMPTZ DEFAULT NOW(),
icd_code VARCHAR(10),
is_sensitive BOOLEAN DEFAULT false, -- HIV, psychiatric, etc.
created_at TIMESTAMPTZ DEFAULT NOW()
);
2.2。啟用 RLS
-- =====================================================
-- ENABLE ROW-LEVEL SECURITY
-- =====================================================
-- Enable RLS trên tất cả sensitive tables
ALTER TABLE patient_schema.patients ENABLE ROW LEVEL SECURITY;
ALTER TABLE patient_schema.medical_records ENABLE ROW LEVEL SECURITY;
ALTER TABLE patient_schema.doctor_patient ENABLE ROW LEVEL SECURITY;
-- FORCE RLS cho table owner (mặc định owner bypass RLS)
ALTER TABLE patient_schema.patients FORCE ROW LEVEL SECURITY;
ALTER TABLE patient_schema.medical_records FORCE ROW LEVEL SECURITY;
ALTER TABLE patient_schema.doctor_patient FORCE ROW LEVEL SECURITY;
-- Verify RLS enabled
SELECT
schemaname,
tablename,
rowsecurity AS rls_enabled,
forcerowsecurity AS rls_forced
FROM pg_tables
WHERE schemaname = 'patient_schema'
ORDER BY tablename;
3. RLS 患者資料隔離策略
3.1。醫院級隔離
-- =====================================================
-- POLICY 1: Hospital Isolation
-- Mỗi user chỉ thấy data của bệnh viện mình
-- =====================================================
CREATE POLICY hospital_isolation ON patient_schema.patients
USING (
hospital_id = current_setting('app.hospital_id', true)::UUID
);
-- Áp dụng cho medical_records
CREATE POLICY hospital_isolation ON patient_schema.medical_records
USING (
hospital_id = current_setting('app.hospital_id', true)::UUID
);
3.2。基於角色的策略
-- =====================================================
-- POLICY 2: Doctor - chỉ thấy bệnh nhân của mình
-- =====================================================
-- Drop policy cũ nếu có
DROP POLICY IF EXISTS hospital_isolation ON patient_schema.patients;
-- Policy tổng hợp cho patients table
CREATE POLICY patient_access ON patient_schema.patients
FOR ALL
USING (
-- Hospital isolation (tất cả roles)
hospital_id = current_setting('app.hospital_id', true)::UUID
AND (
-- CASE 1: Admin - thấy tất cả bệnh nhân trong bệnh viện
current_setting('app.current_role', true) = 'admin'
-- CASE 2: Doctor - thấy bệnh nhân có relationship
OR (
current_setting('app.current_role', true) = 'doctor'
AND EXISTS (
SELECT 1 FROM patient_schema.doctor_patient dp
WHERE dp.patient_id = patients.id
AND dp.doctor_id = current_setting('app.current_user_id', true)::UUID
AND dp.is_active = true
)
)
-- CASE 3: Nurse - thấy bệnh nhân cùng khoa
OR (
current_setting('app.current_role', true) = 'nurse'
AND department_id = current_setting('app.current_dept_id', true)::UUID
)
-- CASE 4: Patient - chỉ thấy hồ sơ của mình
OR (
current_setting('app.current_role', true) = 'patient'
AND id = current_setting('app.current_patient_id', true)::UUID
)
)
)
WITH CHECK (
-- INSERT/UPDATE: chỉ cho phép trong hospital của mình
hospital_id = current_setting('app.hospital_id', true)::UUID
);
3.3。醫療記錄政策
-- =====================================================
-- POLICY cho Medical Records
-- Chi tiết hơn vì chứa sensitive clinical data
-- =====================================================
CREATE POLICY medical_record_access ON patient_schema.medical_records
FOR SELECT
USING (
hospital_id = current_setting('app.hospital_id', true)::UUID
AND (
-- Admin: tất cả records trong bệnh viện
current_setting('app.current_role', true) = 'admin'
-- Doctor: records của bệnh nhân mình (qua doctor_patient)
OR (
current_setting('app.current_role', true) = 'doctor'
AND (
-- Records tạo bởi doctor này
doctor_id = current_setting('app.current_user_id', true)::UUID
-- HOẶC bệnh nhân có relationship
OR EXISTS (
SELECT 1 FROM patient_schema.doctor_patient dp
WHERE dp.patient_id = medical_records.patient_id
AND dp.doctor_id = current_setting('app.current_user_id', true)::UUID
AND dp.is_active = true
)
)
)
-- Nurse: records trong department (trừ sensitive)
OR (
current_setting('app.current_role', true) = 'nurse'
AND department_id = current_setting('app.current_dept_id', true)::UUID
AND is_sensitive = false
)
-- Lab tech: chỉ lab orders
OR (
current_setting('app.current_role', true) = 'lab_tech'
AND record_type = 'lab_order'
AND department_id = current_setting('app.current_dept_id', true)::UUID
)
-- Patient Portal: chỉ records của mình (trừ internal notes)
OR (
current_setting('app.current_role', true) = 'patient'
AND patient_id = current_setting('app.current_patient_id', true)::UUID
AND record_type != 'internal_note'
)
)
);
-- INSERT policy cho medical records
CREATE POLICY medical_record_insert ON patient_schema.medical_records
FOR INSERT
WITH CHECK (
hospital_id = current_setting('app.hospital_id', true)::UUID
AND doctor_id = current_setting('app.current_user_id', true)::UUID
AND current_setting('app.current_role', true) IN ('doctor', 'nurse')
);
-- UPDATE policy - chỉ doctor tạo mới được sửa
CREATE POLICY medical_record_update ON patient_schema.medical_records
FOR UPDATE
USING (
doctor_id = current_setting('app.current_user_id', true)::UUID
AND current_setting('app.current_role', true) = 'doctor'
-- Chỉ sửa trong vòng 24 giờ
AND created_at > NOW() - INTERVAL '24 hours'
);
-- KHÔNG có DELETE policy - medical records không được xóa
3.4。緊急通道（打破玻璃）
-- =====================================================
-- EMERGENCY ACCESS POLICY
-- Cho phép truy cập khẩn cấp khi bệnh nhân nguy kịch
-- =====================================================
-- Bảng ghi log emergency access
CREATE TABLE patient_schema.emergency_access_log (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
staff_id UUID NOT NULL,
patient_id UUID NOT NULL,
reason TEXT NOT NULL,
approved_by UUID,
access_time TIMESTAMPTZ DEFAULT NOW(),
expires_at TIMESTAMPTZ DEFAULT NOW() + INTERVAL '4 hours'
);
-- Function kiểm tra emergency access
CREATE OR REPLACE FUNCTION patient_schema.has_emergency_access(
p_patient_id UUID
) RETURNS BOOLEAN AS $$
BEGIN
RETURN EXISTS (
SELECT 1 FROM patient_schema.emergency_access_log
WHERE staff_id = current_setting('app.current_user_id', true)::UUID
AND patient_id = p_patient_id
AND expires_at > NOW()
);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER STABLE;
-- Thêm emergency access vào patient policy
DROP POLICY IF EXISTS patient_access ON patient_schema.patients;
CREATE POLICY patient_access ON patient_schema.patients
FOR ALL
USING (
hospital_id = current_setting('app.hospital_id', true)::UUID
AND (
-- Các rules bình thường (admin, doctor, nurse, patient)
current_setting('app.current_role', true) = 'admin'
OR (
current_setting('app.current_role', true) = 'doctor'
AND EXISTS (
SELECT 1 FROM patient_schema.doctor_patient dp
WHERE dp.patient_id = patients.id
AND dp.doctor_id = current_setting('app.current_user_id', true)::UUID
AND dp.is_active = true
)
)
OR (
current_setting('app.current_role', true) = 'nurse'
AND department_id = current_setting('app.current_dept_id', true)::UUID
)
OR (
current_setting('app.current_role', true) = 'patient'
AND id = current_setting('app.current_patient_id', true)::UUID
)
-- EMERGENCY ACCESS
OR patient_schema.has_emergency_access(patients.id)
)
)
WITH CHECK (
hospital_id = current_setting('app.hospital_id', true)::UUID
);
4. 在 Quarkus 中將 RLS 與 Keycloak JWT 集成
4.1。連接攔截器
// =====================================================
// RLS CONNECTION INTERCEPTOR
// Set JWT claims as PostgreSQL session variables
// =====================================================
@ApplicationScoped
public class RlsConnectionInterceptor {
@Inject
SecurityIdentity securityIdentity;
@Inject
JsonWebToken jwt;
@Inject
AgroalDataSource dataSource;
/**
* Wrapper method - set RLS variables trước mỗi query
*/
public <T> T executeWithRls(Function<Connection, T> action) {
try (Connection conn = dataSource.getConnection()) {
setRlsVariables(conn);
return action.apply(conn);
} catch (SQLException e) {
throw new RuntimeException("Database operation failed", e);
}
}
/**
* Set session variables từ JWT claims
*/
private void setRlsVariables(Connection conn) throws SQLException {
// Extract claims từ Keycloak JWT
String userId = jwt.getSubject();
String role = extractPrimaryRole();
String hospitalId = jwt.getClaim("hospital_id");
String departmentId = jwt.getClaim("department_id");
String patientId = jwt.getClaim("patient_id");
// Set session variables cho current transaction
try (Statement stmt = conn.createStatement()) {
// SET LOCAL chỉ apply cho transaction hiện tại
stmt.execute("SET LOCAL app.current_user_id = " + quote(userId));
stmt.execute("SET LOCAL app.current_role = " + quote(role));
stmt.execute("SET LOCAL app.hospital_id = " + quote(hospitalId));
if (departmentId != null) {
stmt.execute("SET LOCAL app.current_dept_id = " + quote(departmentId));
}
if (patientId != null) {
stmt.execute("SET LOCAL app.current_patient_id = " + quote(patientId));
}
}
}
private String extractPrimaryRole() {
// Priority: doctor > nurse > lab_tech > admin > patient
Set<String> roles = securityIdentity.getRoles();
if (roles.contains("doctor")) return "doctor";
if (roles.contains("nurse")) return "nurse";
if (roles.contains("lab_tech")) return "lab_tech";
if (roles.contains("admin")) return "admin";
if (roles.contains("patient")) return "patient";
return "unknown";
}
private String quote(String value) {
if (value == null) return "''";
// Prevent SQL injection trong SET LOCAL
return "'" + value.replace("'", "''") + "'";
}
}
4.2。 Hibernate 攔截器（替代）
// =====================================================
// HIBERNATE CONNECTION PROVIDER VỚI RLS
// Tự động set JWT claims khi Hibernate lấy connection
// =====================================================
@ApplicationScoped
public class RlsAgroalOpenConnectionObserver
implements AgroalDataSourceConfigurationSupplier {
@Inject
JsonWebToken jwt;
@Inject
SecurityIdentity identity;
/**
* Sử dụng Agroal connection customizer
*/
@Produces
@ApplicationScoped
public ConnectionCustomizer connectionCustomizer() {
return new ConnectionCustomizer() {
@Override
public void onAcquire(Connection connection) throws SQLException {
// Set RLS variables mỗi khi connection được acquire từ pool
if (identity != null && !identity.isAnonymous()) {
setRlsContext(connection);
}
}
@Override
public void onReturn(Connection connection) throws SQLException {
// Reset variables khi trả connection về pool
try (Statement stmt = connection.createStatement()) {
stmt.execute("RESET ALL");
}
}
};
}
private void setRlsContext(Connection conn) throws SQLException {
try (Statement stmt = conn.createStatement()) {
stmt.execute(String.format(
"SELECT set_config('app.current_user_id', '%s', true), " +
" set_config('app.current_role', '%s', true), " +
" set_config('app.hospital_id', '%s', true)",
sanitize(jwt.getSubject()),
sanitize(extractRole()),
sanitize(jwt.getClaim("hospital_id"))
));
}
}
private String sanitize(String input) {
if (input == null) return "";
// Remove any characters that could cause SQL injection
return input.replaceAll("[^a-zA-Z0-9\\-]", "");
}
private String extractRole() {
if (identity.getRoles().contains("doctor")) return "doctor";
if (identity.getRoles().contains("nurse")) return "nurse";
if (identity.getRoles().contains("admin")) return "admin";
if (identity.getRoles().contains("patient")) return "patient";
return "unknown";
}
}
4.3。 REST 端點範例
@Path("/api/patients")
@Authenticated
@Produces(MediaType.APPLICATION_JSON)
public class PatientResource {
@Inject
RlsConnectionInterceptor rlsInterceptor;
@Inject
PatientService patientService;
/**
* GET /api/patients
* RLS tự động filter dựa trên JWT claims
* - Doctor: chỉ thấy patients có relationship
* - Nurse: chỉ thấy patients cùng department
* - Admin: thấy tất cả patients trong hospital
*/
@GET
public List<PatientDto> listPatients(
@QueryParam("page") @DefaultValue("0") int page,
@QueryParam("size") @DefaultValue("20") int size) {
return rlsInterceptor.executeWithRls(conn -> {
// Query đơn giản - RLS tự động filter
try (PreparedStatement ps = conn.prepareStatement(
"SELECT id, patient_code, status, admission_date, " +
" pgp_sym_decrypt(full_name_encrypted, ?) AS full_name " +
"FROM patient_schema.patients " +
"ORDER BY admission_date DESC " +
"LIMIT ? OFFSET ?")) {
ps.setString(1, getEncryptionKey());
ps.setInt(2, size);
ps.setInt(3, page * size);
ResultSet rs = ps.executeQuery();
List<PatientDto> patients = new ArrayList<>();
while (rs.next()) {
patients.add(new PatientDto(
rs.getString("id"),
rs.getString("patient_code"),
rs.getString("full_name"),
rs.getString("status"),
rs.getDate("admission_date")
));
}
return patients;
} catch (SQLException e) {
throw new RuntimeException(e);
}
});
}
}
5. PHI 欄位的列級加密
5.1。加密輔助函數
-- =====================================================
-- HELPER FUNCTIONS CHO PHI ENCRYPTION
-- =====================================================
-- Function encrypt PHI field
CREATE OR REPLACE FUNCTION patient_schema.encrypt_phi(
p_plaintext TEXT,
p_key TEXT DEFAULT current_setting('app.encryption_key', true)
) RETURNS BYTEA AS $$
BEGIN
IF p_plaintext IS NULL THEN
RETURN NULL;
END IF;
RETURN pgp_sym_encrypt(
p_plaintext,
p_key,
'compress-algo=2, cipher-algo=aes256'
);
END;
$$ LANGUAGE plpgsql IMMUTABLE;
-- Function decrypt PHI field
CREATE OR REPLACE FUNCTION patient_schema.decrypt_phi(
p_encrypted BYTEA,
p_key TEXT DEFAULT current_setting('app.encryption_key', true)
) RETURNS TEXT AS $$
BEGIN
IF p_encrypted IS NULL THEN
RETURN NULL;
END IF;
RETURN pgp_sym_decrypt(p_encrypted, p_key);
EXCEPTION
WHEN OTHERS THEN
-- Log decryption failure nhưng không expose error details
RAISE WARNING 'PHI decryption failed for current user';
RETURN '[ENCRYPTED]';
END;
$$ LANGUAGE plpgsql IMMUTABLE;
-- Function tạo search hash
CREATE OR REPLACE FUNCTION patient_schema.hash_for_search(
p_value TEXT
) RETURNS TEXT AS $$
BEGIN
IF p_value IS NULL THEN
RETURN NULL;
END IF;
-- Normalize: lowercase, trim spaces
RETURN encode(
digest(lower(trim(p_value)), 'sha256'),
'hex'
);
END;
$$ LANGUAGE plpgsql IMMUTABLE;
5.2。帶有加密的 INSERT/SELECT
-- === INSERT patient với encrypted PHI ===
-- Encryption key được set qua session variable
SET LOCAL app.encryption_key = 'key-from-vault';
INSERT INTO patient_schema.patients (
hospital_id,
department_id,
full_name_encrypted,
date_of_birth_encrypted,
national_id_encrypted,
phone_encrypted,
patient_code,
national_id_hash,
admission_date,
status
) VALUES (
'550e8400-e29b-41d4-a716-446655440000',
'660e8400-e29b-41d4-a716-446655440000',
patient_schema.encrypt_phi('Nguyễn Thị B'),
patient_schema.encrypt_phi('1985-03-20'),
patient_schema.encrypt_phi('079085001234'),
patient_schema.encrypt_phi('0912345678'),
'BN-2025-00001',
patient_schema.hash_for_search('079085001234'),
CURRENT_DATE,
'active'
);
-- === SELECT với decryption ===
SET LOCAL app.encryption_key = 'key-from-vault';
SELECT
id,
patient_code,
patient_schema.decrypt_phi(full_name_encrypted) AS full_name,
patient_schema.decrypt_phi(date_of_birth_encrypted) AS date_of_birth,
patient_schema.decrypt_phi(phone_encrypted) AS phone,
status,
admission_date
FROM patient_schema.patients
WHERE status = 'active';
-- === Tìm kiếm bằng hash (không cần decrypt) ===
SELECT
id,
patient_code,
patient_schema.decrypt_phi(full_name_encrypted) AS full_name
FROM patient_schema.patients
WHERE national_id_hash = patient_schema.hash_for_search('079085001234');
6. 使用視圖進行動態資料屏蔽
6.1。屏蔽視圖
-- =====================================================
-- DYNAMIC DATA MASKING VIEWS
-- Hiển thị data theo role của user
-- =====================================================
-- View cho Doctor (full access)
CREATE OR REPLACE VIEW patient_schema.v_patients_doctor AS
SELECT
id,
patient_code,
hospital_id,
department_id,
patient_schema.decrypt_phi(full_name_encrypted) AS full_name,
patient_schema.decrypt_phi(date_of_birth_encrypted) AS date_of_birth,
patient_schema.decrypt_phi(national_id_encrypted) AS national_id,
patient_schema.decrypt_phi(phone_encrypted) AS phone,
status,
admission_date,
discharge_date,
created_at
FROM patient_schema.patients;
-- View cho Nurse (masked sensitive fields)
CREATE OR REPLACE VIEW patient_schema.v_patients_nurse AS
SELECT
id,
patient_code,
hospital_id,
department_id,
patient_schema.decrypt_phi(full_name_encrypted) AS full_name,
-- Masked: chỉ hiện năm sinh
CONCAT('****-**-',
RIGHT(patient_schema.decrypt_phi(date_of_birth_encrypted), 2)
) AS date_of_birth_masked,
-- Masked: chỉ hiện 4 số cuối CCCD
CONCAT('****',
RIGHT(patient_schema.decrypt_phi(national_id_encrypted), 4)
) AS national_id_masked,
-- Masked: chỉ hiện 3 số cuối SĐT
CONCAT('*******',
RIGHT(patient_schema.decrypt_phi(phone_encrypted), 3)
) AS phone_masked,
status,
admission_date,
discharge_date
FROM patient_schema.patients;
-- View cho Reporting (fully anonymized)
CREATE OR REPLACE VIEW patient_schema.v_patients_anonymous AS
SELECT
id,
hospital_id,
department_id,
-- Chỉ age group, không có ngày sinh
CASE
WHEN AGE(patient_schema.decrypt_phi(date_of_birth_encrypted)::DATE) < INTERVAL '18 years' THEN 'Trẻ em (<18)'
WHEN AGE(patient_schema.decrypt_phi(date_of_birth_encrypted)::DATE) < INTERVAL '40 years' THEN 'Trung niên (18-39)'
WHEN AGE(patient_schema.decrypt_phi(date_of_birth_encrypted)::DATE) < INTERVAL '60 years' THEN 'Trung niên (40-59)'
ELSE 'Cao tuổi (60+)'
END AS age_group,
status,
EXTRACT(YEAR FROM admission_date) AS admission_year,
EXTRACT(MONTH FROM admission_date) AS admission_month
FROM patient_schema.patients;
-- === GRANT views theo role ===
GRANT SELECT ON patient_schema.v_patients_doctor TO phi_access;
GRANT SELECT ON patient_schema.v_patients_nurse TO healthcare_app;
GRANT SELECT ON patient_schema.v_patients_anonymous TO healthcare_readonly;
6.2。自動視圖選擇
-- =====================================================
-- FUNCTION TỰ ĐỘNG CHỌN VIEW THEO ROLE
-- =====================================================
CREATE OR REPLACE FUNCTION patient_schema.get_patient_data(
p_patient_id UUID DEFAULT NULL
) RETURNS TABLE (
id UUID,
patient_code VARCHAR,
full_name TEXT,
dob_display TEXT,
national_id_display TEXT,
phone_display TEXT,
status VARCHAR,
admission_date DATE
) AS $$
DECLARE
v_role TEXT := current_setting('app.current_role', true);
BEGIN
CASE v_role
WHEN 'doctor', 'admin' THEN
RETURN QUERY
SELECT
p.id, p.patient_code,
patient_schema.decrypt_phi(p.full_name_encrypted),
patient_schema.decrypt_phi(p.date_of_birth_encrypted),
patient_schema.decrypt_phi(p.national_id_encrypted),
patient_schema.decrypt_phi(p.phone_encrypted),
p.status, p.admission_date
FROM patient_schema.patients p
WHERE (p_patient_id IS NULL OR p.id = p_patient_id);
WHEN 'nurse' THEN
RETURN QUERY
SELECT
p.id, p.patient_code,
patient_schema.decrypt_phi(p.full_name_encrypted),
CONCAT('****-**-', RIGHT(patient_schema.decrypt_phi(p.date_of_birth_encrypted), 2)),
CONCAT('****', RIGHT(patient_schema.decrypt_phi(p.national_id_encrypted), 4)),
CONCAT('*******', RIGHT(patient_schema.decrypt_phi(p.phone_encrypted), 3)),
p.status, p.admission_date
FROM patient_schema.patients p
WHERE (p_patient_id IS NULL OR p.id = p_patient_id);
ELSE
RETURN QUERY
SELECT
p.id, p.patient_code,
'[RESTRICTED]'::TEXT,
'[RESTRICTED]'::TEXT,
'[RESTRICTED]'::TEXT,
'[RESTRICTED]'::TEXT,
p.status, p.admission_date
FROM patient_schema.patients p
WHERE (p_patient_id IS NULL OR p.id = p_patient_id);
END CASE;
END;
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER;
7. 測試 RLS 策略
7.1。測試腳本
-- =====================================================
-- TEST RLS POLICIES
-- =====================================================
-- === Setup test data ===
INSERT INTO patient_schema.hospitals (id, name, code) VALUES
('aaaa0000-0000-0000-0000-000000000001', 'BV Chợ Rẫy', 'BVCR'),
('aaaa0000-0000-0000-0000-000000000002', 'BV Bình Dân', 'BVBD');
INSERT INTO patient_schema.departments (id, hospital_id, name, code) VALUES
('bbbb0000-0000-0000-0000-000000000001', 'aaaa0000-0000-0000-0000-000000000001', 'Khoa Tim', 'CARDIO'),
('bbbb0000-0000-0000-0000-000000000002', 'aaaa0000-0000-0000-0000-000000000001', 'Khoa Nội', 'INTERNAL');
INSERT INTO patient_schema.staff (id, hospital_id, department_id, full_name, role) VALUES
('cccc0000-0000-0000-0000-000000000001', 'aaaa0000-0000-0000-0000-000000000001', 'bbbb0000-0000-0000-0000-000000000001', 'BS. Nguyễn Văn A', 'doctor'),
('cccc0000-0000-0000-0000-000000000002', 'aaaa0000-0000-0000-0000-000000000001', 'bbbb0000-0000-0000-0000-000000000002', 'BS. Trần Thị B', 'doctor'),
('cccc0000-0000-0000-0000-000000000003', 'aaaa0000-0000-0000-0000-000000000001', 'bbbb0000-0000-0000-0000-000000000001', 'ĐD. Lê Văn C', 'nurse');
-- === TEST 1: Doctor chỉ thấy bệnh nhân có relationship ===
BEGIN;
SET LOCAL app.hospital_id = 'aaaa0000-0000-0000-0000-000000000001';
SET LOCAL app.current_user_id = 'cccc0000-0000-0000-0000-000000000001';
SET LOCAL app.current_role = 'doctor';
SET LOCAL app.current_dept_id = 'bbbb0000-0000-0000-0000-000000000001';
-- Phải trả về CHỈ patients có relationship với BS. Nguyễn Văn A
SELECT id, patient_code, status
FROM patient_schema.patients;
-- Verify count
SELECT COUNT(*) AS doctor_a_patient_count
FROM patient_schema.patients;
ROLLBACK;
-- === TEST 2: Nurse chỉ thấy bệnh nhân cùng khoa ===
BEGIN;
SET LOCAL app.hospital_id = 'aaaa0000-0000-0000-0000-000000000001';
SET LOCAL app.current_user_id = 'cccc0000-0000-0000-0000-000000000003';
SET LOCAL app.current_role = 'nurse';
SET LOCAL app.current_dept_id = 'bbbb0000-0000-0000-0000-000000000001';
-- Phải trả về CHỈ patients khoa Tim
SELECT id, patient_code, status
FROM patient_schema.patients;
ROLLBACK;
-- === TEST 3: Cross-hospital isolation ===
BEGIN;
SET LOCAL app.hospital_id = 'aaaa0000-0000-0000-0000-000000000002'; -- BV Bình Dân
SET LOCAL app.current_user_id = 'cccc0000-0000-0000-0000-000000000001';
SET LOCAL app.current_role = 'admin';
-- Phải trả về 0 rows (BS ở BV Chợ Rẫy, query BV Bình Dân)
SELECT COUNT(*) AS cross_hospital_count
FROM patient_schema.patients;
-- Expected: 0
ROLLBACK;
-- === TEST 4: Nurse không thấy sensitive records ===
BEGIN;
SET LOCAL app.hospital_id = 'aaaa0000-0000-0000-0000-000000000001';
SET LOCAL app.current_user_id = 'cccc0000-0000-0000-0000-000000000003';
SET LOCAL app.current_role = 'nurse';
SET LOCAL app.current_dept_id = 'bbbb0000-0000-0000-0000-000000000001';
-- Sensitive records phải bị ẩn
SELECT COUNT(*) AS nurse_sensitive_count
FROM patient_schema.medical_records
WHERE is_sensitive = true;
-- Expected: 0
ROLLBACK;
-- === TEST 5: No DELETE allowed on medical records ===
BEGIN;
SET LOCAL app.hospital_id = 'aaaa0000-0000-0000-0000-000000000001';
SET LOCAL app.current_user_id = 'cccc0000-0000-0000-0000-000000000001';
SET LOCAL app.current_role = 'doctor';
-- Phải FAIL - no DELETE policy exists
DELETE FROM patient_schema.medical_records WHERE id IS NOT NULL;
-- Expected: ERROR or 0 rows affected
ROLLBACK;
7.2。 RLS 效能監控
-- =====================================================
-- MONITORING RLS PERFORMANCE
-- =====================================================
-- Kiểm tra RLS có gây slow queries không
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT id, patient_code
FROM patient_schema.patients
WHERE status = 'active';
-- Tạo index hỗ trợ RLS performance
CREATE INDEX idx_patients_hospital_dept
ON patient_schema.patients(hospital_id, department_id);
CREATE INDEX idx_doctor_patient_active
ON patient_schema.doctor_patient(doctor_id, patient_id)
WHERE is_active = true;
CREATE INDEX idx_medical_records_filter
ON patient_schema.medical_records(hospital_id, department_id, doctor_id, is_sensitive);
-- Monitor RLS policy execution time
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT
query,
calls,
mean_exec_time,
total_exec_time
FROM pg_stat_statements
WHERE query LIKE '%patient_schema.patients%'
ORDER BY mean_exec_time DESC
LIMIT 10;
8. 最佳實務與常見陷阱
8.1。常見陷阱
| 陷阱 | 問題 | 解決方案 |
|---|---|---|
忘記了 FORCE ROW LEVEL SECURITY | 表所有者繞過 RLS | 總是使用 FORCE |
current_setting() 不 true 參數 | 未設定變數時發生錯誤 | 使用 current_setting('var', true) |
| JOIN 表上的 RLS | 效能問題 | 外鍵索引 |
| 原始碼中的加密金鑰 | 關鍵洩漏 | 使用 Vault 或環境變數 |
| 忘記重置會話變數 | 連接池洩漏 | 連接返回時重置 |
8.2。安全檢查表
✅ RLS enabled VÀ forced trên tất cả PHI tables
✅ Hospital isolation policy trên mọi table
✅ Role-based policies (doctor, nurse, admin, patient)
✅ Emergency access logging
✅ PHI fields encrypted với pgcrypto
✅ Search hash indexes cho encrypted fields
✅ Dynamic data masking views
✅ Quarkus interceptor set JWT claims
✅ Connection reset khi return to pool
✅ Performance indexes cho RLS predicates
✅ Test scripts cho tất cả access patterns
✅ No DELETE policy trên medical records
總結
在本課中，我們實現了：
- 行級安全性 (RLS)：啟用、強制和建立醫院隔離、醫病關係、部門訪問和病患入口網站的策略
- 基於角色的策略：對於醫生、護士、管理員、實驗室技術人員、病人不同
- 緊急存取：具有限時存取和審核日誌記錄的打破玻璃機制
- JWT-to-PostgreSQL 整合：Quarkus 攔截器透過 JWT 聲明傳遞
SET LOCAL會話變數 - 列加密：用於加密/解密 PHI 欄位的 pgcrypto 輔助函數
- 動態資料屏蔽：視圖根據角色顯示屏蔽/完整數據
- 測試：綜合測試腳本驗證所有存取模式
- 性能：指數支持 RLS 政策評估
RLS 與列加密相結合，為醫療資料創建強大的兩層保護 - 即使一層被繞過，另一層仍然可以保護 PHI。
練習
-
實作 RLS 策略：使用上述架構建立資料庫，啟用 RLS，並為所有角色（醫師、護理師、管理者、病患）實作策略。插入測試資料並透過測試腳本驗證存取隔離。
-
JWT 整合：編寫一個 Quarkus 攔截器，將 JWT 宣告傳遞到 PostgreSQL 會話變數中。與 3 個不同的使用者（醫生、護士、病人）進行測試 - 驗證每個使用者都看到正確的資料。
-
緊急存取：實施打破玻璃機制：記錄緊急存取權限，授予臨時 4 小時存取權限，到期後撤銷。測試驗證緊急訪問是否正常運作。
-
動態遮罩：為 3 個不同角色建立遮罩視圖。測試驗證醫生看到完整數據，護理師看到封鎖數據，報告看到匿名數據。
| ◀ 上一篇 | 下一篇文章 ▶ |
|---|---|
| 第 10 課：使用 PostgreSQL 加密靜態和傳輸中的數據 | 第 12 課：使用 pgAudit 進行稽核日誌記錄和變更資料捕獲 |