Today I Learned

hashrocket A Hashrocket project

Postgres permissions to insert, but not return.

Postgres permissions can be harsh. If you don't have the select permission on a table you might not be able to do some very rails like things that you think you ought to be able to do, take my friend Jimmy.

set role 'admin';
create role 'jimmy';
create table fruits (id serial primary key, name varchar);
grant insert on fruits to jimmy;
grant usage on fruits_id_seq to jimmy;

Given these permissions it's possible for Jimmy to make inserts into the fruit table like so:

set role 'jimmy';
insert into fruits (name) values ('Apple');

But Rails wants a little more, it wants to know the id of the thing that was just created which is a problem because Jimmy doesn't have select permissions.

set role 'jimmy';
insert into fruits (name) values ('Orange') returning id;
ERROR:  permission denied for relation fruits

Argh this complicates matters, but I'll relent and give Jimmy the appropriate permissions so that he can add records through ActiveRecord.

set role 'admin';
grant select on fruits to jimmy;
set role 'jimmy';
insert into fruits (name) values ('orange') returning id;
See More #sql TILs
Looking for help? Hashrocket developers believe that data quality is as important as code quality. We enjoy all the challenges of relational databases, from finding the fastest index, to structuring data to fit the needs of an application. We're eager to share our experiences; check out PG Casts, our series of free PostgreSQL screencasts.