PostgreSQL学习(4) - 服务器控制

第四章：服务器控制（Server Control）

参考：《PostgreSQL 9 Administration Cookbook，Second Edition》

目录

1. 手动开启数据库                                                     6.  将用户踢出系统：

2. 快速安全的停止服务器                                          7.  多租户（Mutitenamcy）

3. 重新加载服务器配置文件                                       8.  创建多个schema:

4. 拒绝新的连接                                                         9.  限制用户可访问的Schema

5. 限制用户最大可连接的session数                         10. 给用户创建私有数据库

--------------------------------------------------------------------------------------------------------------------------------------------------

1. 手动开启数据库

通常postgresql数据库服务器都是在操作系统启动后，自动启动的，那如何手动启动呢？

$ pg_ctlcluster 9.4 main start

2. 快速安全的停止服务器

$ pg_ctlcluster 9.0 main stop --force

3. 重新加载服务器配置文件

$ pg_ctlcluster 9.4 main reload

4.  拒绝新的连接

• 停止会话池
• 停止服务器
• 限制特定数据库的连接数

mydb=# ALTER DATABASE foo_db CONNECTION LIMIT 0;

• 限制特定用户的连接

mydb=# ALTER USER foo CONNECTION LIMIT 0;

• 改变HBA文件的配置，重新加载文件

5. 限制用户最大可连接的session数：

postgres=# alter role u_bootcamp connection limit 1;     --限制为1个session

postgres=# alter role u_bootcamp connection limit -1;     --取消限制

6. 将用户踢出系统：

pg_terminate_backend() 方法可以终止用户的session

postgres=# SELECT count(pg_terminate_backend(pid)) 
     FROM pg_stat_activity
     WHERE usename NOT IN
         (SELECT usename FROM pg_user  WHERE   usesuper);

7. 多租户（Mutitenamcy）

如果你想在一个服务器上运行多个物理数据库的话，你通常有四种选择：

• 在不同schema上，运行多个表的集合（postgresql实例上的一个数据库上）
• 在同一个postgresql实例上运行多个数据库
• 在同一个虚拟/物理系统上运行多个postgresql实例
• 在同一个物理服务器的独立的虚拟机上分别运行postgresql实例

如果我们的目标是物理资源分离，那第3/4条是最好的选择；如果出于安全性的考虑，那第2条就可以满足要求; 最后如果我们只是想更清晰的进行数据库的管理，那第1条方式就比较适合了

8. 创建多个schema:

postgres=# CREATE SCHEMA finance;
postgres=# CREATE SCHEMA sales;

postgres=# CREATE TABLE finance.month_end_snapshot (.....) --在schema finance中创建表

postgres=# select current_schema;   --查询当前默认schema

--如果创建对象时，没有指明schema，默认在public schema

9. 限制用户可访问的Schema (权限这部分内容感觉好复杂，后续再好好整理吧，先跳过)

当我们访问对象时，是通过search_path参数来确定，去哪个schema里面找到我们想要的对象的。（current_schema是参数的第一个schema）。所以，如果我们想让某个用户只能访问某些表的集合（schema），就通过设置这个用户的search_path来实现：

postgres=# ALTER ROLE fiona SET search_path = 'finance';     --fiona这个用户只能访问finance这个schema
postgres=# ALTER ROLE sally SET search_path = 'sales';

以上我们看到，如果其他用户知道存在finance和sales这两个schema存在，那他是否可以通过设置search_path来使用甚至修改schema里面的对象？幸运的是，我们可以grant or revoke privileges来限制这一危险行为。

postgres=# REVOKE ALL ON SCHEMA finance FROM public;
postgres=# GRANT ALL ON SCHEMA finance TO fiona;
postgres=# REVOKE ALL ON SCHEMA sales FROM public;
postgres=# GRANT ALL ON SCHEMA sales TO sally;

10. 给用户创建私有数据库

将数据与用户进行分离，是数据库管理的关键部分。但是仍然存在一种需求：为用户提供一个私有、安全无风险的区域来使用数据库。

postgres=# create user fred;
postgres=# create database fred owner = fred;    --创建私有数据库

postgres=# BEGIN;                                --权限部分仍是不明白，先跳过
postgres=# REVOKE connect ON DATABASE
postgres=# GRANT connect ON DATABASE fred TO fred;
postgres=# COMMIT;