Investigate user.db fields and their purpose

backuprepo commented

2025-12-21 16:17:46 +01:00

Owner

Originally created by @sparky8251 on GitHub (Jan 6, 2019).

This is part of #42, potentially #302, and maybe even #422 .

So far, this is what I've found:

The users.db has 3 fields in the LocalUsersv2 table.

Id, which contains an INTEGER representation of your user Id. This is the master key.
guid, which contains a GUID that upon initial investigation is derived from the Id
data, which contains a BLOB that really should be more individual fields.

The data blob itself contains a lot of fields. Extracted from my own db and prettyfied, it looks like this:

{
    "UsesIdForConfigurationPath":true,
    "Password":"passwordhash",
    "ImageInfos":[{"Path":"/config/config/users/e23fcdb75728411d94fe4e498ad00ea3/poster.jpg",
        "Type":"Primary",
        "DateModified":"2018-12-28T21:24:57.2894648Z",
        "Width":0,
        "Height":0}],
    "Name":"sparky",
    "LastLoginDate":"2019-01-03T14:26:15.1391116Z",
    "LastActivityDate":"2019-01-05T21:52:51.1598704Z",
    "InternalId":1,
    "RemoteTrailers":[],
    "IsHD":false,
    "IsShortcut":false,
    "Width":0,
    "Height":0,
    "ExtraIds":[],
    "SupportsExternalTransfer":false
}

Of all these fields so far, the following warrant some degree of further investigation:

guid How is it generated and what is it actually used for? Can it be removed? If it cannot, can we convert it to the sqlite type INTEGER?
UsesIdForConfigurationPath What does it toggle if set to false? Can it be removed?
ImageInfos Why is it a vector? If it can be converted to a single element, we should be able to break out more of the blob into its own fields.
Path We should find how it generates the random bit and fix the FQPNs so the database is more portable.
Type Is this why ImageInfos a vector? Can you have second or third photos attached to your user account? Can it be removed?
DateModified Why do we need to know when an image was set? Can it be removed?
Width I have an image set but this is still 0. Why? Can it be removed?
Height I have an image set but this is still 0. Why? Can it be removed?
InternalId is this always set to be identical to the Id field? Can it be removed?
RemoteTrailers What is this? Why is it attached to user accounts? Why is it a vector? Can it be removed?
IsHD What is this? Why is it attached to user accounts? Can it be removed?
IsShortcut What is this? Why is it attached to user accounts? Can it be removed?
Width Why is this repeating and set to 0 even though I have an icon set? Can it be removed?
Height Why is this repeating and set to 0 even though I have an icon set? Can it be removed?
ExtraIds What is this? Why is it a vector? Can it be removed?
SupportsExternalTransfer What is this? Can it be removed?

As we track down where and why these things exist, we should look at creating a LocalUsersv3 table format that doesn't rely on a single large blob. Will make future migrations to new formats much easier. When this eventually leads to a new table format, we should make a jellyfin.db file to store the new table and eventually roll the other dbs into it.

Originally created by @sparky8251 on GitHub (Jan 6, 2019). This is part of #42, potentially #302, and maybe even #422 . So far, this is what I've found: The `users.db` has 3 fields in the `LocalUsersv2` table. `Id`, which contains an `INTEGER` representation of your user Id. This is the master key. `guid`, which contains a `GUID` that upon initial investigation is derived from the `Id` `data`, which contains a `BLOB` that really should be more individual fields. The data blob itself contains a lot of fields. Extracted from my own db and prettyfied, it looks like this: ``` { "UsesIdForConfigurationPath":true, "Password":"passwordhash", "ImageInfos":[{"Path":"/config/config/users/e23fcdb75728411d94fe4e498ad00ea3/poster.jpg", "Type":"Primary", "DateModified":"2018-12-28T21:24:57.2894648Z", "Width":0, "Height":0}], "Name":"sparky", "LastLoginDate":"2019-01-03T14:26:15.1391116Z", "LastActivityDate":"2019-01-05T21:52:51.1598704Z", "InternalId":1, "RemoteTrailers":[], "IsHD":false, "IsShortcut":false, "Width":0, "Height":0, "ExtraIds":[], "SupportsExternalTransfer":false } ``` Of all these fields so far, the following warrant some degree of further investigation: * [x] `guid` How is it generated and what is it actually used for? Can it be removed? If it cannot, can we convert it to the sqlite type `INTEGER`? * [ ] `UsesIdForConfigurationPath` What does it toggle if set to false? Can it be removed? * [ ] `ImageInfos` Why is it a vector? If it can be converted to a single element, we should be able to break out more of the blob into its own fields. * [ ] `Path` We should find how it generates the random bit and fix the FQPNs so the database is more portable. * [ ] `Type` Is this why `ImageInfos` a vector? Can you have second or third photos attached to your user account? Can it be removed? * [ ] `DateModified` Why do we need to know when an image was set? Can it be removed? * [ ] `Width` I have an image set but this is still 0. Why? Can it be removed? * [ ] `Height` I have an image set but this is still 0. Why? Can it be removed? * [ ] `InternalId` is this always set to be identical to the `Id` field? Can it be removed? * [ ] `RemoteTrailers` What is this? Why is it attached to user accounts? Why is it a vector? Can it be removed? * [ ] `IsHD` What is this? Why is it attached to user accounts? Can it be removed? * [ ] `IsShortcut` What is this? Why is it attached to user accounts? Can it be removed? * [ ] `Width` Why is this repeating and set to 0 even though I have an icon set? Can it be removed? * [ ] `Height` Why is this repeating and set to 0 even though I have an icon set? Can it be removed? * [ ] `ExtraIds` What is this? Why is it a vector? Can it be removed? * [ ] `SupportsExternalTransfer` What is this? Can it be removed? As we track down where and why these things exist, we should look at creating a `LocalUsersv3` table format that doesn't rely on a single large blob. Will make future migrations to new formats much easier. When this eventually leads to a new table format, we should make a `jellyfin.db` file to store the new table and eventually roll the other dbs into it.

