RE: pgAdmin support for Kerberos on AWS Cloud DB ?

Here is comes
/Börje

C:\ProgramData\MIT\Kerberos5\krb5.ini

# includedir /etc/krb5.conf.d/
[logging]
# default = FILE:/var/log/krb5libs.log
# default = FILE:/var/log/krb5libs.log
# kdc = FILE:/var/log/krb5kdc.log
# admin_server = FILE:/var/log/kadmind.log
[libdefaults]
dns_lookup_realm = false
ticket_lifetime = 24h
renew_lifetime = 7d
forwardable = true
rdns = false
# pkinit_anchors = FILE:/etc/pki/tls/certs/ca-bundle.crt
default_realm = WCAR.WIRELESSCAR.COM
# default_ccache_name = KEYRING:persistent:%{uid}
default_ccache_name = C:\Temp\Kerberos\cache
[realms]
WCAR.WIRELESSCAR.COM = {
kdc = sealidc01.wcar.wirelesscar.com
admin_server = sealidc01.wcar.wirelesscar.com
}
AWSAD.INTERNAL.WIRELESSCAR.COM = {
kdc = awsad.internal.wirelesscar.com:88
admin_server = awsad.internal.wirelesscar.com
}
[domain_realm]
.awsad.internal.wirelesscar.com = AWSAD.INTERNAL.WIRELESSCAR.COM
awsad.internal.wirelesscar.com = AWSAD.INTERNAL.WIRELESSCAR.COM
.wirelesscar.com = WCAR.WIRELESSCAR.COM
wirelesscar.com = WCAR.WIRELESSCAR.COM
wcar.wirelesscar.com = WCAR.WIRELESSCAR.COM
.wcar.wirelesscar.com = WCAR.WIRELESSCAR.COM
.rds.amazonaws.com = AWSAD.INTERNAL.WIRELESSCAR.COM
.amazonaws.com.cn = AWSAD.INTERNAL.WIRELESSCAR.COM
.amazon.com = AWSAD.INTERNAL.WIRELESSCAR.COM
.rds.amazonaws.com = WCAR.WIRELESSCAR.COM
.amazonaws.com.cn = WCAR.WIRELESSCAR.COM
.amazon.com = WCAR.WIRELESSCAR.COM

From: Khushboo Vashi <khushboo(dot)vashi(at)enterprisedb(dot)com>
Sent: den 4 januari 2022 10:30
To: Börje Johansson <borje(dot)johansson(dot)2(at)wirelesscar(dot)com>
Cc: pgAdmin Support <pgadmin-support(at)postgresql(dot)org>
Subject: Re: pgAdmin support for Kerberos on AWS Cloud DB ?

CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you can confirm the sender and know the content is safe.
Hi,

Please send the kerberos configuration file as well. (krb5.conf or krb5.ini)

Thanks,
Khushboo

On Tue, Jan 4, 2022 at 2:20 PM Börje Johansson <borje(dot)johansson(dot)2(at)wirelesscar(dot)com<mailto:borje(dot)johansson(dot)2(at)wirelesscar(dot)com>> wrote:
Thanks for your reply, hoping Support may help me to find the issue.
Try to clear some things of my setup, to See if that helps:
For the moment I can get it to work with tool DBeaver-Windows, with Kerberos/AD.
also psql for Linux works, with Kerberos/AD.
AWS setup is a lot of steps, not going into that now, but that works, since DBeaver works and psql in Linux also works
We are running AWS RDS Postgres/Aurora DB version 13.4, so not able to login to DB server.

pgAdmin Desktop 6.0 , see below
MIT Kerberos, see below
Connection error in pgadmin, see below
Connection error in psql, see below

[cid:image001(dot)png(at)01D8015B(dot)9A3E5E60]

