Обсуждение: 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
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 DB
>
> I 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 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.
"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
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 DB
>>
>> I 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 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.
> "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
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
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
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
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.