03-PostgreSQL 基础管理

更新时间：2023 年 10 月

版本：PostgreSQL 16

`psql` 基础使用

普通参数
- -c, --command=COMMAND：执行命令并退出
- -d, --dbname=DBNAME：指定连接的数据库。默认为：postgres
- -f, --file=FILENAME：指定命令文件，执行文件中的命令并退出
- -l, --list：列出所有可用（有权限）的数据库，然后退出
- -v, --set=, --variable=NAME=VALUE：设置连接参数，例如：-v ON_ERROR_STOP=1
- -X, --no-psqlrc：不读取 psql 启动文件（~/.psqlrc）
- -1 ("one"), --single-transaction：将所有操作放在一个事务中执行
连接参数
- -h, --host=HOSTNAME：指定连接主机或 socket。默认：local socket
- -p, --port=PORT：指定连接端口。默认：5432
- -U, --username=NAME：指定连接的角色名。默认：当前操作系统用户名，一般为 postgres
- -w, --no-password：指定连接时不使用密码
- -W, --password：指定连接时使用密码（交互式）
输入和输出参数
- -a, --echo-all：输出从脚本中读取的所有输入命令
- -b, --echo-errors：输出执行失败的命令
- -e, --echo-queries：输出发送给服务器的 SQL 命令
- -E, --echo-hidden：显示由内部命令生成的查询
- -L, --log-file=FILENAME：将会话日志发送到指定的文件
- -n, --no-readline：禁用增强的命令行编辑（readline）
- -o, --output=FILENAME：将查询结果发送到指定的文件或管道
- -q, --quiet：安静模式，只输出查询结果，不输出任何消息
- -s, --single-step：单步模式，每执行一个查询都会先进行确认
- -S, --single-line：单行模式，每一行的结尾都会终止SQL命令
输出格式化参数
- -A, --no-align：以无对齐的表格输出模式显示结果
- --csv：以 CSV（逗号分隔值）表格输出模式显示结果
- -F, --field-separator=STRING：以无对齐格式输出，字段（field）分隔符默认为 |
- -R, --record-separator=STRING：以无对齐格式输出，记录（record）分隔符默认为换行符（\n）
- -z, --field-separator-zero：以无对齐格式输出，字段（field）分隔符默认为零字节（\0）
- -0, --record-separator-zero：以无对齐格式输出，记录（record）分隔符默认为零字节（\0）
- -H, --html：以 HTML 表格输出模式显示结果
- -P, --pset=VAR[=ARG]：设置打印选项 VAR 为 ARG（参见\pset命令）
- -T, --table-attr=TEXT：设置 HTML 表格标签的属性（例如：宽度、边框）
- -t, --tuples-only：仅打印记录，不打印字段名称
- -x, --expanded：启用扩展表格输出模式

示例

执行命令并退出

$ psql -h 127.0.0.1 -p 5432 -U postgres -W -d "employees" -c "select * from public.departments limit 3"

格式化输出

$ psql -h 127.0.0.1 -p 5432 -U postgres -W -d "employees" -c "select * from public.departments limit 3" --csv
#### 输出信息 
dept_no,dept_name
d009,Customer Service
d005,Development
d002,Finance

交互式命令

psql 登录后，可以使用交互式命令管理数据库

常用交互命令

帮助命令

\?：显示 psql 的帮助
\h [NAME]：显示某个 SQL 命令的帮助，* 代表所有命令

数据信息管理命令

注：S 可以显示系统对象，+ 会显示更多扩展信息

\l：列出所有数据库
\dn [PATTERN]：列出所有的 scheme
\d[S+]：列出所有表、视图、序列
\d[S+] [NAME]：列出所有表、视图、序列、索引的详细信息，NAME 为要查看的对象名称
\dt[S+] [PATTERN]：列出所有表
\di[S+] [PATTERN]：列出所有索引
\dv[S+] [PATTERN]：列出所有视图
\ds[S+] [PATTERN]：列出所有序列
\dg[S+] [PATTERN]：列出所有角色
\du[S+] [PATTERN]：列出所有角色（同\dg）