Parameter settings:
ALLOW_SAVE_PASSWORD = True
ALLOW_SAVE_TUNNEL_PASSWORD = False
APP_COPYRIGHT = "Copyright (C) 2013 - 2021, The pgAdmin Development Team"
APP_ICON = "pg-icon"
APP_NAME = "pgAdmin 4"
APP_RELEASE = 6
APP_REVISION = 0
APP_SUFFIX = ""
APP_VERSION = "6.0"
APP_VERSION_EXTN = ('.css', '.js', '.html', '.svg', '.png', '.gif', '.ico')
APP_VERSION_INT = 60000
APP_VERSION_PARAM = "ver"
AUTHENTICATION_SOURCES = ['internal', 'kerberos']
CA_FILE = "C:\app\pgAdmin 4\v6\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', 'application/json', 'application/javascript']
COMPRESS_MIN_SIZE = 500
CONSOLE_LOG_FORMAT = "%(asctime)s: %(levelname)s %(name)s: %(message)s"
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 = "C:\Users\AA100077\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(at)pgadmin(dot)org<mailto:pgadmin4(at)pgadmin(dot)org>
EFFECTIVE_SERVER_PORT = 50685
ENABLE_BINARY_PATH_BROWSING = False
ENABLE_PSQL = True
ENHANCED_COOKIE_PROTECTION = True
FILE_LOG_FORMAT = "%(asctime)s: %(levelname)s %(name)s: %(message)s"
FILE_LOG_LEVEL = 30
HELP_PATH = "../../../docs/en_US/html/"
IS_WIN = True
KERBEROS_CCACHE_DIR = "C:\Temp\Kerberos\cache"
KRB_APP_HOST_NAME = "127.0.0.1"
KRB_AUTO_CREATE_USER = False
KRB_KTNAME = "<KRB5_KEYTAB_FILE>"
LANGUAGES = {'en': 'English', 'zh': 'Chinese (Simplified)', 'cs': 'Czech', 'fr': 'French', 'de': 'German', 'it': 'Italian', 'ja': 'Japanese', 'ko': 'Korean', 'pl': 'Polish', 'ru': 'Russian', 'es': 'Spanish'}
LDAP_ANONYMOUS_BIND = False
LDAP_AUTO_CREATE_USER = True
LDAP_BASE_DN = "<Base-DN>"
LDAP_BIND_USER = None
LDAP_CA_CERT_FILE = ""
LDAP_CERT_FILE = ""
LDAP_CONNECTION_TIMEOUT = 10
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 = "C:\Users\AA100077\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_REQUIRED = True
MAX_LOGIN_ATTEMPTS = 3
MAX_QUERY_HIST_STORED = 20
MAX_SESSION_IDLE_TIME = 60
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_API_BASE_URL': None, 'OAUTH2_USERINFO_ENDPOINT': None, 'OAUTH2_SCOPE': None, 'OAUTH2_ICON': None, 'OAUTH2_BUTTON_COLOR': None}]
ON_DEMAND_RECORD_COUNT = 1000
OVERRIDE_USER_INACTIVITY_TIMEOUT = True
PG_DEFAULT_DRIVER = "psycopg2"
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(at)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_MODE = False
SESSION_COOKIE_DOMAIN = None
SESSION_COOKIE_HTTPONLY = True
SESSION_COOKIE_NAME = "pga4_session"
SESSION_COOKIE_SAMESITE = "Lax"
SESSION_COOKIE_SECURE = False
SESSION_DB_PATH = "C:\Users\AA100077\AppData\Roaming\pgAdmin\sessions"
SESSION_EXPIRATION_TIME = 7
SESSION_SKIP_PATHS = ['/misc/ping']
SETTINGS_SCHEMA_VERSION = 31
SHOW_GRAVATAR_IMAGE = True
SQLALCHEMY_TRACK_MODIFICATIONS = False
SQLITE_PATH = "C:\Users\AA100077\AppData\Roaming\pgAdmin\pgadmin4.db"
SQLITE_TIMEOUT = 500
STORAGE_DIR = "C:\Users\AA100077\AppData\Roaming\pgAdmin\storage"
STRICT_TRANSPORT_SECURITY = "max-age=31536000; includeSubDomains"
STRICT_TRANSPORT_SECURITY_ENABLED = False
SUPPORT_SSH_TUNNEL = True
TEST_SQLITE_PATH = "C:\Users\AA100077\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<https://eur06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.pgadmin.org%2Fversions.json&data=04%7C01%7Cborje.johansson.2%40wirelesscar.com%7C2cab820e4df44111112608d9cf64cb1f%7C7a628a40208a4188af06775f9ba954c7%7C0%7C0%7C637768854124253670%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=5rNvbh8WJcNjttKc6pEbr%2B5IV3O3RsB20yuWOzbkogE%3D&reserved=0>
USER_INACTIVITY_TIMEOUT = 0
WEB_SERVER = "Python"
WTF_CSRF_HEADERS = ['X-pgA-CSRFToken']
X_CONTENT_TYPE_OPTIONS = "nosniff"
X_FRAME_OPTIONS = "SAMEORIGIN"
X_XSS_PROTECTION = "1; mode=block"

