PostgreSQL 是一个功能强大的开源关系型数据库管理系统,以其稳定性、高级特性(如窗口函数、全文搜索)和严格的 SQL 标准符合性而闻名。本文将引导您完成其一系列核心操作。
核心概念:实例、数据库、模式与角色
在开始具体操作前,理解以下几个核心概念的区别至关重要:
- 实例 (Instance): 指运行在服务器上的一个 PostgreSQL 后台进程 (
postmaster
) 及其分配到的内存区域。一个实例可以管理多个数据库。它通常与一台服务器(物理或虚拟)一一对应。例如: 你在一台服务器上安装并启动了一个 PostgreSQL 服务,这就是一个实例。 - 数据库 (Database): 是实例下最高级别的对象隔离单位。用于将不同的应用、项目或业务的数据隔离开来,防止意外交叉访问。例如: 你可以在一个实例中创建
app_prod
和app_dev
两个独立的数据库。 - 模式 (Schema): 位于数据库内部,是数据库对象的命名空间和容器,用于进一步组织表、视图、函数等对象。一个数据库可以有多个模式,默认有一个
public
模式。模式有助于实现多租户、模块化设计。例如: 在app_prod
数据库中,可以创建hr_schema
存放人力资源相关的表,创建finance_schema
存放财务相关的表。 - 角色 (Role): 是 PostgreSQL 中权限的载体。它既可以用来代表一个用户 (User),也可以代表一个组 (Group)。
- 用户 (User): 是一个具有登录权限的角色 (
LOGIN
)。CREATE USER
命令本质上是CREATE ROLE ... WITH LOGIN
的别名。 - 组 (Group): 是一个没有登录权限的角色,用于将一组权限集合起来,方便分配给其他角色。
- 用户 (User): 是一个具有登录权限的角色 (
简单比喻: 实例就像一栋大楼,数据库是大楼里的不同公司,模式是公司里的各个部门(财务部、人事部),表是部门里的文件柜,而角色则是拥有不同部门钥匙和文件柜访问权限的员工。
现在,让我们进入具体的操作部分。
1. 创建角色并授予权限
使用 CREATE ROLE
语句创建角色,并使用 GRANT
授予权限。
-- 1.1 创建一个名为 'data_reader' 的角色,无法登录,用于读取权限
CREATE ROLE data_reader WITH NOLOGIN;
-- 1.2 授予该角色对指定数据库(假设为 mydb)中所有现有表(及未来表)的 SELECT 权限
-- 首先,连接到目标数据库:\c mydb
GRANT USAGE ON SCHEMA public TO data_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO data_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO data_reader;
-- 1.3 创建一个名为 'data_writer' 的角色,并授予插入、更新、删除的权限
CREATE ROLE data_writer WITH NOLOGIN;
GRANT USAGE ON SCHEMA public TO data_writer;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO data_writer;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT INSERT, UPDATE, DELETE ON TABLES TO data_writer;
2. 创建用户并分配角色
使用 CREATE USER
创建可以登录的用户,然后将之前创建的角色授予他们。
-- 2.1 创建一个名为 'john' 的用户,并设置密码
CREATE USER john WITH PASSWORD 'securepassword123';
-- 2.2 将 'data_reader' 角色授予给 john
GRANT data_reader TO john;
-- 2.3 创建一个名为 'jane' 的用户,并同时授予读写角色
CREATE USER jane WITH PASSWORD 'anothersecurepassword';
GRANT data_reader, data_writer TO jane;
现在,用户 john
可以连接数据库并执行查询,而 jane
既可以查询也可以修改数据。
3. 回收角色/用户权限
使用 REVOKE
语句来收回权限。
-- 3.1 从用户 jane 身上收回 data_writer 角色(但她可能还保留 data_reader)
REVOKE data_writer FROM jane;
-- 3.2 从角色 data_reader 身上收回所有表上的 SELECT 权限
REVOKE SELECT ON ALL TABLES IN SCHEMA public FROM data_reader;
-- 3.3 彻底删除一个角色(如果该角色还被其他角色拥有,需要先处理依赖关系)
DROP ROLE IF EXISTS data_writer;
4. 基本增删改查 (CRUD) 操作
假设我们有一个简单的 employees
表。
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
department VARCHAR(50),
salary NUMERIC(10, 2)
);
增 (Create) – INSERT
INSERT INTO employees (name, department, salary)
VALUES ('张三', '技术部', 75000);
查 (Read) – SELECT
-- 查询所有列
SELECT * FROM employees;
-- 条件查询
SELECT name, salary FROM employees WHERE department = '技术部';
-- 排序和限制
SELECT * FROM employees ORDER BY salary DESC LIMIT 5;
改 (Update) – UPDATE
UPDATE employees
SET salary = salary * 1.05
WHERE department = '技术部';
删 (Delete) – DELETE
DELETE FROM employees
WHERE name = '张三';
5. 创建视图 (View)
视图是一个虚拟表,基于一个 SQL 查询的结果集。它简化了复杂查询,并可以增加一层安全控制。
-- 创建一个视图,只显示技术部员工的名字和工资,隐藏敏感信息(如ID)
CREATE VIEW tech_employee_salaries AS
SELECT name, salary
FROM employees
WHERE department = '技术部';
-- 之后可以像查询普通表一样查询视图
SELECT * FROM tech_employee_salaries WHERE salary > 70000;
6. 创建存储过程 (Stored Procedure)
PostgreSQL 11 之后,可以使用 CREATE PROCEDURE
创建没有返回值的存储过程,使用 CREATE FUNCTION
创建有返回值的函数。
-- 示例:创建一个存储过程来给特定部门的员工加薪
CREATE OR REPLACE PROCEDURE give_raise(
dep_name VARCHAR,
raise_percent NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE employees
SET salary = salary * (1 + raise_percent / 100)
WHERE department = dep_name;
-- 可以在这里添加更多逻辑,如记录日志等
COMMIT;
END;
$$;
-- 调用存储过程
CALL give_raise('技术部', 10);
总结
通过以上步骤,我们完成了 PostgreSQL 的一套核心操作流程:
- 权限规划:首先创建角色并授予精确的权限集。
- 用户管理:创建用户并将其与角色关联,实现权限分配。
- 数据操作:在授权范围内对数据进行增删改查。
- 高级抽象:使用视图简化查询和加强安全,使用存储过程封装复杂业务逻辑。
理解实例、数据库、模式、角色之间的关系是有效管理和设计 PostgreSQL 架构的基础。始终遵循最小权限原则,只授予用户执行其任务所必需的最低权限,这是数据库安全的最佳实践。