SQL error on creating folders when using PostgreSQL database

[chris968]

Platform info:

• Passbolt Version: 4.5.2 (CE)
• Platform and Target: -- Operating system: CentOS 7 and official docker image -- PHP: 7.4 and 8.2 -- Web server: nginx -- Database server: PostgreSQL 15

How to reproduce:

Create a folder through the web GUI or launching cake passbolt cleanup command. The web GUI throw an error 500 but the application log show the following entry:

2024-03-14 10:17:03 error: [PDOException] SQLSTATE[23502]: Not null violation: 7 ERROR:  null value in column "id" of relation "folders" violates not-null constraint
DETAIL:  Failing row contains (null, test, 2024-03-14 11:17:03.433147+01, 2024-03-14 11:17:03.433182+01, 0b2ad4a5-c7d1-4c1c-95ef-ede2e477f87a, 0b2ad4a5-c7d1-4c1c-95ef-ede2e477f87a). in /var/www/passbolt/vendor/cakephp/cakephp/src/Database/Statement/StatementDecorator.php on line 180
Request URL: /folders.json?api-version=v2&contain%5Bpermission%5D=1
Client IP: 192.168.122.4

The cake passbolt cleanup command show another error message related to the folders table:

2024-03-14 10:22:09 error: [PDOException] SQLSTATE[42883]: Undefined function: 7 ERROR:  operator does not exist: character = uuid
LINE 1: ..." LEFT JOIN "folders" "Folders" ON "Folders"."id" = "Permiss...
                                                             ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts. in /var/www/passbolt/vendor/cakephp/cakephp/src/Database/Statement/StatementDecorator.php on line 180

We reproduce the issue on two distinct setups, the database have been migrated from a working MySQL one.

[stripthis]

Thanks for the report @chris968, we'll look into it.

[chris968]

I just realized that this have an impact on creating new passwords.

[chris968]

My current workaround is to disable the "folders" plugin:

<?php

return [
  'passbolt' => [
    'plugins' => [
      'folders' => [
        'enabled' => false,
      ],
    ],
  ],
];
``

[pabloelcolombiano]

Hi @chris968 ,

it could be that during the migration from MySQL to Postgres, some folders got their id set to null.

1. Could you run this query in your postgres client, to see if this is the case? select * from folders where "id" = null;

2. Also this query could be interesting: select column_name, data_type, character_maximum_length, column_default, is_nullable from INFORMATION_SCHEMA.COLUMNS where table_name = 'folders';

[chris968]

@pabloelcolombiano of course I can run both commands but we have no folders in our setup:

passbolt-db=>  select * from folders where "id" = null;
 id | name | created | modified | created_by | modified_by
----+------+---------+----------+------------+-------------
(0 rows)

passbolt-db=> select column_name, data_type, character_maximum_length, column_default, is_nullable from INFORMATION_SCHEMA.COLUMNS where table_name = 'folders';
 column_name |        data_type         | character_maximum_length | column_default | is_nullable
-------------+--------------------------+--------------------------+----------------+-------------
 created     | timestamp with time zone |                          |                | NO
 modified    | timestamp with time zone |                          |                | NO
 id          | character                |                       36 |                | NO
 name        | character varying        |                      256 |                | NO
 created_by  | character                |                       36 |                | NO
 modified_by | character                |                       36 |                | NO
(6 rows)

[jsm222]

operator does not exist: character = uuid so the folders.id might still be of character type and not of the postgres uuid type @pabloelcolombiano

[pabloelcolombiano]

Hi @chris968 ,

thank you for the feedback, and sorry for the delay on the response. As @jsm222 mentions, we should channge the column type to uuid. It looks like one migration was not run properly. What is the output of this command?

SELECT * FROM "phinxlog" WHERE migration_name IN ('V3120MigrateASCIIFieldsEncodingFolders');

• If there are no entries, I would suggest to run the migrations:

  bin/cake passbolt migrate

• If there is already an entry, then I would run

DELETE FROM "phinxlog" WHERE migration_name = 'V3120MigrateASCIIFieldsEncodingFolders';

and run the migrations again

bin/cake passbolt migrate

and provide us the output of the command.

Once this is done, it would be interesting to check this command again:

select column_name, data_type, character_maximum_length, column_default, is_nullable from INFORMATION_SCHEMA.COLUMNS where table_name = 'folders';

[pabloelcolombiano]

Closing this for now.