Postgres安全相关

参考文档： PostgreSQL 9 Administration Cookbook （第二版）中文版

--用户在新建立的时候，加上superuser属性，会变成超级用户,在pgadmin中的role下可以看到。

create user bb superuser;
alter user bb with password 'oracle';
-- 移除用户的superuser属性
alter user bb nosuperuser;

mydb=# create user bb superuser;
CREATE ROLE
mydb=# 
mydb=# alter user bb with password 'oracle';
ALTER ROLE
mydb=# 

-bash-4.2$ psql -U bb
Password for user bb: 
psql.bin (10.15)
Type "help" for help.

postgres=# 

postgres=# select current_user;
 current_user 
--------------
 bb
(1 row)

postgres=#

postgres=# alter user bb nosuperuser;  -- 然后，可以在pgadmin上看，bb的权限，没有superuser权限了
ALTER ROLE
postgres=# 

-- 收回user2用户在表table1上的所有权限

revoke all on table1 from user2 ;

-- 通过\dp  或者\z 查看权限，通过du user，查看用户具体的权限。Access privileges为空，表示"默认权限"

mydb-# \dp t
                            Access privileges
 Schema | Name | Type  | Access privileges | Column privileges | Policies 
--------+------+-------+-------------------+-------------------+----------
 public | t    | table |                   |                   | 
(1 row)

mydb-# \z t
                            Access privileges
 Schema | Name | Type  | Access privileges | Column privileges | Policies 
--------+------+-------+-------------------+-------------------+----------
 public | t    | table |                   |                   | 
(1 row)

mydb-# \du bb
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 bb        | Superuser, Create role, Create DB, Replication | {}

mydb-# \du postgres
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS+| {}
           | No connections                                             | 

mydb-# 

-- 赋予用户对表的访问权限

GRANT ALL ON someschema TO somerole;
grant usage on someschema to somerole;
GRANT SELECT, INSERT, UPDATE, DELETE ON someschema.sometable TO somegroup;
GRANT somerole TO someuser, otheruser;

-- 通过一个组角色（group role）赋予访问一张表的权限

CREATE GROUP webreaders;
GRANT SELECT ON pages TO webreaders;
GRANT INSERT ON viewlog TO webreaders;
GRANT webreaders TO tim, bob;

GRANT INSERT, UPDATE, DELETE ON comments TO webreaders;   -- bob和tim现在又对表comments有相关权限了。
GRANT SELECT ON ALL TABLES IN SCHEMA staging TO bob;  -- 赋予对schema中所有对象的访问权限 （不过，还是需要用单独的grant语句赋予对schema的访问权）

-- 新建立用户
建立新用户，要么是超级用户，要么需要拥有createrole或者createuser权限。
创建的用户，如果已存在，则无法创建 。创建的用户，是不区分是那个数据库的。
分别在mydb和postgres数据库下创建bob用户，提示已经存在了。也可以在命令行下，使用createuser命令。

mydb=# create user bob;
ERROR:  role "bob" already exists
mydb=# 
postgres=# create user bob;
ERROR:  role "bob" already exists
postgres=# 
-bash-4.2$ createuser bob
createuser: creation of new role failed: ERROR:  role "bob" already exists
-bash-4.2$ 

也可以以交互式来创建用户，会询问创建用户的权限之类的

-bash-4.2$ createuser bob
createuser: creation of new role failed: ERROR:  role "bob" already exists
-bash-4.2$ createuser --interactive alice
Shall the new role be a superuser? (y/n) y
-bash-4.2$ createuser --interactive alice1
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) y
-bash-4.2$ 

查看新建立的用户的权限

mydb=# \du alice
                       List of roles
 Role name |            Attributes             | Member of 
-----------+-----------------------------------+-----------
 alice     | Superuser, Create role, Create DB | {}

mydb=# \du alice1
            List of roles
 Role name | Attributes  | Member of 
-----------+-------------+-----------
 alice1    | Create role | {}

mydb=# 

create user usernamne ，等同于create role username login;
create group groupname ,等同于create role groupname  nologin;

-- 临时阻止一个用户的连接

alter user bob nologin;
alter user bob login;

postgres=# alter user bob nologin;
ALTER ROLE
postgres=# 
-bash-4.2$ psql -U bob
Password for user bob: 
psql.bin: FATAL:  role "bob" is not permitted to log in
-bash-4.2$ 

