pgadmin4 icon indicating copy to clipboard operation
pgadmin4 copied to clipboard
verified publisher icon pgadmin-org

Cannot authenticate with Github Oauth2: SQL error NOT NULL constraint failed: user.username

Open kartoch opened this issue 1 year ago • 1 comments

Describe the bug

Cannot log using oauth2 with github, a SQL error is printed on the screen. As far as I understand, the user returned by github is empty for privacy reason.

On our infrastructure, grafana and argo-cd are working with github Oauth2.

pgadmin4 version: 8.11 (using docker image docker.io/dpage/pgadmin4:8.11)

To Reproduce

Steps to reproduce the behavior:

  1. Setup oauth2 for github:
OAUTH2_CONFIG = [
    {
        'OAUTH2_NAME': 'github',
        'OAUTH2_DISPLAY_NAME': 'Github',
        'OAUTH2_CLIENT_ID': 'XXX',
        'OAUTH2_CLIENT_SECRET': 'XXX',
        'OAUTH2_TOKEN_URL': 'https://github.com/login/oauth/access_token',
        'OAUTH2_AUTHORIZATION_URL': 'https://github.com/login/oauth/authorize',
        'OAUTH2_SERVER_METADATA_URL': None,
        'OAUTH2_API_BASE_URL': 'https://api.github.com/',
        'OAUTH2_USERINFO_ENDPOINT': 'user',
        'OAUTH2_SCOPE': 'profile email user',
        'OAUTH2_USERNAME_CLAIM': 'email',
        'OAUTH2_ICON': 'fa-github',
        'OAUTH2_BUTTON_COLOR': None,
        'OAUTH2_ADDITIONAL_CLAIMS': None,
        'OAUTH2_SSL_CERT_VERIFICATION': True,
        'OAUTH2_LOGOUT_URL': None
    }
]

  1. Restart pgadmin

  2. Authenticate using the github

Expected behavior

The authentication succeed and the user can access pgadmin interface.

Error message

