PostgreSQL管理数据库安全

前言

随着技术的进步和网络技术的发展，安全是任何行业都需要面对的挑战，如前两年爆发的WnCry电脑病毒，数据信息被加密。因此，数据安全始终需要预防。正因为数据库作为存储数据最重要的载体，所以除了硬件物理层面的数据安全防护之外，也需要在软件层面加强数据安全防护。接下来介绍一下PostgreSQL中在数据安全层面对数据库做防护。

一、客户端接入认证

二、管理用户及安全

三、设置密码安全策略

四、行级安全(rls)策略

一、客户端接入认证

PostgreSQL客户端接入认证的主要方式是通过pg_hba.conf文件来进行配置。

1.1 配置客户端接入认证

客户端接入认证背景

如果主机需要远程连接数据库，必须在数据库系统的配置文件中增加此主机的信息，并且进行客户端接入认证。配置文件（默认名称为pg_hba.conf）存放在数据库的数据目录里。hba（host-based authentication）表示是基于主机的认证。

PostgreSQL支持如下三种认证方式，这三种方式都需要配置pg_hba.conf文件。

基于主机的认证：服务器端根据客户端的IP地址、用户名及要访问的数据库来查看配置文件从而判断用户是否通过认证。
口令认证：包括远程连接的加密口令认证和本地连接的非加密口令认证。
SSL加密：使用openssl（通用协议平台）提供服务器端和客户端安全连接的环境。

pg_hba.conf文件的格式是一行写一条信息，表示一个认证规则，空白和注释（以#开头）被忽略。
每个认证规则是由若干空格和/，空格和制表符分隔的字段组成。如果字段用引号包围，则它可以包含空白。一条记录不能跨行存在。

pg_hba.conf文件中的每条记录可以是下面四种格式之一

# local      DATABASE  USER  METHOD  [OPTIONS]
# host      DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostssl    DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostnossl  DATABASE  USER  ADDRESS  METHOD  [OPTIONS]

因为认证时系统是为每个连接请求顺序检查pg_hba.conf里的记录的，所以这些记录的顺序非常关键。

在配置 pg_hba.conf 文件时，请依据通讯需求按照格式内容从上至下配置记录，优先级高的需求需要配置在前面。
若服务端pg_hba.conf文件配置了hostssl，postgresql.conf中必须开启SSL认证模式。

因此对于认证规则的配置建议如下：

靠前的记录有比较严格的连接参数和比较弱的认证方法。
靠后的记录有比较宽松的连接参数和比较强的认证方法。

1.2 pg_hba.conf 配置文件参数说明及认证方式

参数说明

参数名称	描述	取值范围
local	表示这条记录只接受通过Unix域套接字进行的连接。没有这种类型的记录，就不允许Unix域套接字的连接。只有在从服务器本机使用gsql连接且在不指定-U参数的情况下，才是通过Unix域套接字连接。	-
host	表示这条记录既接受一个普通的TCP/IP套接字连接，也接受一个经过SSL加密的TCP/IP套接字连接。	-
hostssl	表示这条记录只接受一个经过SSL加密的TCP/IP套接字连接。	用SSL进行安全的连接，需要配置申请数字证书并配置相关参数。
hostnossl	表示这条记录只接受一个普通的TCP/IP套接字连接。	-
DATABASE	声明记录所匹配且允许访问的数据库。	all：表示该记录匹配所有数据库。 sameuser：表示如果请求访问的数据库和请求的用户同名，则匹配。 samerole：表示请求的用户必须是与数据库同名角色中的成员。 samegroup：与samerole作用完全一致，表示请求的用户必须是与数据库同名角色中的成员。一个包含数据库名的文件或者文件中的数据库列表：文件可以通过在文件名前面加前缀@来声明。文件中的数据库列表以逗号或者换行符分隔。特定的数据库名称或者用逗号分隔的数据库列表。说明：值replication表示如果请求一个复制链接，则匹配，但复制链接不表示任何特定的数据库。如需使用名为replication的数据库，需在database列使用记录 replication 作为数据库名。
USER	声明记录所匹配且允许访问的数据库用户。	all：表明该记录匹配所有用户。 +用户角色：表示匹配任何直接或者间接属于这个角色的成员。说明： +表示前缀符号。一个包含用户名的文件或者文件中的用户列表：文件可以通过在文件名前面加前缀@来声明。文件中的用户列表以逗号或者换行符分隔。特定的数据库用户名或者用逗号分隔的用户列表。
ADDRESS	指定与记录匹配且允许访问的IP地址范围。	支持IPv4和IPv6，可以使用如下两种形式来表示： IP地址/掩码长度。例如，10.10.20.0/24 IP地址子网掩码。例如，10.10.20.0 255.255.255.0 说明：以IPv4格式给出的IP地址会匹配那些拥有对应地址的IPv6连接，比如127.0.0.1将匹配IPv6地址 ::ffff:127.0.0.1
METHOD	声明连接时使用的认证方法。	支持如下几种认证方式 trust reject md5 scram-sha-256 cert peer \| ident ldap password gss gssapi radius

