avatar url too long for pipeline 'avatar' column

[fernandrone]

Component

server

Describe the bug

For some unknown reason (still trying to reproduce) one of our users had their github avatar url generated with a token. For example, a common GitHub avatar url might be:

https://avatars.githubusercontent.com/u/7269710?v=4

While for this user it was showing as:

https://private-avatars.githubusercontent.com/u/2729053?jwt="token"&v=4

This url had a total of 312 characters. The avatar displays correctly in GitHub, but the full URL with the jwt token is sent by the Github webhook to Woodpecker, which tries to store it in the database. The users table uses character varying(500) for avatar but the pipelines table uses character varying(255).

\d+ pipelines;
                                                                   Table "public.pipelines"
        Column        |          Type          | Collation | Nullable |                    Default                     | Storage  | Stats target | Description
----------------------+------------------------+-----------+----------+------------------------------------------------+----------+--------------+-------------
 ...
 avatar               | character varying(255) |           |          |                                                | extended |              |

Then we get:

pq: value too long for type character varying(255)

Steps to reproduce

Unfortunately we're not sure how to generate a GitHub avatar url with a jwt again. Presumably the user simply changed their avatar normally, by going to their user settings page at https://github.com/ and uploading a new image. It could be a GitHub update or bug, but I couldn't find any information about it.

Expected behavior

Wether we can reproduce or not, it seems reasonable to use character varying(500) for the avatar field in both tables.

System Info

2.7.0, Kubernetes

Additional context

No response

Validations

• Read the docs.
• Check that there isn't already an issue that reports the same bug to avoid creating a duplicate.
• Checked that the bug isn't fixed in the next version already [https://woodpecker-ci.org/faq#which-version-of-woodpecker-should-i-use]

[qwerty287]

It actually is 500 chars long:

woodpecker/server/model/user.go

Line 61 in ac1962b

Avatar string `json:"avatar_url" xorm:" varchar(500) 'avatar'"`

Maybe something went wrong in your setup? Can you manually alter the data type?

[zc-devs]

but the pipelines table uses character varying(255)
\d+ pipelines;
avatar | character varying(255)

Confirm on 2.7.1 and next-f87e80381b, Postgres 16.

woodpecker/server/model/pipeline.go

Line 45 in ac1962b

Avatar string `json:"author_avatar" xorm:"avatar"`

---

woodpecker=# \d users
                                           Table "public.users"
     Column      |          Type          | Collation | Nullable |                Default
-----------------+------------------------+-----------+----------+----------------------------------------
 id              | bigint                 |           | not null | nextval('users_user_id_seq'::regclass)
 forge_id        | bigint                 |           |          |
 forge_remote_id | character varying(255) |           |          |
 login           | character varying(255) |           |          |
 token           | text                   |           |          |
 secret          | text                   |           |          |
 expiry          | bigint                 |           |          |
 email           | character varying(500) |           |          |
 avatar          | character varying(500) |           |          |
 admin           | boolean                |           |          |
 hash            | character varying(500) |           |          |
 org_id          | bigint                 |           |          |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "UQE_users_user_hash" UNIQUE, btree (hash)
    "UQE_users_user_login" UNIQUE, btree (login)

woodpecker=# \d pipelines
                                               Table "public.pipelines"
        Column        |          Type          | Collation | Nullable |                    Default
----------------------+------------------------+-----------+----------+------------------------------------------------
 id                   | bigint                 |           | not null | nextval('pipelines_pipeline_id_seq'::regclass)
 repo_id              | bigint                 |           |          |
 number               | bigint                 |           |          |
 author               | character varying(255) |           |          |
 parent               | bigint                 |           |          |
 event                | character varying(255) |           |          |
 status               | character varying(255) |           |          |
 errors               | json                   |           |          |
 created              | bigint                 |           |          |
 updated              | bigint                 |           | not null | 0
 started              | bigint                 |           |          |
 finished             | bigint                 |           |          |
 deploy               | character varying(255) |           |          |
 deploy_task          | character varying(255) |           |          |
 commit               | character varying(255) |           |          |
 branch               | character varying(255) |           |          |
 ref                  | character varying(255) |           |          |
 refspec              | character varying(255) |           |          |
 title                | character varying(255) |           |          |
 message              | text                   |           |          |
 timestamp            | bigint                 |           |          |
 sender               | character varying(255) |           |          |
 avatar               | character varying(255) |           |          |
 email                | character varying(255) |           |          |
 forge_url            | character varying(255) |           |          |
 reviewer             | character varying(255) |           |          |
 reviewed             | bigint                 |           |          |
 changed_files        | text                   |           |          |
 additional_variables | json                   |           |          |
 pr_labels            | json                   |           |          |
 is_prerelease        | boolean                |           |          |
Indexes:
    "pipelines_pkey" PRIMARY KEY, btree (id)
    "IDX_pipelines_pipeline_author" btree (author)
    "IDX_pipelines_pipeline_repo_id" btree (repo_id)
    "IDX_pipelines_pipeline_status" btree (status)
    "UQE_pipelines_s" UNIQUE, btree (repo_id, number)

[fernandrone]

It actually is 500 chars long:

woodpecker/server/model/user.go

Line 61 in ac1962b

Avatar string `json:"avatar_url" xorm:" varchar(500) 'avatar'"`

Maybe something went wrong in your setup? Can you manually alter the data type?

That's the users table, but it's 255 chars on the pipeline table, which also stores the avatar url (maybe there's also an opportunity to dedup the data here...)

By the way we manually altered the pipeline table avatar column from varchar 255 to varchar 500 and it fixed the problem on our end 👍🏻