ObjectId longer than 10 characters fails for PostgreSQL

Issue Description

Specifying objectIdSize for more than 10 characters makes the system unusable.

Steps to reproduce

1. Run the parse-server with --objectIdSize 32 and a PostgreSQL database
2. Signup a new user via ReST API

Expected Results

User gets created.

Actual Outcome

Response to the client:

{
  "code": 1,
  "message": "Internal server error."
}

Server log error entry:

error: value too long for type character(10)
    at Connection.parseE (/parse-server/node_modules/pg/lib/connection.js:600:48)
    at Connection.parseMessage (/parse-server/node_modules/pg/lib/connection.js:399:19)
    at Socket.<anonymous> (/parse-server/node_modules/pg/lib/connection.js:115:22)
    at Socket.emit (events.js:311:20)
    at Socket.EventEmitter.emit (domain.js:482:12)
    at addChunk (_stream_readable.js:294:12)
    at readableAddChunk (_stream_readable.js:275:11)
    at Socket.Readable.push (_stream_readable.js:209:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:186:23)

Environment Setup

• Server

  • parse-server version (Be specific! Don't say 'latest'.) : 4.2.0
  • Operating System: Ubuntu 18.04 LTS
  • Hardware: Docker Container
  • Localhost or remote server? (AWS, Heroku, Azure, Digital Ocean, etc): AWS

• Database

  • MongoDB version: none
  • PostgreSQL version: 12.2
  • Storage engine: ???
  • Hardware: Docker Container
  • Localhost or remote server? (AWS, mLab, ObjectRocket, Digital Ocean, etc): AWS

Logs/Trace

Error from PostgreSQL which traced it to the actual problem:

2020-04-16 10:14:49.055 GMT [244] ERROR:  value too long for type character(10)
2020-04-16 10:14:49.055 GMT [244] STATEMENT:  INSERT INTO "_Session" ("sessionToken","user","createdWith","restricted","expiresAt","updatedAt","createdAt","objectId") VALUES ('r:<redacted>','IrEAlMkPVPnrfZGroeIIsWezAE8nriEY','{"action":"signup","authProvider":"password"}',false,'2020-04-16T10:19:49.054Z','2020-04-16T10:14:49.054Z','2020-04-16T10:14:49.054Z','AYBjC5izngspqIGNTfzL2p1pGuPNNxj0')

Looking into the generated schema, the issue is an inconsistency between objectId columns created as text and the user column in the _Session table, which is set to character(10):

                                              Table "public._Session"
     Column     |           Type           | Collation | Nullable | Default | Storage  | Stats target | Description 
----------------+--------------------------+-----------+----------+---------+----------+--------------+-------------
 objectId       | text                     |           | not null |         | extended |              | 
 createdAt      | timestamp with time zone |           |          |         | plain    |              | 
 updatedAt      | timestamp with time zone |           |          |         | plain    |              | 
 restricted     | boolean                  |           |          |         | plain    |              | 
 user           | character(10)            |           |          |         | extended |              | 
 installationId | text                     |           |          |         | extended |              | 
 sessionToken   | text                     |           |          |         | extended |              | 
 expiresAt      | timestamp with time zone |           |          |         | plain    |              | 
 createdWith    | jsonb                    |           |          |         | extended |              | 
 _rperm         | text[]                   |           |          |         | extended |              | 
 _wperm         | text[]                   |           |          |         | extended |              | 
Indexes:
    "_Session_pkey" PRIMARY KEY, btree ("objectId")
Access method: heap

Workaround

Reduce --objectIdSize parameter to 10.

Just found out that there is no transactional consistency here as well. Even though the user creation failed from the client perspective, I still see entries in the _User table with the larger objectId's:

parse=> select "objectId", "createdAt" from "_User" ;
             objectId             |         createdAt          
----------------------------------+----------------------------
 LRvQtFs990qqz2DeCS8Bl7hOCEuBjn6L | 2020-04-16 09:33:53.624+00
 HuqRzK8WgyBXJ4ADxrVYt6fvIzbKkkrn | 2020-04-16 09:36:15.282+00
 aDTCdLYvGs4afKsQ1sxX0LcESQBSiS0i | 2020-04-16 09:37:19.619+00
 pwV2cPf9BZzqkdvSfcqi3JZquztEvwzq | 2020-04-16 09:49:35.839+00
 jF0xaX9kNnaB8eGm2F2Aib1HJ1QDg5Lu | 2020-04-16 09:49:58.369+00
 j18Hup6Xq7OPsEMkmtdub1sNUGzD436N | 2020-04-16 09:50:02.979+00
 gbBC7M7eDSjrcMmknwdccknGdpS6pfyT | 2020-04-16 09:50:05.742+00
 lmCTRUv3zXoieQfjaRudTUErAresZqaD | 2020-04-16 09:50:15.888+00
 3GMwVHZKZjLd9MZUHYypEqTalkGmlDHe | 2020-04-16 10:09:19.02+00
 IrEAlMkPVPnrfZGroeIIsWezAE8nriEY | 2020-04-16 10:14:48.97+00
 xCQpVjvPBv                       | 2020-04-16 10:30:21.422+00
 cnTpgrNs6M                       | 2020-04-16 10:30:57.692+00
(12 rows)

[cbaker6]

I think the issue is when the _Session table is created and probably every other table that points to the objectId of the _User table here:

parse-server/src/Adapters/Storage/Postgres/PostgresStorageAdapter.js

Line 40 in a5ef0be

return 'char(10)';

The objectId still being created in the user table makes sense as some of the test cases create large objectId's,

parse-server/spec/PostgresStorageAdapter.spec.js

Line 167 in a5ef0be

'INSERT INTO $1:name ($2:name, $3:name) SELECT MD5(random()::text), MD5(random()::text) FROM generate_series(1,5000)',

I haven't had a chance to see how the mongo adapter handles this to make the change, but I may be able to look later

Hi @cbaker6 , should I open a new issue for the stale user entries? In my case as above, all those entries where created via failed API calls (except the last 2 rows with smaller ID's, which finally succeeded).

That seems to be a general issue, as whenever something goes wrong between inserting the _User row and returning success to the client, the _User entry will become stale and also might be harmful for retries.

[cbaker6]

That I'm no sure because it seems the issues are related. I would think the _User table isn't becoming stale, but the table that are pointing to the objectId of the _User table are stale (or not pointing to anything), but then again, I'm guessing.

My guess is when the change to custom objectId's was added, parseTypeToPostgresType in PostgresStorageAdapter just wasn't updated to match the custom objectId. Fixing that may fix the issue if that's really where the problem is. A test case will probably needed to be added to check this in the future.

@dplewis what do you think?

I agree for the fix on this specific issue, but not the general consistency guarantee or client-side assumption of atomic ReST API calls.

[cbaker6]

That PR should fix it. The only thing is @dplewis will need to confirm that 'char(10)' wasn't there for a particular reason. There was a comment here:

parse-server/spec/rest.spec.js

Line 454 in a5ef0be

objectId: 'qwerty1234', // make it 10 chars to match PG storage

about the objectId needing to match 10 characters for postgres. My guess is when that test case was created the tester was getting the same error as was posted here, or char(10) is required for this field for postgres for a reason I'm unaware of.