Flexible permission system

javisantana commented 8 years ago

Intro

We need a way to allow developers to:

access some specific tables (select, insert, create)
allow to access so specific functions like geocoding, routing and so on

without using the current api key (which gives access to everything)

Lot of developers have been asking this for years but now it's really important since the Location Based Services need an api key

Proposal

The proposal is to have different api keys so each has a different permission set. Right now the api_key gives rights to do everything in the database, the concept is the same but limiting per api key

The way to do this would be to have one database user per api key so when calling SQL API that user is used to execute queries. The main user (the one authenticated when using the current API KEY) has the rights to give different permissions to each user.

I don't have a clear idea on how to name users and how to the mapping user <-> api key, it could be something like


select CDB_API_KEY_User('hjk4ui5y34g5h89127387Hjhasd') -> 'user_hjk4ui5y34g5h89127387Hjhasd'

GRANT  SELECT ON mytable to CDB_API_KEY_User('hjk4ui5y34g5h89127387Hjhasd');

This schema fits with the current one and it will be compatible with a future oauth implementation.

User interface

For the user interface (it's not related to the sql api but worth an explanation) we would have a list of api keys with different permissions (per table and per funcionality group, for example geocoder, routing, isolines, DO)

Problems

Sync the database with the UI
Database connection pooling
Maps API auth
Database user naming, if we we

cc @rochoa @danicarrion @AbelVM @jsanz @saleiva @juanignaciosl

juanignaciosl commented 8 years ago

This is somehow analogue to GitHub Personal Access Tokens, as each token has a different permission set.

Database-UI sync can be done (assuming that it can't be computed on the fly when needed because of performance reasons) the same way that groups do at _CDB_Group_API_Request, probably extracting the request mechanism to a generic method. New functions should call new endpoints that extend the current Database API at the Editor.

rochoa commented 8 years ago

I'm missing how to assign quotas: limit this user to do 1000 geocoding calls per day. But I don't know if we want to solve that problem yet.

On the Maps API side: named maps has, half-assed, support for using different database users within the same account.

javisantana commented 8 years ago

the geocoding/other services quotas are managed by the services so I'd not worry right now cc @rafatower

rafatower commented 8 years ago

not an immediate concern but something I'd consider for a quota/billing service cc/ @ethervoid

danicarrion commented 8 years ago

why don't we just implement oauth2?

javisantana commented 8 years ago

@danicarrion because that would mean every user would need to have a carto account, right? sometimes an app just need to have access to a table with select permissions

could you describe the usecase

danicarrion commented 8 years ago

OAuth2 is a common request in sales meetings. IMHO, if we are to invest in adding features to our app authentication engine, we should go in that direction.

First, OAuth2 allows for API-key-like authentication (https://tools.ietf.org/html/rfc6749#section-4.4). So we can start here, simply adapting our APIs to support this.

Then, we could implement the workflow you're proposing on OAuth2. At first glance, I don't see anything there that contradicts the specification.

And, finally, we could implement user authentication via OAuth2 in 2 steps:

Basic authentication (https://github.com/CartoDB/cartodb-management/wiki/OAuth-2.0-(I))
Authorization
Other non-critical operative details, like token expiration

I think it'd be nice if we can kill a few birds with one stone, even if it needs to be a slightly bigger stone. OAuth2 now fits very well with having a mobile sdk too.

javisantana commented 8 years ago

OAuth2 is a common request in sales meetings. IMHO, if we are to invest in adding features to our app authentication engine, we should go in that direction.

Ok, so what are the use cases?

I'm totally against adding complexity if we don't have a very good reason and implementing oauth client side is far more complex than adding a "&api_key=COPY_PASTED_FROM_EDITOR"

javisantana commented 7 years ago

answering to myself, usecases for oauth2:

A third party tool that needs to put data into CARTO (QGIS)
A third party tool that needs to fetch private data from CARTO (QGIS)

javisantana commented 7 years ago

Another requirement: for maps api we would need a way to allow some kind of create access but not to read tables, just for maps api working with analysis

jaakla commented 7 years ago

Some use cases from Mobile SDK point of view:

App to modify/create map data - add personal notes to map
For just reading data named maps and table permission "link only" is usually enough
In most cases oauth2 (assuming it requires named users) would not work, apps are for wide 'anonymous' audience.
App developer has own user-base and wants to give controlled write access to their users (not carto viewer users) - some kind of oauth2+ldap combination?
Using LDS geocode, isozones and routing calls. However, for real end-user app cases our quotas are probably too low anyway.

dgaubert commented 6 years ago

After releasing the new Auth API, we cover some of the needs proposed here. Closing this issue as the new Auth API is a transversal project across multiple repositories.

CartoDB / CartoDB-SQL-API