FrontPage/IT/MW/PostgreSQL/機器管理DB の変更点

• 追加された行はこの色です。
• 削除された行はこの色です。
• FrontPage/IT/MW/PostgreSQL/機器管理DB へ行く。
• FrontPage/IT/MW/PostgreSQL/機器管理DB の差分を削除

#author("2024-10-11T04:08:47+09:00","default:admin","admin")
#author("2024-10-11T04:09:41+09:00","default:admin","admin")
#navi(../)
#Contents
------------------------
IPアドレスが複数存在すること、OSユーザー、プログラムユーザー、データベースユーザー（DBユーザー）を管理し、さらにパスワードを特定のグループに所属するユーザーにのみ見せるためのデータベース設計を提案します。

**要件の整理 [#qf687f97]
+複数のIPアドレス: 各機器が複数のIPアドレスとMACアドレスを持つことを許可。
+OSユーザー、プログラムユーザー、DBユーザー: 各機器やプログラムごとに異なるユーザーを管理。
+パスワード管理: パスワードは特定のグループ（例：管理者グループ）に所属するユーザーにしか見せない。
+セキュリティ管理: パスワードの保護（暗号化など）と、適切な権限管理を行う。
***1. テーブル設計 [#y784ffac]
機器テーブル (equipment)
これは基本的な機器情報を管理するテーブルです。これは変更ありません。

|sql|h
|CREATE TABLE equipment (&br;id SERIAL PRIMARY KEY,&br;name VARCHAR(100) NOT NULL,&br;model VARCHAR(100),&br;serial_number VARCHAR(100) UNIQUE,&br;purchase_date DATE,&br;status VARCHAR(50),&br;location VARCHAR(100)&br;);|
 sql
 CREATE TABLE equipment (
     id SERIAL PRIMARY KEY,
     name VARCHAR(100) NOT NULL,
     model VARCHAR(100),
     serial_number VARCHAR(100) UNIQUE,
     purchase_date DATE,
     status VARCHAR(50),
     location VARCHAR(100)
 );
ネットワーク情報テーブル (network_info)
複数のIPアドレスとMACアドレスを管理するテーブルです。

 sql
 CREATE TABLE network_info (
     id SERIAL PRIMARY KEY,
     equipment_id INT REFERENCES equipment(id) ON DELETE CASCADE,
     ip_address INET,
     mac_address VARCHAR(17),
     interface_name VARCHAR(50)
 );
OS情報テーブル (os_info)
機器のOS情報を管理するテーブルです。

 sql
 CREATE TABLE os_info (
     id SERIAL PRIMARY KEY,
     equipment_id INT REFERENCES equipment(id) ON DELETE CASCADE,
     os_name VARCHAR(100) NOT NULL,
     os_version VARCHAR(50),
     install_date DATE
 );
ユーザグループテーブル (user_groups)
ユーザグループを管理します。特定のグループに所属するユーザーのみがパスワードにアクセスできるように、グループごとに権限を分けます。

-id: 主キー
-group_name: グループ名（例：Admin、Userなど）
-can_view_passwords: このグループに所属するユーザーがパスワードを閲覧可能かどうかのフラグ
 sql
 CREATE TABLE user_groups (
     id SERIAL PRIMARY KEY,
     group_name VARCHAR(50) NOT NULL UNIQUE,
     can_view_passwords BOOLEAN DEFAULT FALSE
 );
ユーザーテーブル (users)
各種ユーザー（OSユーザー、プログラムユーザー、DBユーザーなど）を管理するテーブルです。ユーザーは複数のグループに属することができ、パスワードの閲覧権限もここで決まります。

 sql
 CREATE TABLE users (
     id SERIAL PRIMARY KEY,
     username VARCHAR(100) NOT NULL,
     user_type VARCHAR(50) NOT NULL,  -- 'OS', 'Program', 'DB'など
     group_id INT REFERENCES user_groups(id) ON DELETE CASCADE
 );
パスワード管理テーブル (passwords)
パスワードを管理するテーブルです。ユーザーグループごとの権限に応じて、このテーブルの内容を参照できるようにします。パスワードはセキュリティのために暗号化して保存します。

-id: 主キー
-user_id: パスワードが関連するユーザーのID（usersテーブルの外部キー）
-password: 暗号化されたパスワード
 sql
 CREATE TABLE passwords (
     id SERIAL PRIMARY KEY,
     user_id INT REFERENCES users(id) ON DELETE CASCADE,
     password TEXT NOT NULL -- 暗号化されたパスワードを保存する
 );
重要: passwordフィールドに保存するパスワードは必ず暗号化（例: pgcrypto拡張を使用してハッシュ化）する必要があります。

 sql
 -- pgcrypto拡張のインストール（必要に応じて）
 CREATE EXTENSION pgcrypto;
 
 -- パスワードをハッシュ化して挿入
 INSERT INTO passwords (user_id, password)
 VALUES
 (1, crypt('user_password', gen_salt('bf')));
プログラム情報テーブル (installed_programs)
インストールされているプログラムと、そのユーザーを管理するテーブルです。

 sql
 CREATE TABLE installed_programs (
     id SERIAL PRIMARY KEY,
     equipment_id INT REFERENCES equipment(id) ON DELETE CASCADE,
     program_name VARCHAR(100) NOT NULL,
     version VARCHAR(50),
     install_date DATE
 );
プログラムユーザーテーブル (program_users)
プログラムごとのユーザーと、そのアクセスレベルを管理します。

 sql
 CREATE TABLE program_users (
     id SERIAL PRIMARY KEY,
     program_id INT REFERENCES installed_programs(id) ON DELETE CASCADE,
     user_id INT REFERENCES users(id) ON DELETE CASCADE,
     access_level VARCHAR(50) -- 例: 'Administrator', 'User'など
 );
***2. データの挿入例 [#kf2299cc]
ユーザグループの挿入
 sql
 INSERT INTO user_groups (group_name, can_view_passwords)
 VALUES
 ('Admin', TRUE),
 ('User', FALSE);
ユーザーの挿入（OS、プログラム、DBユーザー）
 sql
 INSERT INTO users (username, user_type, group_id)
 VALUES
 ('os_admin', 'OS', 1),
 ('db_user', 'DB', 2),
 ('program_user', 'Program', 2);
パスワードの挿入（ハッシュ化）
 sql
 INSERT INTO passwords (user_id, password)
 VALUES
 (1, crypt('os_admin_password', gen_salt('bf'))),
 (2, crypt('db_user_password', gen_salt('bf'))),
 (3, crypt('program_user_password', gen_salt('bf')));
プログラムの挿入
 sql
 INSERT INTO installed_programs (equipment_id, program_name, version, install_date)
 VALUES
 (1, 'Microsoft Office', '2019', '2023-02-12'),
 (2, 'LibreOffice', '7.1', '2022-11-16');
プログラムユーザーの挿入
 sql
 INSERT INTO program_users (program_id, user_id, access_level)
 VALUES
 (1, 1, 'Administrator'),
 (2, 3, 'User');
***3. パスワード閲覧のクエリ例 [#s43d718e]
パスワードを閲覧できるのは、特定のグループ（例えばAdminグループ）に所属しているユーザーに限定します。例えば、can_view_passwordsがTRUEのグループに属するユーザーのみがパスワードを取得できるようにします。

パスワードの表示（管理者グループのみ）
 sql
 SELECT u.username, p.password
 FROM users u
 JOIN passwords p ON u.id = p.user_id
 JOIN user_groups g ON u.group_id = g.id
 WHERE g.can_view_passwords = TRUE;

特定ユーザーのパスワードを表示（グループに基づく）
 sql
 SELECT u.username, p.password
 FROM users u
 JOIN passwords p ON u.id = p.user_id
 JOIN user_groups g ON u.group_id = g.id
 WHERE u.username = 'os_admin' AND g.can_view_passwords = TRUE;
***4. セキュリティ上の考慮 [#j555d9ea]
-パスワードのハッシュ化:~
パスワードは平文では保存せず、常にハッシュ化して保存します（pgcryptoのcrypt()関数などを使用）。
-パスワードのアクセス制御:~
パスワードにアクセスできるかどうかは、user_groupsテーブルのcan_view_passwordsフラグに基づいて制御します。
-データベースユーザー権限:~
PostgreSQL自体のユーザー権限（データベースユーザー）を適切に設定し、特定の操作（挿入、削除、更新、参照）を実行できるユーザーを制限します。~
これで、複数のIPアドレスやMACアドレス、OSユーザー、プログラムユーザー、データベースユーザーを管理し、特定のグループに属するユーザーにのみパスワードを見せるためのデータベース設計が完成しました。追加の質問があれば、お知らせください！