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

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 表空间。

问题

可以限制用户只在他们拥有的表空间中创建对象吗？

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;

这会为您提供每个所有者使用的总磁盘空间，而不管表位于何处。它在此处作为 View 定义呈现，供下面使用。

要以相当准确的方式进行这项工作，您需要定期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;

如果所有者超过配额，您可以在所有相关模式上REVOKE CREATE，通常只有分配给用户的模式和公共(public)模式，这样就无法创建新表。您还应该在所有表上 REVOKE INSERT 但这很容易规避，因为所有者可以 GRANT 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% 的配额(这要求每个表都有一个插入前触发器，这可以由 postgres 用户在定期扫描新表时轻松完成，如果超过配额，可以使用相同的触发器拒绝插入)，重复每小时发出一次通知(因此记录最后一次发出通知的时间)等。

这个解决方案是近似的，因为配额不是实时检查的。这是可能的(在每次插入时运行 user_quota_check()，修改为仅检查 session_user 的表)，但很可能开销太大而使它变得有趣。隔夜运行 user_quota_check() 以对配额进行日常管理。并手动鞭打白天占用过多空间的任何用户。