접근 감사

CREATE TABLE audit_log (
  id          BIGSERIAL PRIMARY KEY,
  event_time  TIMESTAMPTZ NOT NULL DEFAULT now(),
  db_user     TEXT NOT NULL DEFAULT current_user,
  app_user    TEXT,
  table_name  TEXT NOT NULL,
  operation   TEXT NOT NULL CHECK (operation IN ('INSERT','UPDATE','DELETE','SELECT')),
  row_id      BIGINT,
  old_data    JSONB,
  new_data    JSONB
);

-- Who is the database user right now?
SELECT current_user,
       session_user;

-- Read an application-level user injected by the app layer
SELECT current_setting('app.current_user', true) AS app_user;

CREATE OR REPLACE FUNCTION fn_audit_changes()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  INSERT INTO audit_log (
    db_user,
    app_user,
    table_name,
    operation,
    row_id,
    old_data,
    new_data
  ) VALUES (
    current_user,
    current_setting('app.current_user', true),
    TG_TABLE_NAME,
    TG_OP,
    COALESCE(NEW.id, OLD.id),
    CASE WHEN TG_OP = 'INSERT' THEN NULL ELSE row_to_json(OLD)::JSONB END,
    CASE WHEN TG_OP = 'DELETE' THEN NULL ELSE row_to_json(NEW)::JSONB END
  );
  RETURN NULL;
END;
$$;

CREATE TRIGGER trg_audit_patients
AFTER INSERT OR UPDATE OR DELETE
ON patients
FOR EACH ROW
EXECUTE FUNCTION fn_audit_changes();

CREATE OR REPLACE FUNCTION get_patient_record(p_id INT)
RETURNS SETOF patients
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  -- Log the read access
  INSERT INTO audit_log (db_user, app_user, table_name, operation, row_id)
  VALUES (
    current_user,
    current_setting('app.current_user', true),
    'patients',
    'SELECT',
    p_id
  );

  RETURN QUERY
  SELECT * FROM patients WHERE id = p_id;
END;
$$;

-- See who is currently connected and what they are running
SELECT pid,
       usename        AS db_user,
       application_name,
       client_addr,
       state,
       query_start,
       LEFT(query, 80) AS current_query
FROM   pg_stat_activity
WHERE  datname = current_database()
ORDER  BY query_start DESC;

SELECT event_time,
       db_user,
       app_user,
       operation,
       old_data,
       new_data
FROM   audit_log
WHERE  table_name = 'patients'
  AND  row_id = 42
ORDER  BY event_time DESC
LIMIT  20;

SELECT app_user,
       COUNT(*) AS records_accessed
FROM   audit_log
WHERE  operation = 'SELECT'
  AND  event_time >= now() - INTERVAL '1 hour'
GROUP  BY app_user
HAVING COUNT(*) > 100
ORDER  BY records_accessed DESC;

-- Only the app role may insert; nobody may update or delete
REVOKE ALL     ON audit_log FROM PUBLIC;
GRANT  INSERT  ON audit_log TO app_role;
GRANT  SELECT  ON audit_log TO auditor_role;

-- Trigger to block any tampering
CREATE OR REPLACE FUNCTION fn_protect_audit()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  RAISE EXCEPTION 'audit_log rows are immutable';
  RETURN NULL;
END;
$$;

CREATE TRIGGER trg_protect_audit
BEFORE UPDATE OR DELETE ON audit_log
FOR EACH ROW EXECUTE FUNCTION fn_protect_audit();

-- View all RLS policies on the patients table
SELECT polname       AS policy_name,
       polcmd        AS command,
       polroles::TEXT AS applies_to,
       polqual::TEXT  AS using_expression,
       polwithcheck::TEXT AS with_check_expression
FROM   pg_policy
WHERE  polrelid = 'patients'::REGCLASS
ORDER  BY polname;