[cid:image002(dot)png(at)01D8015B(dot)9A3E5E60]

Pgadmin-connection

[cid:image003(dot)png(at)01D8015B(dot)9A3E5E60]

[cid:image004(dot)png(at)01D8015B(dot)9A3E5E60]

PSQL login error
C:\app\PostgreSQL\14\bin>psql --version
psql (PostgreSQL) 14.1

C:\app\PostgreSQL\14\bin>psql -d mbcs_int_AuroraRDS -h mbcs-int-aurora-postgres-hotel1.cluster-cqmavtizubqt.eu-west-1.rds.amazonaws.com<https://eur06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmbcs-int-aurora-postgres-hotel1.cluster-cqmavtizubqt.eu-west-1.rds.amazonaws.com%2F&data=04%7C01%7Cborje.johansson.2%40wirelesscar.com%7C2cab820e4df44111112608d9cf64cb1f%7C7a628a40208a4188af06775f9ba954c7%7C0%7C0%7C637768854124253670%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=YYcmN7BC2xdvdcm0L3dkTaA3MwA%2F1bpSho90aKTqOeA%3D&reserved=0> -U aa100077(at)WCAR(dot)WIRELESSCAR(dot)COM<mailto:aa100077(at)WCAR(dot)WIRELESSCAR(dot)COM> -p 5432
psql: error: connection to server at "mbcs-int-aurora-postgres-hotel1.cluster-cqmavtizubqt.eu-west-1.rds.amazonaws.com<https://eur06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmbcs-int-aurora-postgres-hotel1.cluster-cqmavtizubqt.eu-west-1.rds.amazonaws.com%2F&data=04%7C01%7Cborje.johansson.2%40wirelesscar.com%7C2cab820e4df44111112608d9cf64cb1f%7C7a628a40208a4188af06775f9ba954c7%7C0%7C0%7C637768854124253670%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=YYcmN7BC2xdvdcm0L3dkTaA3MwA%2F1bpSho90aKTqOeA%3D&reserved=0>" (10.183.41.9), port 5432 failed: SSPI continuation error: The specified target is unknown or unreachable
(80090303)

AWS Setup have been done, according to:
https://aws.amazon.com/blogs/database/preparing-on-premises-and-aws-environments-for-external-kerberos-authentication-for-amazon-rds/<https://eur06.safelinks.protection.outlook.com/?url=https%3A%2F%2Faws.amazon.com%2Fblogs%2Fdatabase%2Fpreparing-on-premises-and-aws-environments-for-external-kerberos-authentication-for-amazon-rds%2F&data=04%7C01%7Cborje.johansson.2%40wirelesscar.com%7C2cab820e4df44111112608d9cf64cb1f%7C7a628a40208a4188af06775f9ba954c7%7C0%7C0%7C637768854124253670%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=qV4ikAhrA49K5R6Z8gvHeH5aI28eUn29sdWU5TmzMc0%3D&reserved=0>