输入输出命令

\i FILE：从文件中执行命令，一般用于执行 SQL 文件
\o [FILE]：将之后所有指令的结果都发送到文件或管道

连接命令（常用于切换数据库、数据库用户等）

\c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo}

使用指定的用户连接到指定主机和数据库，USER 默认为当前用户，HOST 默认为 local host
\conninfo：打印当前的连接信息
\encoding [ENCODING]：显示连接的字符编码
\password [USERNAME]：修改指定用户的密码

变量设置命令

\set [NAME [VALUE]]：设置变量
\unset NAME：删除变量（置为默认值）

操作系统命令

\cd [DIR]：在操作系统中切换路径
\! [COMMAND]：运行操作系统 shell 指令

示例

切换路径

employees-# \cd /data/sql/

运行 SQL 文件

employees=# \i test.sql 
CREATE TABLE
INSERT 0 4

查询结果，将结果输出到指定文件

employees=# \o employees.out
employees=# SELECT * FROM public.employees;

运行 shell 命令查看结果

employees=# \! cat employees.out
 emp_no | birth_date | first_name | last_name | gender | hire_date  
--------+------------+------------+-----------+--------+------------
   1001 | 1980-01-01 | John       | Doe       | M      | 2020-01-01
   1002 | 1985-05-12 | Jane       | Smith     | F      | 2019-03-15
   1003 | 1990-07-15 | Alice      | Johnson   | F      | 2018-10-01
   1004 | 1982-10-10 | Bob        | Williams  | M      | 2022-02-05
(4 rows)

PG 的角色与权限

在 PostgreSQL 中，角色、用户、组这三者实际上是同一个实体（与 RBAC 鉴权方式不同）。角色可以有登录权限，也可以没有。当角色有登录权限时，这个角色就可以被看作是用户

注意事项

CREATE USER 和 CREATE ROLE 都用于创建角色。CREATE USER 是 CREATE ROLE 的一个别名。只是在创建时，CREATE USER会自动为新角色设置 LOGIN 权限，而 CREATE ROLE 默认的权限则是 NOLOGIN
角色是在数据库集群级别定义的，因此在集群中的所有数据库中都有效（与 MongoDB 有所不同）

角色管理

参考：PostgreSQL: Documentation: 16: CREATE ROLE

创建角色

格式

$ CREATE ROLE name [ [ WITH ] option [ ... ] ]

where option can be:

      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | BYPASSRLS | NOBYPASSRLS
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
    | VALID UNTIL 'timestamp'
    | IN ROLE role_name [, ...]
    | IN GROUP role_name [, ...]
    | ROLE role_name [, ...]
    | ADMIN role_name [, ...]
    | USER role_name [, ...]
    | SYSID uid

常用参数解释

