2

I am not sure what to make of this weird behavior with generating UUID values. I know uuid_generate_v1() is not as secure as uuid_generate_v4().

We are executing uuid_generate_v1() inside a function to generate a unique Id. Initially when the function was created, it was returning all segments of the uuid populated. I recently needed to create another Azure PostgreSQL instance and was verifying the output and noticed that the last segment of the uuid is now zeros. I re-verified the other Azure PostgreSQL instance we have and they too are now returning the trailing zeroes.

uuid_generate_v4() works fine on all the instances.

I have a version of PostgreSQL 9.6 installed in a docker container on my laptop and it returns all segments populated. 

uuid_id
9945111c-b305-11e9-aec6-977857a8b0e6
be647cc2-7cbd-11e9-8498-e7d5a16a0cec

fa1ee220-bf8e-11e9-8b75-000000000000

I am thinking maybe an update was performed, i am not sure where i would check that though.

Hopefully someone might have come across this situation.

Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154
Ed Mendez
  • 1,510
  • 10
  • 14

1 Answers1

2

Missing MAC address

fa1ee220-bf8e-11e9-8b75-000000000000

That hex string represents the 128 bits of a UUID. Those last twelve hex characters are for the 48-bit node id. For Version 1 UUIDs, node id means the MAC address of the machine generating the UUID.

Most of the other hex characters represent (a) the bits for the value of the current date and time, and the remaining few bits represent (b) a small arbitrary number, and (c) the variant and version of UUID. Those three sources of data are readily available, so no problem there, thus you see the first four segments filled-in.

Likely a bug with Azure

So I would guess you are seeing a bug with regard to Azure failing to report a MAC address for its virtual machines hosting the database.

You are not seeing a problem with Version 4 UUIDs because that version does not use a MAC address. Version 4 uses randomly-generated bits instead, 122 of the 128 bits being random.

You are not seeing a problem on your laptop because your computer (or your Docker container) is properly reporting its MAC address to the Postgres plugin that generates the UUID values.

I suggest you report your experience to the Azure staff as a bug. The Azure folks may not consider it a bug; they might purposely choose to not disclose the MAC address of their server for the security reasons you alluded to in your Question.

I doubt the problem lies with Postgres, the Postgres plugin uuid-ossp, or the well-worn OSSP uuid library contained within the plugin.

Workaround

You might try using a special variation on Version 1 UUID offered by the OSSP library: uuid_generate_v1mc(). This uses a random multicast MAC address instead of the real MAC address of the host computer.

Tip: For testing/debugging, you can simply execute SELECT uuid_generate_v1() ;. No need save to rows and retrieve.

Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154
  • Thank you for the thorough explanation. The reason why I saved to rows was to cover all my basis. From my pg client(Navicat), the uuid_generate_v1() was returning with the trailing zeros, but the data that looked correct was generated inside a function and persisted to a Table. I wanted to make sure that the uuid_generate_v1() performed consistently from a client and from a function. And when i looked again, i noticed that the more recent output from uuid_generate_v1() had the trailing zeros. – Ed Mendez Aug 24 '19 at 02:40