【PostgreSQL】PostgreSQL限制用户删表操作（drop table）

---

一、方法一：事件触发器实现

1.1、普通用户下，创建测试表：users_t

普通用户登录，执行下面脚本，创建测试表

drop table if exists       users_t;
CREATE TABLE IF NOT EXISTS users_t
(
  id serial NOT NULL,
  username character varying(40),
  email character varying(100),
  age int8
);
INSERT INTO users_t(username,email,age) VALUES ( 'tzq1','tzq1@qq.com',18);
INSERT INTO users_t(username,email,age) VALUES ( 'tzq2','tzq2@qq.com',19);
INSERT INTO users_t(username,email,age) VALUES ( 'tzq3','tzq3@qq.com',20);
INSERT INTO users_t(username,email,age) VALUES ( 'tzq4','tzq4@qq.com',21);

执行结果如下图：

1.2、超管用户，创建触发器函数：disable_drop_table_f()

超管用户登录，创建触发器函数：disable_drop_table_f()，脚本如下：

drop FUNCTION if exists    disable_drop_table_f cascade;
CREATE OR REPLACE FUNCTION disable_drop_table_f()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
BEGIN
  if tg_tag = 'DROP TABLE'  THEN
    RAISE EXCEPTION 'Command % is disabled.', tg_tag;
  END if;
END;
$$;

执行结果如下图：

1.3、超管用户，创建事件触发器：disable_drop_table_tr

超管用户登录，创建事件触发器：disable_drop_table_tr，脚本如下：

drop   EVENT TRIGGER if exists disable_drop_table_tr;
CREATE EVENT TRIGGER           disable_drop_table_tr
    ON ddl_command_start
EXECUTE FUNCTION disable_drop_table_f();

执行结果如下图：

1.4、普通用户，测试删除表操作（drop table）

普通用户登录，测试删除表操作（drop table），看是否能够将表删掉。

drop table users_t;

执行结果如下图：

可以看到，普通用户做删表操作是不行的。

1.5、总结

这里用函数disable_drop_table_f()里面的RAISE EXCEPTION抛出异常来控制相关的操作。其他操作也可通过类似方式进行限制。

1.6、附

更多的事件类型可查阅：

1.6.1、事件触发器触发矩阵

下表列出了所有命令的事件触发器支持情况。
支持事件触发器的命令标签如下：

二、方法二：修改对象属主，授权给新的用户，只给dml权限

2.1、超管用户登录，创建测试表：users_test_t

超管用户登录，在public schema下，执行下面脚本，创建测试表

drop table if exists       users_test_t;
CREATE TABLE IF NOT EXISTS users_test_t
(
  id serial NOT NULL,
  username character varying(40),
  email character varying(100),
  age int8
);
INSERT INTO users_test_t (username,email,age) VALUES ( 'tzq1','tzq1@qq.com',18);
INSERT INTO users_test_t (username,email,age) VALUES ( 'tzq2','tzq2@qq.com',19);
INSERT INTO users_test_t (username,email,age) VALUES ( 'tzq3','tzq3@qq.com',20);
INSERT INTO users_test_t (username,email,age) VALUES ( 'tzq4','tzq4@qq.com',21);

执行结果如下图：

2.2、授权给普通用户，只给dml权限

grant select, insert on users_test_t to tzq;

2.3、普通用户，测试删除表操作（drop table）

普通用户登录，测试删除表操作（drop table），看是否能够将表删掉。

drop table users_test_t;

执行结果如下图：

可以看到如果不是在普通用户自己的schema下创建的表，不是表的owner，是不允许删除表的，这样也可以控制。