认识 PostgreSQL 基础权限体系

用户1278550

发布于 2022-04-01 09:24:41

6720

发布于 2022-04-01 09:24:41

文章被收录于专栏：idbaidba

前言

对于习惯MySQL 权限体系的朋友来说，MySQL 有数据库，对象层面的权限，而PostgreSQL 多少有些不一样 PostgreSQL 的权限分为四类 : 实例，数据库，schame，对象。下面我们通过实际案例逐一了解。

实例权限

用户访问实例的权限是由控制访问文件 pg_hba.conf 的参数控制。

# TYPE  DATABASE USER ADDRESS  METHOD
local   database user address  auth-method         
host    database user address  auth-method

TYPE：

local：使用Unix域套接字的连接。
host：使用TCP/IP进行的连接，使用该选项需要在postgresql.conf配置 listen_addresses 选项。不在 listen_addresses 的ip无法访问实例。
hostssl：使用SSL加密的TCP/IP进行的连接，需要编译pg时使用 --with-openssl 让pg支持ssl 。
hostnossl：使用明文的TCP/IP进行的连接

database：

匹配的数据库名称，可以是 all , sameuser, samerole, replication。all 表示所有，但不包括replication。多个数据库用“,”隔开。

user：

用户名，all指定它匹配所有用户。可以通过用逗号分隔来多个用户名。

address：

可以包含主机名，IP地址范围。如：172.20.143.0/24、::1/128。0.0.0.0/0表示所有IPv4地址，::0/0表示所有IPv6地址。all以匹配任何IP地址。

auth-method：指定连接与此记录匹配时要使用的身份验证方法：

trust：无条件的允许连接，不需要任何口令，即使口令是错误的
reject：无条件拒绝连接，常用于拒绝某些用户访问，常见的拒绝用户连接template1
md5：使用md5加密的口令进行认证
password：和md5一样，但是口令是以明文形式在网络上传递，比较危险
scram-sha-256：pg10新增，这是当前提供的方法中最安全的方法，但是较旧的客户端库不支持此方法,md5和scram-sha-256会以对应的方式加密再发送密码
ident：映射关系pg_ident.conf文件中
perr：该模式使用连接发起端的操作系统名进行身份验证。仅限于Linux、BSD、Mac OS X和Solaris，并且仅可用于本地服务器发起的连接。

看看具体的pg_hba.conf

# TYPE  DATABASE    USER   ADDRESS   METHOD
local          all               all                         trust
--在本地允许任何用户无密码登录
local          all                all                        peer
--操作系统的登录用户和pg的用户是否一致，一致则可以登录
local          all                all                        ident
--操作系统的登录用户和pg的用户是否一致，一致则可以登录
host          all                all    192.168.163.0/24   md5
--指定客户端IP访问通过md5身份验证进行登录
host          all                all     192.168.163.132/32   password
--指定客户端IP通过passwotd身份验证进行登录

修改完pg_hba.conf文件之后，需要重新加载配置，不用重启数据库：

select pg_reload_conf();

我们可以通过查看表来查看hba：

select * from pg_hba_file_rules;

数据库权限

数据库级别的权限包括允许连接数据库以及数据库中创建schema。我们可以在创建数据库时指定 owner，比如:

create database db00 owner user00;

可以通过如下语句对其他用户进行赋权，让其他用户，角色可以访问该数据库。

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

默认情况下，在创建数据库之后，允许public角色连接，即允许任何人连接。但是不允许除superuser 和 owner 之外的任何人在数据库中创建 schema。默认情况下，在创建数据库之后，pg会自动创建名为 public 的schema，这个schema的all权限已经赋予给public角色，即允许任何人在里面创建对象。

我们通过例子来直观学习一下 database 的权限。

polardb=# create database db01;
CREATE DATABASE
polardb=# create user user01 ;
CREATE ROLE
polardb=# \c db01 user01   ## user01 连接到 db01 
You are now connected to database "db01" as user "user01".
db01=>
db01=> create table t(id int);
CREATE TABLE
db01=> \d  ### 默认在public 下创建表。
       List of relations
 Schema | Name | Type  | Owner
--------+------+-------+--------
 public      | t          | table      | user01
