datastore_search_sql with authorization should show private datasets.

[cphsolutionslab]

After discussions on the CKAN mailing list it seems as if there's a common understanding that datastore_search_sql should be able to work with private datasets if correct authorization header is sent along.
Today it's not able to so.

[rossjones]

It appears that most of the issues to do with this revolve around authentication for some use cases, particularly when doing joins across private/public resources. There may be more.

It isn't clear yet whether it could be implemented but with those use-cases disabled (if you're querying a private resource you can't join to public ones), or even how it would be implemented.

I think there's general agreement it would be nice if it did work the way you suggest, so let's leave this ticket open for discussion of possible routes to take with it.

[wardi]

maybe if the datastore database tables were separated into postgres schemas for each organization owning each private dataset. Then we could create a user that had read permissions on the schemas they should be able to read for the query.

We could either keep users in sync with permissions in the datastore db or create temporary users for each request.

[cphsolutionslab]

What if a user was permitted access to tables across organizations?
Are CKAN resource access implemented directly into PostgreSQL’s user database or is it simpler; structured in a table within PostgreSQL?
Would it be possible to have the API call query permission for access before actual doing the requested query? So if 3 tables where included in the API SQL call, the underlying CKAN API would checkup on the permission for these 3 tables accordingly before performing the actual SQL query with joins?

[davidread]

This issue was closed due to inactivity. Feel free to reopen if you have more feedback or are interested it working on it

[torfsen]

On CKAN 2.6.2, I get internal server errors when calling the datastore_info API function on a private dataset. When I reported that on the ckan-dev mailing list, @mattfullerton told me about this issue.

So this issue obviously also affects datastore_info (and not just datastore_search_sql as noted here and in the docs). Other DataStore API functions might also be affected, but I didn't investigate further.

Irregardless of whether the original issue of accessing private datasets via the DataPusher API by authenticating using an API-key is resolved: even if there is a permission problem then that should trigger the correct error messages instead of an internal server error.