PostgreSQL数据库安全（Security）

本文主要是介绍PostgreSQL数据库安全（Security），希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

查看原文：http://www.sijitao.net/1696.html
在这个章节，我们主要来了解postgresql的安全，主要讲用户权限问题。
在一般的数据库中，至少包含两种用户，管理员和终端用户。这个很好理解，管理员（及超级用户）拥有完全权限，而终端用户只有部分权限。

一、取消用户对表的访问（Revoking user access to a table）
在运行这个命令时，首先需要确保当前用户是管理员，或者是表的所有人，或者必须有grant的权限。
例如：
REVOKE ALL ON mysecrettable FROM userwhoshoudnotseeit;
这个命令取消userwhoshoudnotseeit对mysecrettable 表的所有权限。
不过因为一般表对public组有访问权限，所以要完全拒绝还需要运行下面这个sql。
REVOKE ALL ON mysecrettable FROM PUBLIC;

当然，如果在生产环境中，最好是在建立表的时候就对权限进行一下设定，保证只有指定用户才可以访问。
例如：

[code language="sql"]
CREATE TABLE table1(
...
);
REVOKE ALL ON table1 FROM GROUP PUBLIC;
GRANT SELECT ON table1 TO GROUP webreaders;
GRANT SELECT, INSERT, UPDATE, DELETE ON table1 TO editors;
GRANT ALL ON table1 TO admins;

[/code]

二、授予用户对表的访问（Granting user access to a table）

例如：

[code language="sql"]
CREATE GROUP webreaders;
GRANT SELECT ON pages TO webreaders;
GRANT INSERT ON viewlog TO webreaders;
GRANT webreaders TO tim, bob;

[/code]

首先创建webreaders组，然后授予该组对pages的select权限，对viewlog的insert权限，最后把tim，bob加入到webreaders组后，tim和bob就都拥有pages的select权限和viewlog的insert权限。
修改webreaders组对组内成员的权限设定都有效。
GRANT INSERT, UPDATE, DELETE ON comments TO webreaders;

授予用户对SCHEMA 的权限
GRANT SELECT ON ALL TABLES IN SCHEMA staging TO bob;

三、创建新用户（Creating a new user）
对于postgresql来说，创建新用户可以有两种方法，分别是命令行和sql。

命令行：

[code language="shell"]
pguser@hvost:~$ createuser bob
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) n
pguser@hvost:~$ createuser tim
Shall the new role be a superuser? (y/n) y

[/code]

SQL：

[code language="sql"]
CREATE ROLE bob WITH NOSUPERUSER INHERIT NOCREATEROLE CREATEDB LOGIN;
CREATE ROLE tim WITH SUPERUSER;

[/code]

查看创建的用户

[code language="shell"]
pguser=# \du tim
List of roles
Role name | Attributes | Member of
-----------+-------------+-----------
tim | Superuser | {}
: Create role
: Create DB
pguser=# \du bob
List of roles
Role name | Attributes | Member of
-----------+------------ +-----------
bob | Create DB | {}

[/code]

四、暂时防止用户的连接（Temporarily preventing a user from connecting）
有时候我们只是暂时不想让用户连接数据库，这里也有两种方法。
1、
设定bob不能登陆
pguser=# alter user bob nologin;
恢复bob登陆功能
pguser=# alter user bob login;
2、
设定bob的连接数为0
pguser=# alter user bob connection limit 0;
设定bob的连接数不限制
pguser=# alter user bob connection limit -1;

因为设置对已登录用户不影响，所以可以用下面这个命令强制没权限用户下线。
SELECT pg_terminate_backend(procpid)
FROM from pg_stat_activity a
JOIN pg_roles r ON a.usename = r.rolname AND not rolcanlogin;

五、删除用户但不删除他的数据（Removing a user without dropping their data）
如果用户拥有一些表，那么在删除的时候会提示如下错误，删除失败。

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

最简单的方法是上面四中介绍的，前置用户的登陆权限。
pguser=# alter user bob nologin;

