[BUG] "user" table in Postgresql is a reserved keyword

[diaznet]

Describe the bug A run_migrations task in webapp was stuck indefinitely. I understood this was in relatiiopn with https://github.com/dfir-iris/iris-web/commit/eacb390aed2edbfa4904edd2e20873514e87b130 We sawt an attempt at altering "user" table in Iris DB. While troubleshooting we saw a potential issue. The table exists and has valid data inside. However the name "user" is also a reserved keyword for PostgreSQL.

This query returns the current db active user:

iris_db_prod=> select * from user; user

db_admin_user (1 row)

db_admin_user is the user we use to connect to the DB (via POSTGRES_USER and POSTGRES_ADMIN_USER).

When quoting user, then we get the content of iris_db_prod > user table

iris_db_prod=> select * from "user"; id | user | name | email | uuid | ... ----+-----------------------------------+----------------------+-----------------------------------+--------------------------------------+- ... 1 | [email protected] | User1 | [email protected] | xxx | ... 2 | [email protected] | User2 | [email protected] | xxx | ... (2 rows)

Also with specifying public schema:

iris_db_prod=> select * from public.user; id | user | name | email | uuid | ... ----+-----------------------------------+----------------------+-----------------------------------+--------------------------------------+- ... 1 | [email protected] | User1 | [email protected] | xxx | ... 2 | [email protected] | User2 | [email protected] | xxx | ... (2 rows)

To Reproduce Steps to reproduce the behavior:

1. Use Postgres 16
2. Check commands posted above

Expected behavior "user" keyword as table name should not be used. A quick Google search returns plenty of these conclusions.

Additional context user is a reserved keyword in PosgreSQL 16 documentation, therefore it is best practice to not use it, especially as table name, otgherwise this leads to unexpected behavior like above.