backuprepo

2025-12-21 16:17:46 +01:00

closed this issue
added the
backend

investigation

stale
labels

backuprepo commented

2025-12-21 16:17:51 +01:00

Author

Owner

@ploughpuff commented on GitHub (Jan 10, 2019):

A little digging into the use of GUID in users table.

To quote Microsoft (docs here):

"GUID is represented as a series of lowercase hexadecimal digits in groups of 8, 4, 4, 4, and 12 digits and separated by hyphens. An example of a return value is "382c74c3-721d-4f34-80e5-57657b6cbc27"."

A GUID is created for each new user but this 8-4-4-4-12 representation of it is never seen/used.

Instead, the GUID stored in 'guid' element of the table is the 16-byte encoding of it (GUID.ToByteArray).

Other uses are the hyphen-less representation (GUID.ToString("N"), see docs). An example can be seen when editing your profile. Here it's called userId:
http://localhost:8096/web/index.html#!/myprofile.html?userId=61f49567708f4c1b9905b4bd70799f8e

This hyphen-less GUID is also used to create a folder under /etc/jelly/users (-configdir/users), where the users profile picture, profile xml and config files are stored.

guid, which contains a GUID that upon initial investigation is derived from the Id

The GUID is not based on Id. My understanding being it uses date/timestamp to generate a unique number. Create a user, delete user, and create again and you'll get a different GUID.

guid How is it generated and what is it actually used for? Can it be removed? If it cannot, can we convert it to the sqlite type INTEGER?

Yes, we could remove it. The first field in the table 'Id' is unique and identifies the user sufficiently. The same could be argued for the user name as that's unique too.. My only question before removal would be what benefits does a 32 letter identifier offer us?

@ploughpuff commented on GitHub (Jan 10, 2019): A little digging into the use of GUID in users table. To quote Microsoft (docs [here](https://docs.microsoft.com/en-us/dotnet/api/system.guid.tostring?view=netframework-4.7.2)): >"GUID is represented as a series of lowercase hexadecimal digits in groups of 8, 4, 4, 4, and 12 digits and separated by hyphens. An example of a return value is "382c74c3-721d-4f34-80e5-57657b6cbc27"." A GUID is created for each new user but this 8-4-4-4-12 representation of it is never seen/used. Instead, the GUID stored in 'guid' element of the table is the 16-byte encoding of it (GUID.ToByteArray). Other uses are the hyphen-less representation (GUID.ToString("N"), see [docs](https://docs.microsoft.com/en-us/dotnet/api/system.guid.tostring?view=netframework-4.7.2#System_Guid_ToString_System_String_System_IFormatProvider_)). An example can be seen when editing your profile. Here it's called userId: http://localhost:8096/web/index.html#!/myprofile.html?userId=61f49567708f4c1b9905b4bd70799f8e This hyphen-less GUID is also used to create a folder under /etc/jelly/users (-configdir/users), where the users profile picture, profile xml and config files are stored. >guid, which contains a GUID that upon initial investigation is derived from the Id The GUID is not based on Id. My understanding being it uses date/timestamp to generate a unique number. Create a user, delete user, and create again and you'll get a different GUID. >guid How is it generated and what is it actually used for? Can it be removed? If it cannot, can we convert it to the sqlite type INTEGER? Yes, we could remove it. The first field in the table 'Id' is unique and identifies the user sufficiently. The same could be argued for the user name as that's unique too.. My only question before removal would be what benefits does a 32 letter identifier offer us?

backuprepo commented

2025-12-21 16:17:54 +01:00

Author

Owner

@sparky8251 commented on GitHub (Jan 10, 2019):

Thank you @ploughpuff ! These kinds of write ups right here is what I was hoping for if I opened these database investigation issues.

I see the the GUID is used as a string in other DBs tables (like user prefs and library), so at the very least removing it would touch more than this one DB. Might be why it was made in the first place.

If it really isnt required for the other DBs, could the psuedorandomness of the GUID be a useful security layer or is it just needless obscuration?

@sparky8251 commented on GitHub (Jan 10, 2019): Thank you @ploughpuff ! These kinds of write ups right here is what I was hoping for if I opened these database investigation issues. I see the the GUID is used as a string in other DBs tables (like user prefs and library), so at the very least removing it would touch more than this one DB. Might be why it was made in the first place. If it really isnt required for the other DBs, could the psuedorandomness of the GUID be a useful security layer or is it just needless obscuration?

backuprepo commented

2025-12-21 16:17:56 +01:00

Author

Owner

@sparky8251 commented on GitHub (Jan 10, 2019):

One thing of note here, the GUID key and the user GUID you specifically mentioned are different in my case.

Opening the db up, the raw hex for the GUID key is b7cd3fe228571d4194fe4e498ad00ea3 while the guid used in the user specific pages and image path is e23fcdb75728411d94fe4e498ad00ea3.

Is the e23 derived from the "master" GUID key? If so, why? Can we just remove one and keep the other? Etc.

@sparky8251 commented on GitHub (Jan 10, 2019): One thing of note here, the GUID key and the user GUID you specifically mentioned are different in my case. Opening the db up, the raw hex for the GUID key is `b7cd3fe228571d4194fe4e498ad00ea3` while the guid used in the user specific pages and image path is `e23fcdb75728411d94fe4e498ad00ea3`. Is the `e23` derived from the "master" GUID key? If so, why? Can we just remove one and keep the other? Etc.

backuprepo commented

2025-12-21 16:17:58 +01:00

Author

Owner

@ploughpuff commented on GitHub (Jan 10, 2019):

They are the same :)
https://dotnetfiddle.net/2MYiW4

The GUID "b7cd3fe228571d4194fe4e498ad00ea3" is the 16 element byte array:
https://docs.microsoft.com/en-us/dotnet/api/system.guid.tobytearray?view=netframework-4.7.2

@ploughpuff commented on GitHub (Jan 10, 2019): They are the same :) https://dotnetfiddle.net/2MYiW4 The GUID "b7cd3fe228571d4194fe4e498ad00ea3" is the 16 element byte array: https://docs.microsoft.com/en-us/dotnet/api/system.guid.tobytearray?view=netframework-4.7.2

