PostgreSQL + Rails: is it possible to have a write-only database user in PG?
I am creating JSON API in Ruby on Rails. I want to have write-only accounts that need to feed data into the system, but they are not allowed to read it.
To get an extra layer of security, I would like to apply this rule at the database level.
The idea is to be of type "writer" which uses a separate database connection. This connection should be allowed to insert / update / delete, but not select.
I have everything set up well, but unfortunately Rails generates this request on insert:
INSERT INTO "default"."products" ("id", "name", "sku") VALUES ($1, $2, $3) RETURNING "id"
The "RETURNING id" part makes it unsuccessful 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"
Is there a way to get around this in PG or Rails? I see two options:
- Granting "limited" SELECT permission to the writers on the PG so they can only "see" some of the columns. I don't know if this is possible at all.
- Getting Rails to not add this "RETURNING id" at the end of the request, although this may have side effects.
I found one article from someone who had the same problem and ended up just granting SELECT rights to the writers users:
https://til.hashrocket.com/posts/0c83645c03-postgres-permissions-to-insert-but-not-return
Is there a chance there is a real solution to working on the setting above?
source to share
There is a way to use the row level security feature available as of 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
The user only sees the last row that he has placed in the database. He knows what it is.
source to share