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.56k stars 659 forks source link

OpenID with Keycloak for OAuth2 #7193

Closed butterfly-thomas closed 9 months ago

butterfly-thomas commented 9 months ago

Describe the bug

When logout from PgAdmin, the session is still open on Keycloak, hence unable to log as another user until the current session is closed.

To Reproduce

Steps to reproduce the behavior:

In the config.py:

AUTHENTICATION_SOURCES = ['oauth2','internal']

OAUTH2_CONFIG = [
    {
        'OAUTH2_NAME': 'keycloak',
        'OAUTH2_DISPLAY_NAME': 'keycloak',
        'OAUTH2_CLIENT_ID': 'pgadmin_client',
        'OAUTH2_CLIENT_SECRET': '*********************************',
        'OAUTH2_TOKEN_URL': 'https://***************/realms/*****/protocol/openid-connect/token',
        'OAUTH2_AUTHORIZATION_URL': 'https://***************/realms/*****/protocol/openid-connect/auth',
        'OAUTH2_API_BASE_URL': 'https://***************/realms/*****/protocol/openid-connect/',
        'OAUTH2_SERVER_METADATA_URL': 'https://***************/realms/*****/.well-known/openid-configuration',
        'OAUTH2_USERINFO_ENDPOINT': 'userinfo',
        'OAUTH2_SCOPE': 'openid email profile',
        'OAUTH2_USERNAME_CLAIM': 'preferred_username',
        'OAUTH2_ICON': 'fa-github'
    }
]

Expected behavior

Closing session on Keycloak when logout from PgAdmin

Temporary fix

Changed the redirect URI in pgadmin\authenticate\oauth2.py

@blueprint.route('/logout', endpoint="logout",
                     methods=['GET', 'POST'])
    @pgCSRFProtect.exempt
    def oauth_logout():
        if not current_user.is_authenticated:
            return redirect(get_safe_post_logout_redirect())

        id_token_hint = session['oauth2_token']['id_token']

        for key in list(session.keys()):
            session.pop(key)

        logout_user()

        return redirect(f'https://***************/realms/*/protocol/openid-connect/logout?post_logout_redirect_uri=http%3A%2F%2Flocalhost%3A5050%2Flogin&id_token_hint={id_token_hint}')

Screenshots

keycloak_session keycloak_session_lock

Request

Possibility to pass the redirect URI with parameters to ensure closing the external user provider session when logout.

materpillar commented 9 months ago

Hi there,

I believe this is not only a problem for keycloak as I am currently setting up OAuth2 with AWS Cognito as a IdP and I have the same problem.

Cheers

florian-sabonchi commented 9 months ago

Hello, someone working on it?

khushboovashi commented 9 months ago

Hello, someone Wirkung on it?

If you want, you can work on it.

florian-sabonchi commented 9 months ago

can be closed?

adityatoshniwal commented 9 months ago

can be closed?

It will be closed after one round of testing on the installers.

pravesh-sharma commented 9 months ago

Issue fixed. Verified on snapshot image for docker.

adussarps commented 4 months ago

Hello there, I'm running on the same issue with a Keycloack SSO Setup. The code seems to break when clicking on logout

i.e:

2024-07-22 18:54:40,481: ERROR  pgadmin:        'oauth2_token'
Traceback (most recent call last):
  File "/venv/lib/python3.12/site-packages/flask/app.py", line 880, in full_dispatch_request
    rv = self.dispatch_request()
         ^^^^^^^^^^^^^^^^^^^^^^^
  File "/venv/lib/python3.12/site-packages/flask/app.py", line 865, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)  # type: ignore[no-any-return]
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/pgadmin4/pgadmin/authenticate/oauth2.py", line 83, in oauth_logout
    id_token = session['oauth2_token'].get('id_token')
               ~~~~~~~^^^^^^^^^^^^^^^^
KeyError: 'oauth2_token'

Not too sure what's happening there for now; but just in case, can someone share the config of it's Keycloack client ? I may have a misconfiguration on the logout side.

adityatoshniwal commented 4 months ago

Hello there, I'm running on the same issue with a Keycloack SSO Setup. The code seems to break when clicking on logout

i.e:

2024-07-22 18:54:40,481: ERROR  pgadmin:        'oauth2_token'
Traceback (most recent call last):
  File "/venv/lib/python3.12/site-packages/flask/app.py", line 880, in full_dispatch_request
    rv = self.dispatch_request()
         ^^^^^^^^^^^^^^^^^^^^^^^
  File "/venv/lib/python3.12/site-packages/flask/app.py", line 865, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)  # type: ignore[no-any-return]
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/pgadmin4/pgadmin/authenticate/oauth2.py", line 83, in oauth_logout
    id_token = session['oauth2_token'].get('id_token')
               ~~~~~~~^^^^^^^^^^^^^^^^
KeyError: 'oauth2_token'

Not too sure what's happening there for now; but just in case, can someone share the config of it's Keycloack client ? I may have a misconfiguration on the logout side.

What is your pgAdmin version?

adussarps commented 4 months ago

