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.27k stars 612 forks source link

Cannot Connect to a Server with SSLmode set to "require" #7590

Closed ayrus02 closed 19 hours ago

ayrus02 commented 2 weeks ago

Hi,

We had PostgreSQL server PostgreSQL 14.12 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-22), 64-bit that runs with SSL Mode connection activated and we are enforcing ssl connection on it,

When we try to connect to the server using local installed PGAdmin4 v8.8, we encounter this error "Unable to connect to server:connection is bad: sslmode value "require" invalid when SSL support is not compiled in"

PGAdmin4-Cannot-Connect-With-SSLMode-Require Above picture is the error when trying to connect to server with SSL enforced connection in pg_hba.conf

FYI, the PGAdmin4 is running on web mode on Windows 11, that we run using this command:

C:\Program Files\pgAdmin 4\web>"C:\Program Files\pgAdmin 4\python\python.exe" pgAdmin4.py
Starting pgAdmin 4. Please navigate to http://127.0.0.1:5050 in your browser.
 * Serving Flask app 'pgadmin'
 * Debug mode: off

To Reproduce

Steps to reproduce the behavior:

  1. Go to PGAdmin4 web page mine is HTTP://127.0.0.1:5050
  2. Right Click on Item on Object Explorer
  3. Chose Register > Server form the pop-up menu
  4. Fill the server name, host, port, username, database name and password
  5. On the Parameters tab, turn the Connection Parameters of SSL Mode to require
  6. See the error of "Unable to connect to server:connection is bad: sslmode value "require" invalid when SSL support is not compiled in"

Expected behavior

There will be no error shown and able to connect to the server successfully if given host, port, username, database name and password are correct.

Error message

"Unable to connect to server:connection is bad: sslmode value "require" invalid when SSL support is not compiled in"

Screenshots

PGAdmin4-Cannot-Connect-With-SSLMode-Require

Desktop (please complete the following information):

Additional context

Already test with other 3rd party client software like Navicat and can connect flawlessly, see attached picture below: Query-Run-Navica-02t

The connection setting on Navicat with SSLmode set to require: Navicat-Connection-Settings

We also trying to access the same server with PSQL and can connect successfully with SSLmode turn on: psql-connection-from-windows

khushboovashi commented 2 weeks ago

Hi @ayrus02, I am not able to reproduce this issue on a Mac in server or desktop mode.

Screenshot 2024-06-18 at 2 26 43 PM

Can you try with your Windows desktop mode?

ayrus02 commented 1 week ago

Hi @khushboovashi, I've try to run PgAdmin in Windows with Desktop Mode, and it can connect successfully with SSL, so I can confirm, that the error is only happen when I run the PgAdmin on Web Mode and accessed from http:://127.0.0.1:5050 via Web Browser.

Regards, ayrus02

ayrus02 commented 1 week ago

Hi @khushboovashi today I've just updated to PgAdmin v8.9, and I can confirm that the connection problem with SSL Mode set to 'Require' still exists when I use the Web Mode.

Regards, ayrus02

khushboovashi commented 6 days ago

Hi @khushboovashi today I've just updated to PgAdmin v8.9, and I can confirm that the connection problem with SSL Mode set to 'Require' still exists when I use the Web Mode.

pgAdmin does not ship web app for Windows, how did you manage to run it? Through pip wheel or set the server mode on your own?

Regards, ayrus02

ayrus02 commented 6 days ago

Hi @khushboovashi today I've just updated to PgAdmin v8.9, and I can confirm that the connection problem with SSL Mode set to 'Require' still exists when I use the Web Mode.

pgAdmin does not ship web app for Windows, how did you manage to run it? Through pip wheel or set the server mode on your own?

Regards, ayrus02

As I post on this bug, this is how I run installed version of PgAdmin4 on my WIndows 11:

FYI, the PGAdmin4 is running on web mode on Windows 11, that we run using this command:

C:\Program Files\pgAdmin 4\web>"C:\Program Files\pgAdmin 4\python\python.exe" pgAdmin4.py
Starting pgAdmin 4. Please navigate to http://127.0.0.1:5050/ in your browser.

