PostgreSQL + Rails:PG 中可以有只写数据库用户吗? [英] PostgreSQL + Rails: is it possible to have a write-only database user in PG?

问题描述

我正在用 Ruby on Rails 构建一个 JSON API.我想要只写用户帐户，这些帐户应该向系统提供数据，但不应允许他们从中读取数据.

I am building a JSON API in Ruby on Rails. I want to have write-only user accounts that are supposed to feed data to the system but who should not be allowed to read from it.

为了实现额外的安全层，我想在数据库级别强制执行此规则.

In order to achieve an extra layer of security, I'd like to enforce this rule at the database level.

这个想法是有一个作者"类型的用户，它使用与数据库的单独连接.这个连接应该允许插入/更新/删除但不能选择.

The idea is to have a "writer" type of user which uses a separate connection to the database. This connection should be allowed to insert / update / delete but not to select.

我已经很好地设置了一切，但不幸的是 Rails 在插入时生成了这个查询:

I have everything set up nicely but unfortunately Rails generates this query upon insert:

INSERT INTO "default"."products" ("id", "name", "sku") VALUES ($1, $2, $3) RETURNING "id"

返回 ID"部分使其失败，因为用户没有 SELECT 权限:

The "RETURNING id" part is making it fail because the user does not have SELECT permissions:

ActiveRecord::StatementInvalid: PG::InsufficientPrivilege: ERROR:  permission denied 
for relation products: 
INSERT INTO "default"."products" ("id", "name", "sku") VALUES ($1, $2, $3) RETURNING "id"

有没有办法在 PG 或 Rails 中解决这个问题?我看到的两个选项是:

Is there any way to get around this in PG or Rails? The two options I see are:

1. 向 PG 中的 writer 用户授予有限"的 SELECT 权限，因此他们只能查看"某些列.我不知道这是否可能.
2. 让 Rails 不在查询末尾添加返回 ID"，尽管这可能会产生副作用.

我发现了一篇文章，有人遇到了同样的问题，但最终只授予作者用户 SELECT 权限:

I found one article of someone who had the same issue and ended up just granting SELECT rights to writer users:

https://til.hashrocket.com/posts/0c83645c03-postgres-permissions-to-insert-but-not-return

是否有实际的解决方案可以使上述设置正常工作?

Any chance there is an actual solution to get the setup above to work?

推荐答案

从 PostgreSQL 9.5 开始，有一种方法可以使用行级安全特性:

There is a way using row level security feature available since PostgreSQL 9.5:

create table products(id serial primary key, name text not null, sku text not null);
grant select,insert on products to tometzky;
grant usage on sequence products_id_seq to tometzky;
alter table products enable row level security;
create policy products_tometzky on products to tometzky
  using (id=currval('products_id_seq'));

tometzky=> select * from products;
ERROR:  currval of sequence "products_id_seq" is not yet defined in this session
tometzky=> insert into products (name, sku) values ('a','a') returning id;
1
tometzky=> select * from products;
1|a|a
tometzky=> insert into products (name, sku) values ('b','b') returning id;
2
tometzky=> select * from products;
2|b|b

用户只能看到他放入数据库的最后一行.反正他知道那是什么.

A user only sees the last row he put to the database. He knows what it is anyway.

这篇关于PostgreSQL + Rails:PG 中可以有只写数据库用户吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！