ch04 PostgreSQL 中的全局对象

简介

PostgreSQL 中包含一些安装时就创建的对象, 并且这些对象不会被绑定到任何独立的数据库中. 这些对象可以操作, 或在多个数据库或数据库对象 (例如 tables, indexes, functions 等) 中共享. 了解这些对象可以更好的操作 PostgreSQL.

结构

主要内容

  • Users/Groups/Roles
  • Tablespaces/Databases

目标

学习一些全局对象, 并介绍它的一些操作.

Users/Groups/Roles

你一定听过用户和组. 访问数据库的就是用户, 组是为了方便管理的对象. 但是什么是角色呢? 下面进行讨论.

在 PostgreSQL 8.1 之前, 用户和组是普遍使用的概念. 它们是两个独立的实体. 但是现在引入了角色, 它可以是用户与组的合体.

因此现在在 PostgreSQL 中讨论角色的时候, 本质上是在说一个数据库用户, 或一组数据库用户 (组). 这取决于如何设置. 使用角色就可以控制谁允许访问什么.

角色可以拥有不同的数据库对象, 可以持有多个权限. 它可以赋值给其他角色, 更进一步, 也可以将多个成员授予给其他角色.

可以使用 \du 来查看现有角色.

image-20240626103744087

角色 (Roles) 是全局对象, 是在数据库集群安装时创建的. 它不属于某个独立的数据库.

在安装新的 PostgreSQL 实例, 并使用 initdb 命令初始化时, 一个新的角色就会被创建, 同时该角色具有 superuser 特性. 该角色具有所有权限. 因此该角色可以创建其他角色, 并设置其特权, 也可以创建数据库. 该 "管理员" 角色会命名为初始化 PostgreSQL 的操作系统用户的名字, 通常是 postgres. 我们应该保护该用户的凭证, 因其超管的权限, 避免对系统造成破坏.

创建角色可以使用下面的语法. 同时可以为新与用户添加可选项. 例如 LOGIN 设置准备连接, PASSWORD 来设置密码:

CREATE ROLE demouser LOGIN PASSWORD 'supersecurepass';

进入 psql 后, 可以使用 \h CREATE ROLE 来查看帮助, 可以查看可选项:

image-20240626143423536

创建角色一个比较好的习惯是带上 CREATEROLE 和 CREATEDB, 而不是使用 SUPERUSER. 然后使用这个角色来创建其他角色与数据库.

如果想要删除角色, 可以使用:

DROP ROLE demouser;

注意结尾的分号, 与 MySQL 一样, 表示语句输入结束. 也可以使用 \g.

使用 \! clear 可以清屏

如果使用其他客户端连接到数据库, 而不是 psql, 可以查询 pg_roles 视图来查看角色:

SELECT * FROM pg_roles;

上述以经了解到如何创建角色或用户, 并使其具有 LOGIN 特性. 这个用户可以在数据库中开启一个新的会话. 然而我们必须记住什么角色具有什么权限, 以及将其成员扩展到其他角色中. 比较好的处理办法是权限挂载到组中, 然后将用户添加到组中.

将角色作为组来使用, 通常不会使用它来连接数据库, 开启新的会话. 因此不会添加 LOGIN, PASSWORD 特性. 因此一般可以使用下面代码来创建角色:

CREATE ROLE admins;

再一次解释了, 为什么角色是用户与组的结合体. 根据处理逻辑的不同, 有时角色就是用户, 有时是组.

然后, 为该组 (角色) 附加权限, 例如:

GRANT SELECT ON public.table1 TO admins;
GRANT SELECT ON public.table2 TO admins;

最后, 添加一个, 多多个用户到该组中 (通过授予角色到用户上)

GRANT admins TO demouser, testuser, produser;

实际上这里的用户, 组, 角色, 是一个类型的对象.

然后, 所有用户 demouser, testuser, produser 都可以在 public.table1, public.table2 上执行 SELECT 语句了.