SUPERUSER | NOSUPERUSER：确定新角色是否是超级管理员，超级管理员可以覆盖数据库中的所有访问限制。默认为：NOSUPERUSER
CREATEDB | NOCREATEDB：确定角色是否具有创建数据库的能力。默认为：NOCREATEDB
CREATEROLE | NOCREATEROLE：确定是否允许该角色创建、更改、删除、注释和更改其他角色。默认为：NOCREATEROLE
LOGIN | NOLOGIN：确定是否允许角色登录。CREATE ROLE 时默认是：NOLOGIN；CREATE USER 时默认是：LOGIN；
REPLICATION | NOREPLICATION：确定角色是否为复制角色，复制角色具有很高的权限，能在复制模式（物理或逻辑复制）下连接到服务器。默认为：NOREPLICATION
CONNECTION LIMIT <connlimit>：设置角色可以建立的并发连接数，仅对可登录角色有用
[ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL：设置角色密码。未指定密码无法登录。要求空密码时需显示指定为 PASSWORD NULL

注：关键字 ENCRYPTED 仅仅为了兼容性设置，并不起作用，实际加密方法由配置参数 password_encryption 确定
VALID UNTIL 'timestamp'：设置角色密码失效的日期和时间。如果省略此子句，密码将始终有效
IN ROLE role_name [, ...]：使新角色自动添加为指定现有角色（非管理员）的成员。新创建的角色将继承所列出角色的所有权限，（除非新角色被设置为 NOINHERIT）
ROLE role_name [, ...]：指定哪些已存在的角色应该成为新创建角色的成员。列出的角色将继承新创建角色的所有权限（除非已存在的角色被设置为 NOINHERIT）
ADMIN role_name [, ...]：类似于 ROLE role_name [, ...]，但还会授予列出角色对新角色的 WITH ADMIN OPTION 权限。意味着，被列出的角色不仅会成为新角色的成员，还可以为新角色添加或删除成员

示例

查看当前角色

-- postgres 是默认的超级管理员
employees=# \du
                             List of roles
 Role name |                         Attributes                         
-----------+------------------------------------------------------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS

创建角色并设置密码

CREATE USER davide WITH PASSWORD 'jw8s0F4';

创建一个密码有失效期的角色

CREATE ROLE miriam WITH LOGIN PASSWORD 'jw8s0F4' VALID UNTIL '2025-01-01';

创建一个可以创建数据库、可以管理角色的角色

CREATE ROLE admin WITH CREATEDB CREATEROLE;

查看角色创建情况

postgres=# \du
                             List of roles
 Role name |                         Attributes                         
-----------+------------------------------------------------------------
 admin     | Create role, Create DB, Cannot login
 davide    | 
 miriam    | Password valid until 2025-01-01 00:00:00+08
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS

删除角色

格式

DROP ROLE [ IF EXISTS ] name [, ...]

示例

删除指定角色

DROP ROLE IF EXISTS davide;

修改角色

参考：PostgreSQL: Documentation: 16: ALTER ROLE

修改角色权限的命令格式

权限设置与创建用户时一样

$ ALTER ROLE role_specification [ WITH ] option [ ... ]

where option can be:

      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | BYPASSRLS | NOBYPASSRLS
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
    | VALID UNTIL 'timestamp'

修改角色名的命令格式

$ ALTER ROLE name RENAME TO new_name

设置或重置会话级的配置参数的命令格式

$ ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | DEFAULT }

$ ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter FROM CURRENT

$ ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] RESET configuration_parameter

$ ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] RESET ALL

where role_specification can be:

    role_name
  | CURRENT_ROLE
  | CURRENT_USER
  | SESSION_USER

示例

修改 miriam 用户权限，设置为不可以登陆

postgres=# ALTER ROLE miriam NOLOGIN;

-- 查看设置后的权限
postgres=# \du miriam
                      List of roles
 Role name |                 Attributes                  
-----------+---------------------------------------------
 miriam    | Cannot login                               +
           | Password valid until 2025-01-01 00:00:00+08

权限管理

GRANT 授权有两种形式

授予数据库对象（表、列、视图、外表、序列、数据库、外部数据包装器、外部服务器、函数、过程、过程语言、大型对象、配置参数、模式、表空间或类型）权限
授予角色成员权限

数据库对象

PG 数据库对象常见的权限

SELECT：允许角色查询表、视图或序列的数据
INSERT：允许角色向表或视图中插入数据
UPDATE：允许角色更新表或视图中的数据
DELETE：允许角色从表或视图中删除数据
TRUNCATE：允许角色使用 TRUNCATE 命令快速清空一个表的全部数据
REFERENCES：允许角色在表的列上创建外键约束
TRIGGER：允许角色为表创建或删除触发器
CREATE
- 在数据库上（Database）：允许角色在该数据库中创建新的模式
- 在模式（Schema）上：允许角色在该模式中创建新的对象（如表、视图、序列等）
CONNECT：允许角色连接到指定的数据库
TEMPORARY 或 TEMP：运行角色在指定的数据库中创建临时表
EXECUTE：运行角色执行特定的函数或过程
USAGE
- 在序列（Sequence）上：允许角色查询或设置序列的当前值。但不能更改序列的其他属性
- 在模式（Schema）上：允许角色访问模式中的对象
- 在域（Domain）或数据类型（Type）上：允许角色使用指定的域或数据类型
- 在语言（Language）上：允许角色在该语言（ PL/pgSQL 或 PL/Python）中创建函数和过程
SET：允许角色更改特定配置参数的会话级设置
ALTER SYSTEM：运行角色修改 PostgreSQL 实例的配置文件（postgresql.auto.conf）
ALL PRIVILEGES：所有权限

