摘要

本文为 PostgreSQL 从入门到精通系列第三篇，系统讲解 PostgreSQL 中数据库、表、用户及权限的核心管理语法。通过大量实战命令与示例，从创建、查看、修改到删除，全方位覆盖 DDL 操作，帮助开发者快速掌握 PG 的基础对象管理，为后续 SQL 开发与数据库安全打下坚实基础。

---

前言

掌握了 PostgreSQL 的安装与配置后，我们正式进入数据库的核心操作阶段。无论是开发还是运维，对数据库、表、用户和权限的管理都是最基础、最核心的技能。

本文将通过实战化的方式，带你逐一学习：

• 如何创建、查看和删除数据库
• 如何设计并创建数据表，理解常用约束
• 如何管理数据库用户与角色
• 如何进行精细化的权限控制

这些内容是后续所有 SQL 操作的基石，务必熟练掌握。

---

一、数据库（Database）管理

1.1 创建数据库

-- 创建一个名为 mydb 的数据库
CREATE DATABASE mydb;

-- 创建数据库时指定所有者和编码
CREATE DATABASE mydb2 
OWNER postgres 
ENCODING 'UTF8' 
LC_COLLATE 'zh_CN.UTF-8' 
LC_CTYPE 'zh_CN.UTF-8';

1.2 查看所有数据库

-- 使用 psql 元命令
\l

-- 使用 SQL 查询系统表
SELECT datname FROM pg_database;

1.3 连接数据库

-- 使用 psql 元命令
\c mydb

-- 使用连接字符串
psql -U username -d mydb -h host -p port

1.4 删除数据库

-- 删除数据库（谨慎操作！）
DROP DATABASE mydb2;

-- 如果数据库存在则删除（避免报错）
DROP DATABASE IF EXISTS mydb2;

---

二、数据表（Table）管理

2.1 创建数据表

-- 切换到要操作的数据库
\c mydb;

-- 创建一个用户表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,  -- 自增主键
    username VARCHAR(50) NOT NULL UNIQUE,  -- 用户名，非空且唯一
    email VARCHAR(100) NOT NULL UNIQUE,    -- 邮箱，非空且唯一
    password VARCHAR(100) NOT NULL,        -- 密码
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 创建时间，默认当前时间
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP  -- 更新时间，默认当前时间
);

-- 创建一个文章表，并设置外键关联 users 表
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    author_id INTEGER NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    -- 外键约束，关联 users 表的 id
    FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE CASCADE
);

2.2 查看表结构

-- 查看指定表的结构
\d users;

-- 查看所有表
\dt

2.3 修改数据表

-- 给 users 表添加一个 age 字段
ALTER TABLE users ADD COLUMN age INTEGER;

-- 修改 age 字段的类型
ALTER TABLE users ALTER COLUMN age TYPE VARCHAR(3);

-- 删除 age 字段
ALTER TABLE users DROP COLUMN IF EXISTS age;

-- 给 articles 表的 title 字段添加索引
CREATE INDEX idx_articles_title ON articles(title);

2.4 删除数据表

-- 删除数据表（谨慎操作！）
DROP TABLE articles;

-- 级联删除（同时删除依赖它的对象，如外键）
DROP TABLE IF EXISTS users CASCADE;

---

三、用户与角色（User & Role）管理

PostgreSQL 中用户和角色的概念是统一的，一个角色可以是用户，也可以是一组用户的集合。

3.1 创建用户 / 角色

-- 创建一个名为 dev_user 的用户，并设置密码
CREATE ROLE dev_user WITH LOGIN PASSWORD 'Dev@123456';

-- 创建一个不允许登录的角色（用于权限管理）
CREATE ROLE read_only_role;

3.2 查看用户 / 角色

-- 使用 psql 元命令
\du

-- 使用 SQL 查询系统表
SELECT rolname FROM pg_roles;

3.3 修改用户 / 角色

-- 修改用户密码
ALTER ROLE dev_user WITH PASSWORD 'NewPass@654321';

-- 给用户添加超级用户权限（谨慎！）
ALTER ROLE dev_user WITH SUPERUSER;

3.4 删除用户 / 角色

-- 删除用户
DROP ROLE IF EXISTS dev_user;

---

四、权限（Privilege）管理

精细化的权限控制是数据库安全的核心。

4.1 授予权限（GRANT）

-- 授予用户 dev_user 对 mydb 数据库的所有权限
GRANT ALL PRIVILEGES ON DATABASE mydb TO dev_user;

-- 授予用户 dev_user 对 users 表的查询、插入、更新权限
GRANT SELECT, INSERT, UPDATE ON TABLE users TO dev_user;

-- 授予角色 read_only_role 对 articles 表的查询权限
GRANT SELECT ON TABLE articles TO read_only_role;

-- 将角色 read_only_role 的权限授予用户 dev_user
GRANT read_only_role TO dev_user;

4.2 撤销权限（REVOKE）

-- 撤销用户 dev_user 对 users 表的更新权限
REVOKE UPDATE ON TABLE users FROM dev_user;

-- 撤销用户 dev_user 的 read_only_role 角色
REVOKE read_only_role FROM dev_user;

4.3 实战示例：创建一个只读用户

-- 1. 创建只读角色
CREATE ROLE app_readonly;

-- 2. 授予该角色对所有现有表的只读权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;

-- 3. 授予该角色对未来新建表的只读权限（自动继承）
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO app_readonly;

-- 4. 创建应用用户并赋予只读角色
CREATE ROLE app_user WITH LOGIN PASSWORD 'App@User123';
GRANT app_readonly TO app_user;

---

总结

本文系统学习了 PostgreSQL 中库、表、用户、权限的基础管理语法。从创建到删除，从基础定义到约束关联，再到精细化的权限控制，这些操作构成了数据库管理的基石。熟练掌握这些命令，你就能应对日常开发中大部分的数据库对象管理工作。