或者可以对需要删除的用户重命名。
pguser=# grant bob bobs_replacement;

如果必须要删除用户，那么可以先把对用用户的数据改成其他人，再删除。这个功能在8.2之后才加入。
REASSIGN OWNED BY bob TO bobs_replacement;

如果以上办法都不可行，那么最后可以用linux命令修改。

[code language="shell"]
postgres@vm-199:~$ pg_dump -s test | grep -i "alter.*owner to znq"
ALTER FUNCTION public.check_website_regexp(ifid integer) OWNER TO znq;
ALTER TABLE public.public_opinion_info OWNER TO znq;
ALTER TABLE public.temp OWNER TO znq;
ALTER TABLE public.temp_aid_seq OWNER TO znq;

postgres@vm-199:~$ pg_dump -s test | grep -i "alter.*owner to znq" >tmp.sql
postgres@vm-199:~$ sed -e 's/TO znq/TO zhangnq/' < tmp.sql | psql mydb

[/code]

六、检查所有用户是否拥有一个安全密码（Checking all users have a secure password）
因为PostgreSQL内部没有检查密码的工具，所以我们可以做的是把密码进行md5加密，然后在pg_hba.conf中设定拒绝简单密码登陆。

查看没有对密码进行加密的用户
select usename,passwd from pg_shadow where passwd not like 'md5%' or length(passwd) <> 35;

查看对密码加密过的用户
select usename,passwd from pg_shadow where passwd like 'md5%' and length(passwd) = 35;

不过这个对安全密码来说只完成一半，还需要确认用户是否使用了容易猜的密码，比如"password', 'secret', or 'test' 。这个具体可以看这里：http://www.postgresql.org/docs/devel/static/passwordcheck.html 。

七、对特定用户授予有限的管理权限（Giving limited superuser powers to specific users）
ALTER ROLE BOB WITH CREATEDB;
ALTER ROLE BOB WITH CREATEUSER;
以上命令可以给指定用户赋予权限，但是都只是一些保留的功能。如果需要其他权限，这里可以考虑用封装函数。

例如：

[code language="shell"]
pguser@hvost:~$ psql -U postgres
test2
...
test2=# create table lines(line text);
CREATE TABLE
test2=# copy lines from '/home/bob/names.txt';Chapter 6
137
COPY 37
test2=# SET SESSION AUTHORIZATION bob;
SET
test2=> copy lines from '/home/bob/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.

[/code]

这里可以看到bob没有copy权限，那么应该如何授予？
1、创建函数copy_from：

[code language="sql"]
create or replace function copy_from(tablename text, filepath text)
returns void
security definer
as
$$
declare
begin
execute 'copy ' || tablename || ' from ''' || filepath || '''';
end;
$$ language plpgsql;

[/code]

2、把函数copy_from授权给bob：

[code language="sql"]
revoke all on function copy_from( text, text) from public;
grant execute on function copy_from( text, text) to bob;

[/code]

八、审查数据定义语言（DDL）的修改
首先修改postgresql.conf文件，添加或者修改log_statement = 'ddl' ，重新加载/etc/init-d/postgresql reload 。
查看日志中的修改记录：
postgres@hvost:~$ egrep -i "create|alter|drop" \ /data/pgsql93/pg_log/postgresql-2014-04-23_000000.log

查看谁对数据库进行了修改，需要在postgresql.conf文件中添加log_line_prefix参数%u，推荐参数包括 '%t %u %d'，分别包括timestamp, database user和database name。

九、审查数据变化（Auditing data changes）
这个主要也有两种办法。第一种是通过修改postgresql.conf文件，通过log记录操作在服务器中；第二种是通过创建触发器triggers 实现。
1、设置log_statement = 'mod' 或者 'all'，收集所有INSERT, UPDATE, DELETE和TRUNCATE操作记录。
2、举例说明：

[code language="sql"]
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') THENSecurity
-- 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();