-- 限制用户的并发数连接

alter user bob connection limit 0 ;  --限制用户的连接数为0 
alter user bob connection limit 10 ; -- 允许bob有10个并发连接 

[root@asm12c ~]# psql -U bob   -- 超过连接数后，再次连接，提示太多的连接，设置为-1 ，为不限制连接数 
Password for user bob: 
psql.bin: FATAL:  too many connections for role "bob"
[root@asm12c ~]# 

--强制使nologin类型的用户断开连接

SELECT pg_terminate_backend(pid)  -- 9.2之前的版本，是procpid
FROM  pg_stat_activity a
JOIN pg_roles r ON a.usename = r.rolname AND not rolcanlogin;

-- 删除用户，而不删除数据

testdb=# drop user bob;
ERROR: role "bob" cannot be dropped because some objects depend on it
DETAIL: owner of table bobstable
owner of sequence bobstable_id_seq

-- 解决这个问题的方法，不是删除用户，而是禁止用户的连接

alter user bob nologin;
grant bob bobs_replacement;  -- 将"已删除"用户的权限赋予另一个新用户 （好像没有执行成功）
REASSIGN OWNED BY bob TO bobs_replacement;   -- 将角色bob当前拥有的所有数据库对象转移给了bob_replacement . (reassign owner只对当前数据库生效)

-- 检查所有的用户，是否使用了安全的密码

select usename,passwd from pg_shadow where passwd not like 'md5%' or length(passwd) <> 35;  -- 查看哪些用户使用了未加密的密码
select usename,passwd from pg_shadow where passwd not like 'md5%' or length(passwd) = 35;   -- 查看哪些用户使用了加密的密码 

-- 将受限的超级用户权限赋予指定的用户

alter role bob with createdb;   -- 赋予新建数据库的权限
alter role bob with createuser;  -- 赋予新建用户的权限 

mydb=# create table lines(line text);
CREATE TABLE
mydb=# copy lines from '/tmp/names.txt';
COPY 6
mydb=# set role to bob;
SET
mydb=> copy lines from '/tmp/names.txt';
ERROR:  must be superuser to COPY to or from a file
HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.
mydb=> 
mydb=> select current_user;
 current_user 
--------------
 bob
(1 row)

mydb=> 

--这个时候，查看权限，bob不是superuser

select rolsuper from pg_roles where rolname='bob'
mydb=> select rolsuper from pg_roles where rolname='bob';
 rolsuper 
----------
 f
(1 row)

mydb=> 

--如果要让bob能从文件中复制数据，则需要用超级用户给bob写一个函数 ，并授权只有特定的用户bob访问，通过执行该函数，让bob用户从文件中复制数据

mydb=> set role to postgres;
SET
mydb=# select current_user;
 current_user 
--------------
 postgres
(1 row)

mydb=# create or replace function copy_from(tablename text, filepath text)
mydb-# returns void
mydb-# security definer
mydb-# as
mydb-# $$
mydb$# declare
mydb$# begin
mydb$# execute 'copy ' || tablename || ' from ''' || filepath || '''';
-- 或者execute 'copy ' || quote_ident(tablename) || ' from ' || quote_literal(filepath) ;  -- 两个quote函数将给定字符串返回成合适的引用
mydb$# end;
mydb$# $$ language plpgsql;
CREATE FUNCTION
mydb=# revoke all on function copy_from( text, text) from public;
REVOKE
mydb=# grant execute on function copy_from( text, text) to bob;
GRANT
mydb=# 

mydb=> select current_user;
 current_user 
--------------
 bob
(1 row)

mydb=>

mydb=> select copy_from('lines','/tmp/names.txt');
 copy_from 
-----------
 
(1 row)

mydb=> select * from lines;
ERROR:  permission denied for relation lines
mydb=> 
postgres=# \c mydb
You are now connected to database "mydb" as user "postgres".
mydb=# select * from lines;
 line 
------
 aaaa
 bbbb
 cccc
 dddd
 eeee
 ffff
 aaaa
 bbbb
 cccc
 dddd
 eeee
 ffff
(12 rows)

mydb=# 

-- 两个函数，想让某些开发人员启用日志。 为所有的变量设置default来还原记录日志的状态。