授权

参考：PostgreSQL: Documentation: 16: GRANT

常用命令格式

授予数据库权限

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
    ON DATABASE database_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
    [ GRANTED BY role_specification ]

授予模式权限

GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
    ON SCHEMA schema_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
    [ GRANTED BY role_specification ]

授予数据表权限

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...]
         | ALL TABLES IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]
    [ GRANTED BY role_specification ]

授予数据表某些字段权限

GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
    [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
    ON [ TABLE ] table_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
    [ GRANTED BY role_specification ]

授予序列权限

GRANT { { USAGE | SELECT | UPDATE }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { SEQUENCE sequence_name [, ...]
         | ALL SEQUENCES IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]
    [ GRANTED BY role_specification ]

授予函数、存储过程权限

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
    ON { { FUNCTION | PROCEDURE | ROUTINE } routine_name [ ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) ] [, ...]
         | ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]
    [ GRANTED BY role_specification ]

示例

向所有角色授予 departments 表的查询权限

GRANT CONNECT ON DATABASE employees TO PUBLIC;

GRANT SELECT ON departments TO PUBLIC;

查看权限

-- 查看表权限
employees=> select * from information_schema.table_privileges where table_name='departments';
 grantor  | grantee | table_catalog | table_schema | table_name  | privilege_type | is_grantable | with_hierarchy 
----------+---------+---------------+--------------+-------------+----------------+--------------+---------
 postgres | PUBLIC  | employees     | public       | departments | SELECT         | NO           | YES

测试

employees=# \connect employees miriam;
Password for user miriam: 
You are now connected to database "employees" as user "miriam".

employees=> \dt
            List of relations
 Schema |    Name     | Type  |  Owner   
--------+-------------+-------+----------
 public | departments | table | postgres
 public | employees   | table | postgres
(2 rows)

-- 查询 departments 有权限
employees=> SELECT * FROM  departments limit 1;
 dept_no |    dept_name     
---------+------------------
 d009    | Customer Service
(1 row)

-- 查询 employees 没有权限
employees=> SELECT * FROM employees;
ERROR:  permission denied for table employees

权限回收

权限回收和授权是相反的操作，使用方法基本一致

常用命令格式

回收数据库权限

REVOKE [ GRANT OPTION FOR ]
    { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
    ON DATABASE database_name [, ...]
    FROM role_specification [, ...]
    [ GRANTED BY role_specification ]
    [ CASCADE | RESTRICT ]

回收模式权限

REVOKE [ GRANT OPTION FOR ]
    { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
    ON SCHEMA schema_name [, ...]
    FROM role_specification [, ...]
    [ GRANTED BY role_specification ]
    [ CASCADE | RESTRICT ]

回收数据表权限

REVOKE [ GRANT OPTION FOR ]
    { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...]
         | ALL TABLES IN SCHEMA schema_name [, ...] }
    FROM role_specification [, ...]
    [ GRANTED BY role_specification ]
    [ CASCADE | RESTRICT ]

回收数据表某些字段权限

REVOKE [ GRANT OPTION FOR ]
    { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
    [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
    ON [ TABLE ] table_name [, ...]
    FROM role_specification [, ...]
    [ GRANTED BY role_specification ]
    [ CASCADE | RESTRICT ]

回收序列权限

REVOKE [ GRANT OPTION FOR ]
    { { USAGE | SELECT | UPDATE }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { SEQUENCE sequence_name [, ...]
         | ALL SEQUENCES IN SCHEMA schema_name [, ...] }
    FROM role_specification [, ...]
    [ GRANTED BY role_specification ]
    [ CASCADE | RESTRICT ]

回收函数、存储过程权限

REVOKE [ GRANT OPTION FOR ]
    { EXECUTE | ALL [ PRIVILEGES ] }
    ON { { FUNCTION | PROCEDURE | ROUTINE } function_name [ ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) ] [, ...]
         | ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA schema_name [, ...] }
    FROM role_specification [, ...]
    [ GRANTED BY role_specification ]
    [ CASCADE | RESTRICT ]

示例

回收 departments 表的查询权限

REVOKE SELECT ON departments FROM PUBLIC;

PG 访问控制与认证

参考：PostgreSQL: Documentation: 16: Chapter 21. Client Authentication

PostgreSQL 提供了许多不同的客户端身份认证方法。可以根据（客户端）IP 地址、数据库和角色来选择用于验证特定客户端连接的方法

身份认证方法

参考：PostgreSQL: Documentation: 16: 21.3. Authentication Methods

PostgreSQL 支持很多身份认证方式，常见的认证方式有

Trust authentication：信任身份认证
Password authentication：密码认证
GSSAPI authentication：GSSAPI 身份认证，通常需要访问身份验证服务器，例如 Kerberos 或 Microsoft Active Directory 服务器
LDAP authentication：LDAP 方式身份认证，依赖于 LDAP 认证服务器
RADIUS authentication：RADIUS 方式身份认证，依赖于 RADIUS 认证服务器
Certificate authentication：证书认证，通过检查客户端发出的 SSL 证书来进行身份认证
PAM authentication：PAM 身份认证，依赖于 PAM 库（可插入身份验证模块，一般 Linux 都有安装）

`pg_hba.conf` 文件

参考：PostgreSQL: Documentation: 16: 21.1. The pg_hba.conf File

客户端身份认证的配置，由 pg_hba.conf 配置文件控制，在 initdb 初始化数据库时，会在数据目录中进行创建。可以通过 hba_file 配置参数指定该文件位置。该文件可以控制以下配置

允许哪个主机进行连接
客户端用什么方式进行身份认证
哪个 PostgreSQL 角色可以使用
允许访问哪个数据库

配置完成后，需要使用以下方法使配置生效

向服务器主进程发送 SIGHUP 信号；例：/bin/kill -HUP $MAINPID
命令行运行 pg_ctl reload ；例：/usr/pgsql-15/bin/pg_ctl reload -D /data/pgsql/15/
调用 SQL 函数 pg_reload_conf() ；例：SELECT pg_reload_conf();

文件格式

# TYPE              DATABASE  USER  ADDRESS/IP-address IP-mask    AUTH-METHOD   AUTH-OPTIONS
local               database  user  auth-method [auth-options]
host                database  user  address     auth-method  [auth-options]
hostssl             database  user  address     auth-method  [auth-options]
hostnossl           database  user  address     auth-method  [auth-options]
hostgssenc          database  user  address     auth-method  [auth-options]
hostnogssenc        database  user  address     auth-method  [auth-options]
host                database  user  IP-address  IP-mask      auth-method  [auth-options]
hostssl             database  user  IP-address  IP-mask      auth-method  [auth-options]
hostnossl           database  user  IP-address  IP-mask      auth-method  [auth-options]
hostgssenc          database  user  IP-address  IP-mask      auth-method  [auth-options]
hostnogssenc        database  user  IP-address  IP-mask      auth-method  [auth-options]
include             file
include_if_exists   file
include_dir         directory

字段含义

TYPE

local：仅匹配使用 Unix Socket 的连接
host：匹配使用 TCP/IP 的连接，ipv4 和 ipv6 均会匹配，无论是 SSL 还是非 SSL 连接

注意：默认 PostgreSQL 仅监听在本地地址，根据需要修改 listen_addresses 参数
hostssl：匹配使用 TCP/IP 的连接，ipv4 和 ipv6 均会匹配，只匹配 SSL 连接

注意：使用此选项时，服务器必须以支持 SSL 的方式构建。此外，必须通过设置 ssl 配置参数来启用 SSL。否则该记录将被忽略
hostnossl：与 hostssl 相反，仅匹配不使用 SSL 的 TCP/IP 连接
hostgssenc：仅匹配使用 GSSAPI 加密的 TCP/IP 连接

注意：使用此选项时，服务器必须支持 GSSAPI。否则该记录将被忽略
hostnogssenc：与 hostgssenc 相反，仅匹配不使用 GSSAPI 加密的 TCP/IP 连接

DATABASE

指定数据库名称，如果请求连接的数据库与指定的数据库名称相同，则记录匹配

该字段支持以下值：

<db-name>：匹配指定的数据库名称
all：匹配所有的数据库
sameuser：当连接的用户名（角色名）与数据库名称相同时，匹配
samerole：同 sameuser，当连接的用户名（角色名）与数据库名称相同时，匹配
replication：专门用于物理复制的复制连接

此外，该字段还支持正则表达式。当数据库名称以斜杠（ / ）开头，则数据库名称的其余部分将被视为正则表达式。例如：

# 匹配 miriam 角色连接所有以 emp 开头的数据库
host    /emp.*          miriam          0.0.0.0/0               scram-sha-256

有关 PostgreSQL 的正则表达式语法，请参考：PostgreSQL: Documentation: 16: 9.7. Pattern Matching

如果需要匹配多个数据库

可以通过用逗号分隔的方法声明多个数据库
可以将数据库名单独写入一个文件（每行一个数据库），然后通过前缀 @ 来声明这个包含数据库名的文件

USER

指定角色名称，如果请求连接的角色名与指定的角色名称相同，则记录匹配

角色名称可以是以下一些取值

<role-name>：匹配指定的角色名称
+<role-name>：匹配直接或间接属于该角色的任何角色成员
all：匹配所有的角色

此外，该字段还支持正则表达式。当角色名称以斜杠（ / ）开头，则数据库名称的其余部分将被视为正则表达式。例如：

# 匹配 mir 名称开头的角色连接 employees 数据库
host    employees          /mir.*          0.0.0.0/0               scram-sha-256

有关 PostgreSQL 的正则表达式语法，请参考：PostgreSQL: Documentation: 16: 9.7. Pattern Matching

如果需要匹配多个角色

可以通过用逗号分隔的方法声明多个角色
可以将角色名单独写入一个文件（每行一个角色），然后通过前缀 @ 来声明这个包含角色名的文件

ADDRESS/IP-address IP-mask

注：该字段只适用于 host、hostssl、hostnossl记录

指定连接地址，如果请求连接的客户端地址与指定地址相同，则记录匹配

支持以下值：

<IP-address>：匹配单个 IP 地址。例如：172.20.143.89
<CIDR Block>：：匹配 IP 地址和掩码表示的一个网络段，例如： 172.20.143.0/24 、fe80::7a31:c1ff:0000:0000/96。如果需要匹配单个 IP 地址，则掩码应该为 32，例如：172.20.143.89/32
<IP-address IP-mask>：同 <CIDR Block>

此外，还支持一些特殊值：

all：匹配所有地址
samenet：匹配与任何本地网络接口同一网络的客户端
samehost：匹配与本地机器 IP 地址相同的客户端地址
.<domain>：匹配指定域内的所有机器。例如，.example.com 将匹配 host.example.com、www.example.com 等域名，但不会匹配 example.com
localhost ：匹配本地连接，ipv4 和 ipv6 均匹配
127.0.0.1：匹配本地 ipv4 连接
::1：匹配本地 ipv6 连接
0.0.0.0/0：匹配所有 ipv4 连接
::0/0：匹配所有 ipv6 连接

AUTH-METHOD

指定连接与此记录匹配时要使用的身份认证方法，以下为一些常用的选择（所有选项都是小写的，并且区分大小写）：

trust：无条件允许连接
reject：无条件拒绝链接
scram-sha-256：使用 SCRAM-SHA-256 加密的密码认证

注：需要与配置的密码加密方式一致，配置：postgresql.conf 文件的password_encryption = scram-sha-256 参数
md5：使用 MD5 加密的密码认证

注：需要与配置的密码加密方式一致，配置：postgresql.conf 文件的password_encryption = md5 参数
password：使用明文密码认证
gss：使用 GSSAPI 认证
ldap：使用 LDAP 服务进行认证
radius：使用 RADIUS 服务进行认证
cert：使用 SSL 证书进行认证

注：需要开启 SSL 配置，参考 postgresql.conf 文件的 SSL 部分
pam：使用操作系统提供的可插入认证模块服务（PAM）进行认证

AUTH-OPTIONS

认证选项因认证方法而异，像 LDAP、PAM、RADIUS 等都有自己特定的认证选项，这些选项通过键值对的形式来设置

AUTH-OPTIONS 请参考各个认证方法进行配置：https://www.postgresql.org/docs/16/auth-methods.html

示例

# 允许在本机上的任何用户使用 Unix 域套接字（本地连接的缺省）以任何数据库用户身份连接任何数据库
# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     trust

# 同上，使用的是回环地址（loopback）的 TCP/IP 连接，适用于 ipv4
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             127.0.0.1/32            trust

# 同上，使用独立的子网掩码字段，适用于 ipv4
# TYPE  DATABASE        USER            IP-ADDRESS      IP-MASK             METHOD
host    all             all             127.0.0.1       255.255.255.255     trust

# 同上，使用的是 ipv6 连接
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             ::1/128                 trust

# 同上，包括 ipv4 和 ipv6
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             localhost               trust

# 使用正则表达式匹配数据库
# 匹配 db1、db2 和以 db 开头后面跟 2 到 4 位数字的数据库，例如 db1234、db12
# TYPE  DATABASE                   USER            ADDRESS          METHOD
local   db1,"/^db\d{2,4}$",db2     all             localhost        trust


# 允许 IP 地址为 192.168.93.% 的任何主机与 "postgres" 数据库相连
# 使用与客户端主机上相同 ident 的角色名标识进行认证（通常是操作系统用户名）
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    postgres        all             192.168.93.0/24         ident

# 允许 IP 地址为 192.168.12.10 的主机与 "postgres" 数据库相连
# 使用 scram-sha-256 加密的密码进行认证
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    postgres        all             192.168.12.10/32        scram-sha-256


# 允许 example.com 域中主机中的任何角色连接到任何数据库，使用 scram-sha-256 加密的密码认证
# 但 mike 除外，mike 使用 md5 加密的密码认证
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             mike            .example.com            md5
host    all             all             .example.com            scram-sha-256


# 假设这是唯一配置的四行
# 允许本地角色仅连接到他们自己的数据库（与数据库用户名同名的数据库）
# 有三个例外：名称以 helpdesk 结尾的角色、$PGDATA/admins 文件中包含的角色、support 角色成员，可以连接到所有数据库
# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   sameuser        all                                     md5
local   all             /^.*helpdesk$                           md5
local   all             @admins                                 md5
local   all             +support                                md5

# 可以将上面最后两行合并成一行
local   all             @admins,+support                        md5

# 数据库列表也可以使用文件，然后用 @ 引入
local   db1,db2,@demodbs  all                                   md5

PG 数据管理

数据库

创建数据库

# 命令行创建数据库
$ createdb mydb

# psql 创建数据库
postgres=# CREATE DATABASE mydb;

删除数据库

# 命令行创建数据库
$ dropdb mydb

# psql 删除数据库
postgres=# DROP DATABASE mydb;

查看数据库

-- psql 命令方式
postgres=# \l

-- 查询方式
postgres=# SELECT * FROM pg_database;

连接到指定数据库

# 切换为 postgres 用户
$ su - postgres

$ psql -h 127.0.0.1 -p 5432 -U postgres -W -d mydb

mydb=#

模式

在创建一个新的 database 时，PG 会自动为其创建一个名为 public 的 schema。如果未设置 searc_path 变量，那么 PG 会将你创建的所有对象默认放入 public 这个 schema 中

创建模式

mydb=# CREATE SCHEMA IF NOT EXISTS my_schema;

-- 指定所有者
mydb=# CREATE SCHEMA IF NOT EXISTS myschema AUTHORIZATION username;

查看模式

-- 查询方式
mydb=# SELECT schema_name FROM information_schema.schemata;


-- psql 方式
mydb=# \dn
        List of schemas
   Name    |       Owner       
-----------+-------------------
 my_schema | postgres
 public    | pg_database_owner
(2 rows)

删除模式

-- 仅删除模式
mydb=# DROP SCHEMA my_schema;


-- 删除模式以及关联的所有对象（表、视图等）
mydb=# DROP SCHEMA my_schema CASCADE;

更改默认模式

默认模式为 public，可以通过修改 search_path 来修改会话的默认模式

mydb=# SET search_path TO my_schema;

表

PG 的表是在 scheme 层级之下

创建表

mydb=# CREATE TABLE IF NOT EXISTS weather (
    city            varchar(80),
    temp_lo         int,           -- low temperature
    temp_hi         int,           -- high temperature
    prcp            real,          -- precipitation
    date            date
);

-- 创建表并指定所属模式
mydb=# CREATE TABLE IF NOT EXISTS my_schema.weather (
    city            varchar(80),
    temp_lo         int,           -- low temperature
    temp_hi         int,           -- high temperature
    prcp            real,          -- precipitation
    date            date
);

查看表

mydb=# \dt
          List of relations
 Schema |  Name   | Type  |  Owner   
--------+---------+-------+----------
 public | weather | table | postgres
(1 row)

修改表

mydb=# ALTER TABLE table_name SET SCHEMA my_schema;

删除表

mydb=# DROP TABLE weather;

SQL 脚本执行

方式一：`psql` 登录后交互式执行

切换路径

employees-# \cd /data/sql/

运行 SQL 文件

employees=# \i test.sql 
CREATE TABLE
INSERT 0 4

方式二：通过 `psql` 命令行执行

# 默认情况下，psql 脚本将在遇到 SQL 错误后继续执行。设置 ON_ERROR_STOP 可以在发生 SQL 错误时让 psql 退出
$ psql -h 127.0.0.1 -p 5432 -U postgres -W -d employees --set ON_ERROR_STOP=on -f /data/sql/test.sql默认情况下，psql 脚本将在遇到 SQL 错误后继续执行。您可能希望在设置变量的情况下 ON_ERROR_STOP 运行 psql 以更改该行为，并在发生 SQL 错误时让 psql 退出
Password: 
CREATE TABLE
INSERT 0 4

如果觉得文章对你有用，请随意赞赏

PostgreSQL

03-PostgreSQL 基础管理

https://www.skynemo.cn//archives/03-postgresql16-basic-manage

作者

Nemo

发布于

2023-10-02

更新于

2023-10-27

许可协议

CC BY 4.0

03-PostgreSQL 基础管理

psql 基础使用

登录

psql 常用参数

示例

交互式命令

常用交互命令

示例

PG 的角色与权限

角色管理

创建角色

删除角色

修改角色

权限管理

数据库对象

授权

权限回收

PG 访问控制与认证

身份认证方法

pg_hba.conf 文件

文件格式

字段含义

TYPE

DATABASE

USER

ADDRESS/IP-address IP-mask

AUTH-METHOD

AUTH-OPTIONS

示例

PG 数据管理

数据库

模式

表

SQL 脚本执行

方式一：psql 登录后交互式执行

方式二：通过 psql 命令行执行

作者

发布于

更新于

许可协议

评论

`psql` 基础使用

`psql` 常用参数

`pg_hba.conf` 文件

方式一：`psql` 登录后交互式执行

方式二：通过 `psql` 命令行执行