OGo & PostgreSQL 8.3

PostgreSQL 8.3 not longer performs automatic casting of INT to TEXT when INTs are compared to character types. This change is documented in the release notes. This change causes a database exception to occur in OpenGroupare's ACL processing. The specific error is:

ERROR: IN types character varying and integer cannot be matched


ACL queries like the following cause the exception because they contain a condition that compares string value to an array of integers: auth_id IN ( 9981, 9991,... where auth_id is a VARCHAR(255) value.

A full ACL query might look like:

SELECT DISTINCT B.company_id FROM person AS B WHERE (( ( B.firstname ILIKE '%%yser%%' ) OR ( B.name ILIKE '%%yser%%' ) )) AND (( B.owner_id = 10100 ) OR ( ( B.is_private = 0 OR B.is_private IS NULL ) AND ( ( 0 = ( SELECT COUNT(*) FROM object_acl WHERE object_id = B.company_id ) OR 0 < ( SELECT COUNT(*) FROM object_acl WHERE object_id = B.company_id AND action = 'allowed' AND permissions LIKE '%r%' AND ( auth_id = 10100 OR auth_id IN ( 9981, 9991, 10003, 11530, 55000, 77210, 142730, 142780, 142850, 393280, 423400, 445160, 949410, 949540, 955770, 970730, 970990, 1096320, 9153790, 9153880, 9154000, 10597021, 10735169 ) ) ) ) ) )) AND (B.is_template_user IS NULL OR B.is_template_user = 0) AND (B.db_status 'archived') LIMIT 50


The solution to this problem is to modify the schema of the object_acl table so that the values are integer types; this is possible since the auth_id and object_id only store object ids, which are integers, regardless of the fact that the field type was VARCHAR(255). Originally it was thought that some other values might be used in ACL processing, such as LDAP DNs, but only support for object ids exists and will exist in the forseable future.

The table can be modified using ALTER TABLE:

ALTER TABLE object_acl ALTER COLUMN object_id TYPE INT USING object_id::int;
ALTER TABLE object_acl ALTER COLUMN auth_id TYPE INT USING auth_id::int;


This change will be included as the official schema in OGo 5.4. With this change OpenGroupware should work without any issues in conjunction with PostgreSQL 8.3. The thread discussing this issue is available in the archive.