博客

PG数据库入门知识

  • Post author:
  • Post category:其他




前言

Linux和windows的路劲分隔符是不同的，Linux下是斜杠/,而windows是反斜杠（\）。但在PG里window下也要使用linux的/作为路劲分隔符。



基础知识



为什么选择PG

PostgreSQL是一款企业级关系型数据库管理系统。PostgreSQL之所以如此特别，是因为它不仅仅是一个数据库，还是一个功能强大的应用开发平台。

PostgreSQL在数据类型的支持方面有两个优势，不但支持比绝大多数数据库更丰富的原生数据类型，而且还允许用户按需求自定义数据类型。

PostGRESQL同样还允许用户重定义基础运算符。

PostgreSQL会为每一张用户表自动创建一个数据类型的定义。你比如说我创建一个名为dogs的表，那么PosrgreSQL就会自动在后台创建一个同名的

dogs数据类型

，这一特性将关系型数据库领域的表的概念与面向对象领域的对象概念紧密的联系到了一起，用户可以像处理对象实例一样去处理记录。比如你可以创建一个函数来每次处理一个或者一批对象实例。



不适用PG的场景

  1. 在不安装任何扩展包的情况下，PG需要占用100MB以上的磁盘空间，可以看出它的个头是比较大的，因此在一些存储空间极为有限的小型设备上使用PG是不合适的。因此在一些存储空间极为有限的小型设备上使用PG是不合适的，把PG当成简单的缓存区来用也是不合适的，此时应选用一些更轻量级的数据库。
  2. 因为作为一款企业级数据库产品，PG对其安全也是极其重视的，因此，如果你在开发一个把安全管理放到应用层去做的轻量级应用，那么PG完善的安全机制反倒会成为负担，因为它的角色和权限管理非常复杂，会带来不必要的管理复杂度和性能损耗。

鉴于上面的种种，PG数据库一般是会和别的数据库搭配使用，使他们各展所长。一种常见的组合是把Redis当成PG的查询缓存来用，另一种的组合是用PG做主数据库。



PG数据库对象



database

每个PG服务可以包含多个独立的database



schema

如果把databases比作一个国家，那么schema就是一些独立的省。大多数对象是隶属于某个schema的，然后schema又隶属于某个databases。在创建一个新的database时，PG会自动为其创建一个名为public的schema。如果未设置searc_path变量，那么PG会将你创建的所有对象默认放入public schema中。如果表的数量较少，这是没问题的，但是如果你有几千张表，那么我们还是建议你将他们分门别类放入不同的schema中。



表

任何一个数据库中，表都是最核心的对象类型。在PG中，表首先属于某个schema，而schema有属于某个database，这样就构成一种三级存储结构。PG的表支持两种很强大的功能。第一种是继承，即一张表可以有父表和子表，这种层次化的结构可以极大的简化数据库设计，还可以为你省掉大量的重复查询代码。第二种是创建一张表的同时，系统会自动为此表创建一种对应的自定义数据类型。



PG数据库管理



配置文件


配置文件在data目录下



postgresql.conf

在9.4的版本里引入了一个新的名为postgresql.auto.conf的配置文件，其中配置项会覆盖postgresql.conf的同名配置项。所以建议不要修改postgresql.conf，而是优先修改postgresql.auto.conf


该文件中包含一些通用的设置，比如内存分配 ，新建database的默认存储位置，postgresql服务器的ip地址，日志以及许多其他设置。


  1. 查看postgresql.conf视图即可查看所有配置项内容，无需打开配置文件。
     
SELECT
	name,context,unit,setting,boot_val,reset_val
FROM pg_settings
WHERE name IN ('listen_addressed','deadlock_timeout','shared_buffers','effective_cache_size','work_mem','maintenance_work_mem')
ORDER BY context,name;

