[CKAN-support] datastore permissions issue

Fri Jul 25 14:50:32 UTC 2014

Hi George,

Sorry for taking a while to answer.

There are two issues here.

* The ability to create and delete new tables, and insert rows to them
is caused by a misconfiguration of the datastore permissions. The
maintainers of that instance need to run one of this methods to set up
permissions properly (I'm linking to relevant version docs for
https://staging.data.qld.gov.au, newer versions may need to use
another docs version)

http://docs.ckan.org/en/ckan-2.0.4/datastore-setup.html#set-permissions

* The ability to access system tables is a known issue and we are
discussing the best way to handle it. Although this may leak some
information, users should not be able to perform any inserts or
deletions on this tables, or extract sensitive information like
passwords if the permissions are properly configured.

Access to this tables can not be revoked at the database level, as the
way Postgres works this tables need to be accessed by the query
planner, so we need to limit access to them at the application level.
This is straight forward to use on the latest CKAN version but a bit
more difficult on previous ones. We'll let you know as soon as we have
a working patch.

Let me know if you have any other question,

Adrià

On 25 July 2014 06:42, George Sattler <George.sattler at xvt.com.au> wrote:
> Hi Guys
>
> A bit of an urgent support request if you have a sec to have a look and give
> me some advice…
>
>
>
> I received the below from Smart Service Queensland regarding their sites. It
> appears that it is possible to create, edit, and drop tables using the
> datastore API. I suppose this must be due to a mis-configuration of the
> permissions for the datastore. Would this make sense to you? I’ll probably
> need to run the permission statements again (or for the first time)?
>
>
>
> Actually for that ‘pg_catalog’ request below, I was able to run this
> successfully on the demo.ckan.org website. I’m not sure what this might
> reveal or if it should be kept secret or not?
>
> Hi,
>
> It has come to our attention that the datastorer sql search functionality
> allows you to execute any sql query as the datastore database user. Queries
> seem to be limited to the privileges of the datastore user, i.e. it gave an
> SQL error when I tried to insert into a data set.
>
> I have confirmed that you can:
> * Select from postgres public system tables/views
> * Create tables
> * Insert into tables
> * Update rows in tables
> * Drop tables
>
> Can you please advise if you or CKAN/OKFN are aware of this issue and if so
> what the recommend fix is?
>
> URL examples are below:
> https://staging.data.qld.gov.au/api/action/datastore_search_sql?sql=create
> table temp_test_dlb (dummy decimal);
> https://staging.data.qld.gov.au/api/action/datastore_search_sql?sql=insert
> into temp_test_dlb values (1234);
> https://staging.data.qld.gov.au/api/action/datastore_search_sql?sql=select *
> from temp_test_dlb;
> https://staging.data.qld.gov.au/api/action/datastore_search_sql?sql=select *
> from pg_catalog.pg_class;
> https://staging.data.qld.gov.au/api/action/datastore_search_sql?sql=drop
> table temp_test_dlb;
>
>
>
>
> ________________________________
> This email and any files transmitted with it is confidential and intended
> solely for the use of the addressee. The unauthorised use, dissemination,
> forwarding, printing or copying of this communication is strictly
> prohibited. If you have received this communication in error please notify
> us immediately by reply email and destroy this communication. Any views and
> opinions presented in this email are solely those of the author and do not
> necessarily represent the views of XVT Solutions. The recipient should check
> this email and any attachments for viruses. XVT Solutions accepts no
> liability for the content of this email, and any damage caused by any
> viruses that could potentially be transmitted through this email.
>
> _______________________________________________
> ckan-support mailing list
> ckan-support at lists.okfn.org
> https://lists.okfn.org/mailman/listinfo/ckan-support
>