I'm running dpage/pgadmin4:8 which in my case correspond to the latest version 8.9.

Thanks for having a look !

bguijt commented 2 months ago

I have the same issue (pgAdmin 4 v8.11 with KeyCloak 25.0.5) in Docker image docker.io/dpage/pgadmin4:8.11:

2024-09-10 23:07:21,890: ERROR    pgadmin:    'oauth2_token'
Traceback (most recent call last):
  File "/venv/lib/python3.12/site-packages/flask/app.py", line 880, in full_dispatch_request
    rv = self.dispatch_request()
         ^^^^^^^^^^^^^^^^^^^^^^^
  File "/venv/lib/python3.12/site-packages/flask/app.py", line 865, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)  # type: ignore[no-any-return]
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/pgadmin4/pgadmin/authenticate/oauth2.py", line 83, in oauth_logout
    id_token = session['oauth2_token'].get('id_token')
               ~~~~~~~^^^^^^^^^^^^^^^^
KeyError: 'oauth2_token'

Upon logging out, the browser redirects to http://pgadmin.default.svc.cluster.local/oauth2/logout?next=/browser/ and shows:

{"success":0,"errormsg":"'oauth2_token'","info":"","result":null,"data":null}

I have configured pgAdmin with:

'OAUTH2_LOGOUT_URL': '{{ .Values.keycloak.baseUrl }}/realms/{{ .Values.keycloak.realm }}/protocol/openid-connect/logout?post_logout_redirect_uri={redirect_uri}&id_token_hint={id_token}'

just as documented here: https://www.pgadmin.org/docs/pgadmin4/development/config_py.html#config-py.

If I leave this config option out, the logout doesn't fail for the user (the browser redirects to the login page), but in Keycloak the session is still open.

How can I fix this?

RLutsch commented 3 weeks ago

didn't even get that far I got {"success":0,"errormsg":"Object of type Undefined is not JSON serializable","info":"","result":null,"data":null} here is my config 

---
apiVersion: v1
kind: ConfigMap
metadata:
  name: pgadmin4-config
data:
  config_local.py: |-
    import os

    MASTER_PASSWORD_REQUIRED = True
    AUTHENTICATION_SOURCES = ['oauth2', 'internal']
    OAUTH2_AUTO_CREATE_USER = True
    OAUTH2_CONFIG = [
      {
          'OAUTH2_NAME': 'keycloak',
          'OAUTH2_DISPLAY_NAME': 'keycloak',
          'OAUTH2_CLIENT_ID': 'pgadmin_client',
          'OAUTH2_CLIENT_SECRET': '******',
          'OAUTH2_TOKEN_URL': 'https://******/realms/******/protocol/openid-connect/token',
          'OAUTH2_AUTHORIZATION_URL': 'https://******/realms/******/protocol/openid-connect/auth',
          'OAUTH2_API_BASE_URL': 'https://******/realms/******/protocol/openid-connect/',
          'OAUTH2_SERVER_METADATA_URL': 'https://l******/realms/******/.well-known/openid-configuration',
          'OAUTH2_USERINFO_ENDPOINT': 'userinfo',
          'OAUTH2_SCOPE': 'openid email profile',
          'OAUTH2_USERNAME_CLAIM': 'preferred_username',  
          'OAUTH2_ICON': 'fa-github'
      }
    ]
yogeshmahajan-1903 commented 3 weeks ago

@RLutsch This issue is fixed in #7965 and will be released with 8.13. Can you please add parameter OAUTH2_BUTTON_COLOR in OAUTH2_CONFIG as WA?

RLutsch commented 3 weeks ago

that worked thanks here is if anyone else is intereseted

---
apiVersion: v1
kind: ConfigMap
metadata:
  name: pgadmin4-config
data:
  config_local.py: |-
    import os

    MASTER_PASSWORD_REQUIRED = True
    AUTHENTICATION_SOURCES = ['oauth2', 'internal']
    OAUTH2_AUTO_CREATE_USER = True
    OAUTH2_CONFIG = [
      {
          'OAUTH2_NAME': 'keycloak',
          'OAUTH2_DISPLAY_NAME': 'keycloak',
          'OAUTH2_CLIENT_ID': 'pgadmin_client',
          'OAUTH2_CLIENT_SECRET': '******',
          'OAUTH2_TOKEN_URL': 'https://******/realms/******/protocol/openid-connect/token',
          'OAUTH2_AUTHORIZATION_URL': 'https://******/realms/******/protocol/openid-connect/auth',
          'OAUTH2_API_BASE_URL': 'https://******/realms/******/protocol/openid-connect/',
          'OAUTH2_SERVER_METADATA_URL': 'https://l******/realms/******/.well-known/openid-configuration',
          'OAUTH2_USERINFO_ENDPOINT': 'userinfo',
          'OAUTH2_SCOPE': 'openid email profile',
          'OAUTH2_USERNAME_CLAIM': 'preferred_username',  
          'OAUTH2_ICON': 'fa-github', 
          'OAUTH2_BUTTON_COLOR': 'blue'
      }
    ]