[ckan-dev] data store permission problem

Mon May 27 19:39:33 UTC 2013

Hi Everyone, 

After looking into this a little bit deeper, I think I can help you fix the problem. It's actually a bug in CKAN which I will fix in https://github.com/okfn/ckan/issues/907 soon. There is a way for you to fix this without patching CKAN below which you can do now without having to wait for the patch. I'm sorry for the confusion that this bug caused.

To fix the problem with the permissions during the creation of the nested type, you need to run a datastore_create action. The problem is that a nested type needs to be created once for every datastore database but this fails if the first action is datastore_search. Running a datastore_create action will create the nested type and fix the problem. I will fix the problem but for now this should fix your problem.

@Don I think in your case there is another problem which is that you migrated the datastore from 8.4 to 9.2. PG 9.2 added native support for json so we don't have to fake it any more (which we did for 8.4 but that's a technical detail). Having said this, you can fix this by executing `drop type nested;` on your database, restart CKAN, and then execute a datastore_create command (because of the reason explained above) to created the correct type.

Best wishes and enjoy your datastore,
Dominik 

On 27 May 2013, at 20:37, Henrik Korsgaard <henrikkorsgaard at gmail.com> wrote:

> Hi Don,
> 
> Have you tried logging into the postgres db with a user and create/query
> the tables?
> 
> If I remember correctly (I am not that familiar with postgres), you can
> sign in to postgres with a specific user to a specific db and test the
> permissions 'manually' like so:
> 
> psql --username=the_db_username --dbname=the_db_username -W --host=localhost
> 
> Then you can try viewing the specific db with \dt or \dv or the likes
> 
> Moreover, I had some issues with the permissions due to using identical
> names for both the ckan db and the ckan db user, i.e. ckan_default as
> database and username. This has not been verified as an issue though.
> Lastly, I have had issues with the postgres permissions when setting them
> over a ssh connection due to a locale (language encoding) conflict. But
> again, I have not been able to reproduce the issues.
> 
> -Henrik
> 
> 
> 
> On 27 May 2013 18:21, Don Gourley <donald.gourley at gmail.com> wrote:
> 
>> What other information would be useful to troubleshoot my "permission
>> denied
>> for schema public" problem?
>> 
>> thanks, Don
>> 
>> 
>> Date: Sat, 25 May 2013 12:54:07 -0400
>>> From: Don Gourley <donald.gourley at gmail.com>
>>> Subject: [ckan-dev] data store permission problem
>>> To: ckan-dev at lists.okfn.org
>>> Message-ID:
>>>        <
>>> CABPWEL-pn8pFnNKJqnar2Dy0GpPQ9OSrY8grhoG4R_kkZbjnZw at mail.gmail.com>
>>> Content-Type: text/plain; charset="utf-8"
>>> 
>>> 
>>> After getting basic CKAN 2.0 working on CentOS 6, I set up the data store
>>> according to the directions at
>>> http://docs.ckan.org/en/ckan-2.0/datastore-setup.html
>>> 
>>> When I try to test my installation with this URL:
>>> 
>>> http://localhost/api/3/action/datastore_search?resource_id=_table_metadata
>>> 
>>> I get a 500 server error. In the error log:
>>> 
>>> [Sat May 25 11:47:44 2013] [error] [client 10.12.15.181] Error - <class
>>> 'sqlalchemy.exc.ProgrammingError'>: (ProgrammingError) permission denied
>>> for schema public
>>> [Sat May 25 11:47:44 2013] [error] [client 10.12.15.181]  'CREATE TYPE
>>> "nested" AS (json json, extra text)' {}
>>> 
>>> The paster set-permissions command ran without error. I had to move CKAN
>>> from PostgreSQL 8.4 to 9.2, and dumped and loaded the ckan database to do
>>> that, but the data store database was created and set up directly on the
>>> 9.2 database. I don't know much about the postgreSQL permissions or access
>>> control model, but here's what my databases look like in postgres:
>>> 
>>>                                  List of databases
>>>   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access
>>> privileges
>>> 
>>> -----------+----------+----------+-------------+-------------+-----------------------
>>> ckandb    | ckanuser | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
>>> =Tc/ckanuser
>>>        +
>>>           |          |          |             |             |
>>> ckanuser=CTc/ckanuser
>>> ckandstor | ckanuser | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
>>> =Tc/ckanuser
>>>        +
>>>           |          |          |             |             |
>>> ckanuser=CTc/ckanuser+
>>>           |          |          |             |             |
>>> ckandsro=c/ckanuser
>>> 
>>> 
>>> Any ideas where I went wrong?
>>> 
>>> -Don
>>> 
>> 
>> _______________________________________________
>> ckan-dev mailing list
>> ckan-dev at lists.okfn.org
>> http://lists.okfn.org/mailman/listinfo/ckan-dev
>> Unsubscribe: http://lists.okfn.org/mailman/options/ckan-dev
>> 
>> 
> 
> 
> -- 
> Med venlig hilsen - Best regards,
> 
> *Henrik Korsgaard*
> Phone: +45 22377114
> Office: CAVI 114b, Aarhus University
> 
> NB. I am slowly migrating my university related correspondence to my
> official AU mail: korsgaard at cavi.au.dk - feel free to contact me at that
> address if relevant.
> _______________________________________________
> ckan-dev mailing list
> ckan-dev at lists.okfn.org
> http://lists.okfn.org/mailman/listinfo/ckan-dev
> Unsubscribe: http://lists.okfn.org/mailman/options/ckan-dev

Dominik Moritz
CKAN developer  |  skype: d.moritz  |  @doobly_doo
The Open Knowledge Foundation
Empowering through Open Knowledge
http://okfn.org/  |  @okfn  |  http://ckan.org  |  @CKANproject