Modify

Opened 4 years ago

Closed 5 months ago

#10772 closed defect (fixed)

ProgrammingError: operator does not exist: text = integer

Reported by: Moritz Bunkus Owned by: Steffen Hoffmann
Priority: normal Component: AccountManagerPlugin
Severity: normal Keywords: db PostreSQL
Cc: Ryan J Ollos Trac Release: 1.0

Description

I've checked the other tickets here with similar subjects; none of them applies.

AccountManagerPlugin 0.5dev from trunk, revision 12516; Trac 1.0.1; PostgreSQL 9.1

Happens when you log in. In my special case I've registered a new user account, logged in for the first time. Error occurred.

Error message from log file:

2013-01-08 18:49:21,821 Trac[main] ERROR: Exception caught while post-processing request:
Traceback (most recent call last):
  File "/usr/local/lib/python2.7/dist-packages/Trac-1.0.1dev_r11468-py2.7.egg/trac/web/main.py", line 243, in dispatch
    self._post_process_request(req)
  File "/usr/local/lib/python2.7/dist-packages/Trac-1.0.1dev_r11468-py2.7.egg/trac/web/main.py", line 340, in _post_process_request
    f.post_process_request(req, *(None,)*extra_arg_count)
  File "build/bdist.linux-i686/egg/acct_mgr/web_ui.py", line 123, in post_process_request
    if req.authname and req.authname != 'anonymous':
  File "/usr/local/lib/python2.7/dist-packages/Trac-1.0.1dev_r11468-py2.7.egg/trac/web/api.py", line 304, in __getattr__
    value = self.callbacks[name](self)
  File "/usr/local/lib/python2.7/dist-packages/Trac-1.0.1dev_r11468-py2.7.egg/trac/web/main.py", line 135, in authenticate
    authname = authenticator.authenticate(req)
  File "build/bdist.linux-i686/egg/acct_mgr/util.py", line 83, in wrap
    return func(self, *args, **kwds)
  File "build/bdist.linux-i686/egg/acct_mgr/web_ui.py", line 334, in authenticate
    user = self._remote_user(req)
  File "build/bdist.linux-i686/egg/acct_mgr/web_ui.py", line 680, in _remote_user
    if acctmgr.check_password(user, password) == True:
  File "build/bdist.linux-i686/egg/acct_mgr/api.py", line 273, in check_password
    valid = store.check_password(user, password)
  File "build/bdist.linux-i686/egg/acct_mgr/db.py", line 106, in check_password
    """, (self.key, user))
  File "/usr/local/lib/python2.7/dist-packages/Trac-1.0.1dev_r11468-py2.7.egg/trac/db/util.py", line 65, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
InternalError: current transaction is aborted, commands ignored until end of transaction block
InternalError: current transaction is aborted, commands ignored until end of transaction block

2013-01-08 18:49:21,876 Trac[main] ERROR: Internal Server Error:
Traceback (most recent call last):
  File "/usr/local/lib/python2.7/dist-packages/Trac-1.0.1dev_r11468-py2.7.egg/trac/web/main.py", line 497, in _dispatch_request
    dispatcher.dispatch(req)
  File "/usr/local/lib/python2.7/dist-packages/Trac-1.0.1dev_r11468-py2.7.egg/trac/web/main.py", line 179, in dispatch
    chosen_handler)
  File "/usr/local/lib/python2.7/dist-packages/Trac-1.0.1dev_r11468-py2.7.egg/trac/web/main.py", line 325, in _pre_process_request
    chosen_handler = filter_.pre_process_request(req, chosen_handler)
  File "build/bdist.linux-i686/egg/acct_mgr/api.py", line 420, in pre_process_request
    if not req.session.authenticated or \
  File "/usr/local/lib/python2.7/dist-packages/Trac-1.0.1dev_r11468-py2.7.egg/trac/web/api.py", line 304, in __getattr__
    value = self.callbacks[name](self)
  File "/usr/local/lib/python2.7/dist-packages/Trac-1.0.1dev_r11468-py2.7.egg/trac/web/main.py", line 268, in _get_session
    return Session(self.env, req)
  File "/usr/local/lib/python2.7/dist-packages/Trac-1.0.1dev_r11468-py2.7.egg/trac/web/session.py", line 200, in __init__
    if req.authname == 'anonymous':
  File "/usr/local/lib/python2.7/dist-packages/Trac-1.0.1dev_r11468-py2.7.egg/trac/web/api.py", line 304, in __getattr__
    value = self.callbacks[name](self)
  File "/usr/local/lib/python2.7/dist-packages/Trac-1.0.1dev_r11468-py2.7.egg/trac/web/main.py", line 135, in authenticate
    authname = authenticator.authenticate(req)
  File "build/bdist.linux-i686/egg/acct_mgr/util.py", line 83, in wrap
    return func(self, *args, **kwds)
  File "build/bdist.linux-i686/egg/acct_mgr/web_ui.py", line 334, in authenticate
    user = self._remote_user(req)
  File "build/bdist.linux-i686/egg/acct_mgr/web_ui.py", line 680, in _remote_user
    if acctmgr.check_password(user, password) == True:
  File "build/bdist.linux-i686/egg/acct_mgr/api.py", line 277, in check_password
    self._maybe_update_hash(user, password)
  File "build/bdist.linux-i686/egg/acct_mgr/api.py", line 381, in _maybe_update_hash
    if not get_user_attribute(self.env, 1, user, 'password_refreshed', 1):
  File "build/bdist.linux-i686/egg/acct_mgr/model.py", line 125, in get_user_attribute
    cursor.execute(sql, sql_args)
  File "/usr/local/lib/python2.7/dist-packages/Trac-1.0.1dev_r11468-py2.7.egg/trac/db/util.py", line 65, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
ProgrammingError: operator does not exist: text = integer
LINE 4:         WHERE sid=1 AND authenticated='testfufu' AND name='p...

Relevant configuration:

[account-manager]
db_htpasswd_hash_type = sha
force_passwd_change = true
hash_method = HtPasswdHashMethod
htpasswd_file = /opt/trac/kivitendo/htpasswd
htpasswd_hash_type = sha
login_attempt_max_count = 0
notify_actions =
password_store = SessionStore
persistent_sessions = False
refresh_passwd = true
register_check = BasicCheck, EmailCheck, UsernamePermCheck, RegExpCheck
verify_email = true

[components]
acct_mgr.admin.accountguardadminpage = enabled
acct_mgr.admin.accountmanageradminpage = enabled
acct_mgr.api.accountmanager = enabled
acct_mgr.db.sessionstore = enabled
acct_mgr.guard.accountguard = enabled
acct_mgr.htfile.abstractpasswordfilestore = disabled
acct_mgr.htfile.htdigeststore = disabled
acct_mgr.htfile.htpasswdstore = disabled
acct_mgr.http.httpauthstore = disabled
acct_mgr.notification.accountchangelistener = enabled
acct_mgr.notification.accountchangenotificationadminpanel = enabled
acct_mgr.pwhash.htdigesthashmethod = enabled
acct_mgr.pwhash.htpasswdhashmethod = enabled
acct_mgr.register.basiccheck = enabled
acct_mgr.register.bottrapcheck = enabled
acct_mgr.register.emailcheck = enabled
acct_mgr.register.emailverificationmodule = enabled
acct_mgr.register.regexpcheck = enabled
acct_mgr.register.registrationmodule = enabled
acct_mgr.register.usernamepermcheck = enabled
acct_mgr.web_ui.loginmodule = enabled

I have a fix for this. When I apply it I get the next error message (only showing the last lines) in a related place:

  File "build/bdist.linux-i686/egg/acct_mgr/api.py", line 381, in _maybe_update_hash
    if not get_user_attribute(self.env, 1, user, 'password_refreshed', 1):
  File "build/bdist.linux-i686/egg/acct_mgr/model.py", line 125, in get_user_attribute
    cursor.execute(sql, sql_args)
  File "/usr/local/lib/python2.7/dist-packages/Trac-1.0.1dev_r11468-py2.7.egg/trac/db/util.py", line 65, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
ProgrammingError: operator does not exist: text = integer
LINE 4:         WHERE sid=1 AND authenticated='testfufu' AND name='p...

This error message shows two problems: 1. the data type mismatch and 2. the field order is wrong (have to swap the name and authenticated columns).

I'll attach a diff that fixes both places for me. PostgreSQL is simply way more picky about data types than other databases.

Attachments (1)

acct_mgr-data-types-and-field-order-ticket-10772.diff (1.1 KB) - added by Moritz Bunkus 4 years ago.

Download all attachments as: .zip

Change History (10)

Changed 4 years ago by Moritz Bunkus

comment:1 Changed 4 years ago by Moritz Bunkus

In the second error message testfufu is the user name I've chosen for the new user. Hence the column order problem.

comment:2 Changed 4 years ago by Steffen Hoffmann

Keywords: db PostreSQL added

Easy even to guess, sure.

This is bad. But it means, that it'll just refresh the hash until the end of time - yet a bit funny, that I've never noticed that before. Thanks for reporting it. I'll go and prepare unit testing for these functions alongside a fix for all of us.

Btw, I do strongly agree, that PostgreSQL is a very reliable partner for uncovering db access code weaknesses.

comment:3 Changed 4 years ago by Steffen Hoffmann

Cc: Ryan J Ollos added; anonymous removed

Hm interesting, my new unit tests fail to reproduce it at least for PostgreSQL 8.4.13 (on Debian Squeeze). Didn't try with 9.1 yet, but I will, because its available for me too, just not setup by now.

comment:4 Changed 4 years ago by Steffen Hoffmann

(In [12528]) AccountManagerPlugin: Ensure proper type 'string' even for storing numeric values in Trac db table session_attribute, refs #10772.

In contrast to the proposed patch I preferred to prevent future issues as well by improving the function get_user_attribute itself. However, thanks to mbunkus for testing hash refresh better than me before.

comment:5 Changed 4 years ago by Steffen Hoffmann

Changes including additional unit tests have been run against PostgreSQL server version 9.1.7 too, with similar results. But I've noticed a systematical speed gain of about 7 % for the full test suite compared to running same code with server version 8.4.13.

comment:6 Changed 4 years ago by Steffen Hoffmann

(In [12529]) AccountManagerPlugin: Ensure proper type 'int' for 'authenticated' in Trac db table session_attribute, refs #10772.

Consequent continuation of [12528], and some compatibility code re-arranged.

comment:7 Changed 2 years ago by Steffen Hoffmann

Status: newaccepted

comment:8 Changed 2 years ago by Ryan J Ollos

I've hit this issue running AccountManagerPlugin 0.4.4. It seems significant enough to warrant a 0.4.5 release, assuming 0.5 won't go out for a while. Is that something I can help make happen?

comment:9 Changed 5 months ago by Ryan J Ollos

Resolution: fixed
Status: acceptedclosed

Modify Ticket

Change Properties
Set your email in Preferences
Action
as closed The owner will remain Steffen Hoffmann.
The resolution will be deleted. Next status will be 'reopened'.

Add Comment


E-mail address and name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.