Re: pgadmin and keycloak

Hi,

We are using pgadmin in Openshift, I modified the configuration and
the whole logs are

postfix/postlog: starting the Postfix mail system
[2024-01-02 12:48:41 +0000] [1] [INFO] Starting gunicorn 20.1.0
[2024-01-02 12:48:41 +0000] [1] [INFO] Listening at: http://[::]:80 (1)
[2024-01-02 12:48:41 +0000] [1] [INFO] Using worker: gthread
[2024-01-02 12:48:41 +0000] [88] [INFO] Booting worker with pid: 88
2024-01-02 12:48:44,519: INFO pgadmin:
########################################################
2024-01-02 12:48:44,519: INFO pgadmin: Starting pgAdmin 4 v8.1...
2024-01-02 12:48:44,519: INFO pgadmin:
########################################################
2024-01-02 12:48:44,519: DEBUG pgadmin: Python syspath: ['/pgadmin4',
'/venv/bin', '/pgadmin4', '/usr/lib/python311.zip',
'/usr/lib/python3.11', '/usr/lib/python3.11/lib-dynload',
'/venv/lib/python3.11/site-packages',
'/usr/lib/python3.11/site-packages']
2024-01-02 12:48:45,728: INFO pgadmin: Registering blueprint module:
<AboutModule 'about'>
2024-01-02 12:48:45,730: INFO pgadmin: Registering blueprint module:
<AuthenticateModule 'authenticate'>
2024-01-02 12:48:45,730: INFO pgadmin: Registering blueprint module:
<BrowserModule 'browser'>
2024-01-02 12:48:47,164: INFO pgadmin: Registering blueprint module:
<DashboardModule 'dashboard'>
2024-01-02 12:48:47,177: INFO pgadmin: Registering blueprint module:
<DashboardModule 'dashboard'>
2024-01-02 12:48:47,177: INFO pgadmin: Registering blueprint module:
<HelpModule 'help'>
2024-01-02 12:48:47,177: INFO pgadmin: Registering blueprint module:
<MiscModule 'misc'>
2024-01-02 12:48:48,476: INFO pgadmin: Registering blueprint module:
<PreferencesModule 'preferences'>
2024-01-02 12:48:48,479: INFO pgadmin: Registering blueprint module:
<PgAdminModule 'redirects'>
2024-01-02 12:48:48,480: INFO pgadmin: Registering blueprint module:
<SettingsModule 'settings'>
2024-01-02 12:48:48,484: INFO pgadmin: Registering blueprint module:
<ToolsModule 'tools'>
2024-01-02 12:48:49,386: DEBUG pgadmin: Config server mode: True
2024-01-02 12:48:49,387: DEBUG pgadmin: Not running under the desktop
runtime, port: 5050
::ffff:10.128.2.1 - - [02/Jan/2024:12:49:34 +0000] "GET /misc/ping
HTTP/1.1" 200 4 "-" "kube-probe/1.25"
::ffff:10.128.2.1 - - [02/Jan/2024:12:49:34 +0000] "GET /misc/ping
HTTP/1.1" 200 4 "-" "kube-probe/1.25"
10.248.227.9 - - [02/Jan/2024:12:49:35 +0000] "GET / HTTP/1.1" 302 217
"-" "Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:120.0) Gecko/20100101
Firefox/120.0"
10.248.227.9 - - [02/Jan/2024:12:49:35 +0000] "GET /login?next=%2F
HTTP/1.1" 200 6524 "-" "Mozilla/5.0 (X11; Ubuntu; Linux x86_64;
rv:120.0) Gecko/20100101 Firefox/120.0"
10.248.227.9 - - [02/Jan/2024:12:49:35 +0000] "GET
/browser/js/endpoints.js?ver=80100 HTTP/1.1" 200 18491
"https://pgadmin4.apps.xxx.xxxx.xxx/login?next=%2F" "Mozilla/5.0 (X11;
Ubuntu; Linux x86_64; rv:120.0) Gecko/20100101 Firefox/120.0"
10.248.227.9 - - [02/Jan/2024:12:49:36 +0000] "GET
/tools/translations.js?ver=80100 HTTP/1.1" 200 321
"https://pgadmin4.apps.xxxx.xxxxx.xx/login?next=%2F" "Mozilla/5.0
(X11; Ubuntu; Linux x86_64; rv:120.0) Gecko/20100101 Firefox/120.0"
2024-01-02 12:49:37,685: DEBUG pgadmin: Authentication initiated via
source: oauth2
2024-01-02 12:49:37,686: DEBUG pgadmin: Authentication initiated via
source: oauth2 is failed.
10.248.227.9 - - [02/Jan/2024:12:49:37 +0000] "POST
/authenticate/login HTTP/1.1" 302 819
"https://pgadmin4.apps.xxxxx.xxxx.xxx/login?next=%2F" "Mozilla/5.0
(X11; Ubuntu; Linux x86_64; rv:120.0) Gecko/20100101 Firefox/120.0"
2024-01-02 12:49:47,867: ERROR pgadmin: Expecting value: line 1 column
1 (char 0)
Traceback (most recent call last):
  File "/venv/lib/python3.11/site-packages/requests/models.py", line