创建角色时, 可以使用 INHERIT 或 NOINHERIT 来控制层级结构. 默认, 所有角色均可以从授予权限的角色上继承权限. 但是, 在创建角色时, 指定了 NOINHERIT, 那么该角色无法从已授予权限的角色中继承权限. 如此, 可已设计一些特别的控制. 例如:

CREATE ROLE sampleuser LOGIN INHERIT;
CREATE ROLE admin NOINHERIT;
CREATE ROLE reader NOINHERIT;
GRANT admin TO sampleuser;
GRANT reader TO admin;

任何授予 admin 的权限, sampleuser 都具备, 因为它配置了 INHERIT.

但是, 即使 sampleuser 间接的成为了 reader 的成员, 但是它不会含有 reader 的任何权限. 因为, 这个关系经过了 admin, 它不允许继承权限 (admin 是 NOINHERIT, 它无法继承其他角色的权限).

最后, 看看被称之为预定义的角色. 这些角色在数据库初始化时被创建. 这些角色组含有特定的特性与权限, 并且使用其作用来命名. 这么处理有利于管理与之有关的任务.

然后作者给出一个表格, 列举出了一个角色与其作用的表. 这个表可以在社区文档中可以查阅到, 但需要注意这些可能会随版本的变化而变化.

社区文档中给出了 11 个预定义角色, 而本书中列举出了 9 个.

角色允许访问
pg_read_all_data允许从所有的数据表, 视图, 和序列中读取数据. 因为在这些对象上具有 SELECT 权限, 在所有的 Schema 上具有 USAGE 权限.
pg_write_all_data允许在所有的表, 视图, 和序列中写入数据. 因为这些对象具有 INSERT, UPDATE, 和 DELETE 权限. 同时所有的 Schema 中具有 USAGE 权限.
pg_read_all_settings允许读取所有配置变量, 包括仅对超级用户可见的配置变量.
pg_read_all_stats允许读取所有 pg_stat_* 视图, 并使用各种统计相关扩展.
pg_monitor允许读取/执行各种监控视图和功能. 这是设置监视应用程序用户的最佳选项.
pg_signal_backend允许向另一个后端发送信号以取消查询或终止其会话.
pg_read_server_files允许从数据库在服务器上可以访问的任何位置读取文件.
pg_write_server_files允许写入数据库在服务器上可以访问的任何位置的文件.
pg_execute_server_program允许以数据库运行用户的身份在数据库服务器上执行程序.

表空间 (Tablespaces)

PostgreSQL 中表空间就是操作系统上的一个路径, 用于存储数据库对象. 如表, 索引等.

当 PostgreSQL Database Cluster 初始化时, 在 Data Directory 中会默认创建两个表空间. 分别是: pd_global 和 pg_default.

  • pg_global 用于存储所有的 catelog system data.
  • pg_default 是默认表空间, 用于存储所有的 template0, template1 数据库的数据.

因此, 在创建新数据库时, 不作任何处理, 该属性会将数据库当做 template 数据库.

要创建表空间, 我们需要一个已存在的路径, 同时运行 postres 实例的用户也需要拥有路径的访问权限. 通常是 postgres 用户.

image-20240626175127217

CREATE TABLESPACE newspace LOCATION 
  '/var/lib/postgresql/newspace'

image-20240626175718112

查询已存在的表空间, 使用 psql 命令 \db, 获得查询视图 pg_tablespace

image-20240626175942997

使用 \db+ 可以获得更为详细的信息

image-20240626180051149

表空间主要有两个作用:

  1. 扩展磁盘时扩展数据存储.
  2. 充分利用存储设备性能 (将数据按磁盘存放, 可以提升 IO, 例如索引, 热数据等).

数据库

