Fail to change passwords (1.7.6)

[loren...@gmail.com]

Hello,

I just completed a new (full) installation of XNAT v1.7.6, and I am having problems to change users' passwords (including admin). Anything else seems to work fine so far, I set up an email address and works, added a new user who can login, a new project, edited the site information... 

But when trying to update the password, it seems to work (it says 'Password changed' on the website) but it does not change. I can see errors like this in sql.log and xdat.log

2021-04-14 15:26:28,815 [http-bio-8080-exec-5] ERROR org.nrg.xft.db.DBAction - An SQL exception occurred trying to execute the command: "SELECT update_ls_xdat_user(4,4)"

org.postgresql.util.PSQLException: ERROR: column "oid" does not exist

  Hint: Perhaps you meant to reference the column "xs_item_cache.id".

  Where: PL/pgSQL function update_ls_xdat_user(integer,integer) line 10 at FOR over SELECT rows

*** 

2021-04-14 15:26:28,813 [http-bio-8080-exec-5] ERROR org.nrg.xft.db.PoolDBUtils - An error occurred trying to execute the user USERID query: SELECT update_ls_xdat_user(4,4)

org.postgresql.util.PSQLException: ERROR: column "oid" does not exist

  Hint: Perhaps you meant to reference the column "xs_item_cache.id".

And in velocity.log 

2021-04-14 15:26:29,314 [http-bio-8080-exec-10] ERROR velocity - Left side ($sessionIpCount) of '>' operation has null value at navigations//DefaultTop.vm[\line 152, column 64]

Any ideas? 

Many thanks in advance for your help!

Lorena 

[Herrick, Rick]

What version of PostgreSQL are you using?

 

-- 

Rick Herrick

Sr. Programmer/Analyst

Neuroinformatics Research Group

Washington University School of Medicine

Phone: +1 (314) 273-1645

 

From: xnat_di...@googlegroups.com <xnat_di...@googlegroups.com> on behalf of loren...@gmail.com <loren...@gmail.com>
Date: Wednesday, April 14, 2021 at 9:36 AM
To: xnat_discussion <xnat_di...@googlegroups.com>
Subject: [XNAT Discussion] Fail to change passwords (1.7.6)

* External Email - Caution *

--
You received this message because you are subscribed to the Google Groups "xnat_discussion" group.
To unsubscribe from this group and stop receiving emails from it, send an email to xnat_discussi...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/xnat_discussion/14817340-7138-4361-8c45-a290914c2eb5n%40googlegroups.com.

 

---

The materials in this message are private and may contain Protected Healthcare Information or other information of a sensitive nature. If you are not the intended recipient, be advised that any unauthorized use, disclosure, copying or the taking of any action in reliance on the contents of this information is strictly prohibited. If you have received this email in error, please immediately notify the sender via telephone or return mail.

[loren...@gmail.com]

Hi Rick,

I think it's 10.15 (I think the same I have used before in another machine)

psql -V

psql (PostgreSQL) 10.15 (Ubuntu 10.15-1.pgdg18.04+1)

Best,

Lorena 

[Herrick, Rick]

The reason I ask is that you shouldn’t run into that problem with the default PostgreSQL configuration until version 12. The implicit oid column has been deprecated in PostgreSQL for a long time (back to 8.1, I think?), but was still enabled unless you turned it off explicitly. With the release of PostgreSQL 12 they finally set it so that implicit oids are not supported in the default configuration, as described in the release notes. Support for handling this was added in XNAT 1.8.0, so I’m not surprised your XNAT is having trouble with it, but I am surprised that the issue is there for XNAT to have trouble with.

 

Try opening psql and running this query:

 

xnat=> SELECT oid, elementname, ids, create_date FROM xs_item_cache ORDER BY oid;

  oid  |  elementname   | ids |        create_date

-------+----------------+-----+----------------------------

29267 | xdat:user      | 2   | 2017-09-16 21:24:17.203062

29268 | xdat:user      | 1   | 2017-09-16 21:26:04.715583

29270 | xdat:userGroup | 15  | 2017-09-16 21:28:26.095075

(3 rows)

 

I ran that in PostgreSQL 10.16. When I ran the same query against the same database in 12.6, I got this:

 

xnat=> SELECT oid, elementname, ids, create_date FROM xs_item_cache ORDER BY oid;

ERROR:  column "oid" does not exist

LINE 1: SELECT oid, elementname, ids, create_date FROM xs_item_cache...

 

Presuming this worked previously, I can’t really say why it’s not working now unless the database configuration changed. Check the postgresql.conf configuration for your database. There’s a setting in there that looks like this by default:

 

#default_with_oids = off

 

Note that it’s commented out. But that’s also the default value if default_with_oids isn’t explicitly turned on. In a standard XNAT database dump, you’ll see this sort of thing going on:

 

SET default_with_oids = true;

… do some stuff

SET default_with_oids = false;

… do some more stuff

SET default_with_oids = true;

 

Looking at my database dump, I found the update_ls_xdat_user() function then searched backwards from there for SET default_with_oids and didn’t find it. A ways after the function was defined that was set to false. So it picks up the default default_with_oids as true with the effect that my query above worked properly.

 

I can think of two ways around this issue:

 

• Upgrade your XNAT to 1.8.1, which doesn’t use the oid-based functions any more (it will migrate any existing oid-based functions in the database)
• Dump your database, add SET default_with_oids = true; at the beginning, then reimport the database

 

I don’t understand why this is happening with a database on 10.15, especially if it worked before, so if you can figure that out I’d be interested in knowing.

To view this discussion on the web visit https://groups.google.com/d/msgid/xnat_discussion/ee7b0b96-dd02-425a-aae8-68dc3ba485a7n%40googlegroups.com.

[loren...@gmail.com]

Dear Rick,

Thank you so much for such a detailed explanation, it is really helpful! 

Apologies, I meant that I used that version in a previous installation I did one year ago in a different machine, this one I am testing at the moment is new of this year, in a different VM, so basically this is the first time I try using it and I installed PostgreSQL newly earlier this year. 

In any case, I checked /etc/postgresql/10/main/postgresql.conf which seems to be the one used

sudo -u postgres psql -c 'SHOW config_file'

               config_file               

-----------------------------------------

 /etc/postgresql/10/main/postgresql.conf

(1 row)

and I haven't modified it, there is only one line containing #default_with_oids = off. When I try the query, I indeed get 

xnat=> SELECT oid, elementname, ids, create_date FROM xs_item_cache ORDER BY oid;

ERROR:  column "oid" does not exist

I did the XNAT database dump and I found three calls to that, only that they are the other way around:

SET default_with_oids = false;

… 

SET default_with_oids = true;

… 

SET default_with_oids = false;

I tried to follow method 2, modified the dump adding SET default_with_oids = true; at the end of the first block at the beginning (immediately after SET row_security = off;), then close connections to the db, drop it, recreate it (postgres~$ createdb -O xnat xnat) and import it as xnat user with "psql -U xnat -d xnat < /data/backup/xnat_modified.sql' - the import seems to work but I still can't change the passwords. In fact if I do another dump after this, the line I have added in the modified version doesn't appear, which makes me think I am not doing this correctly or settings are overwritten somehow. 

I have also tried adding default_with_oids = on in /etc/postgresql/10/main/postgresql.conf and did the same process, drop the database, recreate it, import it, but still I can't change the passwords. Sorry, I am really new to PostgreSQL, maybe there is a general restarting step I am forgetting to do? 

If you have any other ideas I can try I would be very keen to see if I can fix it. Otherwise I will upgrade to v1.8 directly. 

Many thanks,

Lorena