create or replace function debugging_info_on()
returns void
security definer
as
$$
begin
set client_min_messages to 'DEBUG1';
set log_min_messages to 'DEBUG1';
set log_error_verbosity to 'VERBOSE';
set log_min_duration_statement to 0;
end;
$$ language plpgsql;
revoke all on function debugging_info_on() from public;
grant execute on function debugging_info_on() to bob;


create or replace function debugging_info_reset()
returns void
security definer
as
$$
begin
set client_min_messages to DEFAULT;
set log_min_messages to DEFAULT;
set log_error_verbosity to DEFAULT;
set log_min_duration_statement to DEFAULT;
end;
$$ language plpgsql;

-- DDL的审计
在postgresql.conf文件中启用ddl审计

mydb=# show log_statement;
 log_statement 
---------------
 none
(1 row)

mydb=# 

mydb=# show log_statement;
 log_statement 
---------------
 ddl
(1 row)

mydb=# 

创建role、修改role、drop role ，

mydb=# create role test;
CREATE ROLE
mydb=# alter user test with password 'oracle';
ALTER ROLE
mydb=# drop user test;
DROP ROLE
mydb=# 

查看日志中的记录

-bash-4.2$ egrep -i "create|alter|drop" postgresql-2021-01-22_000000.log
2021-01-22 10:28:06.830 CST [17162] STATEMENT:  create user bob;
2021-01-22 10:28:18.232 CST [36739] STATEMENT:  create user bob;
2021-01-22 10:31:38.918 CST [37513] STATEMENT:  CREATE ROLE bob NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;
2021-01-22 10:50:54.013 CST [39448] ERROR:  permission denied to drop role
2021-01-22 10:50:54.013 CST [39448] STATEMENT:  drop user bob;
2021-01-22 13:17:39.902 CST [67538] LOG:  statement: create role test;
2021-01-22 13:17:59.610 CST [67538] STATEMENT:  alter user test with password "oracle";
2021-01-22 13:18:08.235 CST [67538] LOG:  statement: alter user test with password 'oracle';
2021-01-22 13:18:16.235 CST [67538] LOG:  statement: drop user test;
-bash-4.2$ 

-bash-4.2$ more  postgresql-2021-01-22_000000.log | grep -E "create|drop|alter"
2021-01-22 10:28:06.830 CST [17162] STATEMENT:  create user bob;
2021-01-22 10:28:18.232 CST [36739] STATEMENT:  create user bob;
2021-01-22 10:50:54.013 CST [39448] ERROR:  permission denied to drop role
2021-01-22 10:50:54.013 CST [39448] STATEMENT:  drop user bob;
2021-01-22 13:17:39.902 CST [67538] LOG:  statement: create role test;
2021-01-22 13:17:59.610 CST [67538] STATEMENT:  alter user test with password "oracle";
2021-01-22 13:18:08.235 CST [67538] LOG:  statement: alter user test with password 'oracle';
2021-01-22 13:18:16.235 CST [67538] LOG:  statement: drop user test;
-bash-4.2$ 

-- 使用触发器，收集数据的变化  （表emp中数据变化后，收集数据到emp_audit中）

CREATE TABLE emp (
empname text NOT NULL,
salary integer
);

CREATE TABLE emp_audit(
operation text NOT NULL,
stamp timestamp NOT NULL,
userid text NOT NULL,
empname text NOT NULL,
salary integer
);

CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS
$emp_audit$
BEGIN
IF (TG_OP = 'DELETE') THEN
INSERT INTO emp_audit SELECT 'DEL', now(), user,OLD.*;
ELSIF (TG_OP = 'UPDATE') THEN
-- save old and new values
INSERT INTO emp_audit SELECT 'OLD', now(), user,OLD.*;
INSERT INTO emp_audit SELECT 'NEW', now(), user,NEW.*;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO emp_audit SELECT 'INS', now(), user,NEW.*;
ELSEIF (TG_OP = 'TRUNCATE') THEN
INSERT INTO emp_audit SELECT 'TRUNCATE', now(), user,'-', -1;
END IF;
RETURN NULL; -- result is ignored bacause this is an AFTER trigger
END;
$emp_audit$ LANGUAGE plpgsql;

CREATE TRIGGER emp_audit AFTER INSERT OR UPDATE OR DELETE ON emp FOR EACH ROW EXECUTE PROCEDURE process_emp_audit();
CREATE TRIGGER emp_audit_truncate AFTER TRUNCATE ON emp FOR EACH STATEMENT EXECUTE PROCEDURE process_emp_audit();

