1. 首页
  2. 数据库运维
  3. PostgreSQL

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数据库(一):http://blog.nbqykj.cn/?p=1428.html

探索postgresql数据库(二):http://blog.nbqykj.cn/?p=1438.html

Postgresql 数据库控制解析(一):http://blog.nbqykj.cn/?p=1454.html

Postgresql 数据库控制解析(二):http://blog.nbqykj.cn/?p=1501.html

PostgreSQL数据库中的表和数据(Tables & Data):http://blog.nbqykj.cn/?p=1576.html

联系我们

0574-55011290

QQ:248687950

邮件:admin@nbhao.org

工作时间:周一至周五,9:00-18:00,节假日休息

QR code