[UX] Get better debugging information on a "psycopg2.errors.InsufficientPrivilege: permission denied for table cw_cwproperty"
One of our users (Carine) ended up having this wonderful useless error:
/usr/lib/python3/dist-packages/paste/request.py:33: DeprecationWarning: Using or importing the ABCs from 'collections' instead of from 'collections.abc' is deprecated, and in 3.8 it will stop working
from collections import MutableMapping as DictMixin
Traceback (most recent call last):
File "/usr/lib/python3/dist-packages/logilab/common/decorators.py", line 94, in __call__
return _cache[args]
KeyError: (False,)
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "/usr/bin/cubicweb-ctl", line 11, in <module>
load_entry_point('cubicweb==3.28.2', 'console_scripts', 'cubicweb-ctl')()
File "/usr/lib/python3/dist-packages/cubicweb/cwctl.py", line 830, in run
CWCTL.run(args)
File "/usr/lib/python3/dist-packages/logilab/common/clcommands.py", line 136, in run
sys.exit(command.main_run(args, rcfile))
File "/usr/lib/python3/dist-packages/logilab/common/clcommands.py", line 265, in main_run
self.run(args)
File "/usr/lib/python3/dist-packages/cubicweb/web/webctl.py", line 147, in run
self.generate_static_dir(config, dest)
File "/usr/lib/python3/dist-packages/cubicweb/web/webctl.py", line 88, in generate_static_dir
for datadir in self._datadirs(config, repo=repo):
File "/usr/lib/python3/dist-packages/cubicweb/web/webctl.py", line 110, in _datadirs
repo = config.repository()
File "/usr/lib/python3/dist-packages/cubicweb/cwconfig.py", line 981, in repository
repo.bootstrap()
File "/usr/lib/python3/dist-packages/cubicweb/server/repository.py", line 406, in bootstrap
config.init_cubes(self.get_cubes())
File "/usr/lib/python3/dist-packages/cubicweb/server/repository.py", line 672, in get_cubes
or self.config.mode == 'test'))
File "/usr/lib/python3/dist-packages/logilab/common/decorators.py", line 69, in wrapped
return self.__call__(*args, **kwargs)
File "/usr/lib/python3/dist-packages/logilab/common/decorators.py", line 96, in __call__
_cache[args] = __me.callable(self, *args)
File "/usr/lib/python3/dist-packages/cubicweb/server/repository.py", line 697, in get_versions
'P pkey ~="system.version.%"', build_descr=False):
File "/usr/lib/python3/dist-packages/cubicweb/server/session.py", line 153, in check_open
return func(cnx, *args, **kwargs)
File "/usr/lib/python3/dist-packages/cubicweb/server/session.py", line 666, in execute
rset = self._execute(self, rql, kwargs, build_descr)
File "/usr/lib/python3/dist-packages/cubicweb/statsd_logger.py", line 135, in __call__
return self.callable(*args, **kw)
File "/usr/lib/python3/dist-packages/cubicweb/server/querier.py", line 564, in execute
results = plan.execute()
File "/usr/lib/python3/dist-packages/cubicweb/server/querier.py", line 181, in execute
result = step.execute()
File "/usr/lib/python3/dist-packages/cubicweb/server/ssplanner.py", line 375, in execute
rql_query_tracing_token=self.rql_query_tracing_token)
File "/usr/lib/python3/dist-packages/cubicweb/server/sources/native.py", line 529, in syntax_tree_search
cursor = cnx.system_sql(sql, args, rql_query_tracing_token=rql_query_tracing_token)
File "/usr/lib/python3/dist-packages/cubicweb/server/session.py", line 153, in check_open
return func(cnx, *args, **kwargs)
File "/usr/lib/python3/dist-packages/cubicweb/server/session.py", line 795, in system_sql
rql_query_tracing_token=rql_query_tracing_token)
File "/usr/lib/python3/dist-packages/cubicweb/statsd_logger.py", line 135, in __call__
return self.callable(*args, **kw)
File "/usr/lib/python3/dist-packages/cubicweb/server/sources/native.py", line 706, in doexec
cursor.execute(str(query), args)
psycopg2.errors.InsufficientPrivilege: permission denied for table cw_cwproperty
bootstrapping instance
The problem turned out she had created a db with a superuser, gave GRANTs to the user of CW but postgresql still wasn't happy.
The real problem here is: this error suck and doesn't give any information on how to solve that at all, thus she lost quite some time and the people doing support to and as the general principle tells: if debugging is hard it's that you don't have the correct informations. Conclusion: we need to display those information.
A quick analysis suggest that we need to show:
- the user doing the connection and if it's a superuser
- the user owning the database and if it's a superuser
- all the GRANTs on this database of the connecting user
Step to proceed:
- find an easy way to reproduce this problem
- the current user is easy to get
- idk how to check if a user is a superuser
- find how to check who has created a database https://stackoverflow.com/questions/17165254/get-db-owners-name-in-postgresql
- same, is it a superuser?
- how to get all GRANTs?
- how to check that we have this specific exception
Exploration of solutions:
- https://stackoverflow.com/questions/17165254/get-db-owners-name-in-postgresql
- the tables pg_catalog and pg_catalog.pg_database seems very interesting
-
select rolcreatedb from pg_authid where rolname = 'your user name'
https://stackoverflow.com/questions/12956743/postgres-how-to-check-if-user-has-createdb-permissions -
has_database_privilege(user, database, privilege)
also https://www.postgresql.org/docs/8.3/functions-info.html see https://stackoverflow.com/questions/12956743/postgres-how-to-check-if-user-has-createdb-permissions