971, in json
    return complexjson.loads(self.text, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/lib/python3.11/json/__init__.py", line 346, in loads
    return _default_decoder.decode(s)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/lib/python3.11/json/decoder.py", line 337, in decode
    obj, end = self.raw_decode(s, idx=_w(s, 0).end())
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/lib/python3.11/json/decoder.py", line 355, in raw_decode
    raise JSONDecodeError("Expecting value", s, err.value) from None
json.decoder.JSONDecodeError: Expecting value: line 1 column 1 (char 0)

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/venv/lib/python3.11/site-packages/flask/app.py", line 1484,
in full_dispatch_request
    rv = self.dispatch_request()
         ^^^^^^^^^^^^^^^^^^^^^^^
  File "/venv/lib/python3.11/site-packages/flask/app.py", line 1469,
in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/pgadmin4/pgadmin/authenticate/oauth2.py", line 56, in oauth_authorize
    status, msg = auth_obj.login()
                  ^^^^^^^^^^^^^^^^
  File "/pgadmin4/pgadmin/authenticate/__init__.py", line 301, in login
    status, msg = self.source.login(self.form)
                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/pgadmin4/pgadmin/authenticate/oauth2.py", line 126, in login
    profile = self.get_user_profile()
              ^^^^^^^^^^^^^^^^^^^^^^^
  File "/pgadmin4/pgadmin/authenticate/oauth2.py", line 201, in get_user_profile
    self.oauth2_current_client].authorize_access_token()
                                ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/venv/lib/python3.11/site-packages/authlib/integrations/flask_client/apps.py",
line 101, in authorize_access_token
    token = self.fetch_access_token(**params, **kwargs)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/venv/lib/python3.11/site-packages/authlib/integrations/base_client/sync_app.py",
line 342, in fetch_access_token
    token = client.fetch_token(token_endpoint, **params)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/venv/lib/python3.11/site-packages/authlib/oauth2/client.py",