首先介绍数据库和实例.

  • 实例由端口, 数据域 (数据目录) 构成, 它包含一个服务.

  • 服务器上可以含有一个, 或多个数据库实例. 它们有独立的端口, 数据目录.

  • 数据库集群 (database cluster), 包含多个数据库, 用户/角色, 以及多个表空间.

  • 一个 postgres 实例总是恰好的管理一个 database cluster 中的数据.

database 是 instance/cluster 的一部分.

每一个 DB Instance/DB Cluster 都包含多个 database.

一个 database 是多个 database schema 的集合. 而 database schema 存储 database 对象, 如表, 视图, 包, 函数等.

数据库是实例的一部分, 在 PostgreSQL 对象架构中的顶层.

然后作者用, 屋子, 房间, 衣柜, 抽屉来比喻实例, 数据库, 架构, 数据库对象.

使用 \l 可以查看默认安装的数据库

image-20240626224200002

CREATE DATABASE 命令

创建数据库使用 CREATE DATABASE 命令, 使用 psql 的元数据命令 (meta-command) \h 可以查看语法:

image-20240626224409614

最简单的创建数据库的语法为:

CREATE DATABASE <database_name>;

image-20240626224558580

createdb 程序

该程序是 CREATE DATABASE 的包装程序:

image-20240626230332225

使用 createdb --help 可获得帮助

image-20240626230412306

使用 pgAdmin 向导

pgAdmin 是一个 GUI 程序. 网址: https://www.pgadmin.org/

下载与安装

下载后, 双击安装. 然后下一步, ...即可

image-20240626231232385

看起来是 Electron 进行开发的.

安装完成后双击运行, 进入初始化也没, 第一次会等待一段时间.

前期准备

数据库配置

连接到 PostgreSQL 需要一些配置

  1. 修改配置文件, 设置监听端口.
  2. 创建秘密.
  3. 修改 pg_hba.conf 配置文件中绑定的 IP.

通过查看后台进程, 来查看配置文件目录:

ps -ef | grep postgres

image-20240627112118196

配置文件目录为: /etc/postgresql/14/main/postgresql.conf, 打开配置文件, 找到 listen_addresses, 修改为服务器 IP.

sudo vim /etc/postgresql/14/main/postgresql.conf

image-20240627112350310

默认 PostgreSQL 监听 localhost.

在配置文件所在的目录, 还有一个文件: pg_hba.conf

image-20240627140242080

在该文件结尾处添加:

host    all    all    0.0.0.0/0    md5

最后重启数据库服务.

使用二进制安装后, 会自动添加后台服务:

image-20240627140628141

可以使用下面命令查看运行状态:

/etc/init.d/postgresql status

image-20240627140822818

使用下面命令重启 PostgreSQL 服务:

sudo systemctl restart postgresql

或使用 service 命令

sudo service postgresql restart

也可以使用 pg_ctl 程序:

sudo -u postgres pg_ctl restart -D /var/lib/postgresql/<version>/main

注意版本, 以及路径. 细节可以参考: https://www.postgresqltutorial.com/postgresql-administration/postgresql-restart-ubuntu/

设置数据库密码

进入数据库后, 使用 \password [USER] 来设置密码

image-20240627141810820

使用 pgAdmin 连接

image-20240627141953693

连接成功后即可看到数据库运行状态:

image-20240627142129911

使用 pgAdmin 创建数据库

右击, 左侧树中的 Database Cluster, 在弹出上下文菜单中选择 Create Database 选项.

image-20240627142910825

或在 Database 节点上右键.

在弹出的向导对话框中输入数据库名字, 以及所有者. 并编辑注释.

image-20240627143403013

然后, 在 Definition 选项卡中设置表空间

image-20240627143338785

设置完成后可以切换至 SQL 选项卡, 会自动生成对应的 SQL 语句:

image-20240627143510863

创建后可以在左侧树中看到新数据库

image-20240627143655626

最后除了创建, 还有 ALTER DATABASE, DROP DATABASE, 以及 dropdb 可以使用, 来进行修改, 删除数据库.

Last Updated:
Contributors: jk