认证方式

认证方式	说明
trust	采用这种认证模式时，使用psql且不指定-U参数的连接，此时不需要口令。 trust认证对于单用户工作站的本地连接是非常合适和方便的，通常不适用于多用户环境。如果想使用这种认证方法，可利用文件系统权限限制对服务器的Unix域套接字文件的访问。要使用这种限制有两个方法：设置参数unix_socket_permissions和unix_socket_group。设置参数unix_socket_directory，将Unix域套接字文件放在一个经过恰当限制的目录里。须知：设置文件系统权限只能Unix域套接字连接，它不会限制本地TCP/IP连接。
reject	无条件地拒绝连接。常用于过滤某些主机。
md5	要求客户端提供一个md5加密的口令进行认证。须知：不推荐使用md5认证，因为md5为不安全的加密算法，存在网络安全风险。
scram-sha-256	md5的升级版本，要求客户端提供一个sha256算法加密的口令进行认证，该口令在传送过程中结合salt（服务器发送给客户端的随机数）的单向sha256加密，增强了安全性。(推荐)
cert	客户端证书认证模式，此模式需进行SSL连接配置且需要客户端提供有效的SSL证书，不需要提供用户密码。须知：该认证方式只支持hostssl类型的规则。
password	使用明文密码

1.3 使用SSL进行安全的TCP/IP连接

从CA认证中心申请到正式的服务器、客户端的证书和密钥。（假设服务器的私钥为server.key，证书为server.crt，客户端的私钥为client.key，证书为client.crt，CA根证书名称为cacert.pem。）此处以OPENSSL生成的认证为基础:

1）部署CA环境

登陆postgres用户执行

[postgres@PGServer2 ~]$ mkdir -p security

拷贝openssl.cnf到security目录下

[postgres@PGServer2 ~]$ cp /etc/pki/tls/openssl.cnf ~/security/

创建CA环境并授权private为777权限

[postgres@PGServer2 ~]$ mkdir -p security/CA/{certs,private}
[postgres@PGServer2 ~]$ chmod 777 security/CA/private/

验证CA环境目录

[postgres@PGServer2 ~]$ tree security/
security/
├── CA
│   ├── certs
│   └── private
└── openssl.cnf

创建serial文件，并写入01

[postgres@PGServer2 ~]$ echo '01' > security/CA/serial

创建index.txt索引文件

[postgres@PGServer2 ~]$ touch security/CA/index.txt

修改openssl.cnf文件中的参数

[postgres@PGServer2 ~]$ vi security/openssl.cnf
[postgres@PGServer2 ~]$ cat security/openssl.cnf  | egrep "security|default_md|new_certs_dir" | awk '{print $1,$2,$3}'
dir = /home/postgres/security/CA
new_certs_dir = $dir/certs
default_md = sha256

2）生成根私钥

生成2048位的CA私钥：输入密码test

[postgres@PGServer2 ~]$ openssl  genrsa -aes256 -out security/CA/private/cakey.pem 2048
Generating RSA private key, 2048 bit long modulus
.+++
....+++
e is 65537 (0x10001)
Enter pass phrase for security/CA/private/cakey.pem:
Verifying - Enter pass phrase for security/CA/private/cakey.pem:

3）生成根证书请求文件

根证书文件名称为server.req
输入cakey.pem的口令test。
输入国家名称:CN
输入省份:Jiangsu
输入城市:NanJing
输入组织名称:gs
其中有些可以省略不填