(sqlite3.IntegrityError) NOT NULL constraint failed: user.username
[SQL: INSERT INTO user (email, username, password, active, confirmed_at, masterpass_check, auth_source, fs_uniquifier, login_attempts, locked) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]
[parameters: (None, None, None, 1, None, None, 'oauth2', 'bd0faa8e9c924897b03a8b152c064aa1', 0, 0)]
(Background on this error at: https://sqlalche.me/e/20/gkpj)

Screenshots

image

kartoch avatar Sep 20 '24 08:09 kartoch

@kartoch Can you please run pgadmin with debug mode by setting environment variable below -

PGADMIN_CONFIG_CONSOLE_LOG_LEVEL: 10

And share the logs?

yogeshmahajan-1903 avatar Sep 20 '24 14:09 yogeshmahajan-1903

The logs after restarting the container with PGADMIN_CONFIG_CONSOLE_LOG_LEVEL: 10 and trying to connect via github:

postfix/postlog: starting the Postfix mail system
[2024-10-07 08:24:03 +0000] [1] [INFO] Starting gunicorn 22.0.0
[2024-10-07 08:24:03 +0000] [1] [INFO] Listening at: http://[::]:80 (1)
[2024-10-07 08:24:03 +0000] [1] [INFO] Using worker: gthread
[2024-10-07 08:24:03 +0000] [90] [INFO] Booting worker with pid: 90
2024-10-07 08:24:07,415: INFO	pgadmin:	########################################################
2024-10-07 08:24:07,416: INFO	pgadmin:	Starting pgAdmin 4 v8.11...
2024-10-07 08:24:07,416: INFO	pgadmin:	########################################################
2024-10-07 08:24:07,416: DEBUG	pgadmin:	Python syspath: ['/pgadmin4', '/venv/bin', '/pgadmin4', '/usr/lib/python312.zip', '/usr/lib/python3.12', '/usr/lib/python3.12/lib-dynload', '/venv/lib/python3.12/site-packages', '/usr/lib/python3.12/site-packages', '/venv/lib/python3.12/site-packages/setuptools/_vendor']
2024-10-07 08:24:08,503: INFO	pgadmin:	Registering blueprint module: <AboutModule 'about'>
2024-10-07 08:24:08,504: INFO	pgadmin:	Registering blueprint module: <AuthenticateModule 'authenticate'>
2024-10-07 08:24:08,536: INFO	pgadmin:	Registering blueprint module: <BrowserModule 'browser'>
2024-10-07 08:24:10,128: INFO	pgadmin:	Registering blueprint module: <DashboardModule 'dashboard'>
2024-10-07 08:24:10,147: INFO	pgadmin:	Registering blueprint module: <HelpModule 'help'>
2024-10-07 08:24:10,147: INFO	pgadmin:	Registering blueprint module: <MiscModule 'misc'>
2024-10-07 08:24:11,336: INFO	pgadmin:	Registering blueprint module: <PreferencesModule 'preferences'>
2024-10-07 08:24:11,339: INFO	pgadmin:	Registering blueprint module: <PgAdminModule 'redirects'>
2024-10-07 08:24:11,340: INFO	pgadmin:	Registering blueprint module: <SettingsModule 'settings'>
2024-10-07 08:24:11,343: INFO	pgadmin:	Registering blueprint module: <ToolsModule 'tools'>
2024-10-07 08:24:12,155: DEBUG	pgadmin:	Config server mode: True
2024-10-07 08:24:12,155: DEBUG	pgadmin:	Not running under the desktop runtime, port: 5050
::ffff:217.182.210.76 - - [07/Oct/2024:08:24:12 +0000] "GET /misc/ping HTTP/1.1" 200 4 "-" "kube-probe/1.30"
::ffff:217.182.210.76 - - [07/Oct/2024:08:24:27 +0000] "GET /misc/ping HTTP/1.1" 200 4 "-" "kube-probe/1.30"
::ffff:217.182.210.76 - - [07/Oct/2024:08:24:57 +0000] "GET /misc/ping HTTP/1.1" 200 4 "-" "kube-probe/1.30"
::ffff:217.182.210.76 - - [07/Oct/2024:08:24:57 +0000] "GET /misc/ping HTTP/1.1" 200 4 "-" "kube-probe/1.30"
10.203.18.248 - - [07/Oct/2024:08:25:07 +0000] "GET /login?next=/ HTTP/1.1" 200 6700 "-" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/129.0.0.0 Safari/537.36"
10.203.18.248 - - [07/Oct/2024:08:25:07 +0000] "GET /favicon.ico?ver=81100 HTTP/1.1" 302 245 "https://pgadmin.dev.REDACTED.com/login?next=/" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/129.0.0.0 Safari/537.36"
10.203.18.248 - - [07/Oct/2024:08:25:07 +0000] "GET /browser/js/endpoints.js?ver=81100 HTTP/1.1" 200 155205 "https://pgadmin.
//login?next=/" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/129.0.0.0 Safari/537.36"
10.203.18.248 - - [07/Oct/2024:08:25:07 +0000] "GET /tools/translations.js?ver=81100 HTTP/1.1" 200 321 "https://pgadmin.dev.REDACTED.com/login?next=/" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/129.0.0.0 Safari/537.36"
2024-10-07 08:25:09,840: DEBUG	pgadmin:	Authentication initiated via source: oauth2
10.203.18.248 - - [07/Oct/2024:08:25:09 +0000] "POST /authenticate/login HTTP/1.1" 302 689 "https://pgadmin.dev.REDACTED.com/login?next=/" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/129.0.0.0 Safari/537.36"
2024-10-07 08:25:09,841: DEBUG	pgadmin:	Authentication initiated via source: oauth2 is failed.
2024-10-07 08:25:10,667: DEBUG	pgadmin:	profile claims: {'login': 'kartoch', 'id': 160925, 'node_id': 'MDQ6VXNlcjE2MDkyNQ==', 'avatar_url': 'https://avatars.githubusercontent.com/u/160925?v=4', 'gravatar_id': '', 'url': 'https://api.github.com/users/kartoch', 'html_url': 'https://github.com/kartoch', 'followers_url': 'https://api.github.com/users/kartoch/followers', 'following_url': 'https://api.github.com/users/kartoch/following{/other_user}', 'gists_url': 'https://api.github.com/users/kartoch/gists{/gist_id}', 'starred_url': 'https://api.github.com/users/kartoch/starred{/owner}{/repo}', 'subscriptions_url': 'https://api.github.com/users/kartoch/subscriptions', 'organizations_url': 'https://api.github.com/users/kartoch/orgs', 'repos_url': 'https://api.github.com/users/kartoch/repos', 'events_url': 'https://api.github.com/users/kartoch/events{/privacy}', 'received_events_url': 'https://api.github.com/users/kartoch/received_events', 'type': 'User', 'site_admin': False, 'name': 'Julien Cartigny', 'company': 'Freelance', 'blog': '', 'location': 'Everywhere', 'email': None, 'hireable': True, 'bio': None, 'twitter_username': 'kartoch', 'notification_email': None, 'public_repos': 11, 'public_gists': 10, 'followers': 35, 'following': 3, 'created_at': '2009-12-02T15:49:36Z', 'updated_at': '2024-09-19T16:51:02Z', 'private_gists': 0, 'total_private_repos': 7, 'owned_private_repos': 7, 'disk_usage': 8673, 'collaborators': 3, 'two_factor_authentication': True, 'plan': {'name': 'free', 'space': 976562499, 'collaborators': 0, 'private_repos': 10000}}
2024-10-07 08:25:10,667: DEBUG	pgadmin:	reason: Additional claim config is None, no check to do.
2024-10-07 08:25:10,667: DEBUG	pgadmin:	idtoken claims: {}
2024-10-07 08:25:10,667: DEBUG	pgadmin:	reason: Additional claim config is None, no check to do.
2024-10-07 08:25:10,669: INFO	pgadmin:	Creating user None with email None from auth source OAUTH2.
10.203.18.248 - - [07/Oct/2024:08:25:10 +0000] "GET /oauth2/authorize?code=4ea262df7d8b2d02d02e&state=sDn7z80YD2mmWqxq4hOAdXwh0OG9Oq HTTP/1.1" 302 205 "https://pgadmin.dev.REDACTED.com/" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/129.0.0.0 Safari/537.36"
10.203.18.248 - - [07/Oct/2024:08:25:10 +0000] "GET /browser/ HTTP/1.1" 302 229 "https://pgadmin.dev.REDACTED.com/" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/129.0.0.0 Safari/537.36"
10.203.18.248 - - [07/Oct/2024:08:25:10 +0000] "GET /login?next=/browser/ HTTP/1.1" 200 7203 "https://pgadmin.dev.REDACTED.com/" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/129.0.0.0 Safari/537.36"
10.203.18.248 - - [07/Oct/2024:08:25:10 +0000] "GET /tools/translations.js?ver=81100 HTTP/1.1" 200 321 "https://pgadmin.dev.REDACTED.com/login?next=/browser/" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/129.0.0.0 Safari/537.36"
10.203.18.248 - - [07/Oct/2024:08:25:10 +0000] "GET /browser/js/endpoints.js?ver=81100 HTTP/1.1" 200 155205 "https://pgadmin.dev.REDACTED.com/login?next=/browser/" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/129.0.0.0 Safari/537.36"

The profile claims contains 'email': None

kartoch avatar Oct 07 '24 08:10 kartoch

The profile claim email is none, so it's failing. pgAdmin only validates the profile, it is sent by OAuth2 provider. Please check your OAuth2 provide settings.

khushboovashi avatar Oct 08 '24 05:10 khushboovashi

No response from the author. Closing the issue.

pravesh-sharma avatar Oct 28 '24 11:10 pravesh-sharma

Hey, I stumbled upon on the same issue. If the Github user has enabled email protection, an additional request to github should be used.

hauserkristof avatar Jan 22 '25 20:01 hauserkristof