Json log format #7138

[sabonchi]

Dear Sir or Madam

I have submitted a pull request on GitHub for the following issue:
https://github.com/pgadmin-org/pgadmin4/issues/7138.

Kind regards,

Florian Sabonchi

Issue 7193

[Florian Sabonchi]

Dear Sir or Madam

is anyone working on this topic?

https://github.com/pgadmin-org/pgadmin4/issues/7193

can I work on it?

Yours sincerely, Florian Sabonchi

OAuth2/OpenID-Connect

[Florian Sabonchi]

Hello what do you think about the implementation of OAuth2

OAuth error when logging in

[Florian Sabonchi]

Hello I would like to integrate OAuth in PG-Admin. Unfortunately I have 
the error that I am redirected back to the home page. Unfortunately I 
could not find this error, what surprises me is that 
current_user.is_authenticated is set to True. For this reason I just 
wanted to ask maybe someone knows what the problem is. You can find my 
source code here:


https://github.com/FlorianJSa/pgadmin4/blob/OAuth2/web/pgadmin/authenticate/__init__.py


I would be very happy if someone could help me with this problem. 
Because I unfortunately have no idea what this could be for an issue

OAUTH2 implementation

[Florian Sabonchi]

Hello in this patch I have implemented oauth2

diff --git a/DEPENDENCIES b/DEPENDENCIES
index 6b4d9cfcf..8efe007a1 100644
--- a/DEPENDENCIES
+++ b/DEPENDENCIES
@@ -51,6 +51,7 @@ sshtunnel0.4.0
 ldap32.9  LGPL v3  https://github.com/cannatag/ldap3
 Flask-BabelEx0.9.4BSD  http://github.com/mrjoes/flask-babelex
 gssapi   1.6.12   LICENSE.txt  https://github.com/pythongssapi/python-gssapi
+authlib  0.15.3   BSD  https://github.com/lepture/authlib
 
 28 dependencies listed.
 
diff --git a/docs/en_US/release_notes.rst b/docs/en_US/release_notes.rst
index 015c3850b..25570b536 100644
--- a/docs/en_US/release_notes.rst
+++ b/docs/en_US/release_notes.rst
@@ -11,6 +11,7 @@ notes for it.
 .. toctree::
:maxdepth: 1
 
+   release_notes_5_2
release_notes_5_1
release_notes_5_0
release_notes_4_30
diff --git a/docs/en_US/release_notes_5_2.rst b/docs/en_US/release_notes_5_2.rst
new file mode 100644
index 0..d9162831a
--- /dev/null
+++ b/docs/en_US/release_notes_5_2.rst
@@ -0,0 +1,22 @@
+
+Version 5.1
+
+
+Release date: 2021-04-22
+
+This release contains a number of bug fixes and new features since the release of pgAdmin4 5.1.
+
+New features
+
+
+
+Housekeeping
+
+
+| `Issue #5319 `_ -  Improve code coverage and API test cases for Server module.
+
+Bug fixes
+*
+
+| `Issue #6293 `_ -  Fixed an issue where the procedure creation is failed when providing the Volatility option.
+| `Issue #6356 `_ -  Mark the Apache HTTPD config file as such in the web DEB and RPM packages.
diff --git a/pkg/debian/build.sh b/pkg/debian/build.sh
index e07d9dd1f..505296c49 100755
--- a/pkg/debian/build.sh
+++ b/pkg/debian/build.sh
@@ -72,6 +72,10 @@ fakeroot dpkg-deb --build "${DESKTOPROOT}" "${DISTROOT}/${APP_NAME}-desktop_${AP
 echo "Creating the web package..."
 mkdir "${WEBROOT}/DEBIAN"
 
+cat << EOF > "${WEBROOT}/DEBIAN/conffiles"
+/etc/apache2/conf-available/pgadmin4.conf
+EOF
+
 cat << EOF > "${WEBROOT}/DEBIAN/control"
 Package: ${APP_NAME}-web
 Version: ${APP_LONG_VERSION}