image-20230201141525888

  • context字段代表配置项的作用范围。

    user表示用户级配置项，他可以被每个用户单独修改，也就是说该配置项针对每个用户都可以有不同的值，用户修改后会在自己的所有会话中生效。如果是超级用户修改了一个user级的配置项，那么此后链接上的用户都会将这个修改过的值作为默认值。

    context值为superuser表示是超级用户级配置项，只能由超级用户修改，修改并且重新加载后会在所有用户会话中生效。非超级用户不能在自己的会话中修改覆盖这个值。

    context值为postmaster表示是整个服务实例级配置项，更改后需要重启postgresSQL服务才能生效。

    context为usr和superuser的配置项可以在database级，用户级，会话级和函数级分别设置。比如说如果会写很长sql的用户来说，work_mem参数应该设置大一些；再比如有密集的排序操作，也就可以调大work_mem的值。database级，用户级，会话级和函数级的参数设置不需要执行重新加载操作。数据库级的参数设置会在用户下次连接到该数据库时生效。会话级和函数级的参数设置立即生效。

  • 我们要注意内存相关参数所用的单位。在上图我们可以看到，内存相关参数，有些是8KB有些是KB

  • setting是指当前设置；boot_val是指默认设置；reset_val是指重新启动服务器或重新加载设置之后的新设置。修改 了设置后，一定要去查看setting和reset_val并确保二者是一致的，否则就说明设置并未生效，需要重启服务器或者重新加载设置。


  1. pg的9.5版本引入了一个新的pg_file_settings视图，通过该视图也可以进行配置信息查询。查询该视图会列出每个配置项所属的配置文件。其中applied字段表示该配置项是否已经生效，如果值为f，表示需要重启服务器或者重新加载配置文件。如果postgresql.conf和postgresql.auto.conf中存在同名配置，那么后者会覆盖前者，并且前者在pg_file_settings中对应的条目会显示applied字段为f

    。 
