autoprokatIS/database_security_postgresql.sql

-- Автопрокат: PostgreSQL 16, схема, роли, права и аудит операций

CREATE TABLE IF NOT EXISTS brand (
  brand_id  SERIAL PRIMARY KEY,
  name      VARCHAR(50) NOT NULL CONSTRAINT uq_brand_name UNIQUE,
  country   VARCHAR(50)
);

CREATE TABLE IF NOT EXISTS car (
  car_id        SERIAL PRIMARY KEY,
  license_plate VARCHAR(15) NOT NULL CONSTRAINT uq_car_plate UNIQUE,
  brand_id      INT NOT NULL CONSTRAINT fk_car_brand
                REFERENCES brand(brand_id) ON UPDATE CASCADE ON DELETE RESTRICT,
  year          SMALLINT CONSTRAINT chk_car_year CHECK (year BETWEEN 1990 AND 2030),
  daily_rate    NUMERIC(10,2) NOT NULL CONSTRAINT chk_car_rate CHECK (daily_rate > 0),
  status        VARCHAR(20) DEFAULT 'Свободен'
                CONSTRAINT chk_car_status CHECK (status IN ('Свободен','В аренде','На ТО'))
);

CREATE TABLE IF NOT EXISTS client (
  client_id  SERIAL PRIMARY KEY,
  full_name  VARCHAR(100) NOT NULL,
  phone      VARCHAR(20) CONSTRAINT uq_client_phone UNIQUE,
  address    TEXT,
  license_no VARCHAR(20) CONSTRAINT uq_client_license UNIQUE,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS rental (
  rental_id     SERIAL PRIMARY KEY,
  car_id        INT NOT NULL CONSTRAINT fk_rental_car
                REFERENCES car(car_id) ON UPDATE CASCADE ON DELETE RESTRICT,
  client_id     INT NOT NULL CONSTRAINT fk_rental_client
                REFERENCES client(client_id) ON UPDATE CASCADE ON DELETE RESTRICT,
  start_date    DATE NOT NULL,
  end_date      DATE NOT NULL CONSTRAINT chk_rental_dates CHECK (end_date >= start_date),
  actual_return DATE,
  total_cost    NUMERIC(10,2) CONSTRAINT chk_rental_cost CHECK (total_cost >= 0),
  status        VARCHAR(20) DEFAULT 'Активна'
                CONSTRAINT chk_rental_status CHECK (status IN ('Активна','Завершена'))
);

CREATE INDEX IF NOT EXISTS idx_car_brand_id ON car(brand_id);
CREATE INDEX IF NOT EXISTS idx_rental_car_id ON rental(car_id);
CREATE INDEX IF NOT EXISTS idx_rental_client_id ON rental(client_id);
CREATE INDEX IF NOT EXISTS idx_rental_dates ON rental(start_date, end_date);
CREATE INDEX IF NOT EXISTS idx_rental_status ON rental(status);

CREATE OR REPLACE VIEW v_rentals_full AS
  SELECT r.rental_id,
         c.full_name AS client_name,
         c.phone AS client_phone,
         ca.license_plate,
         b.name AS brand_name,
         r.start_date,
         r.end_date,
         r.actual_return,
         r.total_cost,
         r.status
  FROM rental r
  JOIN client c ON c.client_id = r.client_id
  JOIN car ca ON ca.car_id = r.car_id
  JOIN brand b ON b.brand_id = ca.brand_id;

CREATE OR REPLACE VIEW v_available_cars AS
  SELECT ca.car_id, ca.license_plate, b.name AS brand, b.country, ca.year, ca.daily_rate
  FROM car ca
  JOIN brand b ON b.brand_id = ca.brand_id
  WHERE ca.status = 'Свободен';

CREATE OR REPLACE VIEW v_client_stats AS
  SELECT c.client_id,
         c.full_name,
         COUNT(r.rental_id) AS total_rentals,
         COALESCE(SUM(r.total_cost), 0) AS total_spent
  FROM client c
  LEFT JOIN rental r ON r.client_id = c.client_id
  GROUP BY c.client_id, c.full_name;

-- Пользователи предметной области
CREATE ROLE rent_admin NOLOGIN;
CREATE ROLE rent_manager NOLOGIN;
CREATE ROLE rent_viewer NOLOGIN;

-- Администратор: полный доступ
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO rent_admin;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO rent_admin;

-- Менеджер: рабочий CRUD без управления пользователями и системной структурой
GRANT SELECT, INSERT, UPDATE ON client, car, rental TO rent_manager;
GRANT SELECT ON brand, v_rentals_full, v_available_cars, v_client_stats TO rent_manager;
GRANT USAGE ON SEQUENCE client_client_id_seq, car_car_id_seq, rental_rental_id_seq TO rent_manager;

-- Аналитик: только чтение и отчеты
GRANT SELECT ON brand, car, client, rental, v_rentals_full, v_available_cars, v_client_stats TO rent_viewer;

-- Примеры учетных записей БД. В реальном проекте пароли задаются администратором отдельно.
-- CREATE USER ivanov WITH PASSWORD 'Str0ng#Pass1' IN ROLE rent_manager;
-- CREATE USER petrov WITH PASSWORD 'Str0ng#Pass2' IN ROLE rent_viewer;
-- CREATE USER db_admin WITH PASSWORD 'Str0ng#Admin1' IN ROLE rent_admin;

-- Row Level Security: явное разрешение по ролям.
ALTER TABLE rental ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS rental_admin_all ON rental;
CREATE POLICY rental_admin_all ON rental
  FOR ALL TO rent_admin
  USING (true)
  WITH CHECK (true);

DROP POLICY IF EXISTS rental_manager_all ON rental;
CREATE POLICY rental_manager_all ON rental
  FOR ALL TO rent_manager
  USING (true)
  WITH CHECK (true);

DROP POLICY IF EXISTS rental_viewer_read ON rental;
CREATE POLICY rental_viewer_read ON rental
  FOR SELECT TO rent_viewer
  USING (true);

-- Аудит операций средствами СУБД
CREATE TABLE IF NOT EXISTS audit_log (
  audit_id   BIGSERIAL PRIMARY KEY,
  changed_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  db_user    TEXT NOT NULL DEFAULT current_user,
  action     TEXT NOT NULL,
  table_name TEXT NOT NULL,
  row_id     TEXT,
  old_data   JSONB,
  new_data   JSONB
);

CREATE OR REPLACE FUNCTION fn_audit_row()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
  pk_name TEXT := TG_ARGV[0];
BEGIN
  IF TG_OP = 'INSERT' THEN
    INSERT INTO audit_log(action, table_name, row_id, new_data)
    VALUES (TG_OP, TG_TABLE_NAME, to_jsonb(NEW)->>pk_name, to_jsonb(NEW));
    RETURN NEW;
  ELSIF TG_OP = 'UPDATE' THEN
    INSERT INTO audit_log(action, table_name, row_id, old_data, new_data)
    VALUES (TG_OP, TG_TABLE_NAME, to_jsonb(NEW)->>pk_name, to_jsonb(OLD), to_jsonb(NEW));
    RETURN NEW;
  ELSE
    INSERT INTO audit_log(action, table_name, row_id, old_data)
    VALUES (TG_OP, TG_TABLE_NAME, to_jsonb(OLD)->>pk_name, to_jsonb(OLD));
    RETURN OLD;
  END IF;
END;
$$;

DROP TRIGGER IF EXISTS trg_audit_brand ON brand;
CREATE TRIGGER trg_audit_brand
AFTER INSERT OR UPDATE OR DELETE ON brand
FOR EACH ROW EXECUTE FUNCTION fn_audit_row('brand_id');

DROP TRIGGER IF EXISTS trg_audit_car ON car;
CREATE TRIGGER trg_audit_car
AFTER INSERT OR UPDATE OR DELETE ON car
FOR EACH ROW EXECUTE FUNCTION fn_audit_row('car_id');

DROP TRIGGER IF EXISTS trg_audit_client ON client;
CREATE TRIGGER trg_audit_client
AFTER INSERT OR UPDATE OR DELETE ON client
FOR EACH ROW EXECUTE FUNCTION fn_audit_row('client_id');

DROP TRIGGER IF EXISTS trg_audit_rental ON rental;
CREATE TRIGGER trg_audit_rental
AFTER INSERT OR UPDATE OR DELETE ON rental
FOR EACH ROW EXECUTE FUNCTION fn_audit_row('rental_id');