Thread: postgresql Secure Mode
Hi, i have a probem,. I am trying to configure postgresql in sure way, I have made the following thing: 1. - I have created the certificate and put this in the directory it data 2. - given him privileges to the user postgresql for the certificate 3. - in the file postgresql.conf, modify the parameters ssl = true to use md5 = true 4. - in the file pg_hba.conf adds one it lines this way hostssl all all 192.168.0.0/255.255.255.0 md5 5. - stsrt postresql But when trying to connect me says that the user cannot authenticate some idea thank you very much -- cordialmente, Ing. Mario Soto Cordones -- cordialmente, Ing. Mario Soto Cordones
> Hi, > > i have a probem,. > > I am trying to configure postgresql in sure way, I have made the > following thing: > > 1. - I have created the certificate and put this in the directory it data > 2. - given him privileges to the user postgresql for the certificate > 3. - in the file postgresql.conf, modify the parameters ssl = true to > use md5 = true > 4. - in the file pg_hba.conf adds one it lines this way hostssl all > all 192.168.0.0/255.255.255.0 md5 > 5. - stsrt postresql > > But when trying to connect me says that the user cannot authenticate > > some idea Hmm. Are you certain you had the password for the user stored somewhere? e.g... 1. Stored in the file $HOME/.pgpass? 2. Stored in some relevant environment variable ($PGPASS)? 3. Included into the DSN (e.g. - 'dbname=this password=something') Having the cert doesn't automatically authenticate anything... -- let name="cbbrowne" and tld="acm.org" in String.concat "@" [name;tld];; http://cbbrowne.com/info/rdbms.html Rules of the Evil Overlord #153. "My Legions of Terror will be an equal-opportunity employer. Conversely, when it is prophesied that no man can defeat me, I will keep in mind the increasing number of non-traditional gender roles." <http://www.eviloverlord.com/>
Hi , not in fact I don't have a password file, I wanted to use autentificacion PAM, I have attempted it but I have not gotten it, on the other hand I don't understand that of the passwords file. Greetings and thank you Mario Soto 2005/8/7, Christopher Browne <cbbrowne@acm.org>: > > Hi, > > > > i have a probem,. > > > > I am trying to configure postgresql in sure way, I have made the > > following thing: > > > > 1. - I have created the certificate and put this in the directory it data > > 2. - given him privileges to the user postgresql for the certificate > > 3. - in the file postgresql.conf, modify the parameters ssl = true to > > use md5 = true > > 4. - in the file pg_hba.conf adds one it lines this way hostssl all > > all 192.168.0.0/255.255.255.0 md5 > > 5. - stsrt postresql > > > > But when trying to connect me says that the user cannot authenticate > > > > some idea > > Hmm. Are you certain you had the password for the user stored > somewhere? > > e.g... > > 1. Stored in the file $HOME/.pgpass? > 2. Stored in some relevant environment variable ($PGPASS)? > 3. Included into the DSN (e.g. - 'dbname=this password=something') > > Having the cert doesn't automatically authenticate anything... > -- > let name="cbbrowne" and tld="acm.org" in String.concat "@" [name;tld];; > http://cbbrowne.com/info/rdbms.html > Rules of the Evil Overlord #153. "My Legions of Terror will be an > equal-opportunity employer. Conversely, when it is prophesied that no > man can defeat me, I will keep in mind the increasing number of > non-traditional gender roles." <http://www.eviloverlord.com/> > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- cordialmente, Ing. Mario Soto Cordones
On Mon, Aug 08, 2005 at 08:44:44AM -0400, Mario Soto Cordones - Venezuela wrote: > Hi , > not in fact I don't have a password file, I wanted to use > autentificacion PAM, I have attempted it but I have not gotten it, on > the other hand I don't understand that of the passwords file. Can you post step-by-step instructions on how you set your system up? You hadn't mentioned PAM in your first report, and you didn't say what certificate you created and how. Also, you didn't show the relevant server log extract. Did you create a client certificate? Did you create the PAM user in Postgres? -- Alvaro Herrera (<alvherre[a]alvh.no-ip.org>) "Entristecido, Wutra (canción de Las Barreras) echa a Freyr a rodar y a nosotros al mar"
Hello, what comments of PAM, was alone that, a comment, since try to make it and I don't work. now I detail them the carried out steps. 1.- make the digital certificate, and it places it in the directory it data of postgresql. openssl req -new -text -out server.req openssl rsa -in privkey.pem -out server.key rm privkey.pem openssl req -x509 -in server.req -text -key server.key -out server.crt chown postgres:postgres server.key && chmod og-rwx server.key 2.- configure postgresql.conf ssl=true password_encryption = true 3.- configure pg_hba.conf hostssl all all 192.168.0.0 255.255.255.0 MD5 4.- service postgresql stop 5.- service postgresql start but when I try to connect myself for example from an application EMS postgresql manager by means of SSL, says that it cannot authenticate in user Thank, you very much 2005/8/8, Alvaro Herrera <alvherre@alvh.no-ip.org>: > On Mon, Aug 08, 2005 at 08:44:44AM -0400, Mario Soto Cordones - Venezuela wrote: > > Hi , > > not in fact I don't have a password file, I wanted to use > > autentificacion PAM, I have attempted it but I have not gotten it, on > > the other hand I don't understand that of the passwords file. > > Can you post step-by-step instructions on how you set your system up? > You hadn't mentioned PAM in your first report, and you didn't say what > certificate you created and how. Also, you didn't show the relevant > server log extract. Did you create a client certificate? Did you > create the PAM user in Postgres? > > -- > Alvaro Herrera (<alvherre[a]alvh.no-ip.org>) > "Entristecido, Wutra (canción de Las Barreras) > echa a Freyr a rodar > y a nosotros al mar" > -- cordialmente, Ing. Mario Soto Cordones
On Mon, Aug 08, 2005 at 10:24:54AM -0400, Mario Soto Cordones - Venezuela wrote: > 3.- configure pg_hba.conf > > hostssl all all 192.168.0.0 255.255.255.0 MD5 I assume the connection is coming from within this network; is that right? (What's the IP address of the client machine?) > 4.- service postgresql stop > 5.- service postgresql start > > but when I try to connect myself for example from an application EMS > postgresql manager by means of SSL, says that it cannot authenticate > in user Ok, so do you have the user created in Postgres by means of CREATE USER or createuser? Try changing the password. Also, please show us the relevant extract of the server log file. -- Alvaro Herrera (<alvherre[a]alvh.no-ip.org>) "[PostgreSQL] is a great group; in my opinion it is THE best open source development communities in existence anywhere." (Lamar Owen)
On Mon, Aug 08, 2005 at 10:24:54AM -0400, Mario Soto Cordones - Venezuela wrote: > 3.- configure pg_hba.conf > > hostssl all all 192.168.0.0 255.255.255.0 MD5 Do you have "MD5" (uppercase) or "md5" (lowercase)? Using uppercase on my 8.0.3 system generates an error in the postmaster logs: LOG: invalid entry in file ".../pg_hba.conf" at line 72, token "MD5" -- Michael Fuhr
2005/8/8, Alvaro Herrera <alvherre@alvh.no-ip.org>: > On Mon, Aug 08, 2005 at 10:24:54AM -0400, Mario Soto Cordones - Venezuela wrote: > > > 3.- configure pg_hba.conf > > > > hostssl all all 192.168.0.0 255.255.255.0 MD5 > > I assume the connection is coming from within this network; is that > right? (What's the IP address of the client machine?) Yes, example ip machine 192.168.0.100 > > > 4.- service postgresql stop > > 5.- service postgresql start > > > > but when I try to connect myself for example from an application EMS > > postgresql manager by means of SSL, says that it cannot authenticate > > in user > > Ok, so do you have the user created in Postgres by means of CREATE USER > or createuser? Try changing the password. Also, please show us the > relevant extract of the server log file. > yes expample the user is vasa the log say LOG: no se pudo cargar el archivo del certificado raiz /var/lib/pgsql/data/bd/root.crt DETALLE: Los certificados de clientes no se verificaran but I don't understand because it leaves that message Thank you very much > -- > Alvaro Herrera (<alvherre[a]alvh.no-ip.org>) > "[PostgreSQL] is a great group; in my opinion it is THE best open source > development communities in existence anywhere." (Lamar Owen) > -- cordialmente, Ing. Mario Soto Cordones
uppercase but not say error thank you 2005/8/8, Michael Fuhr <mike@fuhr.org>: > On Mon, Aug 08, 2005 at 10:24:54AM -0400, Mario Soto Cordones - Venezuela wrote: > > 3.- configure pg_hba.conf > > > > hostssl all all 192.168.0.0 255.255.255.0 MD5 > > Do you have "MD5" (uppercase) or "md5" (lowercase)? Using uppercase > on my 8.0.3 system generates an error in the postmaster logs: > > LOG: invalid entry in file ".../pg_hba.conf" at line 72, token "MD5" > > -- > Michael Fuhr > -- cordialmente, Ing. Mario Soto Cordones
On Mon, Aug 08, 2005 at 11:06:22AM -0400, Mario Soto Cordones - Venezuela wrote: > 2005/8/8, Alvaro Herrera <alvherre@alvh.no-ip.org>: > > On Mon, Aug 08, 2005 at 10:24:54AM -0400, Mario Soto Cordones - Venezuela wrote: > > > but when I try to connect myself for example from an application EMS > > > postgresql manager by means of SSL, says that it cannot authenticate > > > in user > > > > Ok, so do you have the user created in Postgres by means of CREATE USER > > or createuser? Try changing the password. Also, please show us the > > relevant extract of the server log file. > > yes expample the user is vasa > > the log say > > LOG: no se pudo cargar el archivo del certificado raiz > /var/lib/pgsql/data/bd/root.crt > DETALLE: Los certificados de clientes no se verificaran This isn't the problem. Here it's only telling you that were the client to hand a certificate, they wouldn't be checked. There must be other message. > but I don't understand because it leaves that message It's because you don't have the root.crt file, or the file doesn't have the proper permissions. I don't understand why didn't you post this problem to pgsql-es-ayuda first ... -- Alvaro Herrera (<alvherre[a]alvh.no-ip.org>) Y dijo Dios: "Que sea Satanás, para que la gente no me culpe de todo a mí." "Y que hayan abogados, para que la gente no culpe de todo a Satanás"
On Mon, Aug 08, 2005 at 12:40:24PM -0400, Alvaro Herrera wrote: > On Mon, Aug 08, 2005 at 12:24:14PM -0400, Mario Soto Cordones - Venezuela wrote: > > 2005/8/8, Alvaro Herrera <alvherre@alvh.no-ip.org>: > > > > > Yeah, the server thinks it's active, but the client doesn't know it. > > > Did you try connecting with psql? Maybe your EMS client does not > > > support SSL (it'd surprise me.) > > > > Yes EMS suport this but say unable to autenticate user > > Maybe you have to enable it explicitly? Maybe it's not supported in the > free (lite) version? Again, did you try with psql? On private email exchange we found out that the culprit is pgOleDb, which doesn't support SSL connections. -- Alvaro Herrera (<alvherre[a]alvh.no-ip.org>) "There was no reply" (Kernel Traffic)