Cannot connect to remote postgres database
I have been asked to evaluate Oracle, mysql and postgresql as a possible
replacement for our existing Oracle and MsSQL databases. Oracle and
mysql I pretty much have covered. Postgresql, OTOH, is somewhat less
cooperative.
I have the software (v 8.4.13) installed on 64 bit Centos 6. It is
listening on all available interfaces and netstat confirms this. I
created an additional user for the postgres db:
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+-------------+-----------
postgres | Superuser | {}
: Create role
: Create DB
stephen | Superuser | {}
: Create role
: Create DB
I assigned passwords using "alter role etc.."
The problem is that no authentication method except trust seems to work.
in pg_hba.conf:
local all all trust
host all all 198.204.114.0/24 md5
I've tried both of the above users and get the same error each time:
psql: FATAL: password authentication failed for user "<username>"
I tried changing "md5" to "password" and "pam" without success. Onlt
"trust" works As near as I can tell by reading the documentation, it is
setup correctly but I have, obviously, done something wrong.
Any hints on where to start looking?
--
Stephen Carville
Apprentice Cook and Bottle Washer
Lereta LLC
1-800-537-3821 X1326
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Stephen Carville wrote:
I have been asked to evaluate Oracle, mysql and postgresql as a possible
replacement for our existing Oracle and MsSQL databases. Oracle and
mysql I pretty much have covered. Postgresql, OTOH, is somewhat less
cooperative.I have the software (v 8.4.13) installed on 64 bit Centos 6. It is
listening on all available interfaces and netstat confirms this. I
created an additional user for the postgres db:
If you want to evaluate PostgreSQL, you should evaluate v9.2. 8.4 is
pretty ancient and lacks lots of cool features so your comparison won't
be "fair" to PostgreSQL.
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+-------------+-----------
postgres | Superuser | {}
: Create role
: Create DB
stephen | Superuser | {}
: Create role
: Create DBI assigned passwords using "alter role etc.."
Which exact ALTER ROLE did you use? Feel free to redact the actual
password, of course.
The problem is that no authentication method except trust seems to work.
in pg_hba.conf:
local all all trust
host all all 198.204.114.0/24 md5I've tried both of the above users and get the same error each time:
psql: FATAL: password authentication failed for user "<username>"
I tried changing "md5" to "password" and "pam" without success. Onlt
"trust" works As near as I can tell by reading the documentation, it is
setup correctly but I have, obviously, done something wrong.
"md5" is the standard. "password" is plain text (which you don't want)
and "pam" will try to authenticate against OS users, which is probably
not what you want.
Any hints on where to start looking?
Is there any NAT happening between the client and the server? Check the
server's log for a "LOG: connection received: host=x.x.x.x" message so
you can check which IP is reaching the server.
Regards,
Daniel Serodio
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 07/03/2013 01:27 PM, Andrew Sullivan wrote:
Nothin' for nothin', but . . .
On Wed, Jul 03, 2013 at 01:11:35PM -0700, Stephen Carville wrote:
I have the software (v 8.4.13) installed on 64 bit Centos 6. It is
Why for a new project would you select such an old release of the software?
Convenience. It is already there.
Anyways, I figured it out.
I used tshark to capture the traffic. When I looked at the packets I
could see that I was asking for the wrong database!
This does not work:
# psql -h scadev02.lereta.com -U stephen
This does:
# psql -h scadev02.lereta.com -U stephen postgres
I guess it's always easy once you know the answer.
I tried changing "md5" to "password" and "pam" without success. Onlt
"trust" works As near as I can tell by reading the documentation, it is
setup correctly but I have, obviously, done something wrong.Any hints on where to start looking?
If this is the packaged software for the OS, you probably need to look
at the package-specific notes about the authentication methods. But
your experience does seem pretty strange: looking at what you did
_appears_ like it ought to work. Are you sure you have the right
file? Sometimes distributions put them in funky places.A
--
Stephen Carville
Apprentice Cook and Bottle Washer
Lereta LLC
1-800-537-3821 X1326
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Import Notes
Reply to msg id not found: 20130703202713.GG31619@crankycanuck.ca
On 07/03/2013 01:30 PM, Daniel Serodio (lists) wrote:
Stephen Carville wrote:
I have been asked to evaluate Oracle, mysql and postgresql as a possible
replacement for our existing Oracle and MsSQL databases. Oracle and
mysql I pretty much have covered. Postgresql, OTOH, is somewhat less
cooperative.I have the software (v 8.4.13) installed on 64 bit Centos 6. It is
listening on all available interfaces and netstat confirms this. I
created an additional user for the postgres db:If you want to evaluate PostgreSQL, you should evaluate v9.2. 8.4 is
pretty ancient and lacks lots of cool features so your comparison won't
be "fair" to PostgreSQL.
I figured it out.
I used tshark to capture the traffic. When I looked at the packets I
could see that I was asking for the wrong database!
This does not work:
# psql -h scadev02.lereta.com -U stephen
This does:
# psql -h scadev02.lereta.com -U stephen postgres
I guess it's always easy once you know the answer.
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+-------------+-----------
postgres | Superuser | {}
: Create role
: Create DB
stephen | Superuser | {}
: Create role
: Create DBI assigned passwords using "alter role etc.."
Which exact ALTER ROLE did you use? Feel free to redact the actual
password, of course.The problem is that no authentication method except trust seems to work.
in pg_hba.conf:
local all all trust
host all all 198.204.114.0/24 md5I've tried both of the above users and get the same error each time:
psql: FATAL: password authentication failed for user "<username>"
I tried changing "md5" to "password" and "pam" without success. Onlt
"trust" works As near as I can tell by reading the documentation, it is
setup correctly but I have, obviously, done something wrong."md5" is the standard. "password" is plain text (which you don't want)
and "pam" will try to authenticate against OS users, which is probably
not what you want.Any hints on where to start looking?
Is there any NAT happening between the client and the server? Check the
server's log for a "LOG: connection received: host=x.x.x.x" message so
you can check which IP is reaching the server.Regards,
Daniel Serodio
--
Stephen Carville
Apprentice Cook and Bottle Washer
Lereta LLC
1-800-537-3821 X1326
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
2013/7/4 Stephen Carville <scarville@lereta.com>:
On 07/03/2013 01:27 PM, Andrew Sullivan wrote:
Nothin' for nothin', but . . .
On Wed, Jul 03, 2013 at 01:11:35PM -0700, Stephen Carville wrote:
I have the software (v 8.4.13) installed on 64 bit Centos 6. It is
Why for a new project would you select such an old release of the software?
Convenience. It is already there.
yes, but you should to know, so 8.4 will be unsupported at July 2014
Regards
Pavel Stehule
Anyways, I figured it out.
I used tshark to capture the traffic. When I looked at the packets I
could see that I was asking for the wrong database!This does not work:
# psql -h scadev02.lereta.com -U stephen
This does:
# psql -h scadev02.lereta.com -U stephen postgres
I guess it's always easy once you know the answer.
I tried changing "md5" to "password" and "pam" without success. Onlt
"trust" works As near as I can tell by reading the documentation, it is
setup correctly but I have, obviously, done something wrong.Any hints on where to start looking?
If this is the packaged software for the OS, you probably need to look
at the package-specific notes about the authentication methods. But
your experience does seem pretty strange: looking at what you did
_appears_ like it ought to work. Are you sure you have the right
file? Sometimes distributions put them in funky places.A
--
Stephen Carville
Apprentice Cook and Bottle Washer
Lereta LLC
1-800-537-3821 X1326--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 7/3/2013 10:51 PM, Pavel Stehule wrote:
2013/7/4 Stephen Carville<scarville@lereta.com>:
On 07/03/2013 01:27 PM, Andrew Sullivan wrote:
Nothin' for nothin', but . . .
On Wed, Jul 03, 2013 at 01:11:35PM -0700, Stephen Carville wrote:
I have the software (v 8.4.13) installed on 64 bit Centos 6. It is
Why for a new project would you select such an old release of the software?
Convenience. It is already there.
yes, but you should to know, so 8.4 will be unsupported at July 2014
well, its probably the version that redhat 'supports', such as that
is. since there's no painless automatic way to upgrade to a newer
version if you have existing applications and databases, they can't
exactly just 'replace' it with 9.whatever.
--
john r pierce 37N 122W
somewhere on the middle of the left coast
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 07/03/2013 11:10 PM, John R Pierce wrote:
On 7/3/2013 10:51 PM, Pavel Stehule wrote:
2013/7/4 Stephen Carville<scarville@lereta.com>:
On 07/03/2013 01:27 PM, Andrew Sullivan wrote:
Nothin' for nothin', but . . .
On Wed, Jul 03, 2013 at 01:11:35PM -0700, Stephen Carville wrote:
I have the software (v 8.4.13) installed on 64 bit Centos 6. It is
Why for a new project would you select such an old release of the software?
Convenience. It is already there.
yes, but you should to know, so 8.4 will be unsupported at July 2014
well, its probably the version that redhat 'supports', such as that
is. since there's no painless automatic way to upgrade to a newer
version if you have existing applications and databases, they can't
exactly just 'replace' it with 9.whatever.
Postgres and MySQL are being evaluated for a new project that probably
won't even begin for months so I will be upgrading the dev box(es) to
the latest stable versions of each.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general