Personal access token per organisation or repository - DB structure #383

lenikadali commented 2025年08月25日 21:34:08 +02:00

As part of work towards #1712, I've opened this issue to represent the relationship between a personal access token and the repositor(ies) or organisation(s) it is scoped to.

As noted in this issue, XORM doesn't support foreign keys. My initial thought was to add an AccessTokenID field to the Repository and Organisation models, but maybe that's not the best choice 😇

Feedback/Review welcome 🙏

Edit: This issue is meant to be a backend focused discussion. UI/UX discussion for this is #50

As part of work towards [#1712](https://codeberg.org/forgejo/forgejo/issues/1712), I've opened this issue to represent the relationship between a personal access token and the repositor(ies) or organisation(s) it is scoped to. As noted in [this issue](https://gitea.com/xorm/xorm/issues/73), XORM doesn't support foreign keys. My initial thought was to add an `AccessTokenID` field to the `Repository` and `Organisation` models, but maybe that's not the best choice 😇 Feedback/Review welcome 🙏 Edit: This issue is meant to be a backend focused discussion. UI/UX discussion for this is [#50](https://codeberg.org/forgejo/design/issues/50)

mfenniak commented 2025年08月26日 03:03:27 +02:00

Although xorm doesn't support foreign keys, Forgejo's database is full of columns that function just like foreign keys -- they reference another table's primary key, and they're used in joins. They're not defined in the schemas as a foreign key, and so the database doesn't enforce the relationship, but for designing database schema changes thinking in terms of foreign keys is the correct approach.

The AccessToken table's UID column is effectively a foreign key to the User table, indicating the owner of that access token.

At a first glance, adding an AccessTokenID field to the Repository or Organisation tables doesn't quite seem like the right fit. It would indicate that a repository (or organization) can have only one access token. So, if two users both wanted to create personal access tokens that accessed the same repo... the field would be conflicted upon which user's value to use. It also prevents long-term usage by a single user since access tokens often end up being rotated, where it's necessary to create a second token, change some external integrating system, and remove the original token -- but this schema would prevent two tokens from existing.

Although xorm doesn't support foreign keys, Forgejo's database is full of columns that function just like foreign keys -- they reference another table's primary key, and they're used in joins. They're not defined in the schemas as a foreign key, and so the database doesn't enforce the relationship, but for designing database schema changes thinking in terms of foreign keys is the correct approach. The `AccessToken` table's `UID` column is effectively a foreign key to the `User` table, indicating the owner of that access token. At a first glance, adding an `AccessTokenID` field to the `Repository` or `Organisation` tables doesn't quite seem like the right fit. It would indicate that a repository (or organization) can have only one access token. So, if two users both wanted to create personal access tokens that accessed the same repo... the field would be conflicted upon which user's value to use. It also prevents long-term usage by a single user since access tokens often end up being rotated, where it's necessary to create a second token, change some external integrating system, and remove the original token -- but this schema would prevent two tokens from existing.

sclu1034 commented 2025年08月26日 10:07:09 +02:00

One option would be user_id, repository_id and organisation_id columns in the access_token table.

attachment works the same way, with columns issue_id, column_id and release_id being mutually exclusive.

One option would be `user_id`, `repository_id` and `organisation_id` columns in the `access_token` table. `attachment` works the same way, with columns `issue_id`, `column_id` and `release_id` being mutually exclusive.

mfenniak commented 2025年08月26日 18:39:50 +02:00

It's probably a good idea for this project to start with having a clear idea of what is intended to be accomplished -- perhaps mock screenshots of the updated "personal access token" screen that would be present at the end of this effort -- and then review every idea through whether it would be possible to accomplish that goal.

Adding a repository_id and organisation_id column to the access_token table would limit you to having one (or zero) repos/orgs that an access token can access. Because the feature request in #1712 says "finely tiered" I'm imagining that the intent is to have something like GitHub's fine-grained access control tokens where you can select multiple repositories.

(skipped the idea of adding user_id because I'm not sure what this is supposed to mean, as there's already a UID field which is a reference to the owning user)

It's probably a good idea for this project to start with having a clear idea of what is intended to be accomplished -- perhaps mock screenshots of the updated "personal access token" screen that would be present at the end of this effort -- and then review every idea through whether it would be possible to accomplish that goal. Adding a `repository_id` and `organisation_id` column to the `access_token` table would limit you to having *one* (or zero) repos/orgs that an access token can access. Because the feature request in #1712 says "finely tiered" I'm imagining that the intent is to have something like GitHub's fine-grained access control tokens where you can select multiple repositories. (skipped the idea of adding `user_id` because I'm not sure what this is supposed to mean, as there's already a `UID` field which is a reference to the owning user)

lenikadali commented 2025年08月26日 20:13:19 +02:00

@mfenniak wrote in #383 (comment):

It's probably a good idea for this project to start with having a clear idea of what is intended to be accomplished -- perhaps mock screenshots of the updated "personal access token" screen that would be present at the end of this effort -- and then review every idea through whether it would be possible to accomplish that goal.

Correct. There's a design discussion here for that specifically. I made a mistake in not linking it in the description of this discussion. Apologies 🙃

Adding a repository_id and organisation_id column to the access_token table would limit you to having one (or zero) repos/orgs that an access token can access. Because the feature request in #1712 says "finely tiered" I'm imagining that the intent is to have something like GitHub's fine-grained access control tokens where you can select multiple repositories.

Yes I am assuming the same intent in my considerations as well. Both yours and schu1034's initial responses are good feedback regarding how to structure this. Will consider and update this thread 👍

@mfenniak wrote in https://codeberg.org/forgejo/discussions/issues/383#issuecomment-6723844: > It's probably a good idea for this project to start with having a clear idea of what is intended to be accomplished -- perhaps mock screenshots of the updated "personal access token" screen that would be present at the end of this effort -- and then review every idea through whether it would be possible to accomplish that goal. Correct. There's a [design discussion here](https://codeberg.org/forgejo/design/issues/50) for that specifically. I made a mistake in not linking it in the description of this discussion. Apologies 🙃 > Adding a `repository_id` and `organisation_id` column to the `access_token` table would limit you to having _one_ (or zero) repos/orgs that an access token can access. Because the feature request in #1712 says "finely tiered" I'm imagining that the intent is to have something like GitHub's fine-grained access control tokens where you can select multiple repositories. Yes I am assuming the same intent in my considerations as well. Both yours and schu1034's initial responses are good feedback regarding how to structure this. Will consider and update this thread 👍

dallyger commented 2025年08月29日 13:51:33 +02:00

This sounds like a polymorphic relation would be the best idea here to manage it.
In short: in addition to the owner id (UID), also store the owner type.

But I don't know if this is possible, as I haven't worked with XORM or on the Forgejo project.

Example table structure access_token:

id	owner_id	owner_type	token	scope
1	1	user	...	read:repository
2	1	user	...	public-only
3	1	org	...	read:repository,read:issue

... and to manage the repository access, a second table access_token_repository for the many-to-many relation is needed so that multiple tokens can have access to the same repository and the same token has access to multiple repositories:

access_token_id	repository_id
1	1
1	2
3	2

If there are some scopes, that should differ based on the individual repository (like only adding read:issue for repository A, but not for B), another scope column could be added to the second table too.

This sounds like a polymorphic relation would be the best idea here to manage it. In short: in addition to the owner id (UID), also store the owner type. But I don't know if this is possible, as I haven't worked with XORM or on the Forgejo project. Example table structure `access_token`: | id | owner_id | owner_type | token | scope | |---|---|---|---|---| | 1 | 1 | user | ... | read:repository | | 2 | 1 | user | ... | public-only | | 3 | 1 | org | ... | read:repository,read:issue | ... and to manage the repository access, a second table `access_token_repository` for the many-to-many relation is needed so that multiple tokens can have access to the same repository and the same token has access to multiple repositories: | access_token_id | repository_id | | --- | --- | | 1 | 1 | | 1 | 2 | | 3 | 2 | If there are some scopes, that should differ based on the individual repository (like only adding `read:issue` for repository A, but not for B), another scope column could be added to the second table too.

dallyger commented 2025年08月29日 13:58:43 +02:00

Ok, I think I've misunderstood the problem. After reading it all again, it seems, that this is not about "Organization level access tokens", but about "personal access tokens accessing all resources from an organization". In that case, forget the polymorphic relationship, but add a third table access_token_organization following the same schema as the second table.

Also, the scopes selected-repos and selected-orgs could be added, like the existing all and public-only, to determine which table to load from.

Ok, I think I've misunderstood the problem. After reading it all again, it seems, that this is not about "Organization level access tokens", but about "personal access tokens accessing all resources from an organization". In that case, forget the polymorphic relationship, but add a third table `access_token_organization` following the same schema as the second table. Also, the scopes `selected-repos` and `selected-orgs` could be added, like the existing `all` and `public-only`, to determine which table to load from.

lenikadali commented 2025年09月02日 19:38:47 +02:00

An update here after reviewing the discussion above:

At a first glance, adding an AccessTokenID field to the Repository or Organization tables doesn't quite seem like the right fit. It would indicate that a repository (or organisation) can have only one access token. So, if two users both wanted to create personal access tokens that accessed the same repo... the field would be conflicted upon which user's value to use. It also prevents long-term usage by a single user since access tokens often end up being rotated, where it's necessary to create a second token, change some external integrating system, and remove the original token -- but this schema would prevent two tokens from existing.

That's correct. I hadn't thought of this.

One option would be user_id, repository_id and organisation_id columns in the access_token table.

Yes this would make sense. However because Organization is a User model underneath, I am considering making finely grained tokens for organisations out of scope (no pun intended 😉) for this feature.

So in addition to the existing UID field that references the owning user, I'll add a repository ID to the AccessToken model. The two fields can then be queried in a "unique together" query that determines whether the access token has the appropriate permissions to access the repository in question.

(skipped the idea of adding user_id because I'm not sure what this is supposed to mean, as there's already a UID field which is a reference to the owning user)

I think sclu1034 was referring to the UID field on the AccessToken model.

An update here after reviewing the discussion above: > At a first glance, adding an `AccessTokenID` field to the `Repository` or `Organization` tables doesn't quite seem like the right fit. It would indicate that a repository (or organisation) can have only one access token. So, if two users both wanted to create personal access tokens that accessed the same repo... the field would be conflicted upon which user's value to use. It also prevents long-term usage by a single user since access tokens often end up being rotated, where it's necessary to create a second token, change some external integrating system, and remove the original token -- but this schema would prevent two tokens from existing. That's correct. I hadn't thought of this. > One option would be `user_id`, `repository_id` and `organisation_id` columns in the `access_token` table. Yes this would make sense. However because [`Organization`](https://codeberg.org/forgejo/forgejo/src/branch/forgejo/models/organization/org.go#L79-L80) is a `User` model underneath, I am considering making finely grained tokens for organisations out of scope (no pun intended 😉) for this feature. So in addition to the existing `UID` field that references the owning user, I'll add a repository ID to the `AccessToken` model. The two fields can then be queried in a "unique together" query that determines whether the access token has the appropriate permissions to access the repository in question. > (skipped the idea of adding user_id because I'm not sure what this is supposed to mean, as there's already a UID field which is a reference to the owning user) I think sclu1034 was referring to the `UID` field on the `AccessToken` model.

mfenniak commented 2025年09月02日 19:53:57 +02:00

@lenikadali wrote in #383 (comment):

So in addition to the existing UID field that references the owning user, I'll add a repository ID to the AccessToken model. The two fields can then be queried in a "unique together" query that determines whether the access token has the appropriate permissions to access the repository in question.

Depending on your exact intent here, with this design, you are either:

• Limited to having one repo that an access token can access, or
• Duplicating all the other fields on the access token for each new repository that it can access; eg. Token, TokenHash, TokenSalt...

I don't think that either of those options is great.

The new field you're proposing to add would need to be a NULL-able field in order to maintain compatibility with access tokens that are not specific to a repo. I'd advise that this design has a risk -- any existing code that reads from AccessToken will need to be updated in order to check the new nullable field. If you miss any code location that ends up being relevant, you will introduce a security defect that the restricted access token isn't enforced.

If you're familiar with the term "fail safe", it refers to a system that, if it fails in an unexpected manner, it does so in a way that minimizes harm. I'm worried that your design is the opposite of that. If an error occurs during the implementation such as missing a check on the new field, this will "fail open" by allowing access to resources that aren't expected to be accessed.

I apologies for only being critical in all my comments on this thread. It is much easier to critique a design than to propose one, and I'm challenged by the fact that I'm not personally interested in designing and implementing this feature. 🙂 So, I'll make a high-level proposal to help even out my critique comments: I would suggest exploring the creation of a completely new table, let's call it AccessToken2, and then a join-table AccessToken2Access which links one access token to one-or-more resources it can access (repos, organizations). By default this new table will do nothing, and so every location that access tokens are required to permit something would require development work -- but it will be "fail safe", in that if you miss something you'll cause no security risk. By creating a new table rather than reusing AccessToken, you'll avoid the confusion that could be caused if the join-table is empty -- does that mean the access token can access everything or nothing? -- a very big distinction.

@lenikadali wrote in https://codeberg.org/forgejo/discussions/issues/383#issuecomment-6860329: > So in addition to the existing `UID` field that references the owning user, I'll add a repository ID to the `AccessToken` model. The two fields can then be queried in a "unique together" query that determines whether the access token has the appropriate permissions to access the repository in question. Depending on your exact intent here, with this design, you are either: - Limited to having one repo that an access token can access, or - Duplicating all the *other* fields on the access token for each new repository that it can access; eg. `Token`, `TokenHash`, `TokenSalt`... I don't think that either of those options is great. The new field you're proposing to add would need to be a `NULL`-able field in order to maintain compatibility with access tokens that are not specific to a repo. I'd advise that this design has a risk -- any existing code that reads from `AccessToken` will need to be updated in order to check the new nullable field. If you miss any code location that ends up being relevant, you will introduce a security defect that the restricted access token isn't enforced. If you're familiar with the term "fail safe", it refers to a system that, if it fails in an unexpected manner, it does so in a way that minimizes harm. I'm worried that your design is the opposite of that. If an error occurs during the implementation such as missing a check on the new field, this will "fail open" by allowing access to resources that aren't expected to be accessed. I apologies for only being critical in all my comments on this thread. It is much easier to critique a design than to propose one, and I'm challenged by the fact that I'm not personally interested in designing and implementing this feature. 🙂 So, I'll make a high-level proposal to help even out my critique comments: I would suggest exploring the creation of a completely new table, let's call it `AccessToken2`, and then a join-table `AccessToken2Access` which links one access token to one-or-more resources it can access (repos, organizations). By default this new table will do nothing, and so every location that access tokens are required to permit something would require development work -- but it will be "fail safe", in that if you miss something you'll cause no security risk. By creating a new table rather than reusing `AccessToken`, you'll avoid the confusion that could be caused if the join-table is empty -- does that mean the access token can access **everything** or **nothing**? -- a very big distinction.

lenikadali commented 2025年09月17日 15:19:00 +02:00

@dallyger wrote in #383 (comment):

Ok, I think I've misunderstood the problem. After reading it all again, it seems, that this is not about "Organisation level access tokens", but about "personal access tokens accessing all resources from an organisation".

Not quite 🙂 The issue is about having a personal access token accessing specific resources. Whether these can be under an organisation or not, is treated separately I think.

@mfenniak wrote in #383 (comment):

I apologise for only being critical in all my comments on this thread. It is much easier to critique a design than to propose one, and I'm challenged by the fact that I'm not personally interested in designing and implementing this feature. 🙂

No worries 😇 I would rather go back to the drawing board than commit something that causes unnecessary maintenance burden further down the line.

I'll make a high-level proposal to help even out my critique comments: I would suggest exploring the creation of a completely new table, let's call it AccessToken2, and then a join-table AccessToken2Access which links one access token to one-or-more resources it can access (repos, organizations). By default this new table will do nothing, and so every location that access tokens are required to permit something would require development work -- but it will be "fail safe", in that if you miss something you'll cause no security risk. By creating a new table rather than reusing AccessToken, you'll avoid the confusion that could be caused if the join-table is empty -- does that mean the access token can access everything or nothing? -- a very big distinction.

Will think about this and see how to implement it 👍

@dallyger wrote in https://codeberg.org/forgejo/discussions/issues/383#issuecomment-6799564: > Ok, I think I've misunderstood the problem. After reading it all again, it seems, that this is not about "Organisation level access tokens", but about "personal access tokens accessing all resources from an organisation". Not quite 🙂 The issue is about having a personal access token accessing **_specific_** resources. Whether these can be under an organisation or not, is treated separately I think. @mfenniak wrote in https://codeberg.org/forgejo/discussions/issues/383#issuecomment-6860464: > I apologise for only being critical in all my comments on this thread. It is much easier to critique a design than to propose one, and I'm challenged by the fact that I'm not personally interested in designing and implementing this feature. 🙂 No worries 😇 I would rather go back to the drawing board than commit something that causes unnecessary maintenance burden further down the line. > I'll make a high-level proposal to help even out my critique comments: I would suggest exploring the creation of a completely new table, let's call it `AccessToken2`, and then a join-table `AccessToken2Access` which links one access token to one-or-more resources it can access (repos, organizations). By default this new table will do nothing, and so every location that access tokens are required to permit something would require development work -- but it will be "fail safe", in that if you miss something you'll cause no security risk. By creating a new table rather than reusing `AccessToken`, you'll avoid the confusion that could be caused if the join-table is empty -- does that mean the access token can access **everything** or **nothing**? -- a very big distinction. Will think about this and see how to implement it 👍

oltdaniel commented 2025年10月18日 13:40:44 +02:00

I just wanted to chime in on this discussion (which I haven't looked at before giving this feature a go myself). forgejo/forgejo#1712 (comment)

I think the idea of @dallyger (#383 (comment)) is the most simple way to go about this. It would simply require extending the scopes stored in the AccessToken, without any changes to the schema, which would require refactoring of all functions and api routes involved.

Just adding new scopes with the certain ids or repo or org, would simply require adapting the UI and and verification mechanism. And it would allow any number of repos or orgs. The only thing that ir not present, but I don't see any meaningful application for, is to limit the token to no repo and no org.

I just wanted to chime in on this discussion (which I haven't looked at before giving this feature a go myself). https://codeberg.org/forgejo/forgejo/issues/1712#issuecomment-7787045 I think the idea of @dallyger (https://codeberg.org/forgejo/discussions/issues/383#issuecomment-6799564) is the most simple way to go about this. It would simply require extending the scopes stored in the AccessToken, without any changes to the schema, which would require refactoring of all functions and api routes involved. Just adding new scopes with the certain ids or repo or org, would simply require adapting the UI and and verification mechanism. And it would allow any number of repos or orgs. The only thing that ir not present, but I don't see any meaningful application for, is to limit the token to no repo and no org.

iQ commented 2025年10月20日 10:41:36 +02:00

I hope I'm not to late to the discussion party here, but first I wanted to say, Thank you that this discussions starts and this really important topic on granular access tokens is started.
I understand that this discussion here is "only" about specifying "claims" of the personal(per user) access tokens. This is important and should not be undermined by my comment, BUT:

Another related issue is also, that sooner or later we will need Access-Tokens that are not only owned by a specific user but also are owned by an Organization or by an Repository.
User stories for that are simple. If a user if offboarded from a repository, companies have to ensure that operational processes keep running. This would mean that all relevant repositories are assigned to an organization an not a user (which is possible) but also means, that automation tokens are never owned by a user but by the organisation as well.

To allow more fine-grained and intuitive control I would also argue that also tokens owned/managed by a repository must be possible, but this would essentially be possible by having an dedicated org for this repo.

So, I don't want to solve these additional features here in the discussion. As said, I understand this is only for PATs here, BUT I believe it is important to have these upcoming requirements in Mind, when designing database tables and relationships, to not go construct a dead end which makes it hard to add the functionality, but to plan in the design for that.

Thanks

I hope I'm not to late to the discussion party here, but first I wanted to say, Thank you that this discussions starts and this really important topic on granular access tokens is started. I understand that _this_ discussion here is "only" about specifying "claims" of the personal(per user) access tokens. This is important and should not be undermined by my comment, BUT: Another related issue is also, that sooner or later we will need Access-Tokens that are not only owned by a specific user but also are owned by an Organization or by an Repository. User stories for that are simple. If a user if offboarded from a repository, companies have to ensure that operational processes keep running. This would mean that all relevant repositories are assigned to an organization an not a user (which is possible) but also means, that automation tokens are never owned by a user but by the organisation as well. To allow more fine-grained and intuitive control I would also argue that also tokens owned/managed by a repository must be possible, but this would essentially be possible by having an dedicated org for this repo. So, I don't want to solve these additional features here in the discussion. As said, I understand this is only for **P**ATs here, BUT I believe it is important to have these upcoming requirements in Mind, when designing database tables and relationships, to not go construct a dead end which makes it hard to add the functionality, but to plan in the design for that. Thanks

wetneb commented 2025年10月27日 15:43:34 +01:00

Org-level access tokens have been discussed here: forgejo/forgejo#6142. I agree it's a separate feature request worth being aware of.

Org-level access tokens have been discussed here: https://codeberg.org/forgejo/forgejo/issues/6142. I agree it's a separate feature request worth being aware of.

wetneb commented 2026年01月29日 14:42:58 +01:00

@lenikadali are you still working on implementing @mfenniak's proposed database schema?
If there is any way I can help, I would be glad to lend a hand.

@lenikadali are you still working on implementing @mfenniak's proposed database schema? If there is any way I can help, I would be glad to lend a hand.

lenikadali commented 2026年01月29日 19:57:03 +01:00

@wetneb wrote in #383 (comment):

@lenikadali are you still working on implementing @mfenniak's proposed database schema? If there is any way I can help, I would be glad to lend a hand.

So my plan is to work on the frontend first since that's an area I am not as comfortable with as the backend. Once that's finalised, I will return to the backend. The proposed database schema you mention is the one I intend to use.

@wetneb wrote in https://codeberg.org/forgejo/discussions/issues/383#issuecomment-10248332: > @lenikadali are you still working on implementing @mfenniak's proposed database schema? If there is any way I can help, I would be glad to lend a hand. So my plan is to work on the frontend first since that's an area I am not as comfortable with as the backend. Once that's finalised, I will return to the backend. The proposed database schema you mention is the one I intend to use.

lenikadali commented 2026年02月27日 18:54:48 +01:00

@wetneb wrote in #383 (comment):

@lenikadali are you still working on implementing @mfenniak's proposed database schema? If there is any way I can help, I would be glad to lend a hand.

I am no longer working on this as of now for personal reasons. Fortunately, it looks to be covered by the series of PRs proposed in #11311 but feel free to continue in whatever direction you see fit 🙏

@wetneb wrote in https://codeberg.org/forgejo/discussions/issues/383#issuecomment-10248332: > @lenikadali are you still working on implementing @mfenniak's proposed database schema? If there is any way I can help, I would be glad to lend a hand. I am no longer working on this as of now for personal reasons. Fortunately, it looks to be covered by the series of PRs proposed in [#11311](https://codeberg.org/forgejo/forgejo/pulls/11311) but feel free to continue in whatever direction you see fit 🙏

mfenniak commented 2026年03月23日 16:32:28 +01:00

Closing as per-repository access tokens are now implemented.

Closing as per-repository access tokens are now implemented.