avatar url too long for pipeline 'avatar' column

fernandrone commented 3 weeks ago

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

[X] Read the docs.
[X] Check that there isn't already an issue that reports the same bug to avoid creating a duplicate.
[X] 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 commented 2 weeks ago

It actually is 500 chars long: https://github.com/woodpecker-ci/woodpecker/blob/ac1962bb7b36ee9d8f04cdfd568151c88b90309a/server/model/user.go#L61

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

zc-devs commented 2 weeks ago

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

Confirm on 2.7.1 and next-f87e80381b, Postgres 16.

https://github.com/woodpecker-ci/woodpecker/blob/ac1962bb7b36ee9d8f04cdfd568151c88b90309a/server/model/pipeline.go#L45

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 commented 1 week ago

It actually is 500 chars long:

https://github.com/woodpecker-ci/woodpecker/blob/ac1962bb7b36ee9d8f04cdfd568151c88b90309a/server/model/user.go#L61

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 👍🏻

qwerty287 commented 1 week ago

Ah sorry, I missed that it's the pipeline table. I'll try to fix it asap

woodpecker-ci / woodpecker