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

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/&gt;

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/&gt;

---------------------------(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"

LOG Say:

LOG: conexión recibida: host=192.168.0.100 port=1175
FATAL: no hay una línea en pg_hba.conf para «192.168.0.100»,
usuario «vasa», base de datos «vasa», SSL inactivo
LOG: conexión recibida: host=192.168.0.100 port=1226
FATAL: no hay una línea en pg_hba.conf para «192.168.0.100»,
usuario «vasa», base de datos «vasa», SSL inactiv

but SSl its active

select * from pg_settings where name = 'ssl'

ssl on Connections and Authentication / Security and Authentication
Enables SSL connections.
postmaster bool configuration file

thank you

2005/8/8, Mario Soto Cordones - Venezuela <msotocl@gmail.com>:

Hola Alvaro no po lostee en espanol porque pense que este problema
correspondia a la lista de admin, o me equivoco

Saludos

2005/8/8, Alvaro Herrera <alvherre@alvh.no-ip.org>:

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"

--
cordialmente,

Ing. Mario Soto Cordones

--
cordialmente,

Ing. Mario Soto Cordones

Mario Soto Cordones - Venezuela <msotocl@gmail.com> writes:

LOG Say:
LOG: conexión recibida: host=192.168.0.100 port=1175
FATAL: no hay una línea en pg_hba.conf para «192.168.0.100»,
usuario «vasa», base de datos «vasa», SSL inactivo
LOG: conexión recibida: host=192.168.0.100 port=1226
FATAL: no hay una línea en pg_hba.conf para «192.168.0.100»,
usuario «vasa», base de datos «vasa», SSL inactiv

but SSl its active

What that's showing is that the client isn't trying to use SSL. So
either you have client-side code that's not SSL-aware at all, or the
configuration problem is on the client side.

regards, tom lane

On Mon, Aug 08, 2005 at 11:41:24AM -0400, Mario Soto Cordones - Venezuela wrote:

LOG Say:

LOG: conexión recibida: host=192.168.0.100 port=1175
FATAL: no hay una línea en pg_hba.conf para «192.168.0.100»,
usuario «vasa», base de datos «vasa», SSL inactivo
LOG: conexión recibida: host=192.168.0.100 port=1226
FATAL: no hay una línea en pg_hba.conf para «192.168.0.100»,
usuario «vasa», base de datos «vasa», SSL inactiv

but SSl its active

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.)

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"Las cosas son buenas o malas segun las hace nuestra opini�n" (Lisias)

2005/8/8, Alvaro Herrera <alvherre@alvh.no-ip.org>:

On Mon, Aug 08, 2005 at 11:41:24AM -0400, Mario Soto Cordones - Venezuela wrote:

LOG Say:

LOG: conexión recibida: host=192.168.0.100 port=1175
FATAL: no hay una línea en pg_hba.conf para «192.168.0.100»,
usuario «vasa», base de datos «vasa», SSL inactivo
LOG: conexión recibida: host=192.168.0.100 port=1226
FATAL: no hay una línea en pg_hba.conf para «192.168.0.100»,
usuario «vasa», base de datos «vasa», SSL inactiv

but SSl its active

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

thank

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"Las cosas son buenas o malas segun las hace nuestra opinión" (Lisias)

--
cordialmente,

Ing. Mario Soto Cordones

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>:

On Mon, Aug 08, 2005 at 11:41:24AM -0400, Mario Soto Cordones - Venezuela wrote:

LOG Say:

LOG: conexión recibida: host=192.168.0.100 port=1175
FATAL: no hay una línea en pg_hba.conf para «192.168.0.100»,
usuario «vasa», base de datos «vasa», SSL inactivo
LOG: conexión recibida: host=192.168.0.100 port=1226
FATAL: no hay una línea en pg_hba.conf para «192.168.0.100»,
usuario «vasa», base de datos «vasa», SSL inactiv

but SSl its active

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?

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"La felicidad no es ma�ana. La felicidad es ahora"

2005/8/8, Alvaro Herrera <alvherre@alvh.no-ip.org>:

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>:

On Mon, Aug 08, 2005 at 11:41:24AM -0400, Mario Soto Cordones - Venezuela wrote:

LOG Say:

LOG: conexión recibida: host=192.168.0.100 port=1175
FATAL: no hay una línea en pg_hba.conf para «192.168.0.100»,
usuario «vasa», base de datos «vasa», SSL inactivo
LOG: conexión recibida: host=192.168.0.100 port=1226
FATAL: no hay una línea en pg_hba.conf para «192.168.0.100»,
usuario «vasa», base de datos «vasa», SSL inactiv

but SSl its active

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?

the EMS it's professional edition licenced

from the server I connect with psql and makes it

bash-3.00$ psql -U vasa -h 192.168.0.2 vasa
Contraseña:
Bienvenido a psql 8.0.3, el terminal interactivo de PostgreSQL.

Digite: \copyright para ver los tÃ(c)rminos de distribución
\h para obtener ayuda sobre comandos SQL
\? para obtener ayuda sobre comandos internos
\g o punto y coma (;) para ejecutar consulta
\q para salir

conexión SSL (cifrado: DHE-RSA-AES256-SHA, bits: 256)

this is correct but I am working direct in the server, not from an
application client like visual basic for example.

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"La felicidad no es mañana. La felicidad es ahora"

--
cordialmente,

Ing. Mario Soto Cordones

On Mon, Aug 08, 2005 at 01:03:03PM -0400, Mario Soto Cordones - Venezuela wrote:

from the server I connect with psql and makes it

bash-3.00$ psql -U vasa -h 192.168.0.2 vasa
Contraseña:
Bienvenido a psql 8.0.3, el terminal interactivo de PostgreSQL.

Digite: \copyright para ver los t�(c)rminos de distribución
\h para obtener ayuda sobre comandos SQL
\? para obtener ayuda sobre comandos internos
\g o punto y coma (;) para ejecutar consulta
\q para salir

conexión SSL (cifrado: DHE-RSA-AES256-SHA, bits: 256)

Ok, so it works with psql and the server is configured correctly.
Congratulations!

2005/8/8, Alvaro Herrera <alvherre@alvh.no-ip.org>:

Maybe you have to enable it explicitly? Maybe it's not supported in the
free (lite) version? Again, did you try with psql?

the EMS it's professional edition licenced

Since you have a EMS license, you can complain to those guys, since
obviously the problem is with their client software.

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"Es fil�sofo el que disfruta con los enigmas" (G. Coli)

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)