SELECT name,sourcefile,sourceline,setting,applied
FROM pg_file_settings
WHERE name IN ('listen_addresses','deadlock_timeout','shared_buffers','effective_cache_size','work_mem','maintenance_work_mem')
ORDER BY name;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Xyqx17ME-1678169115200)(https://picture-1312074320.cos.ap-beijing.myqcloud.com/typroa/image-20230201172342278.png)]

以下几个配置项需要注意，不然可能导致客户端无法连接：


  • listen_addresses

表示postgresql服务使用的ip地址，我这里设置的*，表示使用任意的ip均可连接到POSTgresql数据库。


  • port

pg库的侦听端口，默认值为5432.


  • max_connections

系统允许的最大并发连接数


  • log_destination

定义日志文件的输出格式，默认值是stderr。如果是要记日志的话切记要讲logging_collection配置项设为on。

下面介绍的这些配置项会影响系统的整体性能，其默认值一般不是最优的。建议根据实际情况进行调整。


  • shared_buffers

此设置定义了用于缓存最近访问过的数据页的内存区大小，所有用户会话均可共享此缓存区。此设置对查询速度有着重大影响，一般来说设置的越大越好，至少应该达到系统的总内存的25% ，但不适宜超过8GB，因为超过会出现边际收益递减效应，即消耗的内存很多，但得到的速度提升却很少，这是得不偿失的，意义不大的。


  • effective_cache_size

该配置是一个估算值，表示操作系统分配多少内存给PG专用。系统并不会根据这个值来真实地分配这么多内存，但是规划器会根据这个值来判断系统是否能提供查询执行过程所需的内存。如果将此值设置的过小，远远小于系统的真实可用内存量，那么可能会给规划器造成误导，让规划器认为系统可用内存有限，从而选择不使用索引而是执行全表扫描（因为使用索引虽然速度快，但需要占用更多的中间内存）。在一台专用于 运行pgsql数据库服务的机器上，建议是将effective_cache_size的值设为系统总内存的一般或者更多。


  • work_mem

此设置指定了用于执行排序，散列关联，表扫描等操作的最大内存大小。要得到此设置的最优值需要考虑以下因素：数据库的使用方式，需要预留多少内存给除数据库系统外的程序，以及服务器是否专用于pgsql服务等问题。如果使用场景仅仅是有很多用户并发执行简单查询，那么这个值可以设得小一点，这样每个用户都得以较为公平的使用内存，否则第一个用户就可能会把内存占光。这个值该设多大同样取决于你的机器总共有多少内存可用


  • maintenance_work_mem

此设置指定了可用于vaccum(即清空已标记为“被删除”状态的记录)这类系统内部维护操作的内存总量。其值不应大于1GB，此设置的更改可动态生效，执行重新加载既可。


  • max_parallel_workers_per_gather

这是9.6版本新引入的一个配置项，用于控制语句执行的并行度。该配置项决定了执行计划的每个gather节点中最多允许启动多少个worker进程并行工作。默认值为0，表示不启用并行功能。


  1. 修改postgresql.conf中配置项的值
     
ALTER SYSTEM

这是9.4版本之后引入的一个命令，这个命令可以更改设置。

🎯例如：设置一个全局生效的work_mem： 

ALTER SYSTEM work_mem = '500MB';

这个命令呢是不会直接修改postgresql.conf文件本身，而是去修改postgresql.auto.conf文件。

每个设置有何各自不同的特性，有的更改后必须重启数据库服务才能生效，有的只要重新加载一次就可以了，下面这个命令可以实现设置重新加载： 

SELECT pg_reload_conf();

如果你需要时常修改很多配置项，那么可以尝试将他们分门别类存放到多个配置文件中，然后通过在postgresql.conf中使用include或者include_if_exists前缀来引入这些配置文件。具体语法如下： 

include '配置文件名'

这里的配置文件可以是绝对路径也可以是相对路径，相对路径的起始位置就是postgresql.conf文件本身所在的位置。


  1. 修改了postgresql。conf文件，结果数据库无法启动，该如何？

    首要的解决方式为：日志文件，该文件位于postgresql数据文件夹的更目录或者pg_log子文件下。去查看日志文件的最后一行。常见的错误有：shared_buffers设得太大了。还有一个常见问题是由于上次系统异常关闭导致遗留了一个没来得及删除的postmaster.pid文件，该文件处于数据文件下，你可以手动删除该文件并启动postgresql。



pg_hba.conf

该文件用于控制Postgresql服务器的访问权限，具体包括：允许哪些用户连接到哪个数据库，允许哪些ip地址连接到本服务器，以及指定连接时使用的身份验证模式。该文件的修改是可动态生效的，执行一次配置重加载既可。一个典型的pg_hba.conf文件如下图所示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QglmeoS8-1678169115200)(https://picture-1312074320.cos.ap-beijing.myqcloud.com/typroa/%E5%BE%AE%E4%BF%A1%E5%9B%BE%E7%89%87_20230215090734.jpg)]

1️⃣身份验证模式。一般有以下几种常用选项：ident，trust，MD5，peer以及passwd。

2️⃣用于定义ipv6网段。轧制油服务器支持ipv6才可以配置该项，如果在非ipv6网络环境下配置了这样的条目，会导致pg_hba.conf文件无法加载，从而进一步导致任何客户端都无法连接。

3️⃣用于定义ipv4网段。第一部分是网络地址，后面跟着子网掩码。

4️⃣这是针对SSL连接的规则。在本图中，任何使用SSL方式连接的客户端都可以连接到PGSQL实例上去。

SSL相关配置在postgresql.conf和postgresql.auto.conf中，包含一下几项：ssl，ssl_cert_file,ssl_key_file。一旦确认客户端支持SSL，postgresql服务端就会接受其连接请求，并且该连接上的所有传输内容都会使用ssl key加密。

5️⃣这是允许与本节点构成复制关系的其他Postgresql服务器节点的IP网段。

对于每一个连接请求，就像防火墙一样，这玩意是

从上到下匹配规则链

的。

PostgreSQL10 中引入了一个新的名为pg_hba_file_rules的视图，通过查询该视图可以直接看到pg_hba.conf中的内容。


  1. 修改pg_hba.conf文件，结果服务器奔溃了

    、

    don’t害怕，因为如果postgres服务无法正确的解析pg_hba.conf文件，那么为确保系统安全，他会禁止所有的连接请求甚至是禁止系统启动。你去看一手日志，然后将错误的内荣改正就好，如果你经常会改错，那就事先记得备份一手。

  2. 身份验证方法

    最常用的身份验证方法有以下这些：


    • trust

      这是最不安全的身份验证模式，用户无需提供密码就可以连接到数据库，只要源端IP地址，连接用户名，要访问的databases名都与该条规则匹配，用户就可以连上来。trust模式很不安全，因此要有所限制。


    • MD5

      该模式很常用，要求连接发起者携带MD5算法加密的密码。


    • password

      该模式要求连接发起者携带明文密码进行身份验证。


    • ident

      该身份验证模式下，系统会将请求发起者的操作系统用户映射为postgresSQL数据库内部用户，并以该内部用户的权限登陆，且此时无需提供登陆密码。Windows上是不支持ident验证方式。


    • peer

      该模式下系统会直接从操作系统内核获取当前连接发起者的操作系统用户名，如果与其请求连接的postgresql用户名一致，即可连接成功。该模式仅可用于Linux，BSD，macOS和Solaris，并且仅可用于本地服务器发起的连接。


    • cert

      该模式要求客户端必须使用SSL方式进行连接。连接发起者必须提供一个合法的SSL做证书。该模式使用一个身份认证文件来讲SSL证书文件映射为postgresql数据库的内部用户 ，该模式支持只在所有支持SSL连接的平台上都可用。

image-20230223155422658

配置文件你得这么写他才要密码呢，不然全是trust。


他这个连接的匹配规则为：从上到下，如果有一条符合就不再往下匹配

举个栗子：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qEwpm7p0-1678169115201)(https://picture-1312074320.cos.ap-beijing.myqcloud.com/typroa/%E4%B8%BE%E4%B8%AA%E6%A0%97%E5%AD%90.gif)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PfkZpetZ-1678169115201)(https://picture-1312074320.cos.ap-beijing.myqcloud.com/typroa/image-20230224165540215.png)]

对于上面的这样的规则，本地的登陆的时候，第一条是需要密码的，那么他的登陆模式就是需要密码的。



pg_ident.conf

如果该文件存在，则系统会基于文件内容将当前登录的操作系统用户映射为一个postgreSQL数据库内部用户的身份来登录。有些人会把操作系统的root用户映射为postgreSQL的postgres超级用户账号。

在PG的官方术语体系中，角色role就表示用户，但并不是所有的角色都需要具备登录权限，比如组角色通常就不需要。

如果你在安装过程中使用了默认配置，则上述的文件会位于Postgresql主数据文件夹中。当然，如果你不确定这些文件的具体位置，以超级用户身份连接到任何一个数据库上并执行下面的查询语句就会查到： 

SELECT name ,setting FROM pg_settings WHERE category='File Locations';

image-20230201102515089



让配置文件生效

有些配置项修改后会需要重启pgsql服务实例，但是有的配置项修改只需要重新加载一下配置文件既可生效，如何判断呢？

我们可以查看该配置项的context属性，如果是postmaster ，那么需要重启；如果是user，那么重新加载配置文件既可。

你说怎么查context属性？？？上面有，你可以看看那个select pg_setting那个语句。

  1. 重新加载配置文件 
	pg_ctl reload -D  /usr/local/pgsql/data  (后面跟的是你的数据目录)

  1. 重新加载pg服务 

    pg_ctl  restart -D /usr/local/pgsql/data  (后面跟的是你的数据目录)



连接管理

🏭我们呢可能时不时地会遇到一些想要终止数据库连接的情况，比如有人执行了写得很糟糕的SQL语句，把系统资源耗光。发生这个呢，我们当然是希望能结束这些操作或者彻底干脆终止这个连接。

想要终止正在执行的语句并杀掉连接，使用以下步骤：

  1. 查出活动连接列表及其进程ID 
SELECT * FROM pg_stat_activity;

pg_stat_activity视图包含每个连接上在最近一次执行的语句，使用的用户名（usename字段），所在的databases名（datname字段）以及语句开始执行的时间。通过查询该视图可以找到需要终止的会话所对应的进程ID。

  1. 停止连接（假设对应的进程号为1234）上的活动查询 

    SELECT pg_cancel_backend(1234);

    该操作不会终止连接本身

  2. 终止该连接 

    SELECT pg_terminate_backend(1234);

    有时候你会需要终止这个连接，特别是执行数据库恢复之前。如果仅仅是终止了正在执行的语句而没有彻底杀掉连接，客户端是可以立即重新执行刚刚被终止掉的语句的，这又会导致系统陷入之前的状态。为了避免此种情况的发生，可以采用直接终止连接的方式。如果你未停止某个连接上正在执行的语句就直接终止该连接，那么这些语句也会被停止掉。

Postgresql支持在select查询语句中调用函数。因此，尽管pg_terminate_backend和SELECT pg_cancel_backend一次仅能处理一个连接，但你可以通过select语句中调用函数的方式实现一次处理多个连接。

📦比如说希望一次性终止某个用户的所有连接，那么可以执行以下语句。 

SELECT pg_terminate_backend(pid) FROM pg_stat_activity
where  usename  = 'some_role';

Postgresql有一些语句参数可以用来控制语句的运行状态，一旦语句运行期间的某些状态值超过了这些运行参数所限定的范围，该语句会被系统自动杀掉。这些参数可以在服务实例级，databases级，用户级，会话级和函数级设置。参数值设为0代表禁用。


  • deadlock_timeout

    该参数表示阿紫进行死锁检测之前需要等待多久。默认是1000毫秒。如果你的业务系统中有大量更新操作，那么建议增大该值以减少死锁检测的次数。

    当然，与其依赖这个参数就解决死锁，其实是更建议在UPDATE语句中加NOWAIT子句来避免死锁。

    例如： 

    SELECT FOR UPDATE NOWAIT 。。。


  • statement_timeout

    该参数可以控制一个语句能够执行的最长时间，超出限定的时间后该语句会被自动终止。该参数默认值为0，即无限制。

    但是为了防止误杀，最好不要把这个参数设置为全局级别，仅在要控制的函数的定义中针对该函数自身设置一下既可。


  • lock_timeout

    该参数控制锁等待的最长时间，超出限定时间后等待锁的语句就会被自动终止。对于执行数据更新的语句来说，该参数有较大的价值，因为每次更新数据之前都必须先获取待修改的记录上的排他锁，所以更新语句之间是最容易发生锁等待的。lock_timeout的值应该设得比statement_timeout小，否则就是总会语句先超时，这样lock_timeout就毫无意义了。


死锁检测是很昂贵的操作，因此系统不会每次发生锁等待都去做死锁检测


  • idle_in_transaction_session_timeout

    该参数表示一个事务可以处于idle状态的最长时间，超过限定的时间后该事务会被自动回滚。该参数的默认值为0，表示事务可以永久处于idle状态。该参数是9.6版本引入的。可以起到两个作用：防止一个空闲事务占着记录锁一致不释放从而阻塞别的事务继续运行，还可以防止一个数据库连接被一个空闲事务永远占用。


查看被阻塞语句的情况：

在pg的9.6版本中，pg_stat_activity视图中的waiting字段变为了wait_event_type和wait_event的字段，其中记录了当前会话上的语句在等待什么资源。



角色

PG中使用

角色

（role）机制来处理用户身份认证。拥有登陆数据库权限的角色称为

可登录角色

（login role)。一个角色可以继承其他角色的权限从而成为其