From: Khushboo Vashi <khushboo(dot)vashi(at)enterprisedb(dot)com<mailto:khushboo(dot)vashi(at)enterprisedb(dot)com>>
Sent: den 4 januari 2022 06:21
To: Börje Johansson <borje(dot)johansson(dot)2(at)wirelesscar(dot)com<mailto:borje(dot)johansson(dot)2(at)wirelesscar(dot)com>>
Cc: pgAdmin Support <pgadmin-support(at)postgresql(dot)org<mailto:pgadmin-support(at)postgresql(dot)org>>
Subject: Re: pgAdmin support for Kerberos on AWS Cloud DB ?

CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you can confirm the sender and know the content is safe.
Hi Börje,

[Looping pgAdmin support....]

On Mon, Jan 3, 2022 at 7:46 PM Börje Johansson <borje(dot)johansson(dot)2(at)wirelesscar(dot)com<mailto:borje(dot)johansson(dot)2(at)wirelesscar(dot)com>> wrote:
Hi Khushboo

My name is Börje and working at WirelessCar in Sweden.
I'm working with Databases and Postgres a lot.
I read your description of Kerberos authentication, that was great!

We have introduce Kerberos/AD for our AWS Cloud Postgres databases, and trying pgAdmin to work here...
We have mostly Windows Client and cannot get it to work.
If your pgAdmin is installed on Windows and installation has been done by pgAdmin installers (by default pgAdmin is in desktop mode on Windows (single user mode)), then it should work.

Share the details of the steps you performed to setup the Kerberos on AWS as well as on Windows and pgAdmin.

Thanks,
Khushboo

Do you know if there are support for Windows Clients against AWS Cloud Postgres RDS ?

Thanks,
Khushboo

Rgds Börje

Börje Johansson
Data Management Team

[cid:image005(dot)png(at)01D8015B(dot)9A3E5E60]

Address | Vädursgatan 6, SE-412 50 Göteborg
Sweden
+46 (0)720 70 28 03
borje(dot)johansson(dot)2(at)wirelesscar(dot)com<mailto:borje(dot)johansson(dot)2(at)wirelesscar(dot)com>
www.wirelesscar.com<https://eur06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.wirelesscar.com%2F&data=04%7C01%7Cborje.johansson.2%40wirelesscar.com%7C2cab820e4df44111112608d9cf64cb1f%7C7a628a40208a4188af06775f9ba954c7%7C0%7C0%7C637768854124253670%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=PmReXK5Z%2B5gV1VTNYQJ%2B0%2FzGB5qmx7Rc2FUF1zDRsk4%3D&reserved=0>
Follow us | LinkedIn<https://eur06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.linkedin.com%2Fcompany%2Fwirelesscar%2F&data=04%7C01%7Cborje.johansson.2%40wirelesscar.com%7C2cab820e4df44111112608d9cf64cb1f%7C7a628a40208a4188af06775f9ba954c7%7C0%7C0%7C637768854124253670%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=mRaoQOF1m45rYxa7eQGpoOy8fY6qAhjjHaEqcDct4HU%3D&reserved=0>
#wearewirelesscar<https://eur06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.linkedin.com%2Fcompany%2Fwirelesscar%2Fmycompany%2F&data=04%7C01%7Cborje.johansson.2%40wirelesscar.com%7C2cab820e4df44111112608d9cf64cb1f%7C7a628a40208a4188af06775f9ba954c7%7C0%7C0%7C637768854124253670%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=NQgCB%2Fi0il8B1TeN1xFx%2BqYyXIZjuBCHCeYm2sNiqUA%3D&reserved=0>
Regarding WirelessCar's treatment of your personal data - please click here<https://eur06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.wirelesscar.com%2Fprivacy-policy%2F&data=04%7C01%7Cborje.johansson.2%40wirelesscar.com%7C2cab820e4df44111112608d9cf64cb1f%7C7a628a40208a4188af06775f9ba954c7%7C0%7C0%7C637768854124253670%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=DNrF05%2FRPc63%2BzmmfhlrzYJFUTkLRlPOOPdaelowfbQ%3D&reserved=0>.