line 207, in fetch_token
    return self._fetch_token(
           ^^^^^^^^^^^^^^^^^^
  File "/venv/lib/python3.11/site-packages/authlib/oauth2/client.py",
line 364, in _fetch_token
    return self.parse_response_token(resp)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/venv/lib/python3.11/site-packages/authlib/oauth2/client.py",
line 338, in parse_response_token
    token = resp.json()
            ^^^^^^^^^^^
  File "/venv/lib/python3.11/site-packages/requests/models.py", line
975, in json
    raise RequestsJSONDecodeError(e.msg, e.doc, e.pos)
requests.exceptions.JSONDecodeError: Expecting value: line 1 column 1 (char 0)
10.248.227.9 - - [02/Jan/2024:12:49:47 +0000] "GET

/oauth2/authorize?state=WiEiZw2b0KaGY8T9NHtCjQc6p5QyDy&session_state=fba96987-00e4-44d7-800a-c8192885a193&iss=http%3A%2F%2Fkeycloak.xxxx.xxx%3A8080%2Frealms%2Fiam&code=e75a1d2a-1a7d-4fca-8c00-e84540366b0e.fba96987-00e4-44d7-800a-c8192885a193.ec389ead-d683-4f45-a63a-d93f0814efaf
HTTP/1.1" 500 104 "-" "Mozilla/5.0 (X11; Ubuntu; Linux x86_64;
rv:120.0) Gecko/20100101 Firefox/120.0"

Related to https and http. At this moment, we are using https in
pgadmin and http in keycloak, only for testing.

As you can see we don't have more details. Can you share with us your
configuration?

Best regards,
Jose

On Tue, Jan 2, 2024 at 1:31 PM Yogesh Mahajan
<yogesh.mahajan@enterprisedb.com> wrote:
>
> Hi,
>
> I am not able to reproduce the issue. I have tried with SSL and Non-SSL enabled Keycloak.
> Can you please add lines below in config_local.py -
>
> import logging
> FILE_LOG_LEVEL = logging.DEBUG
> CONSOLE_LOG_LEVEL = logging.DEBUG
>
> And get the pgadmin logs?
>
> Also can you please share OS details and the pgadmin version?
>
> Thanks,
> Yogesh Mahajan
> EnterpriseDB
>
>
> On Tue, Jan 2, 2024 at 12:49 PM Jose M Barreiro <jmbarreiro@gmail.com> wrote:
>>
>> Hi Yogesh,
>>
>> Thank you for your support. I have several problems with pgadmin and
>> keycloak. For example,
>>
>> if I use OAUTH2_SERVER_METADATA_URL, I received this error ""403
>> Client Error: Forbidden for url:
>> http://keycloak.xxx.xxxx:8080/realms/iam/.well-known/openid-configuration""
>>
>> I I used your example, I received this error "
>>
>> {"success":0,"errormsg":"'OAUTH2_API_BASE_URL'","info":"","result":null,"data":null}
>>
>> If I use this configuration
>> >
>> > OAUTH2_CONFIG = [
>> >   {
>> >       'OAUTH2_NAME': 'keycloak',
>> >       'OAUTH2_DISPLAY_NAME': 'KEYCLOAK',
>> >       'OAUTH2_CLIENT_ID': 'pgadmin',
>> >       'OAUTH2_CLIENT_SECRET': 'xxxx',
>> >       'OAUTH2_TOKEN_URL': 'http://keycloak.xxx.xxx:8080/realms/iam/protocol/openid-connect/token',
>> >       'OAUTH2_AUTHORIZATION_URL': 'http://keycloak.xxx.xxx:8080/realms/iam/protocol/openid-connect/auth',
>> >       'OAUTH2_USERINFO_ENDPOINT': 'http://keycloak.xxx.xxx:8080/realms/iam/protocol/openid-connect/userinfo',
>> >       'OAUTH2_API_BASE_URL': 'http://keycloak.xxx.xxx:8080/realms/iam',
>> >       'OAUTH2_ICON': 'fa-google',
>> >       'OAUTH2_BUTTON_COLOR': '#0000ff',
>> >       'OAUTH2_SCOPE': 'openid',
>> >       'OAUTH2_SSL_CERT_VERIFICATION': 'False',
>> >       'OAUTH2_ADDITIONAL_CLAIMS': {
>> >              'groups': ["administrators"],
>> >       }
>> >   }
>> > ]
>>
>> I receive this error
>>
>> {"success":0,"errormsg":"Expecting value: line 1 column 1 (char
>> 0)","info":"","result":null,"data":null}
>>
>> In the logs you can see
>> >
>> > 10.248.227.10 - - [02/Jan/2024:07:16:47 +0000] "POST /authenticate/login HTTP/1.1" 302 791
"https://pgadmin4.apps.xxxx.xxxx.dplt/login?next=%2F""Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:120.0) Gecko/20100101
Firefox/120.0"
>> >
>> > 2024-01-02 07:16:48,400: ERROR pgadmin: Expecting value: line 1 column 1 (char 0)
>> > Traceback (most recent call last):
>> >   File "/venv/lib/python3.11/site-packages/requests/models.py", line 971, in json
>> >     return complexjson.loads(self.text, **kwargs)
>> >            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
>> >   File "/usr/lib/python3.11/json/__init__.py", line 346, in loads
>> >     return _default_decoder.decode(s)
>> >            ^^^^^^^^^^^^^^^^^^^^^^^^^^
>> >   File "/usr/lib/python3.11/json/decoder.py", line 337, in decode
>> >     obj, end = self.raw_decode(s, idx=_w(s, 0).end())
>> >                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
>> >   File "/usr/lib/python3.11/json/decoder.py", line 355, in raw_decode
>> >     raise JSONDecodeError("Expecting value", s, err.value) from None
>> > json.decoder.JSONDecodeError: Expecting value: line 1 column 1 (char 0)
>> > During handling of the above exception, another exception occurred:
>>
>>
>> Traceback (most recent call last):
>>   File "/venv/lib/python3.11/site-packages/flask/app.py", line 1484,
>> in full_dispatch_request
>>     rv = self.dispatch_request()
>>          ^^^^^^^^^^^^^^^^^^^^^^^
>>   File "/venv/lib/python3.11/site-packages/flask/app.py", line 1469,
>> in dispatch_request
>>     return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
>>            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
>>   File "/pgadmin4/pgadmin/authenticate/oauth2.py", line 56, in oauth_authorize
>>     status, msg = auth_obj.login()
>>                   ^^^^^^^^^^^^^^^^
>>   File "/pgadmin4/pgadmin/authenticate/__init__.py", line 301, in login
>>     status, msg = self.source.login(self.form)
>>                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
>>   File "/pgadmin4/pgadmin/authenticate/oauth2.py", line 126, in login
>>     profile = self.get_user_profile()
>>               ^^^^^^^^^^^^^^^^^^^^^^^
>>   File "/pgadmin4/pgadmin/authenticate/oauth2.py", line 201, in get_user_profile
>>     self.oauth2_current_client].authorize_access_token()
>>                                 ^^^^^^^^^^^^^^^^^^^^^^^^
>>   File "/venv/lib/python3.11/site-packages/authlib/integrations/flask_client/apps.py",
>> line 101, in authorize_access_token
>>     token = self.fetch_access_token(**params, **kwargs)
>>             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
>>   File "/venv/lib/python3.11/site-packages/authlib/integrations/base_client/sync_app.py",
>> line 342, in fetch_access_token
>>     token = client.fetch_token(token_endpoint, **params)
>>             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
>>   File "/venv/lib/python3.11/site-packages/authlib/oauth2/client.py",
>> line 207, in fetch_token
>>     return self._fetch_token(
>>            ^^^^^^^^^^^^^^^^^^
>>   File "/venv/lib/python3.11/site-packages/authlib/oauth2/client.py",
>> line 364, in _fetch_token
>>     return self.parse_response_token(resp)
>>            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
>>   File "/venv/lib/python3.11/site-packages/authlib/oauth2/client.py",
>> line 338, in parse_response_token
>>     token = resp.json()
>>             ^^^^^^^^^^^
>>   File "/venv/lib/python3.11/site-packages/requests/models.py", line
>> 975, in json
>>     raise RequestsJSONDecodeError(e.msg, e.doc, e.pos)
>> requests.exceptions.JSONDecodeError: Expecting value: line 1 column 1 (char 0)
>> 10.248.227.10 - - [02/Jan/2024:07:16:48 +0000] "GET
>>
/oauth2/authorize?state=zhsCc9Nspt61zaWitYqfT61JoHiHer&session_state=4d4bdc0d-3901-4d13-af89-d1646a3115b3&iss=http%3A%2F%2Fkeycloak.xxxx.xxxx%3A8080%2Frealms%2Fiam&code=dd98dd4a-bd20-49aa-861d-39f5d5af1795.4d4bdc0d-3901-4d13-af89-d1646a3115b3.ec389ead-d683-4f45-a63a-d93f0814efaf
>> HTTP/1.1" 500 104 "-" "Mozilla/5.0 (X11; Ubuntu; Linux x86_64;
>> rv:120.0) Gecko/20100101 Firefox/120.0"
>>
>> Thanks for your support,
>>
>> Best regards,
>> Jose
>>
>>
>> On Tue, Jan 2, 2024 at 5:41 AM Yogesh Mahajan
>> <yogesh.mahajan@enterprisedb.com> wrote:
>> >
>> > Hi Jose,
>> >
>> > pgAdmin 4 supports keycloak for authentication. Is there any error/difficulty while configuration?
>> > Here is typical configuration for keycloak provider -
>> >
>> > AUTHENTICATION_SOURCES = ['internal','oauth2']
>> > OAUTH2_CONFIG = [
>> >
>> >  {  'OAUTH2_NAME': 'keycloak',
>> >     'OAUTH2_DISPLAY_NAME': 'Login with Keycloak',
>> >     'OAUTH2_CLIENT_ID': '<keycloak client id>',
>> >     'OAUTH2_CLIENT_SECRET': '<client secret>',
>> >     'OAUTH2_TOKEN_URL': 'https://<keycloak server ip:port>/realms/<realm_name>/protocol/openid-connect/token',
>> >     'OAUTH2_AUTHORIZATION_URL': 'https://<keycloak server
ip:port>/realms/<realm_name>/protocol/openid-connect/auth',
>> >
>> >     'OAUTH2_API_BASE_URL': None,
>> >     'OAUTH2_USERINFO_ENDPOINT': 'https://<keycloak server
ip:port>/realms/<realm_name>/protocol/openid-connect/userinfo',
>> >     'OAUTH2_SCOPE': 'openid',
>> >     'OAUTH2_USERNAME_CLAIM': None,
>> >     'OAUTH2_ICON': None,
>> >     'OAUTH2_BUTTON_COLOR': None,
>> >     'OAUTH2_SERVER_METADATA_URL': 'https://<keycloak server
ip:port>//realms/<realm_name>/.well-known/openid-configuration',
>> >     'OAUTH2_SSL_CERT_VERIFICATION': False
>> > }]
>> >
>> >
>> >
>> > Thanks,
>> > Yogesh Mahajan
>> > EnterpriseDB
>> >
>> >
>> > On Mon, Jan 1, 2024 at 10:05 PM Jose M Barreiro <jmbarreiro@gmail.com> wrote:
>> >>
>> >> Hi,
>> >>
>> >> First of all, Happy New Year!!!!
>> >>
>> >> I have a problem with pgadmin and keycloak. We need to change our IDP, actually we are using okta and pgadmin is
workingfine with it. 
>> >>
>> >> We need to configure pgadmin to use keycloak but it's not possible to configure pgadmin to work with keycloak.
>> >>
>> >> Can you help us to understand the reason?
>> >>
>> >> Best regards,
>> >> Jose
>> >>