PostgreSQL 数据库基本操作指南

PostgreSQL 是一个功能强大的开源关系型数据库管理系统,以其稳定性、高级特性(如窗口函数、全文搜索)和严格的 SQL 标准符合性而闻名。本文将引导您完成其一系列核心操作。

核心概念:实例、数据库、模式与角色

在开始具体操作前,理解以下几个核心概念的区别至关重要:

  • 实例 (Instance): 指运行在服务器上的一个 PostgreSQL 后台进程 (postmaster) 及其分配到的内存区域。一个实例可以管理多个数据库。它通常与一台服务器(物理或虚拟)一一对应。例如: 你在一台服务器上安装并启动了一个 PostgreSQL 服务,这就是一个实例。
  • 数据库 (Database): 是实例下最高级别的对象隔离单位。用于将不同的应用、项目或业务的数据隔离开来,防止意外交叉访问。例如: 你可以在一个实例中创建 app_prodapp_dev 两个独立的数据库。
  • 模式 (Schema): 位于数据库内部,是数据库对象的命名空间和容器,用于进一步组织表、视图、函数等对象。一个数据库可以有多个模式,默认有一个 public 模式。模式有助于实现多租户、模块化设计。例如:app_prod 数据库中,可以创建 hr_schema 存放人力资源相关的表,创建 finance_schema 存放财务相关的表。
  • 角色 (Role): 是 PostgreSQL 中权限的载体。它既可以用来代表一个用户 (User),也可以代表一个组 (Group)
    • 用户 (User): 是一个具有登录权限的角色 (LOGIN)。CREATE USER 命令本质上是 CREATE ROLE ... WITH LOGIN 的别名。
    • 组 (Group): 是一个没有登录权限的角色,用于将一组权限集合起来,方便分配给其他角色。

简单比喻实例就像一栋大楼,数据库是大楼里的不同公司,模式是公司里的各个部门(财务部、人事部),是部门里的文件柜,而角色则是拥有不同部门钥匙和文件柜访问权限的员工。

现在,让我们进入具体的操作部分。

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 的一套核心操作流程:

  1. 权限规划:首先创建角色并授予精确的权限集。
  2. 用户管理:创建用户并将其与角色关联,实现权限分配。
  3. 数据操作:在授权范围内对数据进行增删改查。
  4. 高级抽象:使用视图简化查询和加强安全,使用存储过程封装复杂业务逻辑。

理解实例、数据库、模式、角色之间的关系是有效管理和设计 PostgreSQL 架构的基础。始终遵循最小权限原则,只授予用户执行其任务所必需的最低权限,这是数据库安全的最佳实践。

This entry was posted in 应用. Bookmark the permalink.