diff --git a/pkg/redhat/build.sh b/pkg/redhat/build.sh
index 09a058cdf..c21fc9006 100755
--- a/pkg/redhat/build.sh
+++ b/pkg/redhat/build.sh
@@ -159,7 +159,7 @@ cp -rfa %{pga_build_root}/web/* \${RPM_BUILD_ROOT}
 
 %files
 /usr/pgadmin4/bin/*
-/etc/httpd/conf.d/*
+%config(noreplace) /etc/httpd/conf.d/*
 EOF
 
 mkdir -p "${WEBROOT}/etc/httpd/conf.d"
diff --git a/web/pgadmin/__init__.py b/web/pgadmin/__init__.py
index a73335371..e69619201 100644
--- a/web/pgadmin/__init__.py
+++ b/web/pgadmin/__init__.py
@@ -44,11 +44,13 @@ from pgadmin.utils.csrf import pgCSRFProtect
 from pgadmin import authenticate
 from pgadmin.utils.security_headers import SecurityHeaders
 from pgadmin.utils.constants import KERBEROS
+from pgadmin.utils.constants import OAUTH
 
 # Explicitly set the mime-types so that a corrupted windows registry will not
 # affect pgAdmin 4 to be load properly. This will avoid the issues that may
 # occur due to security fix of X_CONTENT_TYPE_OPTIONS = "nosniff".
 import mimetypes
+
 mimetypes.add_type('application/javascript', '.js')
 mimetypes.add_type('text/css', '.css')
 
@@ -697,19 +699,20 @@ def create_app(app_name=None):
 )
 abort(401)
 login_user(user)
-elif config.SERVER_MODE and\
-app.PGADMIN_EXTERNAL_AUTH_SOURCE ==\
-KERBEROS and \
+elif config.SERVER_MODE and \
 not current_user.is_authenticated and \
 request.endpoint in ('redirects.index', 'security.login'):
-return authenticate.login()
-
+if app.PGADMIN_EXTERNAL_AUTH_SOURCE == KERBEROS:
+return authenticate.login()
+elif app.PGADMIN_EXTERNAL_AUTH_SOURCE == OAUTH and len(config.AUTHENTICATION_SOURCES) == 1:
+# forwarding to OAuth if only one authentication is enabled
+return authenticate.oauth_login()
 # if the server is restarted the in memory key will be lost
 # but the user session may still be active. Logout the user
 # to get the key again when login
 if config.SERVER_MODE and current_user.is_authenticated and \
 app.PGADMIN_EXTERNAL_AUTH_SOURCE != \
-KERBEROS and \
+KERBEROS and OAUTH not in config.AUTHENTICATION_SOURCES and\
 current_app.keyManager.get() is Non

oauth2 patch

[Florian Sabonchi]

Hello in this patch I have implemented OAuth2. Unfortunately I wasn't 
sure how the test should look like, because I couldn't find anything in 
the developer documentation, so I decided not to write one for now.


The configuration has to look like this here is an example for github:

OAUTH2_NAME = 'github'
OAUTH2_CLIENT_ID = 'secret'
OAUTH2_CLIENT_SECRET = 'secret'
OAUTH2_TOKEN_URL = 'https://github.com/login/oauth/access_token'
OAUTH2_AUTHORIZATION_URL = 'https://github.com/login/oauth/authorize'
OAUTH2_API_BASE_URL = 'https://api.github.com/'
OAUTH2_USERINFO_ENDPOINT = 'https://api.github.com/user'
OAUTH_ENDPOINT_NAME = 'user'


>From edb9278a9ce81e92aaf8778d3d069a18e3d6e2d3 Mon Sep 17 00:00:00 2001
From: Florian Sabonchi 
Date: Tue, 23 Mar 2021 20:07:12 +0100
Subject: [PATCH] Draft for oauth2 added

---
 DEPENDENCIES  |  1 +
 docs/en_US/oauth2.rst | 36 
 requirements.txt  |  2 +
 web/config.py | 20 +++-
 web/pgadmin/__init__.py   | 19 ++--
 web/pgadmin/authenticate/__init__.py  | 56 +--
 web/pgadmin/authenticate/oauth.py | 92 +++
 web/pgadmin/browser/__init__.py   | 22 +++--
 web/pgadmin/browser/tests/test_oauth_login.py |  0
 web/pgadmin/messages.pot  |  7 ++
 web/pgadmin/static/scss/_pgadmin.style.scss   |  3 +
 .../templates/security/login_user.html|  9 +-
 web/pgadmin/utils/constants.py|  4 +-
 web/pgadmin/utils/master_password.py  |  3 +-
 14 files changed, 243 insertions(+), 31 deletions(-)
 create mode 100644 docs/en_US/oauth2.rst
 create mode 100644 web/pgadmin/authenticate/oauth.py
 create mode 100644 web/pgadmin/browser/tests/test_oauth_login.py

diff --git a/DEPENDENCIES b/DEPENDENCIES
index 6b4d9cfcf..8efe007a1 100644
--- a/DEPENDENCIES
+++ b/DEPENDENCIES
@@ -51,6 +51,7 @@ sshtunnel0.4.0
 ldap32.9  LGPL v3  https://github.com/cannatag/ldap3
 Flask-BabelEx0.9.4BSD  http://github.com/mrjoes/flask-babelex
 gssapi   1.6.12   LICENSE.txt  https://github.com/pythongssapi/python-gssapi
+authlib  0.15.3   BSD  https://github.com/lepture/authlib
 
 28 dependencies listed.
 
diff --git a/docs/en_US/oauth2.rst b/docs/en_US/oauth2.rst
new file mode 100644
index 0..44dd2cfe5
--- /dev/null
+++ b/docs/en_US/oauth2.rst
@@ -0,0 +1,36 @@
+.. _enabling_ldap_authentication:
+
+**
+`Enabling OAUTH Authentication`:index:
+**
+
+
+To enable OAUTH authentication for pgAdmin, you must configure the OAUTH
+settings in the *config_local.py* or *config_system.py* file (see the
+:ref:`config.py ` documentation) on the system where pgAdmin is
+installed in Server mode. You can copy these settings from *config.py* file
+and modify the values for the following parameters:
+
+
+.. csv-table::
+   :header: "**Parameter**", "**Description**"
+   :class: longtable
+   :widths: 35, 55
+
+   "AUTHENTICATION_SOURCES","The default value for this parameter is *internal*.
+   To enable LDAP authentication, you must include *oauth* in the list of values
+   for this parameter. you can modify the value as follows:
+
+   * [‘oauth’, ‘internal’]: pgAdmin will display an additional button for authenticating with oauth
+
+"OAUTH2_NAME", "The name of the of the oauth provider"
+"OAUTH2_CLIENT_ID","Oauth client id'
+"OAUTH2_CLIENT_SECRET", "Oauth secret"
+"OAUTH2_TOKEN_URL","This url is used to generate a token for OpenID Connect."
+"OAUTH2_AUTHORIZATION_URL", "This url is used for authentication"
+"OAUTH2_API_BASE_URL", "Oauth base url"
+"OAUTH2_USERINFO_ENDPOINT", "Endpoint for openid connect"
+"OAUTH_ENDPOINT_NAME", "Name of the Endpoint"
+
+Important note: if you change the e-mail address stored in the account, the account will be lost.
+Because the e-mail address is used to find a user.
\ No newline at end of file
diff --git a/requirements.txt b/requirements.txt
index edd7000bd..3823d8144 100644
--- a/requirements.txt
+++ b/requirements.txt
@@ -36,3 +36,5 @@ 

Bug #6337

[Florian Sabonchi]

Hello,

Is someone already working on ticket #6337 or can I start working on it?

https://redmine.postgresql.org/issues/6337

Regards,

Florian Sabonchi

Bug #6337 Patch

[Florian Sabonchi]

Hi I have a patch for bug #6337, in this patch you have the possibility 
to set in the configuration file the value MAX_LOGIN_ATTEMPTS which sets 
the number of failed login attempts that are allowed. If this value is 
exceeded the account is locked and can be reset by an administrator. By 
setting the variable to the value zero this feature is deactivated this 
is necessary if the account of the administrator was locked.


Comment:

Unfortunately the test cases fail because there seems to be a bug with 
the migration, but unfortunately I was not able to locate this bug.


Unfortunately, in my opinion, the documentation does not sufficiently 
explain how to correctly create the migrations.


I would be very happy if you could expand the documentation in the 
future what this concerns and create a detailed guide to create a 
migration.  (This also concerns the instructions for the integration test)


With kind regards,

Florian Sabonchi

>From beb82a64075e7c52904669178804ae0835d0e924 Mon Sep 17 00:00:00 2001
From: Florian Sabonchi 
Date: Sun, 11 Jul 2021 00:47:13 +0200
Subject: [PATCH 1/2] first draft for bruteforce_protection

---
 web/migrations/versions/6650c52670c2_.py  | 33 
 web/pgadmin/authenticate/__init__.py  | 39 ---
 web/pgadmin/model/__init__.py |  4 +-
 web/pgadmin/tools/user_management/__init__.py | 16 ++--
 .../static/js/user_management.js  | 13 +++
 5 files changed, 94 insertions(+), 11 deletions(-)
 create mode 100644 web/migrations/versions/6650c52670c2_.py

diff --git a/web/migrations/versions/6650c52670c2_.py b/web/migrations/versions/6650c52670c2_.py
new file mode 100644
index 0..90cc17d36
--- /dev/null
+++ b/web/migrations/versions/6650c52670c2_.py
@@ -0,0 +1,33 @@
+
+"""empty message
+
+Revision ID: 6650c52670c2
+Revises: c465fee44968
+Create Date: 2021-07-10 18:12:38.821602
+
+"""
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+from pgadmin import db
+
+revision = '6650c52670c2'
+down_revision = 'c465fee44968'
+branch_labels = None
+depends_on = None
+
+
+def upgrade():
+db.engine.execute(
+'ALTER TABLE user ADD COLUMN locked BOOLEAN DEFAULT FALSE'
+)
+db.engine.execute(
+'ALTER TABLE user ADD COLUMN login_attempts int DEFAULT 0'
+)
+
+
+def downgrade():
+# pgAdmin only upgrades, downgrade not implemented.
+pass
diff --git a/web/pgadmin/authenticate/__init__.py b/web/pgadmin/authenticate/__init__.py
index db42d230a..5a21d3d9b 100644
--- a/web/pgadmin/authenticate/__init__.py
+++ b/web/pgadmin/authenticate/__init__.py
@@ -12,18 +12,18 @@
 import config
 import copy
 
-from flask import current_app, flash, Response, request, url_for,\
+from flask import current_app, flash, Response, request, url_for, \
 session, redirect
 from flask_babelex import gettext
 from flask_security.views import _security
 from flask_security.utils import get_post_logout_redirect, \
-get_post_login_redirect
+get_post_login_redirect, logout_user
 
+from pgadmin import db, User
 from pgadmin.utils import PgAdminModule
 from pgadmin.utils.constants import KERBEROS, INTERNAL, OAUTH2, LDAP
 from pgadmin.authenticate.registry import AuthSourceRegistry
 
-
 MODULE_NAME = 'authenticate'
 auth_obj = None
 
@@ -46,14 +46,29 @@ def login():
 
 auth_obj = AuthSourceManager(form, copy.deepcopy(
 config.AUTHENTICATION_SOURCES))
-if OAUTH2 in config.AUTHENTICATION_SOURCES\
-and 'oauth2_button' in request.form:
+if OAUTH2 in config.AUTHENTICATION_SOURCES \
+and 'oauth2_button' in request.form:
 session['auth_obj'] = auth_obj
 
 session['auth_source_manager'] = None
+
+username = form.data['email']
+user = User.query.filter_by(username=username).first()
+
+if user:
+if user.login_attempts >= config.MAX_LOGIN_ATTEMPTS > 0:
+user.locked = True
+else:
+user.locked = False
+db.session.commit()
+
 # Validate the user
 if not auth_obj.validate():
 for field in form.errors:
+if user:
+if config.MAX_LOGIN_ATTEMPTS > 0:
+user.login_attempts += 1
+db.session.commit()
 for error in form.errors[field]:
 flash(error, 'warning')
 return redirect(get_post_logout_redirect())
@@ -65,15 +80,27 @@ def login():
 status, msg = auth_obj.login()
 current_auth_obj = auth_obj.as_dict()
 
+if status:
+if user.login_attempts >= config.MAX_LOGIN_ATTEMPTS > 0:
+flash(gettext('Account locked'),
+  'warning')
+logout_user()
+return redirect(get_post_logout_

Bug #6531

[Florian Sabonchi]

Hello someone is already working on bug #6531



OpenPGP_0x9B79A5A968AF5F8F.asc
Description: OpenPGP public key


OpenPGP_signature
Description: OpenPGP digital signature

Patch for Bug #6832

[Florian Sabonchi]

Hello I have written a patch for the bug #6832

From 40a72af814c34080883f069fd239047df48b2b7b Mon Sep 17 00:00:00 2001
From: Florian Sabonchi 
Date: Sat, 9 Oct 2021 12:57:49 +0200
Subject: [PATCH] fixe for Bug #6832

---
 web/pgAdmin4.py | 5 -
 web/pgadmin/__init__.py | 6 ++
 2 files changed, 6 insertions(+), 5 deletions(-)

diff --git a/web/pgAdmin4.py b/web/pgAdmin4.py
index dc1924cbd..5618ec245 100644
--- a/web/pgAdmin4.py
+++ b/web/pgAdmin4.py
@@ -103,7 +103,10 @@ if config.SERVER_MODE:
 app.wsgi_app = ReverseProxied(app.wsgi_app)
 
 # Authentication sources
-if len(config.AUTHENTICATION_SOURCES) > 0:
+if len(config.AUTHENTICATION_SOURCES) > 1 and \
+config.AUTHENTICATION_SOURCES[1] != INTERNAL:
+app.PGADMIN_EXTERNAL_AUTH_SOURCE = config.AUTHENTICATION_SOURCES[1]
+elif len(config.AUTHENTICATION_SOURCES) > 0:
 app.PGADMIN_EXTERNAL_AUTH_SOURCE = config.AUTHENTICATION_SOURCES[0]
 else:
 app.PGADMIN_EXTERNAL_AUTH_SOURCE = INTERNAL
diff --git a/web/pgadmin/__init__.py b/web/pgadmin/__init__.py
index 37eb26ccc..0fa8c4c83 100644
--- a/web/pgadmin/__init__.py
+++ b/web/pgadmin/__init__.py
@@ -777,10 +777,8 @@ def create_app(app_name=None):
 # but the user session may still be active. Logout the user
 # to get the key again when login
 if config.SERVER_MODE and current_user.is_authenticated and \
-app.PGADMIN_EXTERNAL_AUTH_SOURCE != \
-KERBEROS and app.PGADMIN_EXTERNAL_AUTH_SOURCE != \
-OAUTH2 and\
-current_app.keyManager.get() is None and \
+app.PGADMIN_EXTERNAL_AUTH_SOURCE == INTERNAL and \
+current_app.keyManager.get() is None and \
 request.endpoint not in ('security.login', 'security.logout'):
 logout_user()
 
-- 
2.25.1



OpenPGP_0x9B79A5A968AF5F8F.asc
Description: OpenPGP public key


OpenPGP_signature
Description: OpenPGP digital signature

feature #6640

[Florian Sabonchi]

Hi I have written a patch for feature #6640

From fd3978884501845099ca6547cd342ead0f833b14 Mon Sep 17 00:00:00 2001
From: Florian Sabonchi 
Date: Sun, 10 Oct 2021 12:38:50 +0200
Subject: [PATCH] first draft for feature #6640

---
 docs/en_US/oauth2.rst  |  1 +
 web/config.py  |  2 ++
 web/pgadmin/authenticate/oauth2.py | 19 +--
 3 files changed, 20 insertions(+), 2 deletions(-)

diff --git a/docs/en_US/oauth2.rst b/docs/en_US/oauth2.rst
index 4cc2628f5..6cf2f5aba 100644
--- a/docs/en_US/oauth2.rst
+++ b/docs/en_US/oauth2.rst
@@ -36,6 +36,7 @@ and modify the values for the following parameters:
 "OAUTH2_AUTO_CREATE_USER", "Set the value to *True* if you want to automatically
 create a pgAdmin user corresponding to a successfully authenticated Oauth2 user.
 Please note that password is not stored in the pgAdmin database."
+   "ALLOWED_ORGANIZATIONS", "Github organizations which are allowed. If the user is in an organization that is not in the list, logging in is not possible."
 
 Redirect URL
 
diff --git a/web/config.py b/web/config.py
index 7a1f4ab1f..ec8ec0959 100644
--- a/web/config.py
+++ b/web/config.py
@@ -719,6 +719,8 @@ OAUTH2_CONFIG = [
 'OAUTH2_ICON': None,
 # UI button colour, ex: #ff
 'OAUTH2_BUTTON_COLOR': None,
+# Allowed github organizations
+'ALLOWED_ORGANIZATIONS': [''],
 }
 ]
 
diff --git a/web/pgadmin/authenticate/oauth2.py b/web/pgadmin/authenticate/oauth2.py
index cc1143e06..866e12680 100644
--- a/web/pgadmin/authenticate/oauth2.py
+++ b/web/pgadmin/authenticate/oauth2.py
@@ -8,11 +8,12 @@
 ##
 
 """A blueprint module implementing the Oauth2 authentication."""
+import requests as requests
 
 import config
 
 from authlib.integrations.flask_client import OAuth
-from flask import current_app, url_for, session, request,\
+from flask import current_app, url_for, session, request, \
 redirect, Flask, flash
 from flask_babelex import gettext
 from flask_security import login_user, current_user
@@ -91,7 +92,6 @@ class OAuth2Authentication(BaseAuthentication):
 
 def __init__(self):
 for oauth2_config in config.OAUTH2_CONFIG:
-
 OAuth2Authentication.oauth2_config[
 oauth2_config['OAUTH2_NAME']] = oauth2_config
 
@@ -130,6 +130,17 @@ class OAuth2Authentication(BaseAuthentication):
 
 user, msg = self.__auto_create_user(profile)
 if user:
+organizations = self.get_organizations(profile['organizations_url'])
+
+for oauth2_config in config.OAUTH2_CONFIG:
+allowed_organizations = oauth2_config['ALLOWED_ORGANIZATIONS']
+if allowed_organizations:
+for organization in organizations:
+if organization['login'] not in allowed_organizations:
+return False, gettext("You are in an organization "
+  "that is not on the "
+  "whitelist")
+
 user = db.session.query(User).filter_by(
 username=profile['email'], auth_source=OAUTH2).first()
 current_app.login_manager.logout_view = \
@@ -137,6 +148,10 @@ class OAuth2Authentication(BaseAuthentication):
 return login_user(user), None
 return False, msg
 
+def get_organizations(self, organizations_url: str):
+organizations = requests.get(organizations_url)
+return organizations.json()
+
 def get_user_profile(self):
 session['oauth2_token'] = self.oauth2_clients[
 self.oauth2_current_client].authorize_access_token()
-- 
2.25.1



OpenPGP_0x9B79A5A968AF5F8F.asc
Description: OpenPGP public key


OpenPGP_signature
Description: OpenPGP digital signature