That‘s in my experience also the best approach. I wrote an article a few days ago about the exact thing: An integer auto incrementing PK with an UUID you use externally:
1. They are still sorted in increasing timestamp order (at millisecond granularity), so they should have good DB index characteristics.
2. At the same time, they contain 62 bits of randomness, would should pretty much eliminate IDOR attacks if there is a bug elsewhere that isn't doing proper access checks. Not good enough for secure tokens, but just good defense against access permission check bugs.
That is, you should basically get the best of both worlds: ordered keys with enough randomness to make ID-increment attacks infeasible.
https://www.postgresql.org/docs/current/sql-createtype.html
> (This restriction is made because an erroneous type definition could confuse or even crash the server.)
Uhh, what..
> Generally these functions have to be coded in C or another low-level language.
Oh, okay. That sounds like this not really doable with many hosted Postgres services out there.
> Except for WITHOUT ROWID tables, all rows within SQLite tables have a 64-bit signed integer key that uniquely identifies the row within its table. This integer is usually called the "rowid". The rowid value can be accessed using one of the special case-independent names "rowid", "oid", or "_rowid_" in place of a column name. If a table contains a user defined column named "rowid", "oid" or "_rowid_", then that name always refers the explicitly declared column and cannot be used to retrieve the integer rowid value.
> [...] If an INSERT statement attempts to insert a NULL value into a rowid or integer primary key column, the system chooses an integer value to use as the rowid automatically. A detailed description of how this is done is provided separately. [2]