Schema migration fails for user 'jabref' in Postgres

[mundry]

JabRef version

5.9 (latest release)

Operating system

GNU / Linux

Details on version and operating system

No response

Checked with the latest development build

• I made a backup of my libraries before testing the latest development version.
• I have tested the latest development version and the problem persists

Steps to reproduce the behaviour

Hi,

the schema migration in version 5.9 fails in Postgres if the database user is called jabref.

This is due to how the search path in Postgres works. By default the search path consists of "$user", public, where $user is the name of the current session user. (see Postgres documentation on search path)

This means that when copying over the data, the migration copies from an empty jabref schema into an empty jabref schema, since the source table names are unqualified (i.e. no schema name is specified). (see PostgreSQLProcessor.java)

When upgrading to version 5.9, upon starting the client, the new jabref schema gets created, along with the tables, and the VersionDBStructure entry in METADATA is created, too. But copying over the entire data from the ENTRY, FIELD and METADATA tables fails silently.

Using a different database user name the migration works as expected.

This behaviour has been tested using Client version

JabRef 5.10--2023-02-27--fba970c
Linux 6.1.13-200.fc37.x86_64 amd64
Java 19.0.2
JavaFX 19+11

and Postgres v15.2.

Appendix

No response

[Siedlerchr]

Thanks for the detailed description and investigation. Do you have an idea how to solve this? Or should we just add a warning?

[mundry]

The naive approach would be to assume the tables are in the public schema and prefix the source tables with public..

Messing with the search path just for the migration should be a last resort in my opinion.

I don't know whether there is a way to look up what schema a specific table is in. That might be the most robust approach.

Alternatively, the name of the current session user could also be fetched and compared to the target schema, in this case named jabref. SELECT session_user or SELECT current_user should be the way to go here