PostgreSQL数据库安全（Security）

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

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

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

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;

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

例如：

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

首先创建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。

命令行：

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

SQL：

CREATE ROLE bob WITH NOSUPERUSER INHERIT NOCREATEROLE CREATEDB LOGIN;
CREATE ROLE tim WITH SUPERUSER;

查看创建的用户

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 | {}

四、暂时防止用户的连接（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命令修改。

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

六、检查所有用户是否拥有一个安全密码（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;
以上命令可以给指定用户赋予权限，但是都只是一些保留的功能。如果需要其他权限，这里可以考虑用封装函数。

例如：

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.

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

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;

2、把函数copy_from授权给bob：

revoke all on function copy_from( text, text) from public;
grant execute on function copy_from( text, text) to bob;

八、审查数据定义语言（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、举例说明：

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();

十、结合LDAP（Integrating with LDAP）
具体参考教程，这里博主没测试过。

十一、使用SSL连接（Connecting using SSL）
使用ssl会影响数据传输速度，如果确定网络安全，可以不需要使用ssl 。
修改postgresql.conf，把ssl设置成on 。

pg_hba.conf设置：
host all all 192.168.54.1/32 md5
hostssl all all 0.0.0.0/0 md5
这个意思是192.168.1.0/24不使用ssl，其他ip连接都使用ssl 。

十二、对敏感数据进行加密（Encrypting sensitive data）
pgcrypto的使用，具体可以参考下面几个网址：

http://www.postgresql.org/docs/9.0/static/pgcrypto.html
http://www.openssl.org/
http://www.gnupg.org/gph/en/manual.html

PostgreSQL数据库安全（Security）

相关内容

联系我们

0574-55011290

PostgreSQL数据库安全（Security）

相关内容

相关推荐

请登录

联系我们

0574-55011290