PostgreSQL 数据库基本操作指南

52 views Posted on 2025-09-02 by 林坤源

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)： 是一个没有登录权限的角色，用于将一组权限集合起来，方便分配给其他角色。

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

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

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.