成员角色

；拥有成员角色的角色称为

组角色

。（一个组角色可以是另一个组角色的成员角色，并且这种角色之间的继承关系可以无限多层，但除非你非常有把握）拥有登录权限的组角色称为可登录的组角色。然而，基于安全性的考虑，管理员一般是不会给组角色登陆权限的。一个角色还可以被授权于

超级用户

权限，此种授权要谨慎。

PG在最近的版本已经不再使用“用户”和“组”这两个术语，别人可能还会用，你就记住，用户代表可登录角色，组代表组角色就好。当然，PG为了保持前向兼容，create user和create group 这两个命令在当前版本中也是支持的，但与时俱进嘛，最好不要使用他们，请使用create role。

当然，也不是不可以用，create user sgs_zhangweiwu login encrypted password ‘mima’; user和role的最大区别就是多一个login属性罢了。



创建可登录角色

在PG安装的过程中的数据初始化阶段，系统会默认创建一个名为postgres的可登录角色（同时会创建一个名为postgres的同名databases）。你可以通过前面介绍过的ident或者peer身份验证机制来将操作系统的root用户映射到数据库的postgres角色，这样可以实现root用户免密登陆，或者通过设置trust模式的效果也是一样。数据库安装完成的第一件事就是用psql或者pgadmin工具以postgres角色身份登陆，然后创建其他已经规划好的角色。

