search

pgadmin-org / pgadmin4

pgAdmin is the most popular and feature rich Open Source administration and development platform for PostgreSQL, the most advanced Open Source database in the world.
https://www.pgadmin.org
Other
2.47k stars 648 forks source link

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

Open kartoch opened 3 weeks ago

kartoch commented 3 weeks ago

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

yogeshmahajan-1903 commented 3 weeks ago

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

PGADMIN_CONFIG_CONSOLE_LOG_LEVEL: 10

And share the logs?

kartoch commented 1 week ago

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

khushboovashi commented 1 week ago

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.