login fails on PostgreSQL

[dpocock]

The Apache error.log contains the following:

Fail to execute query: [SELECT * FROM user WHERE username='admin' AND password = PASSWORD('test123');]
ERROR:  column "username" does not exist

This query appears to work though, notice it uses quotes around user and it uses md5 instead of password

SELECT * FROM "user" WHERE username='admin' AND password = md5('test123');

This was observed in 5.0.5

[dpocock]

I found the PASSWORD() function Admin.php and Internal.php. Changing it to md5() and wrapping user in double quotes I can log in again.

[adubovikov]

https://github.com/sipcapture/homer-api/commit/94f2ed82d31c1fa2c1756637f37e43d10050440b

can you please test it with last git ?

[adubovikov]

https://github.com/sipcapture/homer-api/commit/e4d9f950a6de58167a50a44baf7be1fbde936efc

and this

[dpocock]

great, I was just about to ask where the kamailio.cfg had gone

[dpocock]

the kamailio.cfg in https://github.com/sipcapture/homer-api/commit/94f2ed82d31c1fa2c1756637f37e43d10050440b still has the FROM_UNIXTIME, see issue #109 for the fix

[adubovikov]

https://github.com/sipcapture/homer-api/commit/ff96a34f3ac3d111d6240000a3fad7a88e521d15

[adubovikov]

so, should we close it ?

[dpocock]

more issues:

instead of time_zone, you need time zone, and no = http://www.mydbapool.com/changing-the-current-time-zone-in-mysql-postgresql/

In the file sql/postgres/schema_configuration.sql you need to change from md5 to crypt

For crypto to work, you need to log into homer_configuration once as the DBA (user postgres) and do

$ psql homer_configuration
# CREATE EXTENSION pgcrypto;

That only needs to be done once, it can't be done from the schema_configuration file as that should be loaded by the homer_user login

[adubovikov]

https://github.com/sipcapture/homer-api/commit/9303f848e14ef090245a3fc6fb77e572f3cd57ee

[adubovikov]

can we close the ticket ?

[dpocock]

yes, this can be closed