backuprepo commented

2025-12-21 16:18:00 +01:00

Author

Owner

@sparky8251 commented on GitHub (Jan 10, 2019):

Good lord that is confusing... Thanks for looking into it! Still got a long ways to go...

@sparky8251 commented on GitHub (Jan 10, 2019): Good lord that is confusing... Thanks for looking into it! Still got a long ways to go...

backuprepo commented

2025-12-21 16:18:01 +01:00

Author

Owner

@LogicalPhallacy commented on GitHub (Feb 1, 2019):

Replacing GUIDs with integers would likely result in a perf increase (MS SQL Server and some of the other bigger SQL implementations have a GUID typing, but the way jellyfin handles them int will almost always be cleaner). It would require a bit of db conversion type things, it offers no security benefit due to the way auth is implemented.

I think there is a valid argument for keeping the blob format for a users table as it greatly insulates against injection, but the work to port to entity framework would do so as well.

@LogicalPhallacy commented on GitHub (Feb 1, 2019): Replacing GUIDs with integers would likely result in a perf increase (MS SQL Server and some of the other bigger SQL implementations have a GUID typing, but the way jellyfin handles them int will almost always be cleaner). It would require a bit of db conversion type things, it offers no security benefit due to the way auth is implemented. I think there is a valid argument for keeping the blob format for a users table as it greatly insulates against injection, but the work to port to entity framework would do so as well.

backuprepo commented

2025-12-21 16:18:03 +01:00

Author

Owner

@cvium commented on GitHub (Feb 1, 2019):

Doesn't it already use some (probably bastardized) version of prepared statements? So I think we're OK wrt. injection?

@cvium commented on GitHub (Feb 1, 2019): Doesn't it already use some (probably bastardized) version of prepared statements? So I think we're OK wrt. injection?

backuprepo commented

2025-12-21 16:18:05 +01:00

Author

Owner

@sparky8251 commented on GitHub (Feb 1, 2019):

Well, I'd say if the case for JSON blobs in the database is "Well, what about injection?" we should seek to solve the injection issues higher up the stack.

@sparky8251 commented on GitHub (Feb 1, 2019): Well, I'd say if the case for JSON blobs in the database is "Well, what about injection?" we should seek to solve the injection issues higher up the stack.

backuprepo commented

2025-12-21 16:18:08 +01:00

Author

Owner

@stale[bot] commented on GitHub (Jul 29, 2019):

Issues go stale after 60d of inactivity. Mark the issue as fresh by adding a comment or commit. Stale issues close after an additional 7d of inactivity. If this issue is safe to close now please do so. If you have any questions you can reach us on Matrix or Social Media.

@stale[bot] commented on GitHub (Jul 29, 2019): Issues go stale after 60d of inactivity. Mark the issue as fresh by adding a comment or commit. Stale issues close after an additional 7d of inactivity. If this issue is safe to close now please do so. If you have any questions you can reach us on [Matrix or Social Media](https://jellyfin.readthedocs.io/en/latest/getting-help/).

Rows
Columns

Investigate user.db fields and their purpose #210