🛴创建具备登陆权限的角色 

CREATE ROLE leo LOGIN PASSWORD 'mimajiushiwo'  VALID UNTIL  'infinity'  CREATEDB;

VALID子句是可选的，其功能是为此角色的权限设定的有效期，如果不写则该角色永久有效，CREATEDB 子句表明为此角色赋予了创建新数据库的权限。

🐘创建具备超级用户权限的角色 

CREATE ROLE regina LOGIN PASSWORD 'queen' VALID UNTIL '2023-2-17 00:00' SUPERUSER;


上面的两个例子中我们创建的都是可登录的角色，如果创建不可登录的角色，省略掉LOGIN PASSWORD 子句既可

。



创建组角色

一般是不应该授予组角色登录权限，但是你想，也不是不可以。

🍡以下的SQL创建组角色 

CREATE ROLE zujuese INHERIT;

注意这个关键词INHERIT的用法。他表示组角色zujuese的任何一个成员角色都将自动继承其除“超级用户权限”外的所有权限。如果不写

INHERIT，默认也会有INHERIT的效果。

如果希望禁止组角色将其权限授予成员角色，加上NOINHERIT关键字。

🌮以下语句可以为组角色添加成员角色 

GRANT zujuese TO leo;
GRANT zujuese TO regina;

我们之前提到过SUPERUSER超级用户权限是没有办法被继承的，然而成员角色却可以通过SET ROLE命令来实现“冒名顶替”其组角色的身份，从而获得其父角色所拥有的SUPERUSER权限，当然这种冒名顶替的状态是有期限的，仅限于当前会话存续期间有效。

举例如下：

我们呢先给zujuese超级用户权限： 

ALTER ROLE zujuese SUPERUSER;

上面这个leo是zujuese的成员角色，也继承了绝大多数的权限，但leo登陆后依然不具备SUPERUSER权限。但是呢，我们只要执行以下语句就可以获得SUPERUSER权限： 

SET ROLE zujuese;


我在下面才想明白这个问题：这个口令的意思是，leo扮演了zujuse在这个角色，虽然leo是zujuese的成员角色，但是superuser这个权限是没有被通过默认的赋权方式赋予的，此时set role一执行，就表示leo现在扮演了zujuese，所以才有了superuser权限

但是这种方式获得的SUPERUSER权限仅仅在会话存续期间有效。

虽然这个操作逻辑看起来好像很傻很呆 ，但是如果你不希望登录到以superuser身份犯下一些无法挽回的错误，那么这个方法是值得你考虑的。

所有的用户都可以使用SET ROLE这个命令，但是还有一个比它更加强大的命令：SET SESSION AUTHORIZATION ，该命令只允许具备superuser权限的用户执行。为了便于理解呢，我们先介绍两个postgresql中的全局变量：current_user和session_user。执行下面的语句，我们就可以看到在这两个变量值。 

SELECT session_user ,current_user;

首次登陆后，这两个值相同。执行set role就会修改current_user的值，要是执行SET SESSION AUTHORIZATION 就会同时改变current_user和session_user的值。

以下是set role命令的主要特点。

  • set role无须superuser 权限既可执行。
  • set role会修改current_user变量的值，但不会修改session_user的值。
  • 一个具备superuser权限的session_user同名角色可以通过set role设置为任何用户。
  • 非超级用户可以通过set role 设置为session_user同名角色或者其所属的组用户。
  • set role命令可以让执行角色获取到所“扮演”角色的全部权限，当然除了，set role 和SET SESSION AUTHORIZATION权限。

SET SESSION AUTHORIZATION是比set role 更为强大的命令，其关键特性如下：

  • 只有超级用户才可以执行SET SESSION AUTHORIZATION
  • SET SESSION AUTHORIZATION在整个会话存续期间都是有效的，也就是说及时超级用户通过SET SESSION AUTHORIZATION来扮演了一个非超级用户，只要会话未中断，都可以在上面再次执行SET SESSION AUTHORIZATION命令的。
  • SET SESSION AUTHORIZATION会将current_user和session_user修改为要扮演的角色。
  • 具备超级用户权限的session_user同名角色可以通过set role 来扮演任何其他角色。

下面呢，我们就展示一波：

  1. 🏰 
psql -U leo -d postgres

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uAkIfVQo-1678169115201)(C:/Users/49190/AppData/Roaming/Typora/typora-user-images/image-20230220151905467.png)] 

SELECT session_user ,current_user;

image-20230220152052201 

set role zujuese;
SELECT session_user ,current_user;

image-20230220152454323 

alter role leo superuser;
set role regina ;
SELECT session_user ,current_user;

image-20230220153030609 

set session authorization regina;

image-20230220153313278


为啥这个不成功呢，这就是我们上面说的set role的特点，是有了全部权限，但是set role和set session authorization权限是没有的。

  1. 所以我们退出后，再用leo登陆：

    image-20230220153945665


这说明什么呢，这说明我们上次扮演的时候给我们赋予的superuser权限是会话结束还是生效的

所以我们这波set session authorization直接就是成功了。



创建database

一个不含任何附加子句的最简单的SQL语句既可创建一个database： 

create database xndb;

该命令会以template1库为模板生成一份副本并将此副本作为新的database。任何一个拥有createdb权限的角色都能够创建新的database。



模板数据库

望文生义嘛，模板数据库就是创建新的database时所使用的骨架。创建新的database时，postgressql会基于模板数据库制作一份副本，其中会包含所有数据库设置和数据文件。

postgreSQL安装后默认附带两个模板数据库，template0和template1.如果创建新的数据库未指定使用哪个模板，那么系统默认会使用template1库作为新库的模板。

你要切记一点，任何时候呢都不要去轻易修改template0模板数据库，因为合适初始的干净的数据库模板，如果其他数据库模板被破坏，基于这个数据库做个副本就可以了。当然你可以建立自己的模板数据库。

基于某个模板来创建新数据库的基本语法如下： 

create database my_db template my_template_db;

当然的当然，你可以指定一个数据库为模板数据库，那么pg就会禁止对其进行修改和编辑或者删除。那要怎么做呢？

以超级用户身份运行以下的SQL可以使得任何数据库成为模板数据库。 

UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'mydb';

如果你想修改或者删除被标记为模板的数据库，将上述的语句中的datistemplate字段修改为FALSE既可。



schema的使用

我们之前就有过介绍，Pg和MySQL是很不相同的，pg数据库是一个三级结构，schema呢就是第二级，schema对database中的对象进行逻辑分组。因为如果你的database中有很多的表，那么管理起来是很麻烦的，所以考虑把他们放到不同的schema去管理。同一个schema中的对象名是不允许重复的，但是