(1 row)
db01=> create schema db01 ;   ###user01 没有db01 的相关权限创建schame 的权限
ERROR:  permission denied for database db01
polardb=# grant all privileges on database db01 to user01;
GRANT
polardb=# \c db01 user01
You are now connected to database "db01" as user "user01".
db01=> create schema db01 ;
CREATE SCHEMA

schema 权限

schema包括查看schema的对象以及在schema中创建对象，新建的 schema 只有超级用户和owner 有权限在该shcmea下查看或者新建对象

通过如下赋权语句将schame 的访问权限授予其他用户。

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

基于上面的案例，继续测试

polardb=# \c db01 user01  
You are now connected to database "db01" as user "user01".
db01=> create table db01.t(id int);
CREATE TABLE
db01=> show  search_path;
   search_path
-----------------
 "$user", public
(1 row)
db01=> \d  ##此时查看不到 在db01 schema 创建的表。需要设置 search_path
       List of relations
 Schema | Name | Type  | Owner
--------+------+-------+--------
 public | t    | table | user01
 public | t2   | table | user01
db01=> set  search_path to db01;
SET
db01=> \d
       List of relations
 Schema | Name | Type  | Owner
--------+------+-------+--------
 db01   | t    | table | user01
(1 row)

注意:

schema的owner默认是该schema下的所有对象的owner。同时PostgreSQL还允许用户在别人的schema下创建对象（赋权后），所以一个对象可能属于”两个”owner。更”糟糕”的是schema 的owner有 drop该schema下面的所有对象的权限。或者直接drop schema cascade来删除整个schema.

对象权限

这里说的对象主要是表，存储过程，触发器，索引等等。具体的grant 语句可以去看官方文档。http://www.postgres.cn/docs/12/sql-grant.html

我们通过一个案例,了解给新建用户赋予访问某个 schema 下的表的权限。

创建用户 r1

polardb=# create role r1 with password 'a1234b' login;
CREATE ROLE
polardb=# grant all on database db01 to r1;
GRANT

用户 r1 访问 db01

db01=> \c db01 r1    ## 使用 r1 用户访问 db01, 默认是在public schame 下面
You are now connected to database "db01" as user "r1".
db01=> show search_path;
   search_path
-----------------
 "$user", public
(1 row)
db01=> set search_path to db01;  ## 设置当前schema 为 db01
SET
db01=> \d  ## 因为 r1 没有 schame的访问权限，故查看不到任何表
Did not find any relations.
db01=> \c db01 user01
You are now connected to database "db01" as user "user01".
db01=> grant select on all tables in schema db01 to r1;
GRANT
db01=> \c db01 r1
You are now connected to database "db01" as user "r1".
db01=> set search_path to db01;
SET
db01=> \d
Did not find any relations.
db01=> \c db01 user01
You are now connected to database "db01" as user "user01".
db01=> grant all on schema db01 to r1; ## 将访问，创建schema对象的权限赋予 r1 
GRANT
db01=> \c db01 r1
You are now connected to database "db01" as user "r1".
db01=> set search_path to db01;
SET
db01=> \d
       List of relations
 Schema | Name | Type  | Owner
--------+------+-------+--------
 db01   | t    | table | user01
(1 row)
db01=> select * from t limit 3;
 id
----
  1
  2
  3
(3 rows)

角色

PostgreSQL 使用角色的概念管理数据库访问权限。一个角色可以被看成是一个数据库用户或者是一个数据库用户组，角色可以拥有数据库对象（表,函数,索引等）并且能够控制谁能访问哪些对象。此外，还可以把一个角色A中的权限授予给另一个角色B，创建角色A时必须有 WITH ADMIN OPTION

角色属性

PostgreSQL 数据库的角色具有下面几种属性

login 权限，就是连接数据库的权限，仅限于连接，未必能读写。
superuser 超级管理员权限
role creation 创建角色的权限
database creation 创建数据库的权限
initiating replication 类似MySQL中的复制账号，只有此权限的用户才能发起流复制。
password 设置用户的密码。

其中 login,superuser，createrole 是特殊权限，只有创建角色时使用 with admin option 时，才能将这些特殊权限授予其他角色/用户。否则不会像其他普通权限一样被继承。

角色和用户的区别: