赞
踩
准备数据
- [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
下载地址 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;
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。