[postgres@PGServer2 ~]$ openssl req -config security/openssl.cnf -new -key security/CA/private/cakey.pem -out security/CA/careq.pem
Enter pass phrase for security/CA/private/cakey.pem:
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:CN
State or Province Name (full name) []:Jiangsu
Locality Name (eg, city) [Default City]:Nanjing
Organization Name (eg, company) [Default Company Ltd]:gs
Organizational Unit Name (eg, section) []:gs
Common Name (eg, your name or your server's hostname) []:shaohua
Email Address []: 

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:Postgres@DB
An optional company name []:
[postgres@PGServer2 ~]$

4）生成自签名根证书

使用openssl.cnf中的配置
输入cakey.pem的口令，
检查输出的请求与签名是否匹配

[postgres@PGServer2 ~]$ openssl ca -config security/openssl.cnf -out security/CA/cacert.pem -keyfile security/CA/private/cakey.pem  -selfsign -infiles security/CA/careq.pem
Using configuration from security/openssl.cnf
Enter pass phrase for security/CA/private/cakey.pem:
Check that the request matches the signature
Signature ok
Certificate Details:
        Serial Number: 1 (0x1)
        Validity
            Not Before: Mar 30 07:16:21 2020 GMT
            Not After : Mar 30 07:16:21 2021 GMT
        Subject:
            countryName               = CN
            stateOrProvinceName       = Jiangsu
            organizationName          = gs
            organizationalUnitName    = gs
            commonName                = shaohua
        X509v3 extensions:
            X509v3 Basic Constraints: 
                CA:FALSE
            Netscape Comment: 
                OpenSSL Generated Certificate
            X509v3 Subject Key Identifier: 
                B2:5E:02:8B:7E:8C:19:56:D3:00:17:71:9C:BF:B5:DA:33:C3:21:4F
            X509v3 Authority Key Identifier: 
                keyid:B2:5E:02:8B:7E:8C:19:56:D3:00:17:71:9C:BF:B5:DA:33:C3:21:4F

Certificate is to be certified until Mar 30 07:16:21 2021 GMT (365 days)
Sign the certificate? [y/n]:y


1 out of 1 certificate requests certified, commit? [y/n]y
Write out database with 1 new entries
Data Base Updated

已下发名为cacert.pem的CA根证书

5）生成服务器证书私钥

[postgres@PGServer2 security]$ openssl genrsa -aes256 -out server.key 2048
Generating RSA private key, 2048 bit long modulus
..................................................................................................................................+++
.......................................................+++
e is 65537 (0x10001)
Enter pass phrase for server.key:
Verifying - Enter pass phrase for server.key:

6）生成服务器证书请求文件

输入server.key的口令
输入系统要求的一些信息
其中一些字段可以不填，确保和创建CA时的内容一致

[postgres@PGServer2 security]$ openssl req -config openssl.cnf -new -key server.key -out server.req
Enter pass phrase for server.key:
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:CN
State or Province Name (full name) []:Jiangsu
Locality Name (eg, city) [Default City]:Nanjing
Organization Name (eg, company) [Default Company Ltd]:gs
Organizational Unit Name (eg, section) []:gs
Common Name (eg, your name or your server's hostname) []:shaohua
Email Address []:

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:Postgres@DB
An optional company name []:

7）生成服务器证书

修改CA/index.txt.attr中的属性为no

[postgres@PGServer2 security]$ vi CA/index.txt.attr 
[postgres@PGServer2 security]$ cat CA/index.txt.attr 
unique_subject = no

下发生成的服务器证书请求文件，下发成功后，会生成一个正式的服务器证书server.crt

使用openssl.cnf中的配置，输入cakey.pem中的口令

检查请求与签名是否匹配

[postgres@PGServer2 security]$ openssl ca -config openssl.cnf -in server.req -out server.crt -days 3650 -md sha256
Using configuration from openssl.cnf
Enter pass phrase for /home/postgres/security/CA/private/cakey.pem:
Check that the request matches the signature
Signature ok
Certificate Details:
        Serial Number: 2 (0x2)
        Validity
            Not Before: Mar 30 07:26:57 2020 GMT
            Not After : Mar 28 07:26:57 2030 GMT
        Subject:
            countryName               = CN
            stateOrProvinceName       = Jiangsu
            organizationName          = gs
            organizationalUnitName    = gs
            commonName                = shaohua
        X509v3 extensions:
            X509v3 Basic Constraints: 
                CA:FALSE
            Netscape Comment: 
                OpenSSL Generated Certificate
            X509v3 Subject Key Identifier: 
                64:96:2B:B7:1E:CC:DD:22:D9:0D:07:79:A7:22:FC:23:FB:66:86:FC
            X509v3 Authority Key Identifier: 
                keyid:B2:5E:02:8B:7E:8C:19:56:D3:00:17:71:9C:BF:B5:DA:33:C3:21:4F

Certificate is to be certified until Mar 28 07:26:57 2030 GMT (3650 days)
Sign the certificate? [y/n]:y


1 out of 1 certificate requests certified, commit? [y/n]y
Write out database with 1 new entries
Data Base Updated

1.4 PostgreSQL服务器使用ssl认证连接

1）启动PostgreSQL服务器

将ssl认证功能启用

[postgres@PGServer2 ~]$ pg_ctl  start -D $PGDATA -l /tmp/logfile
waiting for server to start.... done
server started
[postgres@PGServer2 security]$ psql
psql (12.2)
Type "help" for help.

postgres=# show ssl;
 ssl 
-----
 off
(1 row)

postgres=# alter system set ssl = on;
ALTER SYSTEM

2）配置server.key和server.crt文件的位置

[postgres@PGServer2 ~]$ cat $PGDATA/postgresql.conf  | egrep -v "^#" | egrep "ssl_key_file|ssl_cert_file"
ssl_cert_file = '/home/postgres/security/server.crt'
ssl_key_file = '/home/postgres/security/server.key'

3）重新启动PostgreSQL服务器

输入之前ssl认证文件配置的密码后，数据库即能启动

[postgres@PGServer2 ~]$ pg_ctl  start -D $PGDATA -l /tmp/logfile
waiting for server to start....Enter PEM pass phrase:.
 done
server started

二、管理用户及安全

2.1 默认权限机制

数据库对象创建后，进行对象创建的用户就是该对象的所有者。集群安装后的默认情况下，未开启三权分立，数据库系统管理员具有与对象所有者相同的权限。也就是说对象创建后，默认只有对象所有者或者系统管理员可以查询、修改和销毁对象，以及通过GRANT将对象的权限授予其他用户。

为使其他用户能够使用对象，必须向用户或包含该用户授予必要的权限。

不同的权限与不同的对象类型关联。要撤消已经授予的权限，可以使用REVOKE。对象所有者的权限（例如ALTER、 DROP、GRANT和REVOKE）是隐式的，无法授予或撤消。即只要拥有对象就可以执行对象所有者的这些隐式权限。对象所有者可以撤消自己的普通权限，例如，使表对自己以及其他人只读。

系统表和系统视图要么只对系统管理员可见，要么对所有用户可见。标识了需要系统管理员权限的系统表和视图只有系统管理员可以查询。

数据库提供对象隔离的特性，对象隔离特性开启时，用户只能查看有权限访问的对象(表、视图、字段、函数)，系统管理员不受影响。

2.2 用户

使用CREATE USER 和 ALTER USER 可以创建和管理数据库用户。数据库包含一个或者多个已命名的数据库。用户和角色在整个数据库中是共享的，但是数据并不共享。即用户可以连接到任何数据库，但当连接成功后，任何用户都只能访问连接请求里声明的那个数据库。

2.2.1 管理员用户

管理员用户可以管理数据库中的对象，可以通过WITH SUPERUSER关键词创建管理员用户

示例:创建具有管理员角色的用户admin1和admin2

postgres=# CREATE USER admin1 WITH SUPERUSER ENCRYPTED PASSWORD 'admin1';
CREATE ROLE
postgres=# CREATE USER admin2 WITH SUPERUSER ENCRYPTED PASSWORD 'admin2';
CREATE ROLE

2.2.2 业务用户

对于有多个业务部门，各部门间使用不同的数据库用户进行业务操作，同时有一个同级的数据库维护部门使用数据库管理员进行维护操作的场景下，业务部门可能希望在未经授权的情况下，管理员用户只能对各部门的数据进行控制操作（DROP、ALTER、TRUNCATE），但是不能进行访问操作（INSERT、DELETE、UPDATE、SELECT、COPY）。即针对管理员用户，表对象的控制权和访问权要能够分离，提高普通用户数据安全性。

示例:创建普通用户user1和user2

postgres=# CREATE USER user1 WITH ENCRYPTED PASSWORD 'user1';
CREATE ROLE
postgres=# CREATE USER user2 WITH ENCRYPTED PASSWORD 'user2';
CREATE ROLE

2.3 模式

Schema又称作模式。通过管理Schema，允许多个用户使用同一数据库而不相互干扰，可以将数据库对象组织成易于管理的逻辑组，同时便于将第三方应用添加到相应的Schema下而不引起冲突。

每个数据库包含一个或多个Schema。数据库中的每个Schema包含表和其他类型的对象。数据库创建初始，默认具有一个名为public的Schema，且所有用户都拥有此Schema的权限。可以通过Schema分组数据库对象。Schema类似于操作系统目录，但Schema不能嵌套。

相同的数据库对象名称可以应用在同一数据库的不同Schema中，而没有冲突。例如，a_schema和b_schema都可以包含名为mytable的表。具有所需权限的用户可以访问数据库的多个Schema中的对象。

在初始数据库postgres中创建用户时，系统会自动帮助用户创建一个同名Schema。在其他数据库中，若需要同名Schema，则需要用户手动创建。

数据库对象是创建在数据库搜索路径中的第一个Schema内的。

创建SCHEMA可以使用CREATE SCHEMA语句创建。

更改SCHEMA名称或者所有者，可以使用ALTER SCHEMA语句进行修改。

要删除SCHEMA及其对象，使用DROP SCHEMA 语法可以进行删除。

要在SCHEMA内创建表，以schema.tablename格式创建表。不指定schemaname时，对象默认创建到search_path中的第一个schema名称。

示例1:创建schema对象s1和s2

postgres=# CREATE SCHEMA s1;
CREATE SCHEMA
postgres=# CREATE SCHEMA s2;
CREATE SCHEMA

示例2:修改schema s1为schema1，s2为schema2

postgres=# ALTER SCHEMA  s1 RENAME TO schema1;
ALTER SCHEMA
postgres=# ALTER SCHEMA  s2 RENAME TO schema2;
ALTER SCHEMA

示例3: 创建schema1下的表 st1 ,schema t2下的表为st2

postgres=# CREATE TABLE schema1.st1(id int,name varchar(20));
CREATE TABLE
postgres=# CREATE TABLE schema2.st2(id int,name varchar(20));
CREATE TABLE

示例4：查看创建的表 st1 和 st2

--需要先设置schema的搜索路径，否则找不到该schema1和schema2下的对象
postgres=# set search_path = public,schema1,schema2;
SET
--需要注意的是，设置search_path为多个时，不要加引号
postgres=# \d
         List of relations
 Schema  | Name | Type  |  Owner   
---------+------+-------+----------
 schema1 | st1  | table | postgres
 schema2 | st2  | table | postgres

示例5:为schema1和schema2下的两张表st1和st2插入数据

postgres=# INSERT INTO schema1.st1 VALUES(1,'PostgreSQL');
INSERT 0 1
postgres=# INSERT INTO schema2.st2 VALUES(1,'MySQL');
INSERT 0 1

示例6：使用之前创建的普通用户user1和user2访问schema1和schema2下的表

postgres=# \c postgres user1;
You are now connected to database "postgres" as user "user1".
postgres=> SELECT * FROM schema1.st1 ;
ERROR:  permission denied for schema schema1
LINE 1: SELECT * FROM schema1.st1 ;
--此时，user1用户没有足够的权限可以使用schema1，因此需要对用户user1授权对schema1的使用权限
postgres=# \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# GRANT USAGE ON SCHEMA schema1 TO user1;
GRANT
--再次使用user1查看schema1下的对象st1
postgres=# \c postgres user1;
You are now connected to database "postgres" as user "user1".
postgres=> SELECT * FROM schema1.st1 ;
ERROR:  permission denied for relation st1
--此时虽然user1对schema虽然具有使用权限，但是没有权限访问schema1下的对象st1，
--因此还需要对schema1.st1授权user1可以访问的权限，即select权限
postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# GRANT SELECT ON TABLE schema1.st1 TO user1;
GRANT
--再次使用user1对schema1下的对象st1进行查询
postgres=# \c postgres user1;
You are now connected to database "postgres" as user "user1".
postgres=> SELECT * FROM schema1.st1 ;
 id |    name    
----+------------
  1 | PostgreSQL
(1 row)

对于用户user2，如果想要对schema2下的对象st2进行查看，需要做上述同样的操作。

综上案例，可以发现，如果一个普通用户对于schema下的对象进行访问，修改等操作，首先要让普通用户具有对schema的使用权限，其次对schema下的对象要授权普通用户具有访问修改的权限。

三、设置密码安全策略

设置账户的密码安全策略

用户密码存储在系统表pg_authid中，为防止用户密码泄露，PostgreSQL对用户密码进行加密存储，所采用的加密算法由配置参数password_encryption_type决定。

当参数password_encryption设置为 scram-sha-256 时，表示采用 scram-sha-25 6方式对密码加密。
当参数password_encryption设置为 md5 时，表示采用md5方式对密码加密。md5为不安全的加密算法，不建议使用。

注意:PostgreSQL数据库默认采用MD5加密。该参数设置后需要重新启动数据库服务器，并且需要更改原有密码，使修改后用户使用的密码加密算法生效。

示例1:查看数据库默认加密算法

postgres=# SHOW password_encryption ;
 password_encryption 
---------------------
 md5

示例2:修改默认加密算法为 scram-sha-256

postgres=# ALTER SYSTEM SET password_encryption = 'scram-sha-256';
ALTER SYSTEM
--重新启动数据库服务器
[postgres@PGServer2 ~]$ pg_ctl  stop -D $PGDATA -l /tmp/logfile && pg_ctl start -D $PGDATA -l /tmp/logfile
waiting for server to shut down.... done
server stopped
waiting for server to start....Enter PEM pass phrase:.
 done
server started

示例3:查看之前创建的用户user1使用的密码加密策略

postgres=# SELECT rolname,rolpassword FROM pg_authid  WHERE rolname = 'user1';
 rolname |             rolpassword             
---------+-------------------------------------
 user1   | md57d1b5a4329b6478e976508ab9a49ee3d

示例4:配置pg_hba.conf中的METHOD为 scram-sha-256

[postgres@PGServer2 ~]$ cat $PGDATA/pg_hba.conf  | grep 10
host    all             all             10.10.20.0/24           scram-sha-256

示例5:使用user1连接到postgres数据库

--user1的密码为 test
[postgres@PGServer2 ~]$ psql -h 10.10.20.101 -p 5432 -U user1 -d postgres
Password for user user1: 
psql: FATAL:  password authentication failed for user "user1"
FATAL:  password authentication failed for user "user1"
--此时输入test的密码，但是连接失败，原因是由于在更改password_encryption为scram-sha-256之前，user1用户的密码存储使用的加密策略为 md5 加密，示例3可以查看到user1采用md5加密
--解决方法:重新修改user1的密码
postgres=# ALTER USER user1 WITH ENCRYPTED PASSWORD 'user1';
ALTER ROLE
--查看修改后的user1的密码加密策略为scram-sha-256
postgres=# SELECT rolname,rolpassword FROM pg_authid  WHERE rolname = 'user1';
 rolname |                                                              rolpassword                                                              
---------+---------------------------------------------------------------------------------------------------------------------------------------
 user1   | SCRAM-SHA-256$4096:oAK38X5JC7XJLanDu+WBBQ==$ErZytCUN6ot/qfXnnxNnTneM0MmQVLvepghMP0bAJlg=:uUy8CiCgCU6E4+fbZdUPhofvFSLb53zHJDJUGQTdG+w=
--重新使用密码user1进行连接
[postgres@PGServer2 ~]$ psql -U user1 -d postgres -h 10.10.20.101
Password for user user1: 
psql (10.11)
Type "help" for help.

postgres=>

密码策略设置为scram-sha-256对数据隔离访问具有安全性，因此生产环境中，建议使用scram-sha-256进行加密。

四、行级安全(rls)策略

行级访问控制特性将数据库访问控制精确到数据表行级别，使数据库达到行级访问控制的能力。不同用户执行相同的SQL查询操作，读取到的结果是不同的。

用户可以在数据表创建行访问控制(Row Level Security)策略，该策略是指针对特定数据库用户、特定SQL操作生效的表达式。当数据库用户对数据表访问时，若SQL满足数据表特定的Row Level Security策略，在查询优化阶段将满足条件的表达式，按照属性(PERMISSIVE | RESTRICTIVE)类型，通过AND或OR方式拼接，应用到执行计划上。

行级访问控制的目的是控制表中行级数据可见性，通过在数据表上预定义Filter，在查询优化阶段将满足条件的表达式应用到执行计划上，影响最终的执行结果。当前受影响的SQL语句包括SELECT，UPDATE，DELETE。

示例:假设一张表中存储了不同用户的数据，但是不同的用户只能看到自身相关的信息，不能访问其它用户的数据信息。

--创建用户 lily,lucy,tom
postgres=# CREATE USER lily WITH ENCRYPTED PASSWORD 'lily';
CREATE ROLE
postgres=# CREATE USER lucy WITH ENCRYPTED PASSWORD 'lucy';
CREATE ROLE
postgres=# CREATE USER tom WITH ENCRYPTED PASSWORD 'tom';
CREATE ROLE
--创建表 user_data，并插入不同用户的不同数据
postgres=# postgres=# CREATE TABLE user_data(id int primary key,name varchar(20),gender varchar(6),nickname varchar(20),age int2,carrer varchar(20));
CREATE TABLE
postgres=#  INSERT INTO user_data  VALUES (1,'lily','女','昵称:丽丽',20,'销售');
INSERT 0 1
postgres=#  INSERT INTO user_data  VALUES (2,'lucy','女','昵称:路西',23,'经理');
INSERT 0 1
postgres=#  INSERT INTO user_data  VALUES (3,'tom','男','昵称:汤姆猫',22,'程序员');
INSERT 0 1

--将数据表user_data中读取权限授权给lily,lucy和tom
postgres=# GRANT SELECT ON user_data TO lily,lucy,tom;
GRANT
--启用行访问控制策略
postgres=# ALTER TABLE user_data ENABLE ROW LEVEL SECURITY ;
ALTER TABLE
--创建行访问控制策略，当前用户只能查看用户自身数据
postgres=# CREATE POLICY user_data_rls ON user_data FOR SELECT to PUBLIC USING (name = CURRENT_USER);
CREATE POLICY
--使用lily登录查看user_data数据
 [postgres@PGServer2 ~]$ psql -U lily -d postgres -h 10.10.20.101
Password for user lily: 
psql (10.11)
Type "help" for help.

postgres=> select * from user_data ;
 id | name | gender | nickname  | age | carrer 
----+------+--------+-----------+-----+--------
  1 | lily | 女     | 昵称:丽丽 |  20 | 销售
(1 row)

使用lucy登录查看user_data数据
postgres=> \c postgres lucy 10.10.20.101
Password for user lucy: 
You are now connected to database "postgres" as user "lucy".
postgres=> select * from user_data ;
 id | name | gender | nickname  | age | carrer 
----+------+--------+-----------+-----+--------
  2 | lucy | 女     | 昵称:路西 |  23 | 经理

--使用tom用户查看user_data数据
postgres=> \c postgres tom 10.10.20.101
Password for user tom: 
You are now connected to database "postgres" as user "tom".
postgres=> select * from user_data ;
 id | name | gender |  nickname   | age | carrer 
----+------+--------+-------------+-----+--------
  3 | tom  | 男     | 昵称:汤姆猫 |  22 | 程序员

小结:

以上就是PostgreSQL数据库中关于客户端接入认证，用户级别安全和基于行安全(RLS)的内容。

在PostgreSQL中，除了这些来控制数据库的安全以外，还有pg_hba中的其它基于主机的访问控制策略，数据库访问审计策略及对数据的加密策略。此章节中不再对涉及的其它安全访问控制进行一一探讨。

posted @ 2020-04-08 14:48 晟数阅读(1682) 评论(0) 编辑收藏举报

刷新页面返回顶部

PostgreSQL管理数据库安全

公告