找到你要的答案

Q:postgres jdbc client cert user vs db user

Q:Postgres JDBC客户端证书用户和数据库用户

Backstory:

I've gotten jdbc to connect to postgres using a client cert. In java I set the user in the properties, and the driver looks it up in the keystore and sends it along. All was good.

But I just found out that I won't be getting certs with a CN of pg-user. The certs I'll be getting will have a CN of pg-user.XYZ.foo.com & pg-user.ABC.foo.com. This looks like a job for username maps. Hey they even have regexp, it'll be perfect.

I got unix user root logging in to postgres as pg-user using a username map and local ident authentication using psql -d db -U pg-user. But in that case postgres knows BOTH that the user is root, AND is trying to log in as pg-user.

Problem:

What I can't figure out is how to tell the postgres jdbc driver to grab the cert from the keystore with a CN of pg-user.XYZ.foo.com, but present to postgres as user pg-user. It appears to be the single argument of user that controls both. Does anyone know how to do this?

This page includes a list of the connection options, but it doesn't seem to offer a way to split the user names. The closest I'm seeing is the option to write my own sslfactory, and I'm really hoping to avoid that...

Backstory:

我已经得到了JDBC连接使用客户端证书Postgres在java我设置用户的属性,和司机是在密钥并将其沿。一切都好。

但我刚刚发现我不会用CN PG用户获得的证书。证书我会将有一个CN pg-user.xyz.foo.com &;pg-user.abc.foo.com。这看起来像用户名地图的工作。嘿,他们甚至有帮助,那将是完美的。

我得到了UNIX用户根登录到Postgres作为PG用户使用用户名映射和局部识别身份验证使用psql D DB U PG用户。但在这种情况下,Postgres既知道用户是根,并试图登录用户为PG。

Problem:

我不知道如何告诉Postgres的JDBC驱动程序从中攫取与CN pg-user.xyz.foo.com密钥的证书,但目前用户用户postgres PG。它似乎是用户的单一参数,控制两者。有人知道怎么做吗?

此页包含连接选项的列表,但它似乎不提供拆分用户名称的方法。最近我看到的是我自己写的sslfactory选项,我真的希望能避免…

answer1: 回答1:

Thanks to @harmic's comment I was able to solve this.

Starting with to following three files:

  • pg-user.pem which has a CN of pg-user.XYZ.foo.com
  • pg-user-chain.pem which contains the chain certs
  • pg-user.private_key which contains the private key for the cert

Then I created the pkcs12 file like this:

cat pg-user.pem pg-user-chain.pem > cert-and-chain.pem
openssl pkcs12 -export -out ssl_cert.p12 -in cert-and-chain.pem -name pg-user -inkey private_key.pem -passout pass:{password here}

After that I declared the user property for the connection to be pg-user, and it worked. In order to test, I altered the regexp in pg_ident to not match, then I could no longer log in, I changed it back and I could.

感谢@ harmic评论我能够解决这。

从以下三个文件开始:

  • pg-user.pem which has a CN of pg-user.XYZ.foo.com
  • pg-user-chain.pem which contains the chain certs
  • pg-user.private_key which contains the private key for the cert

然后我创建的PKCS12文件像这样:

cat pg-user.pem pg-user-chain.pem > cert-and-chain.pem
openssl pkcs12 -export -out ssl_cert.p12 -in cert-and-chain.pem -name pg-user -inkey private_key.pem -passout pass:{password here}

之后,我声明了用户属性的连接为PG用户,它的工作。为了测试,我在pg_ident改变正则表达式不匹配,那么我可以不再登录,我换回来,我可以。

postgresql  ssl  jdbc  x509certificate  client-certificates