防止用户在默认表空间中创建表

Question

我有一个问题，目前还没有找到任何线索。我会尽力解释清楚，但如果需要更多细节，请随时询问！

背景

我正在使用Windows上的Postgres 9.2.4，并且需要为每个用户实现某种配额管理。

据我所知，没有这样的内置功能，大多数答案都指向使用文件系统的配额管理能力。

有一个单独的数据库，每个用户将拥有自己的模式。

我采取的方法包括通过具有不同表空间的不同位置来分离每个用户的数据文件，每个用户都是其表空间的所有者（因此我可以按照每个文件夹设置限额配置）。

这导致了我面临的问题...

问题

当创建表时，用户可以选择pg_default表空间存储数据。

让我更加困惑的是，如果稍后将表空间更改为由用户拥有的表空间，然后尝试将其切换回pg_default表空间，则会抛出权限被拒绝的错误。

为了澄清这里的顺序，以下是一些示例代码：

-- Creates the table in the default tablespace
CREATE TABLE test_schema.test_table ( ) 
TABLESPACE pg_default;

-- Changes the tablespace to the one owned by the user
ALTER TABLE test_schema.test_table
SET TABLESPACE user_tablespace;

-- Tries to set back the pg_default tablespace (throws permission denied to pg_default tablespace)
ALTER TABLE test_schema.test_table
SET TABLESPACE pg_default;

所有这些命令都使用没有管理员特权的用户登录执行。pg_default表空间归postgres登录（管理员帐户）所有。

我猜这可能与数据库表空间有关，它被设置为使用pg_default表空间。

问题

是否可能限制用户仅在其拥有的表空间中创建对象？

- Chopin

---

回到 pg_default 的问题是一个 bug。刚刚提出了修复方案 - 请参见 http://www.postgresql.org/message-id/[e-mail] - Craig Ringer

@CraigRinger 感谢您指出这一点，真是巧合！据我所知，无论所有权如何，用户始终具有使用 database 表空间的权限，因为它是按照这种方式设计的。 - Chopin

Answer 1

如果您使用磁盘配额，那么您将给自己带来很多工作。实际上，在PostgreSQL中有一个近似的解决方案，只需进行一些微调即可，无需创建大量的表空间（但为每个用户提供自己的命名空间仍然是个好主意）。

函数pg_total_relation_size(regclass)会为您提供表格的总磁盘空间使用情况，包括其索引和TOAST表。因此，请扫描pg_class并求和：

CREATE VIEW user_disk_usage AS
  SELECT r.rolname, SUM(pg_total_relation_size(c.oid)) AS total_disk_usage
  FROM pg_class c, pg_roles r
  WHERE c.relkind = 'r'
    AND c.relowner = r.oid
  GROUP BY c.relowner;

这将为您提供每个所有者使用的总磁盘空间，无论表位于何处。这里呈现它作为视图定义以供下面使用。

为了使其以相当准确的方式工作，您需要定期进行VACUUM ANALYZE数据库操作。如果您有低流量时段（例如每天的凌晨3点至5点或星期日），请使用用户postgres运行计划任务来运行它。为该作业创建一个函数，该函数执行VACUUM，然后进行配额检查。

CREATE FUNCTION user_quota_check() RETURNS void AS $$
DECLARE
  user_data record;
BEGIN
  -- Vacuum the database to get accurate disk use data
  VACUUM FULL ANALYZE;

  -- Find users over disk quota
  FOR user_data IN SELECT * FROM user_disk_usage LOOP
    IF (user_data.total_disk_usage > <<your quota>>) THEN
      EXECUTE 'REVOKE CREATE ON SCHEMA ' || <<user''s schema name>> || ', PUBLIC FROM ' || user_data.rolname;
      -- REVOKE INSERT privileges too, unless you work with BEFORE INSERT triggers on all tables
    END IF;
  END LOOP;
END; $$ LANGUAGE plpgsql;
REVOKE ALL ON FUNCTION user_quota_check() FROM PUBLIC;

如果所有者超过配额，您可以在所有相关模式上撤销CREATE权限，通常仅限于分配给用户和公共模式的模式，以便不能创建新表。您还应该在所有表上撤销INSERT权限，但这很容易被规避，因为所有者可以立即授予INSERT权限。然而，这可能是对用户采取更严厉行动的原因。最好您将在数据库中的每个表上创建一个before insert触发器，使用像上面那样的日常扫描。

用户仍然具有SELECT权限，因此他/她仍然可以访问数据。更有趣的是，DELETE和TRUNCATE将允许用户释放磁盘空间并解决锁定问题。然后可以使用类似于上述函数重新安装权限。

CREATE FUNCTION reclaim_disk_space() RETURNS void AS $$
DECLARE
  disk_use bigint;
BEGIN
  -- Vacuum current_user's tables.
  -- Slow and therefore adequate punishment for going over quota.
  VACUUM FULL VERBOSE ANALYZE;

  -- Now re-instate privileges if enough space was reclaimed.
  SELECT total_disk_usage INTO disk_use
  FROM user_disk_usage
  WHERE rolname = session_user;
  IF (disk_use < <<your quota>>) THEN
    EXECUTE 'GRANT CREATE ON SCHEMA ' || <<user''s schema name>> || ', PUBLIC TO ' || user_data.rolname;
    -- GRANT INSERT privileges too, unless you work with BEFORE INSERT triggers on all tables
    RAISE NOTICE 'Disk use under quota limit. Privileges restored.';
  ELSE
    RAISE NOTICE 'Still using too much disk space. Free up more space.';
  END IF;
END; $$ LANGUAGE plpgsql;      

被锁定的用户可以在删除足够的数据以低于配额限制后自行调用此函数。您可以添加更复杂的功能，例如表格列出每个用户的配额（而不是总配额），并将实际使用与该配额进行比较，在插入触发器上发出RAISE NOTICE，当超过配额的80％时（这需要每个表都有一个before insert触发器，可以通过postgres用户在常规新表扫描中轻松完成，相同的触发器可用于拒绝插入如果超过配额），每小时重复一次该通知（因此记录最后发出通知的时间）等等。此解决方案是近似的，因为配额未在实时中检查。这是可能的（在每次插入时运行user_quota_check()，修改为仅检查session_user的表），但很可能会产生过多的开销，使其变得无趣。在晚上运行user_quota_check()，以便对配额进行每日管理。并手动惩罚任何在白天使用过多空间的用户。

- Patrick

---

感谢您提供如此详细的答案。我最终采用了与您描述的非常相似的策略（基于我的特定情况有些微小的差异），使用反应式方法（调度作业以定期执行检查并阻止用户进行进一步插入）而不是积极主动的方法（防止用户超过配额）。我没有考虑到VACUUM操作，我会进行修订。我接受了您的答案，尽管它没有直接回答原始问题，但它很好地解决了背后的场景（也许我应该重新表述一下）。再次感谢！ - Chopin