PostgreSQL数据安全配置

行级访问限制

准备数据

[postgres@localhost ~]$ cd resoruce/
[postgres@localhost resoruce]$ vi RLSData.sql
[postgres@localhost resoruce]$ psql -U pgadmin -d postgres -p 5666 < RLSData.sql 

create table passwd(
user_name text unique not null
,pwhas text
,uid int primary key
,gid int not null
,rel_name text not null
,home_phone text
,extra_info text
,home_dir text not null
,shell text not null
);

insert into passwd values('appuser','xxx',0,0,'appuser','111-222-333',null,'/root','/bin/dash');
insert into passwd values('appuser1','xxx',1,1,'appuser1','211-222-333',null,'/home/appuser1','/bin/zsh');
insert into passwd values('appuser2','xxx',2,1,'appuser2','311-222-333',null,'/home/appuser2','/bin/zsh');

create user appuser1 login password '1qaz@WSX';
create user appuser2 login password '1qaz@WSX';

grant select ,insert,update,delete on passwd to appuser1;
grant select ,insert,update,delete on passwd to appuser2;
grant select ,insert,update,delete on passwd to readonlyuser;

创建一个策略，使得用户只能访问属于自己的行

postgres=# alter table passwd enable row level security ;
postgres=# create policy appuser1_select_passwd on passwd for select to appuser1 using(current_user=user_name);

[postgres@localhost resoruce]$ psql -U pgadmin -d postgres
psql (12.0)
Type "help" for help.

postgres=# \d
         List of relations
 Schema |  Name  | Type  |  Owner  
--------+--------+-------+---------
 public | passwd | table | pgadmin
(1 row)

postgres=# alter table passwd enable row level security ;
ALTER TABLE
postgres=# create policy appuser1_select_passwd on passwd for select to appuser1 using(current_user=user_name);
CREATE POLICY
postgres=# \c postgres appuser1
You are now connected to database "postgres" as user "appuser1".
postgres=> select * from passwd;
 user_name | pwhas | uid | gid | rel_name | home_phone  | extra_info |    home_dir    |  shell   
-----------+-------+-----+-----+----------+-------------+------------+----------------+----------
 appuser1  | xxx   |   1 |   1 | appuser1 | 211-222-333 |            | /home/appuser1 | /bin/zsh
(1 row)

postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# select * from passwd;
 user_name | pwhas | uid | gid | rel_name | home_phone  | extra_info |    home_dir    |   shell   
-----------+-------+-----+-----+----------+-------------+------------+----------------+-----------
 appuser   | xxx   |   0 |   0 | appuser  | 111-222-333 |            | /root          | /bin/dash
 appuser1  | xxx   |   1 |   1 | appuser1 | 211-222-333 |            | /home/appuser1 | /bin/zsh
 appuser2  | xxx   |   2 |   1 | appuser2 | 311-222-333 |            | /home/appuser2 | /bin/zsh
(3 rows)

创建一个策略，使得用户可以向有行限制的表中插入数据

postgres=# drop policy appuser1_insert_passwd on passwd;
postgres=# create policy appuser1_insert_passwd on passwd for insert to appuser1 with check(true);

列级加密

创建加密扩展  这里需不需要编译 后面再验证一下 

postgres=> \c postgres pgadmin
postgres=# create extension pgcrypto ;

准备数据 使用crypt函数对数据加密

create table test_user(
 id serial
,user_name text
,password text
);

insert into test_user(user_name,password) values('appuser1',crypt('123456',gen_salt('md5')));
insert into test_user(user_name,password) values('appuser2',crypt('223456',gen_salt('md5')));

验证数据是否加密

postgres=# select * from test_user;
 id | user_name |              password              
----+-----------+------------------------------------
  1 | appuser1  | $1$YiUide3K$Z6z2pIGyi8HsB7q3IFK1c1
  2 | appuser2  | $1$k0GXH0mZ$KzhzcTGsQAl3I/1FnCr4G0

postgres=# select (password=crypt('123456',password)) from test_user;
 ?column? 
----------
 t
 f

postgres=# select * from test_user where password=crypt('123456',password);
 id | user_name |              password              
----+-----------+------------------------------------
  1 | appuser1  | $1$YiUide3K$Z6z2pIGyi8HsB7q3IFK1c1

使用PG_AUDIT插件进行审计

下载地址 https://github.com/pgaudit/pgaudit

编译pg_audit模块

[postgres@localhost contrib]$ cd ~/resoruce/
[postgres@localhost resoruce]$ unzip pgaudit-REL_12_STABLE.zip
[postgres@localhost resoruce]$ cp -r pgaudit-REL_12_STABLE ~/resoruce/postgresql-12.0/contrib/
[postgres@localhost resoruce]$ cd ~/resoruce/postgresql-12.0/contrib/pgaudit-REL_12_STABLE/
[postgres@localhost pgaudit-REL_12_STABLE]$ make
[postgres@localhost pgaudit-REL_12_STABLE]$ make install
[postgres@localhost pgaudit-REL_12_STABLE]$ cp pgaudit.so ~/soft/lib/

修改配置文件 添加预加载库

[postgres@localhost pgaudit-REL_12_STABLE]$ vi $PGDATA/postgresql.conf

--最后一行添加
shared_preload_libraries = 'pgaudit'

重启数据库 创建audit扩展

[postgres@localhost pgaudit-REL_12_STABLE]$ pg_ctl restart
[postgres@localhost pgaudit-REL_12_STABLE]$ psql -U pgadmin -d postgres
psql (12.0)
Type "help" for help.

postgres=# create extension pgaudit;

创建audit用户

postgres=# create user pgaudituser with password '1qaz@WSX';
CREATE ROLE
postgres=# alter system set pgaudit.role = 'pgaudituser';
ALTER SYSTEM
postgres=# select pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

postgres=# show pgaudit.role 
;
 pgaudit.role 
--------------
 pgaudituser
(1 row)

将要审计的表给audit用户赋权

postgres=# \c appdb appuser
appdb=> grant select,delete on table appuser.app to pgaudituser;