Serving Flask app 'pgadmin'
Debug mode: off`

I've use this mode of PgAdmin4 because I needed the tabbed feature in web browser that exist before the Desktop Mode introduced in PgAdmin4 long time ago...

PgAdmin4-Tab

I need the PgAdmin can be accessed via browser tab (greed arrow) because the tab inside PgAdmin4 (red arrow) I use it for grouping the task I'm doing in PgAdmin4

ayrus02 commented 6 days ago

Hi @khushboovashi, below I post another piece of information on Help > About pgAdmin 4 dialog:

PgAdmin4-About-Dialog

And below are the Server Configuration that displayed on About Dialog:

ALLOW_SAVE_PASSWORD = True
ALLOW_SAVE_TUNNEL_PASSWORD = False
APP_COPYRIGHT = "Copyright (C) 2013 - 2024, The pgAdmin Development Team"
APP_DEFAULT_EMAIL = "pgadmin4@pgadmin.org"
APP_ICON = "pg-icon"
APP_NAME = "pgAdmin 4"
APP_PATH = "pgadmin"
APP_RELEASE = 8
APP_REVISION = 9
APP_SHORT_NAME = "pgadmin4"
APP_SUFFIX = ""
APP_VERSION = "8.9"
APP_VERSION_EXTN = ('.css', '.js', '.html', '.svg', '.png', '.gif', '.ico')
APP_VERSION_INT = 80900
APP_VERSION_PARAM = "ver"
APP_WIN_PATH = "pgAdmin"
AUTHENTICATION_SOURCES = ['internal']
AUTO_DISCOVER_SERVERS = True
AZURE_CREDENTIAL_CACHE_DIR = "D:\Users\xxxxxxx\AppData\Roaming\pgAdmin\azurecredentialcache"
CA_FILE = "C:\Program Files\pgAdmin 4\web\cacert.pem"
CHECK_EMAIL_DELIVERABILITY = False
CHECK_SESSION_FILES_INTERVAL = 24
CHECK_SUPPORTED_BROWSER = True
COMPRESS_LEVEL = 9
COMPRESS_MIMETYPES = ['text/html', 'text/css', 'text/xml', 'text/javascript', 'application/json', 'application/javascript']
COMPRESS_MIN_SIZE = 500
CONFIG_DATABASE_CONNECTION_MAX_OVERFLOW = 100
CONFIG_DATABASE_CONNECTION_POOL_SIZE = 5
CONFIG_DATABASE_URI = ""
CONSOLE_LOG_FORMAT = "%(asctime)s: %(levelname)s    %(name)s:   %(message)s"
CONSOLE_LOG_FORMAT_JSON = OrderedDict({'time': 'asctime', 'message': 'message', 'level': 'levelname'})
CONSOLE_LOG_LEVEL = 30
CONTENT_SECURITY_POLICY = "default-src ws: http: data: blob: 'unsafe-inline' 'unsafe-eval';"
COOKIE_DEFAULT_DOMAIN = None
COOKIE_DEFAULT_PATH = "/"
DATA_DIR = "D:\Users\xxxxxxx\AppData\Roaming\pgAdmin"
DEBUG = False
DEFAULT_BINARY_PATHS = {'pg': '$DIR/../runtime', 'ppas': ''}
DEFAULT_SERVER = "127.0.0.1"
DEFAULT_SERVER_PORT = 5050
DESKTOP_USER = "pgadmin4@pgadmin.org"
DISABLED_LOCAL_PASSWORD_STORAGE = False
EFFECTIVE_SERVER_PORT = 5050
ENABLE_BINARY_PATH_BROWSING = False
ENABLE_PSQL = True
ENABLE_SERVER_PASS_EXEC_CMD = False
ENHANCED_COOKIE_PROTECTION = True
FILE_LOG_FORMAT = "%(asctime)s: %(levelname)s   %(name)s:   %(message)s"
FILE_LOG_FORMAT_JSON = OrderedDict({'time': 'asctime', 'message': 'message', 'level': 'levelname'})
FILE_LOG_LEVEL = 30
FIXED_BINARY_PATHS = {'pg': '', 'pg-12': '', 'pg-13': '', 'pg-14': '', 'pg-15': '', 'pg-16': '', 'ppas': '', 'ppas-12': '', 'ppas-13': '', 'ppas-14': '', 'ppas-15': '', 'ppas-16': ''}
HELP_PATH = "../../../docs/en_US/html/"
IS_WIN = True
JSON_LOGGER = False
KERBEROS_CCACHE_DIR = "D:\Users\xxxxxxx\AppData\Roaming\pgAdmin\krbccache"
KEYRING_NAME = "Windows WinVaultKeyring"
KRB_APP_HOST_NAME = "127.0.0.1"
KRB_AUTO_CREATE_USER = True
KRB_KTNAME = "<KRB5_KEYTAB_FILE>"
LANGUAGES = {'en': 'English', 'zh': 'Chinese (Simplified)', 'cs': 'Czech', 'fr': 'French', 'de': 'German', 'id': 'Indonesian', 'it': 'Italian', 'ja': 'Japanese', 'ko': 'Korean', 'pl': 'Polish', 'pt_BR': 'Portuguese (Brazilian)', 'ru': 'Russian', 'es': 'Spanish'}
LDAP_ANONYMOUS_BIND = False
LDAP_AUTO_CREATE_USER = True
LDAP_BASE_DN = "<Base-DN>"
LDAP_BIND_FORMAT = "{LDAP_USERNAME_ATTRIBUTE}={LDAP_USERNAME},{LDAP_BASE_DN}"
LDAP_BIND_USER = None
LDAP_CA_CERT_FILE = ""
LDAP_CERT_FILE = ""
LDAP_CONNECTION_TIMEOUT = 10
LDAP_DN_CASE_SENSITIVE = False
LDAP_IGNORE_MALFORMED_SCHEMA = False
LDAP_KEY_FILE = ""
LDAP_SEARCH_BASE_DN = "<Search-Base-DN>"
LDAP_SEARCH_FILTER = "(objectclass=*)"
LDAP_SEARCH_SCOPE = "SUBTREE"
LDAP_SERVER_URI = "ldap://<ip-address>:<port>"
LDAP_USERNAME_ATTRIBUTE = "<User-id>"
LDAP_USE_STARTTLS = False
LOGIN_ATTEMPT_FIELDS = ['password']
LOGIN_BANNER = ""
LOG_FILE = "D:\Users\xxxxxxx\AppData\Roaming\pgAdmin\pgadmin4.log"
LOG_ROTATION_AGE = 1440
LOG_ROTATION_MAX_LOG_FILES = 90
LOG_ROTATION_SIZE = 10
MAIL_DEBUG = False
MAIL_PORT = 25
MAIL_SERVER = "localhost"
MAIL_USERNAME = ""
MAIL_USE_SSL = False
MAIL_USE_TLS = False
MASTER_PASSWORD_HOOK = None
MASTER_PASSWORD_REQUIRED = True
MAX_LOGIN_ATTEMPTS = 3
MAX_QUERY_HIST_STORED = 20
MAX_SESSION_IDLE_TIME = 60
MFA_EMAIL_SUBJECT = None
MFA_ENABLED = False
MFA_FORCE_REGISTRATION = False
MFA_SUPPORTED_METHODS = ['email', 'authenticator']
MODULE_BLACKLIST = ['test']
NODE_BLACKLIST = []
OAUTH2_AUTO_CREATE_USER = True
OAUTH2_CONFIG = [{'OAUTH2_NAME': None, 'OAUTH2_DISPLAY_NAME': '<Oauth2 Display Name>', 'OAUTH2_CLIENT_ID': None, 'OAUTH2_CLIENT_SECRET': None, 'OAUTH2_TOKEN_URL': None, 'OAUTH2_AUTHORIZATION_URL': None, 'OAUTH2_SERVER_METADATA_URL': None, 'OAUTH2_API_BASE_URL': None, 'OAUTH2_USERINFO_ENDPOINT': None, 'OAUTH2_SCOPE': None, 'OAUTH2_USERNAME_CLAIM': None, 'OAUTH2_ICON': None, 'OAUTH2_BUTTON_COLOR': None, 'OAUTH2_ADDITIONAL_CLAIMS': None, 'OAUTH2_SSL_CERT_VERIFICATION': True, 'OAUTH2_LOGOUT_URL': None}]
ON_DEMAND_RECORD_COUNT = 1000
OVERRIDE_USER_INACTIVITY_TIMEOUT = True
PASSWORD_LENGTH_MIN = 6
PG_DEFAULT_DRIVER = "psycopg3"
PROXY_X_FOR_COUNT = 1
PROXY_X_HOST_COUNT = 0
PROXY_X_PORT_COUNT = 1
PROXY_X_PREFIX_COUNT = 0
PROXY_X_PROTO_COUNT = 1
SECURITY_EMAIL_SENDER = "no-reply@localhost"
SECURITY_EMAIL_SUBJECT_PASSWORD_CHANGE_NOTICE = "Your password for pgAdmin 4 has been changed"
SECURITY_EMAIL_SUBJECT_PASSWORD_NOTICE = "Your pgAdmin 4 password has been reset"
SECURITY_EMAIL_SUBJECT_PASSWORD_RESET = "Password reset instructions for pgAdmin 4"
SECURITY_EMAIL_VALIDATOR_ARGS = {'check_deliverability': False}
SEND_FILE_MAX_AGE_DEFAULT = 31556952
SERVER_HEARTBEAT_TIMEOUT = 30
SERVER_MODE = False
SESSION_COOKIE_DOMAIN = None
SESSION_COOKIE_HTTPONLY = True
SESSION_COOKIE_NAME = "pga4_session"
SESSION_COOKIE_PATH = "/"
SESSION_COOKIE_SAMESITE = "Lax"
SESSION_COOKIE_SECURE = False
SESSION_DB_PATH = "D:\Users\xxxxxxx\AppData\Roaming\pgAdmin\sessions"
SESSION_EXPIRATION_TIME = 1
SESSION_SKIP_PATHS = ['/misc/ping']
SETTINGS_SCHEMA_VERSION = 40
SHARED_STORAGE = []
SHOW_GRAVATAR_IMAGE = True
SQLALCHEMY_TRACK_MODIFICATIONS = False
SQLITE_PATH = "D:\Users\xxxxxxx\AppData\Roaming\pgAdmin\pgadmin4.db"
SQLITE_TIMEOUT = 500
STORAGE_DIR = "D:\Users\xxxxxxx\AppData\Roaming\pgAdmin\storage"
STRICT_TRANSPORT_SECURITY = "max-age=31536000; includeSubDomains"
STRICT_TRANSPORT_SECURITY_ENABLED = False
SUPPORT_SSH_TUNNEL = True
TEST_SQLITE_PATH = "D:\Users\xxxxxxx\AppData\Roaming\pgAdmin\test_pgadmin4.db"
THREADED_MODE = True
UPGRADE_CHECK_ENABLED = True
UPGRADE_CHECK_KEY = "pgadmin4"
UPGRADE_CHECK_URL = "https://www.pgadmin.org/versions.json"
USER_INACTIVITY_TIMEOUT = 0
WEBSERVER_AUTO_CREATE_USER = True
WEBSERVER_REMOTE_USER = "REMOTE_USER"
WEB_SERVER = "Python"
WTF_CSRF_HEADERS = ['X-pgA-CSRFToken']
X_CONTENT_TYPE_OPTIONS = "nosniff"
X_FRAME_OPTIONS = "SAMEORIGIN"
X_XSS_PROTECTION = "1; mode=block"

Regards, ayrus02

khushboovashi commented 5 days ago

Hi @ayrus02, I suggest using server mode with Python Wheel packages as we do not support web mode on Windows. Ref: https://www.pgadmin.org/download/pgadmin-4-python/

ayrus02 commented 5 days ago

Hi @khushboovashi, I'm not doing your suggestion to use the Server Mode PgAdmin4, because I use it only for myself inside my development environment, and if I use the Server Mode, I will be asked to input username and password every time I access the web address for the first time.

Currently I've successfully can connect to SSL enabled Postgres Server with the same setting, with running using:

C:\Program Files\pgAdmin 4\web>"C:\Program Files\pgAdmin 4\python\python.exe" pgAdmin4.py
Starting pgAdmin 4. Please navigate to http://127.0.0.1:5050/ in your browser.

Serving Flask app 'pgadmin'
Debug mode: off

Apparently the problem with this mode of PgAdmin4 is the libpq.dll used is undetermined. In my computer environment, there are many version of PostgreSQL, PgAdmin4 and PHP version installed, and with all these version installed in the same time, I'm not sure which libpq.dll is being used by the PgAdmin4, and it look like used the one with no SSL support compiled to libpq.dll.

The reason I was suggesting the server mode is because of these path settings. It is perfectly okay if you have copied the libpq to a different place; alternatively, you can set that path in the environment, too.

I am closing this issue.

So the solution is I copy the libpq.dll from

C:\Program Files\pgAdmin 4\runtime\libpq.dll

to

C:\Program Files\pgAdmin 4\python\libpq.dll

Then restart the PgAdmin4 instance by running again the command:

C:\Program Files\pgAdmin 4\web>"C:\Program Files\pgAdmin 4\python\python.exe" pgAdmin4.py
Starting pgAdmin 4. Please navigate to http://127.0.0.1:5050/ in your browser.

Serving Flask app 'pgadmin'
Debug mode: off

And voila the PgAdmin4 can connect to SSL Enabled PostgreSQL server without problem.

Regards, ayrus02