Cartodb: Missing permission in cdb_analysis_catalog for org users

[rochoa]

STR

1. Admin user goes to "Your organization".
2. Click on ADD USERS > Create User.
3. Creates a new user with username matching the pattern {{username}}-carto. I don't know if the fact of using - in the name is relevant, but just in case that's how I reproduced it.
4. With the new user API key verify you can GET /api/v2/sql?api_key={{API_KEY}}&q=select%20*%20from%20cdb_tablemetadata.
   

   Current result

With the new user API key:

• GET /api/v2/sql?api_key={{API_KEY}}&q=select%20*%20from%20cdb_analysis_catalog fails with {"error":["permission denied for relation cdb_analysis_catalog"]}.

Expected result

• GET /api/v2/sql?api_key={{API_KEY}}&q=select%20*%20from%20cdb_analysis_catalog should work.
  

  Notes

New, non-org, users work OK and they can query cdb_analysis_catalog table using the API key.

Running the rake task cartodb:db:set_user_privileges_in_cartodb_schema for the user fix the problem.

To be more specific, it is also fixed by doing the following:

u = User.where(username: '{{username}}-carto').first
u.db_service.queries.run_in_transaction(u.db_service.queries.grant_read_on_schema_queries('cartodb'))

As I pointed before, this seems to be related just to the way organisation users are created.

cc @zenitraM @dgaubert @xavijam

[javitonino]

Ok, some findings: this only happens on old organizations. New organizations have a DB role like cdb_org_member_aaaaaaaaaaaaaaaaaa that is assigned to the catalog and to new users, and they get the permissions that way.

Should we be using the role, or adding each users explicit permission to use the catalog? Not sure when we changed the approach. If we use the first one, it would be a matter of ensuring the role is added to the catalog for all orgs (e.g: via rake task).

[gfiorav]

Should we be using the role, or adding each users explicit permission to use the catalog? Not sure when we changed the approach. If we use the first one, it would be a matter of ensuring the role is added to the catalog for all orgs (e.g: via rake task).

+1

[javitonino]

Fixed with the rake in https://github.com/CartoDB/cartodb/pull/9614

[rochoa]

Thanks :-)!