如何在PostgreSQL中将表设置为只读

Question

我需要防止应用程序核心表格的意外更改。在应用程序运行期间，我不希望任何人更改行，但是，在部署新版本应用程序时，我会进行更改。例如：

create table procedure (
  id int primary key not null,
  name varchar(50) not null
);

insert into procedure (id, name) values (1, 'Send Exam Request');
insert into procedure (id, name) values (2, 'Confirm Exam Data');
insert into procedure (id, name) values (3, 'Close Visit');

-- Now, make the table read-only.

太棒了，应用程序的第一个版本已经成功地运行在生产环境中。

下个月当我部署应用程序的下一个版本时：

-- Make the table read-write.

insert into procedure (id, name) values (4, 'Request Visit'); -- new proc
delete from procedure where id = 2; -- remove old unsupported proc

-- Now, make the table read-only again.

理想情况下，在应用程序正常运行期间，如果有人尝试更改它，会显示错误消息。类似这样的内容：

insert into procedure (id, name) values (5, 'New bad procedure');
ERROR: Cannot modify table procedure. 
Contact the system administrator if you want to make this change.

有什么最佳方法可以实现这个吗？

- The Impaler

Answer 1

创建一个触发器，使其抛出一个错误：

create function do_not_change()
  returns trigger
as
$$
begin
  raise exception 'Cannot modify table procedure. 
Contact the system administrator if you want to make this change.';
end;
$$
language plpgsql;

create trigger no_change_trigger
  before insert or update or delete on "procedure"
  execute procedure do_not_change();

请注意，procedure是一个关键字。您应避免使用该名称的表。

---

更好的选择是将创建表格的DB用户与修改表格的用户分开。创建者应该是所有者，而“修改”用户仅被授予必要的权限：对于所有常规表，包括select、insert、update、delete，对于procedure表格只有select权限。这将需要一些更改您的部署脚本，但绝对是更安全的选项（特别是如果只有少数人知道“创建者”密码）

- user330315

---

听起来非常不错。我能否在部署到生产环境的 SQL 的第一步中禁用此触发器，并在最后一步重新启用它？ - The Impaler

啊... ALTER TABLE table_name DISABLE TRIGGER no_change_trigger 和 ALTER TABLE table_name ENABLE TRIGGER no_change_trigger。 - The Impaler

警告来自搜索引擎的人们：移除触发器会使表对所有用户可写。相反，使用适当的访问控制，比听起来简单得多！ - undefined

Answer 2

这就是访问控制的作用。将对特定表格（或更好的是表格视图）的插入权限限制为某些用户（也许只有你自己）。其他用户如果尝试向该表格中插入数据，将会收到“权限不足”的错误信息。同样适用于更新、删除和查询。

- Jon Heggland

---

我喜欢这个想法，但是我不明白部署 SQL 脚本是否能够使它在几分钟内变为可读写，最后再次变为只读。也许这并不必要，因为它将由数据库管理员运行。 - The Impaler

GRANT和REVOKE语句当然可以在脚本中运行。但正如您所指出的那样，这并不是必要的，因为部署脚本将由管理员而不是普通用户运行。 - Jon Heggland