77 lines
2.7 KiB
SQL
77 lines
2.7 KiB
SQL
-- 创建数据库
|
|
CREATE DATABASE user_management;
|
|
|
|
-- 连接到数据库
|
|
\c user_management;
|
|
|
|
-- 创建用户表
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id SERIAL PRIMARY KEY,
|
|
username VARCHAR(50) NOT NULL UNIQUE,
|
|
password VARCHAR(100) NOT NULL,
|
|
email VARCHAR(100) UNIQUE,
|
|
phone VARCHAR(20),
|
|
full_name VARCHAR(100),
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
active BOOLEAN NOT NULL DEFAULT TRUE
|
|
);
|
|
|
|
-- 创建角色表
|
|
CREATE TABLE IF NOT EXISTS roles (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(50) NOT NULL UNIQUE,
|
|
code VARCHAR(50) NOT NULL UNIQUE,
|
|
description VARCHAR(255),
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
active BOOLEAN NOT NULL DEFAULT TRUE
|
|
);
|
|
|
|
-- 创建用户角色关联表
|
|
CREATE TABLE IF NOT EXISTS user_roles (
|
|
user_id BIGINT NOT NULL,
|
|
role_id BIGINT NOT NULL,
|
|
PRIMARY KEY (user_id, role_id),
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
|
|
);
|
|
|
|
-- 创建索引
|
|
CREATE INDEX idx_users_username ON users(username);
|
|
CREATE INDEX idx_users_email ON users(email);
|
|
CREATE INDEX idx_users_created_at ON users(created_at);
|
|
|
|
-- 初始用户数据
|
|
INSERT INTO users (username, password, email, full_name, active)
|
|
VALUES
|
|
('admin', '$2a$10$X/uMCeMesW0IMfXjP6bCdelNz4KoP8fWoB0MRoJ5qlAKgj.lKVKLm', 'admin@example.com', '系统管理员', true),
|
|
('user1', '$2a$10$SzX.nt1QqI4PqcbVOIyLHOX6TZKGcgWa9koR/TA2WJg.YhMV.vTDy', 'user1@example.com', '测试用户1', true),
|
|
('user2', '$2a$10$SzX.nt1QqI4PqcbVOIyLHOX6TZKGcgWa9koR/TA2WJg.YhMV.vTDy', 'user2@example.com', '测试用户2', true)
|
|
ON CONFLICT (username) DO NOTHING;
|
|
|
|
-- 初始角色数据
|
|
INSERT INTO roles (name, code, description, active)
|
|
VALUES
|
|
('系统管理员', 'ADMIN', '拥有系统所有权限', true),
|
|
('普通用户', 'USER', '拥有基本操作权限', true),
|
|
('访客', 'GUEST', '仅拥有查看权限', true)
|
|
ON CONFLICT (code) DO NOTHING;
|
|
|
|
-- 分配角色
|
|
INSERT INTO user_roles (user_id, role_id)
|
|
SELECT u.id, r.id
|
|
FROM users u, roles r
|
|
WHERE u.username = 'admin' AND r.code = 'ADMIN'
|
|
ON CONFLICT DO NOTHING;
|
|
|
|
INSERT INTO user_roles (user_id, role_id)
|
|
SELECT u.id, r.id
|
|
FROM users u, roles r
|
|
WHERE u.username IN ('user1', 'user2') AND r.code = 'USER'
|
|
ON CONFLICT DO NOTHING;
|
|
|
|
-- 授权
|
|
GRANT ALL PRIVILEGES ON DATABASE user_management TO postgres;
|
|
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO postgres;
|
|
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO postgres; |