Inserting in shared tables fails because of sequence permission

[juanignaciosl]

I can add rows in this table through the editor, but not with the SQL API:

➜  ~  curl -d "api_key=<myapikey>&q=insert into rtorre.csv_3 (kkk) values ('Japan')" https://juanignaciosl.cartodb.com/api/v2/sql
{"error":["permission denied for sequence csv_3_cartodb_id_seq"]}%

I report it here for @rochoa to take a look, but it probably involves a fix in CartoDB backend organization user creation

[rochoa]

Which pg role/user is doing the query in the editor? The same user/role should generate the error, right?

[rochoa]

bump!

[javisantana]

that means the editor is not using the right permissions ?

[juanignaciosl]

In the editor table owner role is used.

[javisantana]

and why not the current user performing the edition?

[juanignaciosl]

¯_(ツ)_/¯

Let's summon @rafatower , maybe he knows a reason on why Table always uses owner. It just looks like usage of Table has always been based on owner instead of viewer_user, which looks like is not used for data handling at all.

[javisantana]

I think it's a bug in rails.

FYI: Basically those inserts should be performed from the frontend using SQL, it's the only part of the frontend that still needs to be migrated cc @CartoDB/frontend

[juanignaciosl]

Ok, back to the issue, this might turn into a "user should've had permission on sequence" error, isn't it? Maybe I can fix it at CartoDB/issues#3859 as well.

[javisantana]

shouldn't be fixed in cartodb-postgres?

[juanignaciosl]

True, maybe in CDB_Organization_Add_Table_Read_Write_Permission?

[rochoa]

Yes, I think so. That function should take care to grant permission on table associated entities.

Will this pop for other entities like indexes? The problem I see is: what happens if another entity is associated to the table, like a new index, will that mean that the granted users will get again in a situation where they cannot insert? Something like STR:

1. User A creates table wadus with sequence wadus_cartodb_id_seq
2. User A grants write permission to User B with CDB_Organization_Add_Table_Read_Write_Permission
3. User B insert data into wadus
4. User A creates a new index in table wadus.

[juanignaciosl]

Adding an index is not a problem, isn't it? It's transparent to user B. Only entities with high coupling to the tables, like sequences, should be granted, and I can't think about any other specific need. Maybe if A creates a trigger on wadus which uses a function that B can't use? Rare corner cases, I think.

[rafatower]

IMO there's no good reason to do that with the owner from Table. The editor should rely more on the sql api for its job whenever possible.