同一个database中的不同的schema中的对象是可以重名的

。但是如果你将数据库中的所有表都塞到默认的public schema中，那你遇到重名的对象只是迟早的问题。

比如说我们举个栗子：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-y1aghSqk-1678169115203)(https://picture-1312074320.cos.ap-beijing.myqcloud.com/typroa/%E4%B8%BE%E4%B8%AA%E6%A0%97%E5%AD%90.gif)]

我现在开了一家宠物店，我呢之前是将所有的数据全都存到了宠物信息都存到了一个schema里去，但是这样就有一个弊端，一个客户可以看到另一个客户的宠物信息，这是泄露隐私的，那么为了隔离客户信息，我就要你为，每个客户新建一个schema，然后在每个schema中建立同样一张dogs表。

然后呢，把这些宠物数据从单一的dogs表分拆到不同的客户的schema的dogs表中。最后为每个schema创建一个与之同名的可登录的角色。现在呢所有的宠物信息就完全被分散到他们对应的schema 下了。

当然，骚操作还没有完，我们上面为啥要让角色和schema同名呢，是为了用一个技巧：

当然在那之前，我们先介绍一下search_path 这个系统变量。前面呢，我们已经提到了同一个schema中的对象是不允许重名的，但不同的schema这种的对象可以重名。就比如说我们所有的schema里都有一张dogs表。所以这就出现一个问题，当你 

\c dbname

切换到你的目标数据库执行下面的语句： 

SELECT * FROM dogs;

这样的语句时，pg数据库就很懵，说它不知道你要从哪个schema中去查dogs表，解决这个问题，最简单的解决方法就是在表名前加上所属schema的名称。

image-20230221162634122

就是像上面这样。

还有另一种方法那就是通过设置search_path变量来解决。就像下面这样：

image-20230221163208729


search_path可以设置多个逗号隔开的变量，啥意思呢，就是意思会在这些schema里依次查找的意思。

书接上文，我们回到为啥要把用户名和schema设为同名，在pg中有一个罕为人知的系统变量叫做user，它代表了当前登录的用户名字。执行select user就可以看到他的值。当然系统变量user和current_user完全相同，用哪个都行。那么我们就可以这样设置 search_path变量了。 

set search_path = "$user" , 另一个schema;

我们查看当前的search_path： 

show search_path;

image-20230221171339152


你比如说，我们新建一个用户之后就可以这样设置search，那么下次你登录上这个角色后，select就会按照search_path的schema来检索。

还有就是你检索要先\C切换数据库呀，不然你还是搜不到数据信息。 

alter role postgres  set search_path= '$user',public,李四;



权限管理

PG是很骚的，它的权限管理机制是非常灵活且自由的。PG的权限控制甚至可以精确到字段和记录级别。你甚至可以为同一张表中的每个字段单独设置其访问权限。

行级权限控制是PG9.5首次引入的。



权限的类型

PG有几十种权限，其中一些是基本不会用到的。常见的几种权限包括：SELECT，INSERT，UPDATE，ALTER，EXECUTE以及TRUNCATE。

大多数权限需要上下文，也就是需要你去绑定一个特定的数据库对象才有意义。你比如说一个角色拥有ALTER权限，却不告诉它在哪个数据库对象上拥有此权限，这是没有意义的。在table1上拥有ALTER权限，在table2上有SELECT权限，在function1上拥有execute权限，就像这样，权限才有意义。当然，你还要明确的一点是：不是所有的权限都适用于所有的数据库对象，你比如说一张表上的execute权限就是完全说不通的。

当然还有一些权限是无需绑定数据库对象的，比如说CREATEDB和CREATE ROLE。



入门介绍

  1. PG在安装阶段会默认创建一个超级用户角色以及一个database，二者的名称都是postgres。

  2. 在创建你自己的收个database之前，需要先创建一个角色作为此database的所有者，所有者可以登陆该库。语法如下： 

    CREATE ROLE mydb_admin LOGIN PASSWORD 'something';

  3. 创建database并设定其所有者： 

    CREATE DATABASE mydb WITH owner = mydb_admin;

  4. 然后我们使用mydb_admin 身份登陆并创建schema和表。



GRANT

GRANT命令是授予权限的基本手段。基本的使用方法如下： 

GRANT some_privilege TO some_role;


下面有几个关于GRANT的使用原则：

  • 只有权限的拥有者才能将权限授予别人，并且拥有者自身还得有GRANT操作的权限。（你自己没有的东西，你肯定给不了别人）

  • 有些权限只有对象的所有者才能拥有，任何情况下都不能授予别人。这类权限包括DROP和ALTER。

  • 对象的所有者天然拥有此对象的所有权限，不需要再次授权。但是要注意一点：一个对象的所有者并不意味着天然拥有此对象的子对象。例如：虽然你是某个database的属主，但这并不意味着你必然是该database下的所有schema的属主。

  • 授权时可以加上WITH GRANT子句，这意味着被授权者可以将得到的权限再次授予别人，从而实现权限的传递。 

    GRANT ALL ON ALL TABLES  IN SCHEMA public TO mydb_admin WITH GRANT OPTION;

  • 如果希望一次新疆某一类对象的所有权限都授予某人，可以使用ALL关键字来指代所有对象，而不需要针对每一个对象都操作一遍。请注意此处的ALL tables 涵盖了所有的普通表，外部表以及视图。 

    GRANT SELECT ，REFERENCES,TRIGGER ON
ALL TABLES IN SCHEMA my_schema TO
PUBLIC;

  • 如果希望将权限授予所有人，可以用public关键字来指代所有角色。 

    GRANT USAGE ON SCHEMA my_schema TO PUBLIC;



取消权限

系统会默认将某些权限授予PUBLIC（所有人）。这些权限包括：CONNECT，CREATE，TEMPTABLE（针对database），EXECUTE（针对函数）。有时候出于安全考虑，你也会取消一些默认权限。 

REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA my_schema  FROM PUBLIC;

revoke connect on database cusdb（新建库名）from public;   回收public角色的权限，不让public角色看到新建的库

revoke create on schema public from public;    回收public角色在schemapublic上的创建权限。



默认权限

默认权限可以简化权限管理工作，该机制允许用户在数据库对象创建之前就对其设置权限。

新增或者修改默认权限不会影响已有的权限设置，只有当某个对象的某项权限未专门设定的情况下，默认权限设定才会生效。

☔️

定义schema的默认权限
 

 GRANT USAGE  ON SCHEMA my_schema TO PUBLIC;
  1. 允许所有能够连接到此databases的用户在my_schema中创建和访问对象，同时该用户需要已经具备访问此schema中所有对象的权限。为用户授予某个schema的USAGE权限是允许该用户访问schema中所有对象的前提条件。如果用户拥有访问schema中某张表的查询权限，却没有该schema的usage权限，则用户也不能访问这张表。 
ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema
GRANT SELECT ,REFERENCES ON TABLES TO PUBLIC;
  1. 为所有具备此schema的USAGE的权限的用户授予该schema中后续创建的所有表的查询和引用（REFERENCES）权限（引用权限指的是针对该表的某些字段建立外键约束的权限） 
ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema
GRANT ALL ON TABLES TO mydb_admin WITH GRANT OPTION;
  1. 把该schema中所有后续创建的表的所有权限都授予mydb_admin角色。同时还允许mydb_admin组的所有成员将本schema中所有后续创建的表的部分或者全部权限授予其他用户。所有权限包括：插入，更新，删除，截断表，创建触发器，创建约束等。



PG的与众不同之处

Pg中一个database的所有者并不天然对此库中的所有对象拥有完全的控制权。比如另一个角色可以在你的库里创建一张表，你虽然是库的所有者，但是你却无权访问这张表，但是你是有权把整张表都删除掉的。


在对schema中包含的表和函数等做完授权操作后，切记不要忘记授予schema本身的usage权限。

户授予某个schema的USAGE权限是允许该用户访问schema中所有对象的前提条件。如果用户拥有访问schema中某张表的查询权限，却没有该schema的usage权限，则用户也不能访问这张表。 

ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema
GRANT SELECT ,REFERENCES ON TABLES TO PUBLIC;
  1. 为所有具备此schema的USAGE的权限的用户授予该schema中后续创建的所有表的查询和引用（REFERENCES）权限（引用权限指的是针对该表的某些字段建立外键约束的权限） 
ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema
GRANT ALL ON TABLES TO mydb_admin WITH GRANT OPTION;
  1. 把该schema中所有后续创建的表的所有权限都授予mydb_admin角色。同时还允许mydb_admin组的所有成员将本schema中所有后续创建的表的部分或者全部权限授予其他用户。所有权限包括：插入，更新，删除，截断表，创建触发器，创建约束等。



PG的与众不同之处

Pg中一个database的所有者并不天然对此库中的所有对象拥有完全的控制权。比如另一个角色可以在你的库里创建一张表，你虽然是库的所有者，但是你却无权访问这张表，但是你是有权把整张表都删除掉的。


在对schema中包含的表和函数等做完授权操作后，切记不要忘记授予schema本身的usage权限。


版权声明：本文为wxnshuai原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。