Обсуждение: SSL Certificates in Windows 7 & Postgres 9.3
I am trying to connect using SSL in Windows 7 and Postgres v9.3. The console output error message is "Failed to establish a connection to 127.0.0.1" The error message from the pg_log is: ----------------------------------------------------------- 2014-12-15 19:20:24 GMT FATAL: connection requires a valid client certificate 2014-12-15 19:20:25 GMT FATAL: connection requires a valid client certificate 2014-12-15 19:20:26 GMT FATAL: no pg_hba.conf entry for host "127.0.0.1", user "SYSTEM", database "postgres", SSL off 2014-12-15 19:20:55 GMT LOG: could not accept SSL connection: No connection could be made because the target machine actively refused it. ----------------------------------------------------------- This is the connection string from the console app. string conStr = "Server=127.0.0.01; " + "User Id=my_role; " + "Password=''; " + "Database=dbname; " + "SSL=True; " + "Sslmode=Require; "; ----------------------------------------------------------- This is the pg_hba.conf hostssl all all 127.0.0.1/32 cert clientcert=1 hostssl all all ::1/128 cert clientcert=1 ----------------------------------------------------------- This is the postgresql.conf listen_addresses = '*' port = 5432 max_connections = 100 ssl = on ssl_ciphers = 'DEFAULT:!LOW:!EXP:!MD5:@STRENGTH' ssl_renegotiation_limit = 512MB ssl_cert_file = 'server.crt' ssl_key_file = 'server.key' ssl_ca_file = 'root.crt' password_encryption = off shared_buffers = 128MB ----------------------------------------------------------- I followed all documentation for creating the certificates, ie. ----------------------------------------------------------- Server Side openssl genrsa -des3 -out server.key 2048 openssl rsa -in server.key -out server.key openssl req -new –key server.key -days 3650 -out server.crt –config "D:\openssl\v9.8\openssl.cnf” ----------------------------------------------------------- Client Side openssl genrsa -des3 -out postgresql.key 2048 openssl rsa -in postgresql.key -out postgres.key openssl req -new -key postgresql.key -out postgresql.csr –config "D:\openssl\v9.8\openssl.cnf” copy server.crt root.crt openssl x509 -req -in postgresql.csr -CA root.crt -CAkey server.key -out postgresql.crt -CAcreateserial ---------------------------------------------------------- Windows Pkcs12 file: openssl pkcs12 -export -out postgrcli.p12 -name "My Certificate" -in postgresql.crt -inkey postgresql.key ----------------------------------------------------------- The Visual Studio solution includes as a project / reference, the source code of Npgsql v2.2.0. However, the program never reaches any of the breakpoints I put throughout the Npgsql code . ----------------------------------------------------------- I really need some help, please. Any suggestions? I have scoured the documentation and the internet. Maybe I can try a psql command. What would that command be with the certificate included? Thank you in advance. -- View this message in context: http://postgresql.nabble.com/SSL-Certificates-in-Windows-7-Postgres-9-3-tp5830749.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 12/15/2014 11:41 AM, harpagornis wrote: > I am trying to connect using SSL in Windows 7 and Postgres v9.3. The console > output error message is "Failed to establish a connection to 127.0.0.1" The > error message from the pg_log is: > ----------------------------------------------------------- > 2014-12-15 19:20:24 GMT FATAL: connection requires a valid client > certificate > 2014-12-15 19:20:25 GMT FATAL: connection requires a valid client > certificate > 2014-12-15 19:20:26 GMT FATAL: no pg_hba.conf entry for host "127.0.0.1", > user "SYSTEM", database "postgres", SSL off > 2014-12-15 19:20:55 GMT LOG: could not accept SSL connection: No connection > could be made because the target machine actively refused it. > ----------------------------------------------------------- > This is the connection string from the console app. > > string conStr = > "Server=127.0.0.01; " + > "User Id=my_role; " + > "Password=''; " + > "Database=dbname; " + > "SSL=True; " + > "Sslmode=Require; "; > > ----------------------------------------------------------- > This is the pg_hba.conf > hostssl all all 127.0.0.1/32 cert clientcert=1 > hostssl all all ::1/128 cert clientcert=1 > ----------------------------------------------------------- > This is the postgresql.conf > > listen_addresses = '*' > port = 5432 > max_connections = 100 > ssl = on > ssl_ciphers = 'DEFAULT:!LOW:!EXP:!MD5:@STRENGTH' > ssl_renegotiation_limit = 512MB > ssl_cert_file = 'server.crt' > ssl_key_file = 'server.key' > ssl_ca_file = 'root.crt' > password_encryption = off > shared_buffers = 128MB > ----------------------------------------------------------- > I followed all documentation for creating the certificates, ie. > ----------------------------------------------------------- > Server Side > openssl genrsa -des3 -out server.key 2048 > openssl rsa -in server.key -out server.key > openssl req -new –key server.key -days 3650 -out server.crt –config > "D:\openssl\v9.8\openssl.cnf” > ----------------------------------------------------------- > Client Side > openssl genrsa -des3 -out postgresql.key 2048 > openssl rsa -in postgresql.key -out postgres.key > openssl req -new -key postgresql.key -out postgresql.csr –config > "D:\openssl\v9.8\openssl.cnf” > copy server.crt root.crt > openssl x509 -req -in postgresql.csr -CA root.crt -CAkey server.key -out > postgresql.crt -CAcreateserial > ---------------------------------------------------------- > Windows Pkcs12 file: > openssl pkcs12 -export -out postgrcli.p12 -name "My Certificate" -in > postgresql.crt > -inkey postgresql.key > ----------------------------------------------------------- > The Visual Studio solution includes as a project / reference, the source > code of Npgsql v2.2.0. > However, the program never reaches any of the breakpoints I put throughout > the Npgsql code . > ----------------------------------------------------------- > I really need some help, please. Any suggestions? I have scoured the > documentation and the internet. Maybe I can try a psql command. What would > that command be with the certificate included? Thank you in advance. Did you set the CN of the client certificate to the user that you are connecting as. For a good run through/explanation see: http://www.howtoforge.com/postgresql-ssl-certificates > > > > -- > View this message in context: http://postgresql.nabble.com/SSL-Certificates-in-Windows-7-Postgres-9-3-tp5830749.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- Adrian Klaver adrian.klaver@aklaver.com
Yes, I set the CN to 127.0.0.1 for all certificates. I verified that for all certificates using openssl verify. I can connect o.k. without the SSL. -- View this message in context: http://postgresql.nabble.com/SSL-Certificates-in-Windows-7-Postgres-9-3-tp5830749p5830768.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 12/15/2014 01:13 PM, harpagornis wrote: > Yes, I set the CN to 127.0.0.1 for all certificates. I verified that for all > certificates using openssl verify. I can connect o.k. without the SSL. The CN needs to be the user not the IP address. Take a look at the link I sent earlier, it is a great help. > > > > -- > View this message in context: http://postgresql.nabble.com/SSL-Certificates-in-Windows-7-Postgres-9-3-tp5830749p5830768.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- Adrian Klaver adrian.klaver@aklaver.com
I changed the CN in all the certificates from 127.0.0.1 "my_role", which is the user id. Now the pg_log contains this: --------------------------------------------------------------------------------------- 2014-12-15 22:28:04 GMT LOG: database system was shut down at 2014-12-15 22:28:01 GMT 2014-12-15 22:28:04 GMT LOG: database system is ready to accept connections 2014-12-15 22:28:04 GMT LOG: autovacuum launcher started 2014-12-15 22:28:05 GMT FATAL: the database system is starting up 2014-12-15 22:28:06 GMT FATAL: connection requires a valid client certificate 2014-12-15 22:28:06 GMT FATAL: no pg_hba.conf entry for host "127.0.0.1", user "SYSTEM", database "postgres", SSL off -- View this message in context: http://postgresql.nabble.com/SSL-Certificates-in-Windows-7-Postgres-9-3-tp5830749p5830783.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
harpagornis wrote > I changed the CN in all the certificates from 127.0.0.1 "my_role", which > is the user id. Now the pg_log contains this: > --------------------------------------------------------------------------------------- > 2014-12-15 22:28:04 GMT LOG: database system was shut down at 2014-12-15 > 22:28:01 GMT > 2014-12-15 22:28:04 GMT LOG: database system is ready to accept > connections > 2014-12-15 22:28:04 GMT LOG: autovacuum launcher started > 2014-12-15 22:28:05 GMT FATAL: the database system is starting up > 2014-12-15 22:28:06 GMT FATAL: connection requires a valid client > certificate > 2014-12-15 22:28:06 GMT FATAL: no pg_hba.conf entry for host "127.0.0.1", > user "SYSTEM", database "postgres", SSL off The first question I'd ask is who this "SYSTEM" user is. I don't recall that any PostgreSQL code identifies itself as "SYSTEM" but given how quickly it attempts to connect I may simply be mistaken. Regardless, you either need to get "SYSTEM" to use an SSL client certificate or create an pg_hba.conf entry that will allow it to connect without one. David J. -- View this message in context: http://postgresql.nabble.com/SSL-Certificates-in-Windows-7-Postgres-9-3-tp5830749p5830784.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 12/15/2014 02:36 PM, harpagornis wrote: > I changed the CN in all the certificates from 127.0.0.1 "my_role", which is > the user id. Now the pg_log contains this: > --------------------------------------------------------------------------------------- > 2014-12-15 22:28:04 GMT LOG: database system was shut down at 2014-12-15 > 22:28:01 GMT > 2014-12-15 22:28:04 GMT LOG: database system is ready to accept connections > 2014-12-15 22:28:04 GMT LOG: autovacuum launcher started > 2014-12-15 22:28:05 GMT FATAL: the database system is starting up > 2014-12-15 22:28:06 GMT FATAL: connection requires a valid client > certificate > 2014-12-15 22:28:06 GMT FATAL: no pg_hba.conf entry for host "127.0.0.1", > user "SYSTEM", database "postgres", SSL off Well something is trying to connect not using SSL. Previously you showed your pg_hba.conf as: This is the pg_hba.conf hostssl all all 127.0.0.1/32 cert clientcert=1 hostssl all all ::1/128 cert clientcert=1 If that is all of it there is no provision for a non-SSL connection. The question then is who is "SYSTEM" user? Is it the same as my_role or is something else? Best guess is that there is a system user trying to connect in your setup. > > > > > -- > View this message in context: http://postgresql.nabble.com/SSL-Certificates-in-Windows-7-Postgres-9-3-tp5830749p5830783.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- Adrian Klaver adrian.klaver@aklaver.com
Yes, I did intend for only SSL connections. The console app must be the
SYSTEM user then, directly or maybe indirectly through the Windows
Certificate Store. I already added root.crt to the trusted certificates
through Windows MMC. Here is my console app, in which I provide the
certificate, so what else needs to be done?
-----------------------------------------------------------------------------
NpgsqlConnection conn = new NpgsqlConnection(conStr);
conn.ProvideClientCertificatesCallback += new
ProvideClientCertificatesCallback(MyProvideClientCertificates);
/*This callback simply returns true indicating you are accepting the server
certificate. Obviously, returning true without doing any validation should
be done for testing purposes only. */
conn.ValidateRemoteCertificateCallback += (a, b, c) => { return true; };
try
{
conn.Open();
System.Console.WriteLine("Connection opened");
}
catch (Exception e)
{
System.Console.WriteLine(e);
}
finally
{
conn.Close();
System.Console.ReadLine();
}
}
private static void MyProvideClientCertificates(X509CertificateCollection
clienteCertis)
{
const string clientcert = "d:\postgrclient.p12";
X509Certificate2 cert = new X509Certificate2(clientcert, "password",
X509KeyStorageFlags.PersistKeySet |
X509KeyStorageFlags.MachineKeySet);
Console.WriteLine(cert.HasPrivateKey);
clienteCertis.Add(cert);
}
--
View this message in context:
http://postgresql.nabble.com/SSL-Certificates-in-Windows-7-Postgres-9-3-tp5830749p5830786.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
harpagornis wrote > The console app must be the SYSTEM user then, directly or maybe indirectly > through the Windows Certificate Store. Doubtful. The log also shows the attempt is to access the "postgres" database while your provided connection string accesses "dbname" You should probably start over and build up a simple "hello world" level program that will let you more easily figure out which moving pieces are giving you grief. Start without SSL, get stuff working, then add SSL pieces one-by-one and checking application and PostgreSQL logs to see what reaction you get at each point. David J. -- View this message in context: http://postgresql.nabble.com/SSL-Certificates-in-Windows-7-Postgres-9-3-tp5830749p5830788.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 12/15/2014 02:36 PM, harpagornis wrote:
>> 2014-12-15 22:28:06 GMT FATAL: connection requires a valid client
>> certificate
>> 2014-12-15 22:28:06 GMT FATAL: no pg_hba.conf entry for host "127.0.0.1",
>> user "SYSTEM", database "postgres", SSL off
> Well something is trying to connect not using SSL.
It might be that libpq is trying an SSL connection, it's not working,
and it immediately tries a non-SSL connection. It'd likely be worth
turning on log_connections to help debug this --- I think, but not
totally sure, that would record any such extra connection attempt.
> The question then is who is "SYSTEM" user?
Yeah, that looks pretty fishy.
regards, tom lane
On 12/15/2014 03:25 PM, harpagornis wrote: > Yes, I did intend for only SSL connections. The console app must be the > SYSTEM user then, directly or maybe indirectly through the Windows > Certificate Store. I already added root.crt to the trusted certificates > through Windows MMC. Here is my console app, in which I provide the > certificate, so what else needs to be done? In addition to what David said I would probably ask on the Npgsql forum: http://pgfoundry.org/forum/forum.php?forum_id=519&group_id=1000140 -- Adrian Klaver adrian.klaver@aklaver.com
On 12/15/2014 03:49 PM, Tom Lane wrote: > Adrian Klaver <adrian.klaver@aklaver.com> writes: >> On 12/15/2014 02:36 PM, harpagornis wrote: >>> 2014-12-15 22:28:06 GMT FATAL: connection requires a valid client >>> certificate >>> 2014-12-15 22:28:06 GMT FATAL: no pg_hba.conf entry for host "127.0.0.1", >>> user "SYSTEM", database "postgres", SSL off > >> Well something is trying to connect not using SSL. > > It might be that libpq is trying an SSL connection, it's not working, > and it immediately tries a non-SSL connection. It'd likely be worth > turning on log_connections to help debug this --- I think, but not > totally sure, that would record any such extra connection attempt. > >> The question then is who is "SYSTEM" user? > > Yeah, that looks pretty fishy. The OP said they are running the code in a Visual Studio project. If I where to hazard a guess, what is happening is: The project is opening a connection as 'SYSTEM', outside the OPs code, to fetch system information from Postgres using the postgres database as the connection point. This connection is not SSL and as you say is failing over to non-SSL, except there is no non-SSL line in pg_hba.conf. > > regards, tom lane > > -- Adrian Klaver adrian.klaver@aklaver.com
Thank you all. Would something like Wireshark, WinPcap or WFetch show me more about the SYSTEM connection? -- View this message in context: http://postgresql.nabble.com/SSL-Certificates-in-Windows-7-Postgres-9-3-tp5830749p5830806.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Ah! I figured out the pg_log error about "No pg_hba.conf entry for host "127.0.0.1", user "SYSTEM", database "postgres", SSL off." That error occurs when I go into Windows Component Services and restart postgres. But, after I delete that pg_log file, there still is no connection and no other pg_log errors. The console output is still, "Failed to establish a connection to 127.0.0.1" I will post at PgFoundry. -- View this message in context: http://postgresql.nabble.com/SSL-Certificates-in-Windows-7-Postgres-9-3-tp5830749p5830842.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 12/15/2014 09:54 PM, harpagornis wrote: > Ah! I figured out the pg_log error about "No pg_hba.conf entry for host > "127.0.0.1", user "SYSTEM", database "postgres", SSL off." That error > occurs when I go into Windows Component Services and restart postgres. > > But, after I delete that pg_log file, there still is no connection and no > other pg_log errors. The console output is still, "Failed to establish a > connection to 127.0.0.1" I will post at PgFoundry. Can you connect outside of Npgsl using psql? > > > > -- > View this message in context: http://postgresql.nabble.com/SSL-Certificates-in-Windows-7-Postgres-9-3-tp5830749p5830842.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- Adrian Klaver adrian.klaver@aklaver.com
Right, I want to try connecting by psql, but the postgres documentation and "psql -- help" do not list the syntax for providing the certificate. I tried the following, but the error was : "Connection requires a valid certificate" ---------------------------------------------------------------- $ psql postgresql://dbmaster:5433/mydb?sslmode=require ---------------------------------------------------------------- I also tried PgAdminIII after entering the certificate names in the properties page for the localhost. The error in pg_log was, "could not accept SSL connection: no certificate returned" I also got this error message on the screen from the PgAdminIII program, "Error connecting to the server: SSL error tlsv1 alert unknown ca." -- View this message in context: http://postgresql.nabble.com/SSL-Certificates-in-Windows-7-Postgres-9-3-tp5830749p5830955.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
O.K. I just found the environment variables for SSL, described in Man 31.14. I will try that. -- View this message in context: http://postgresql.nabble.com/SSL-Certificates-in-Windows-7-Postgres-9-3-tp5830749p5830961.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Trying to connect via psql, I looked at the documentation and around the web, but could not find the right syntax for including all the SSL connection variables on the psql command line. I tried using the URL-type psql command, variations of this: -------------------------------------------------------------- psql postgresql://localhost:5432/mydb?sslmode=require?sslcert=postgresql.crt -------------------------------------------------------------- I wasn't sure if the question mark is supposed to precede each variable (sslmode, sslcert, etc.)? Since I could not get that syntax right, then I tried connecting with psql the other way: --------------------------------------------------------------- psql dbname=dbname user=my_role sslmode=verify-full sslcert=postgresql.crt sslkey=postgresql.key sslrootcert=root.crt ---------------------------------------------------------------- The console output was: "Warning command-line argument sslmode=verify-full ignored" "Warning command-line argument sslcert=postgresql.crt ignored" "Warning command-line argument sslkey=postgresql.key ignored" "Warning command-line argument sslrootcert=root.crt ignored" ---------------------------------------------------------------- After lots of typing, and getting nowhere with either of those two methods, I edited pg_env.bat to this: ------------------------------------------------------------ @SET PATH="C:\Program Files\PostgreSQL\9.3\bin";%PATH% @SET PGDATA=D:\PostgresDat @SET PGDATABASE=postgres @SET PGUSER=postgres @SET PGPORT=5432 @SET PGSSLCERT=D:\POSTGRESDAT\POSTGRESQL.CRT @SET PGSSLKEY=D:\POSTGRESDAT\POSTGRESQL.KEY @SET PGSSLROOTCERT=D:\POSTGRESDAT\ROOT.CRT @SET PGSSLMODE=VERIFY-CA @SET PGLOCALEDIR=C:\Program Files\PostgreSQL\9.3\share\locale ---------------------------------------------------------------- Then, I tried psql again, with fewer variables, like this: 'psql -d dbname -U my_role'. The console output was this: ---------------------------------------------------------------- Sever closed the connection unexpectedly. This probably means the server terminated abnormally before or while processing the request. ---------------------------------------------------------------- The pg_log had this: the database system is starting up autovacuum launcher started connection requires a valid client certificate no pg_hba.conf entry for host "127.0.0.1", user "SYSTEM", database "postgres", SSL off connection requires a valid client certificate connection requires a valid client certificate ---------------------------------------------------------------- Any suggestions as to the correct psql syntax for connecting with SSL? Thank you. -- View this message in context: http://postgresql.nabble.com/SSL-Certificates-in-Windows-7-Postgres-9-3-tp5830749p5830985.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
harpagornis wrote > psql dbname=dbname user=my_role sslmode=verify-full sslcert=postgresql.crt > sslkey=postgresql.key sslrootcert=root.crt This is a psql command with 6 input arguments/options specified psql "dbname=dbname [...]" is a psql command with 1 input argument/option specified which is treated as a "conninfo" string as documented at: http://www.postgresql.org/docs/9.3/static/app-psql.html > I wasn't sure if the question mark is supposed to precede each variable > (sslmode, sslcert, etc.)? You guessed wrong...admittedly the documentation assumes the reader knows how the format of a URI...separate key=value pairs in the "query" part of the URI (which is separated from the path by the aforementioned "?") are separated by ";" or "&" http://en.wikipedia.org/wiki/URI_scheme David J. -- View this message in context: http://postgresql.nabble.com/SSL-Certificates-in-Windows-7-Postgres-9-3-tp5830749p5830993.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Unless I am missing something, I still do not see where how / where to specify sslcert, sslkey, root.crt in the psql command. The referenced links don't much go beyond these input variables [ dbname [ username ] [ host ] [ port ] . -- View this message in context: http://postgresql.nabble.com/SSL-Certificates-in-Windows-7-Postgres-9-3-tp5830749p5831006.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
harpagornis <shenlong@runbox.com> writes: > Unless I am missing something, I still do not see where how / where to > specify sslcert, sslkey, root.crt in the psql command. You don't. The SSL certificates are stored in files whose names are known to the psql code. See http://www.postgresql.org/docs/9.3/static/libpq-ssl.html#LIBPQ-SSL-CLIENTCERT regards, tom lane
harpagornis wrote > @SET PATH="C:\Program Files\PostgreSQL\9.3\bin";%PATH% > @SET PGDATA=D:\PostgresDat > @SET PGDATABASE=postgres > @SET PGUSER=postgres > @SET PGPORT=5432 > @SET PGSSLCERT=D:\POSTGRESDAT\POSTGRESQL.CRT > @SET PGSSLKEY=D:\POSTGRESDAT\POSTGRESQL.KEY > @SET PGSSLROOTCERT=D:\POSTGRESDAT\ROOT.CRT > @SET PGSSLMODE=VERIFY-CA > @SET PGLOCALEDIR=C:\Program Files\PostgreSQL\9.3\share\locale I get your frustration, and the tendency to resort to "kitchen sink" experimentation, but I have to ask whether you understand the difference between Server SSL and Client SSL... Setting PGDATA does nothing for the client and putting client SSL stuff inside the server's "data" directory doesn't make any sense. David J. -- View this message in context: http://postgresql.nabble.com/SSL-Certificates-in-Windows-7-Postgres-9-3-tp5830749p5831010.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
I understand all of that, about certificates. I only included all of the pg_env.bat file for completeness. You understand that I am trying to connect to the database by using just psql? -- View this message in context: http://postgresql.nabble.com/SSL-Certificates-in-Windows-7-Postgres-9-3-tp5830749p5831018.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
harpagornis wrote > I understand all of that, about certificates. I only included all of the > pg_env.bat file for completeness. You understand that I am trying to > connect to the database by using just psql? Yes, and are doing so with non-default locations for pretty much everything. Given that I've never actually done this myself I am only of limited helpfulness. Mostly I'm trying to point out things that seem strange/wrong in hopes that you or someone else will have an ah-ha! moment. I'm also a few years removed from Windows deployment at this point... Have you confirmed that everything you have works without SSL? At this point you've thrown so much stuff at us that its hard to tell exactly what you have going on. If you can play with a clean system and make use of standard/default configurations you can at least get something provably working then figure out how to set it up in a custom arrangement. David J. -- View this message in context: http://postgresql.nabble.com/SSL-Certificates-in-Windows-7-Postgres-9-3-tp5830749p5831020.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Yes, I have tested that the connection and it does work without SSL. I have also verified with openssl that all the certificates have the same CN, issuer, etc. I am working in a development environment, hence the seemingly odd file location. It may not seem that way, but I am narrowing down the issues. My original post was about connecting using SSL inside a Visual Studio console app. I also initially inquired about, and someone else also suggested, attempting to connect outside of the console app, just using psql with the certificates. That would to remove any issues that may be arising with references inside the console app. So I still think trying to connect using psql is something relatively simple worth trying. -- View this message in context: http://postgresql.nabble.com/SSL-Certificates-in-Windows-7-Postgres-9-3-tp5830749p5831022.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
To anyone following this thread, I would also like to point out the following, from Man 31.18.1. In verify-full mode, the cn (Common Name) attribute of the certificate is matched against the host name. If the cn attribute starts with an asterisk (*), it will be treated as a wildcard, and will match all characters except a dot (.). This means the certificate will not match subdomains. If the connection is made using an IP address instead of a host name, the IP address will be matched (without doing any DNS lookups). ----------------------------------------------------------------- So it seems that when creating self-signed certificates for use in verify-full mode, the CN is not the user id, but instead, the host name, ie. 127.0.0.1, which is what I had. -- View this message in context: http://postgresql.nabble.com/SSL-Certificates-in-Windows-7-Postgres-9-3-tp5830749p5831037.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 12/16/2014 08:56 PM, harpagornis wrote: > To anyone following this thread, I would also like to point out the > following, from Man 31.18.1. > > In verify-full mode, the cn (Common Name) attribute of the certificate is > matched against the host name. If the cn attribute starts with an asterisk > (*), it will be treated as a wildcard, and will match all characters except > a dot (.). This means the certificate will not match subdomains. If the > connection is made using an IP address instead of a host name, the IP > address will be matched (without doing any DNS lookups). > ----------------------------------------------------------------- > So it seems that when creating self-signed certificates for use in > verify-full mode, the CN is not the user id, but instead, the host name, ie. > 127.0.0.1, which is what I had. That is true for the server certificate, but for the client certificate you need the CN=username. Run through the below again: http://www.howtoforge.com/postgresql-ssl-certificates > > > > -- > View this message in context: http://postgresql.nabble.com/SSL-Certificates-in-Windows-7-Postgres-9-3-tp5830749p5831037.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- Adrian Klaver adrian.klaver@aklaver.com
Thank you. That resolved it. After revising the certificates, I was able to connect with psql. I really appreciate all the help. -- View this message in context: http://postgresql.nabble.com/SSL-Certificates-in-Windows-7-Postgres-9-3-tp5830749p5831051.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Some more questions if you please. I am in Houston, and we don't have a postgres users group. If anyone knows of some postgres groups that have webcasts of their meetings, let me know. Thanks. I am trying to figure out how to manage the SSL client certificates in a Windows 7 environment, both in development mode and alternatively, a working / production environment. Lets say I have several application databases, and each database has one or more user ids for logging in, then I will need to create a separate client certificate for each user id that is being used to login. Correct? Since the client certificate is always named postgresql.crt, then I will need to place these variations of the postgresql.crt file in separate folders. Correct? The location that postgres looks for the server and client and root certificates is the location specified in the environmental variable, Search_Path. Correct? So in a development environment, I will need to manually edit the PG_DATA and Search_Path environmental variables and restart the postgres service whenever I want to test connecting with a different postgresql.crt? And, in a working / production environment, I will need to programmatically set the PG_DATA and Search_path environmental variables and restart postgres? Are there other methods/ practices commonly used for these operations? Thank you in advance. -- View this message in context: http://postgresql.nabble.com/SSL-Certificates-in-Windows-7-Postgres-9-3-tp5830749p5831182.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 12/17/2014 11:38 AM, harpagornis wrote: > Some more questions if you please. I am in Houston, and we don't have a > postgres users group. If anyone knows of some postgres groups that have > webcasts of their meetings, let me know. Thanks. > > I am trying to figure out how to manage the SSL client certificates in a > Windows 7 environment, both in development mode and alternatively, a working > / production environment. Lets say I have several application databases, > and each database has one or more user ids for logging in, then I will need > to create a separate client certificate for each user id that is being used > to login. Correct? Yes. > > Since the client certificate is always named postgresql.crt, then I will > need to place these variations of the postgresql.crt file in separate > folders. Correct? Well the client certificate is for a user, so it needs to be in the users home directory. > > The location that postgres looks for the server and client and root > certificates is the location specified in the environmental variable, > Search_Path. Correct? As far as I know there is no such env variable. In any case search_path if for schema discovery in a database. For a list of the env variables see: http://www.postgresql.org/docs/9.3/static/libpq-envars.html > > So in a development environment, I will need to manually edit the PG_DATA > and Search_Path environmental variables and restart the postgres service > whenever I want to test connecting with a different postgresql.crt? Well there is no search_path env variable to set and PG_DATA is PGDATA. PGDATA is for the cluster data directory and postgresql.crt is the client certificate. Not sure why you would edit PGDATA or restart Postgres? > > And, in a working / production environment, I will need to programmatically > set the PG_DATA and Search_path environmental variables and restart > postgres? Are there other methods/ practices commonly used for these > operations? Thank you in advance. See above. > > > > -- > View this message in context: http://postgresql.nabble.com/SSL-Certificates-in-Windows-7-Postgres-9-3-tp5830749p5831182.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- Adrian Klaver adrian.klaver@aklaver.com
> Are there other methods/ practices commonly used for these operations? > Thank you in advance. You've got some serious confusion between client/server and other things going on here...Adrian addressed those. It might help to think of the fact that the clients and server are typically not on the same physical machine. They should NOT be sharing ANY configuration files between each other. It may be they happen to have identical copies of a given file but those copies should be in different locations. Oh, And where did you get the idea that "search_path" had anything to do with this? You should probably look into using a pg_service.conf file on the client. http://www.postgresql.org/docs/9.0/static/libpq-pgservice.html On the server side of things: create virtual machines Since the server only has a single identity there is no obvious need or provision to have it provide alternative names to the SSL-related files that it uses. The client, though, can assume multiple identities and so while there is a set of default file names there are ways to override those - via environment variables or conninfo settings (which is where pg_service.conf comes in). If the servers and clients share a trusted signing chain some degree of "sharing" can be achieved but the only thing being checked then is global identity. (in theory...) It is possible to setup distinct chains so that development clients cannot connect to production servers using the same set of credentials that they use to connect to development servers. David J. -- View this message in context: http://postgresql.nabble.com/SSL-Certificates-in-Windows-7-Postgres-9-3-tp5830749p5831208.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
I am developing a Windows desktop application so the client is the desktop application. Depending on the installation, the app will be running in single user mode, with the server and client both on only one machine, using 127.0.0.1. In that type of installation, there is little or no reason the server and the client cannot share configuration files. Alternatively, the app could be installed for multiple users on a network server. Even in that type of installation, is there some reason that the client should never access server configuration files? Regarding the search_path, when I connected with psql and certificates, I was only able to do so after I copied the certificates and keys into the Postgres folder located in the Roaming folder, located in the AppData folder, located in my Windows User name folder, located in the Windows system Users folder. I thought I read somewhere that particular folder derives from the search_path setting. -- View this message in context: http://postgresql.nabble.com/SSL-Certificates-in-Windows-7-Postgres-9-3-tp5830749p5831232.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 12/17/2014 10:14 PM, harpagornis wrote: > I am developing a Windows desktop application so the client is the desktop > application. Depending on the installation, the app will be running in > single user mode, with the server and client both on only one machine, using > 127.0.0.1. In that type of installation, there is little or no reason the > server and the client cannot share configuration files. Alternatively, the > app could be installed for multiple users on a network server. Even in that > type of installation, is there some reason that the client should never > access server configuration files? the client has no USE for the server configuration files, there's NOTHING in there of interest to him, or that he should know that he can't figure out by querying the server.. we're talking about SSL here. there's the *SERVER* public and private key, and there's the *USER* public and private key. THESE ARE DIFFERENT, even if you're doing SSL to/from the same system (it can be argued that using SSL on localhost is a waste of time, but whatever). -- john r pierce 37N 122W somewhere on the middle of the left coast
On 12/17/2014 10:14 PM, harpagornis wrote: > I am developing a Windows desktop application so the client is the desktop > application. Depending on the installation, the app will be running in > single user mode, with the server and client both on only one machine, using > 127.0.0.1. In that type of installation, there is little or no reason the > server and the client cannot share configuration files. Alternatively, the > app could be installed for multiple users on a network server. Even in that > type of installation, is there some reason that the client should never > access server configuration files? Yes, security. In any case for what you are trying to do the server configuration is not something the client has to fool with. > > Regarding the search_path, when I connected with psql and certificates, I > was only able to do so after I copied the certificates and keys into the > Postgres folder located in the Roaming folder, located in the AppData > folder, located in my Windows User name folder, located in the Windows > system Users folder. I thought I read somewhere that particular folder > derives from the search_path setting. > It is important to remember that SSL is not provided by Postgres, it just has the ability to use SSL to make a connection. So it just piggybacks on an existing program. In doing that it makes certain assumptions to start with. For server side: http://www.postgresql.org/docs/9.3/static/ssl-tcp.html For libpq clients: http://www.postgresql.org/docs/9.3/static/libpq-ssl.html Important the above is for libpq based clients such as psql. I am not sure how non-libpq clients such as the Postgres JDBC client handle this. The beginning assumptions can be modified by setting environment variables(again this is for libpq clients): http://www.postgresql.org/docs/9.3/static/libpq-envars.html or the postgresql.conf http://www.postgresql.org/docs/9.3/static/runtime-config-connection.html#RUNTIME-CONFIG-CONNECTION-SECURITY For information on what search_path really does see: http://www.postgresql.org/docs/9.3/static/runtime-config-client.html > > > > -- > View this message in context: http://postgresql.nabble.com/SSL-Certificates-in-Windows-7-Postgres-9-3-tp5830749p5831232.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- Adrian Klaver adrian.klaver@aklaver.com
Thank you all. You have given me a lot to digest for my project. -- View this message in context: http://postgresql.nabble.com/SSL-Certificates-in-Windows-7-Postgres-9-3-tp5830749p5831358.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.