使用SSL连接Google SQL PostgreSQL实例的Npgsql连接字符串问题

问题描述 #

通过公网IP连接Google SQL PostgreSQL实例正常，但配置SSL连接时始终失败，错误提示28000: connection requires a valid client certificate，使用的证书均为从Google Cloud Console下载的*.pem文件。

脱敏连接字符串 #

Include Error Detail=True;Client Certificate=client-cert.pem;Root Certificate=server-ca.pem;Client Certificate Key=client-key.pem;Trust Server Certificate=true;SSL Mode=Prefer;Persist Security Info=True;Password=[secret];Username=[secret];Database=[secret];Host=[secret];

错误堆栈跟踪 #

28000: connection requires a valid client certificate
at Npgsql.NpgsqlConnector.<g__ReadMessageLong|194_0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Npgsql.NpgsqlConnector.d__0.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.ValidateEnd(Task task)
at Npgsql.NpgsqlConnector.d__175.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Npgsql.ConnectorPool.d__41.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Npgsql.ConnectorPool.<>c__DisplayClass38_0.<g__RentAsync|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Npgsql.NpgsqlConnection.<>c__DisplayClass41_0.<g__OpenAsync|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Npgsql.NpgsqlConnection.Open()
at MavPeople.Common.GetAuthObject(String login) in C:\Projects\MavPeople\MavPeople\Common.vb:line 326
at MavPeople.MainForm.MainForm_Load(Object sender, EventArgs e) in C:\Projects\MavPeople\MavPeople\MainForm.vb:line 86

排查及解决步骤 #

• 调整SSL模式：将SSL Mode=Prefer改为SSL Mode=Require或SSL Mode=VerifyFull。Prefer模式允许降级为非SSL连接，而Google SQL可能强制要求SSL并验证客户端证书，需明确强制SSL连接。
• 确认证书路径：确保证书文件使用绝对路径，或放置在程序运行的工作目录下，避免相对路径导致的加载失败。
• 验证密钥文件：检查client-key.pem是否有格式错误（如多余空行、字符缺失），若密钥有加密（GCP下载的通常无加密），需添加Client Certificate Key Password参数。
• 修正证书信任配置：将Trust Server Certificate=true改为Trust Server Certificate=false，同时确保Root Certificate指向的server-ca.pem为正确的Google根证书，完成服务器证书有效性验证。
• 检查用户SSL设置：在Google Cloud Console中确认数据库用户是否被配置为强制SSL连接，确保用户要求与客户端配置匹配。
• 升级Npgsql版本：旧版Npgsql可能存在SSL证书加载bug，升级至最新稳定版可解决兼容性问题。

内容的提问来源于stack exchange，提问作者Shane Brodie