-- 结果

mydb=# insert into emp values('aaaa',10000);
INSERT 0 1
mydb=# select * from emp_audit;
 operation |           stamp            |  userid  | empname | salary 
-----------+----------------------------+----------+---------+--------
 INS       | 2021-01-22 13:41:52.360436 | postgres | aaaa    |  10000
(1 row)

-- 使用单个审计触发器从多张表中获取数据的变动

--创建扩展，否则hstore类型没有的

Create extension hstore;

-- 创建数据表

create table people (id serial primary key, name text,age int);
create table hats (id serial primary key,owner int references people,name text, colour text);

-- 创建审计日志表

create table audit_log(ts timestamp,tablename text,op text,oldrow hstore,newrow hstore);

-- 创建记录变动的触发器

create or replace function log_hstore() returns trigger as $$
begin 
insert into audit_log
values (clock_timestamp(),
TG_OP,
TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME,
(CASE WHEN TG_OP in('UPDATE','DELETE') THEN hstore(OLD) END),
(CASE WHEN TG_OP in('INSERT','UPDATE') THEN hstore(NEW) END)
);
RETURN NULL;
END;
$$ LANGUAGE plpgsql;

--建立触发器

create trigger audit_trigger after insert or update or delete on hats for each row execute procedure log_hstore();
create trigger audit_trigger after insert or update or delete on people for each row execute procedure log_hstore();

-- 插入数据测试，使用returning 返回数据

insert into people (name,age) values('AAAA',16),('cccc',19) returning id,name;    
insert into hats(owner,name,colour) values(2,'bbbb','red') returning *;
update hats set owner=1 where id =1 ;

-- 查看审计结果

select * from audit_log ;

mydb=# insert into people (name,age) values('AAAA',16),('cccc',19) returning id,name;
 id | name 
----+------
  1 | AAAA
  2 | cccc
(2 rows)

INSERT 0 2
mydb=# insert into hats(owner,name,colour) values(2,'bbbb','red') returning *;
 id | owner | name | colour 
----+-------+------+--------
  1 |     2 | bbbb | red
(1 row)

INSERT 0 1
mydb=# update hats set owner=1 where id =1 ;
UPDATE 1
mydb=# select * from audit_log ;
             ts             | tablename |      op       |                          oldrow                          |                          newrow         
                 
----------------------------+-----------+---------------+----------------------------------------------------------+-----------------------------------------
-----------------
 2021-01-22 14:13:04.92023  | INSERT    | public.people |                                                          | "id"=>"1", "age"=>"16", "name"=>"AAAA"
 2021-01-22 14:13:04.920465 | INSERT    | public.people |                                                          | "id"=>"2", "age"=>"19", "name"=>"cccc"
 2021-01-22 14:13:24.841253 | INSERT    | public.hats   |                                                          | "id"=>"1", "name"=>"bbbb", "owner"=>"2",
 "colour"=>"red"
 2021-01-22 14:13:32.418795 | UPDATE    | public.hats   | "id"=>"1", "name"=>"bbbb", "owner"=>"2", "colour"=>"red" | "id"=>"1", "name"=>"bbbb", "owner"=>"1",
 "colour"=>"red"
(4 rows)

mydb=# 

-- 使用触发器收集变动信息，将信息通过dblink或者plproxy保存到另一台机器上（略）

-- 查看登录的用户

mydb=# select current_user,session_user;
 current_user | session_user 
--------------+--------------
 postgres     | postgres
(1 row)

mydb=# set role to bob;
SET
mydb=> select current_user,session_user;
 current_user | session_user 
--------------+--------------
 bob          | postgres
(1 row)

mydb=> 

-- 新建带有noinherit选项，且没有权限的用户角色

create user alice2 noinherit;
create user alice3 noinherit;

--为每组需要分配的权限创建角色

create group sales;
create group superuser;
grant postgres to superuser;

-- 为每个用户分配需要的角色

grant sales to alice2;
grant superuser to alice2;
grant sales to alice3;

以上用户alice2，alice3登录后，是没有任何权限的，仅仅只能登录。但是可以通过set role to sales切换到sales角色。还可以切换到superuser角色（有点类似OS上的su命令切换用户）。

-- 一些SSL ，LDAP、用户加密等等（略）。

END