psycopg2-binary should not be used in production - psycopg2 should be used instead

[rdimaio]

Description

For PostgreSQL-related functions, Rucio uses the psycopg2-binary library:

rucio/requirements.txt

Line 34 in 3397448

psycopg2-binary==2.9.9 # postgresql_extras

However, the psycopg2 docs say:

The psycopg2-binary package is meant for beginners to start playing with Python and PostgreSQL without the need to meet the build requirements.
If you are the maintainer of a published package depending on psycopg2 you shouldn’t use psycopg2-binary as a module dependency. For production use you are advised to use the source distribution.

See also: https://stackoverflow.com/questions/70330567/what-is-the-different-about-psycopg2-and-psycopg2-binary-python-package

Additionally there is also psycopg3, which introduces many new features (the differences between psycopg2 and psycopg3 are listed here), like support for static typing.

To do:

• If possible, we might want to consider upgrading from psycopg2 to psycopg3
• If not, at the very least we should move from psycopg2-binary to psycopg2 as recommended in their docs.

This is currently a blocker for this issue: #6666, which requires the psycopg2.sql module.

Steps to reproduce

N/A

Rucio Version

No response

Additional Information

No response

[Ban42]

Hi,
I started having a look on this, these are some notes about my analysis:

• Currently psycopg is used in:

  • core/did_meta_plugins/postgres_meta.py
  • db/sqla/session.py
  • In the requirements (requirements.txt and setuputil.py)

  I tried to re-implement the functionalities of "postgres_meta.py" with psycopg3 and they seem to work. The only difference is that the result of the query in psycopg2 is an OrderedDict while in psycopg3 is a normal dict.

  In the next days, I will check "session.py" and reproduce its functionalities with psycopg3

• Apparently in psycopg3 the binary installation is not deprecated for production systems (even though is advised), which could be an extra reason to move to psycopg3. Source is their official Documentation

Let me know if you need me to perform other checks (like running tests on a MR) to validate the possibility to switch to psycopg3.

P.S. Feel free to assign me the issue and/or telling me if I am doing something wrong
Cheers

[Ban42]

Continuing the message above.

About 'sessions.py', the only place in which it seems to be used is the function 'psql_convert_decimal_to_float' which performs a functionality that in psycopg3 is built-in as mentioned in their documentation

Cheers