[ckan-dev] datastore: permission denied Schema public

Wed Mar 27 08:46:12 UTC 2013

Hi,

unfortunately the error has not gone. It now complains that the readonlyuser has write permissions.

What I did:
- Switched postgresql locale to "en_US.UTF-8"
- Re-Initialized the complete postgresql databases
- Installed CKAN databases "ckandb" and "datastore"
- Did a "git pull" to update to latest master branch
- Set permissions regarding "Option 1: Paster command" - all commands succeed

I am using both DBs on the same server and "ckanuser" as write user for both.

The users in set_permissions.sql are:
#######################
-- name of the main CKAN database
\set maindb 'ckandb'
-- the name of the datastore database
\set datastoredb 'datastore'
-- username of the ckan postgres user
\set ckanuser "ckanuser"
-- username of the datastore user that can write
\set wuser "ckanuser"
-- username of the datastore user who has only read permissions
\set rouser "readonlyuser"
#######################

The error is:
#######################
(pyenv)ckan at www :~/pyenv/src/ckan> paster --plugin=ckan db init
2013-03-27 09:12:25,893 INFO [ckanext.datastore.plugin] Connection url postgresql://readonlyuser:xxx@localhost /datastore
Traceback (most recent call last):
 File "/opt/ckan/pyenv/bin/paster", line 8, in <module>
 load_entry_point('PasteScript==1.7.5', 'console_scripts', 'paster')()
 File "/opt/ckan/pyenv/lib/python2.7/site-packages/paste/script/command.py", line 104, in run
 invoke(command, command_name, options, args[1:])
 File "/opt/ckan/pyenv/lib/python2.7/site-packages/paste/script/command.py", line 143, in invoke
 exit_code = runner.run(args)
 File "/opt/ckan/pyenv/lib/python2.7/site-packages/paste/script/command.py", line 238, in run
 result = self.command()
 File "/opt/ckan/pyenv/src/ckan/ckan/lib/cli.py", line 124, in command
 self._load_config()
 File "/opt/ckan/pyenv/src/ckan/ckan/lib/cli.py", line 86, in _load_config
 load_environment(conf.global_conf, conf.local_conf)
 File "/opt/ckan/pyenv/src/ckan/ckan/config/environment.py", line 349, in load_environment
 plugin.configure(config)
 File "/opt/ckan/pyenv/src/ckan/ckanext/datastore/plugin.py", line 66, in configure
 self._check_read_permissions()
 File "/opt/ckan/pyenv/src/ckan/ckanext/datastore/plugin.py", line 175, in _check_read_permissions
 raise Exception("We have write permissions on the read-only database.")
Exception: We have write permissions on the read-only database.
#######################

Output from "psql -l":
#######################
postgres at www :~> psql -l
 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
 datastore | ckanuser | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/ckanuser +
 | | | | | ckanuser=CTc/ckanuser +
 | | | | | readonlyuser=c/ckanuser
 postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
 | | | | | postgres=CTc/postgres
 template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
 | | | | | postgres=CTc/postgres
(5 rows)
#######################

It seems that the readonlyuser really still has write permissions, because I am also able to execute the "create table" command within a psql shell.
I am not really familiar with postgresql, but could it be that basic permission settings have changed in postgresql 9.2.3?

Any other hints?

Cheers
Ralf

----------------ursprüngliche Nachricht-----------------
Von: "User66" user66 at arcor.de 
An: ckan-dev at lists.okfn.org 
Datum: Tue, 26 Mar 2013 15:13:16 +0100
-------------------------------------------------

> Hi Dominik,
> 
> thanks for the tip.
> 
> After looking into the code your explanation sounds reasonable. I will
> switch the postgresql language to English. If you don't hear from me
> again, this issue is fixed (I can test it not before this evening).
> 
> Cheers
> Ralf
> 
> 
> Am 26.03.2013 11:06, schrieb Dominik Moritz:
>> Hallo Ralf,
>>
>> We expect a permission error because there should be a connection that is not 
>> allowed to write. However, the check only works if the error message is in 
>> english, otherwise an exception is raised. 
>>
>> There is an issue and a pull request at 
>> https://github.com/okfn/ckan/pull/642.
>>
>> Until this fix is in the release branch, you could set the language of your 
>> postgres installation to english. 
>>
>> Best wishes,
>> Dominik