Postgres on Unix/Debian - seeking "dummies guide"

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Postgres on Unix/Debian - seeking "dummies guide"

Damian
Hi all,

Utter newbie question. There will be no single answer - so if anyone could just point me in the right direction with a "recipe" I would be most grateful.

In summary ...
===========
- for last 10 years been Windows based with a java desktop accessing Postres with no issues at all
- currently trying to port the app to Debian
- it's 20+ years since I last used *nix
- After several days - I still can't get the java app on (say) Debian user "damian" to access the db that is running as Debian user "postgres". PgAdmin3 works fine from "damian", but not the java app with the same user & pwds.
- seeking any beginner clues / recipie for the most basic setup to get it working
- not looking for "best practice", just an effective guide to what would work for initial dev phase.


In more detail ...
=============
Our distributed Java desktop app uses Postgres as its DB, and has been on Windows for many years, and that process (dev to deployment) is completely solid. In the target industry all customers must be on Windows, hence so is our dev environment.

My current task is porting to Debian - but it's my first Unix since 1993 when I played on Solaris - so my familiarity has long gone and I'm fumbling the basics. For the life of me I can't get the app to get access to the DB.

For these trials I'm using Debian on VirtualBox. I don't think that would be relevant.

I *think* it is only a matter of users, permissions, roles, groups, pwds etc - but after following 10+ tutorials and 2 days of trying I am officially struggling. Particularly roles and groups, these are in the tutorials, but 

I just want to get the most basic case working, and I can scale from there.

2 Debian users / passwords
- postgres / pgpwd
- damian / mypwd

DB added to PG
- db name: mydb
- db owner: postgres


PostgreSQL installed for user postgres - all good

for user damian
1. PgAdmin3 installed and connects to DB nicely
2. Java app installed and running properly in every way except it is denied access to the DB

Even if I use dbuser: "postgres" and dbpwd: "pgpwd" the logs say ...
<logs>
Connection matched pg_hba.conf line 92: "host all all 127.0.0.1/32 md5"
postgres@qdb FATAL:  password authentication failed for user "postgres"
postgres@qdb DETAIL:  Password does not match for user "postgres".
</logs>

- What username and password is PG expecting?
- is this expecting a role to be created or something?

Any clues would be much appreciated.

Thanks,
-Damian





Reply | Threaded
Open this post in threaded view
|

Re: Postgres on Unix/Debian - seeking "dummies guide"

Jayadevan
Hi,

Even if I use dbuser: "postgres" and dbpwd: "pgpwd" the logs say ...
<logs>
Connection matched pg_hba.conf line 92: "host all all 127.0.0.1/32 md5"
postgres@qdb FATAL:  password authentication failed for user "postgres"
postgres@qdb DETAIL:  Password does not match for user "postgres".
</logs>

A guess - pgAdmin3 is connsting via localhost and using trust authentication. So it works. Not so for your java app. May be you could connect via pgadmin, reset password for postgres or damian to whatever you have set in the java app and try? 


Regards,
Jayadevan
Reply | Threaded
Open this post in threaded view
|

Re: Postgres on Unix/Debian - seeking "dummies guide"

Andrej Ricnik-Bay
In reply to this post by Damian
Hi Damian,

What does your pg_hba.conf and and postgres.conf look like?


Cheers,
Andrej

On 11 June 2018 at 17:04, Damian Carey <[hidden email]> wrote:

> Hi all,
>
> Utter newbie question. There will be no single answer - so if anyone could
> just point me in the right direction with a "recipe" I would be most
> grateful.
>
> In summary ...
> ===========
> - for last 10 years been Windows based with a java desktop accessing Postres
> with no issues at all
> - currently trying to port the app to Debian
> - it's 20+ years since I last used *nix
> - After several days - I still can't get the java app on (say) Debian user
> "damian" to access the db that is running as Debian user "postgres".
> PgAdmin3 works fine from "damian", but not the java app with the same user &
> pwds.
> - seeking any beginner clues / recipie for the most basic setup to get it
> working
> - not looking for "best practice", just an effective guide to what would
> work for initial dev phase.
>
>
> In more detail ...
> =============
> Our distributed Java desktop app uses Postgres as its DB, and has been on
> Windows for many years, and that process (dev to deployment) is completely
> solid. In the target industry all customers must be on Windows, hence so is
> our dev environment.
>
> My current task is porting to Debian - but it's my first Unix since 1993
> when I played on Solaris - so my familiarity has long gone and I'm fumbling
> the basics. For the life of me I can't get the app to get access to the DB.
>
> For these trials I'm using Debian on VirtualBox. I don't think that would be
> relevant.
>
> I *think* it is only a matter of users, permissions, roles, groups, pwds etc
> - but after following 10+ tutorials and 2 days of trying I am officially
> struggling. Particularly roles and groups, these are in the tutorials, but
>
> I just want to get the most basic case working, and I can scale from there.
>
> 2 Debian users / passwords
> - postgres / pgpwd
> - damian / mypwd
>
> DB added to PG
> - db name: mydb
> - db owner: postgres
>
>
> PostgreSQL installed for user postgres - all good
>
> for user damian
> 1. PgAdmin3 installed and connects to DB nicely
> 2. Java app installed and running properly in every way except it is denied
> access to the DB
>
> Even if I use dbuser: "postgres" and dbpwd: "pgpwd" the logs say ...
> <logs>
> Connection matched pg_hba.conf line 92: "host all all 127.0.0.1/32 md5"
> postgres@qdb FATAL:  password authentication failed for user "postgres"
> postgres@qdb DETAIL:  Password does not match for user "postgres".
> </logs>
>
> - What username and password is PG expecting?
> - is this expecting a role to be created or something?
>
> Any clues would be much appreciated.
>
> Thanks,
> -Damian
>
>
>
>
>



--
Please don't top post, and don't use HTML e-Mail :}  Make your quotes concise.

http://www.georgedillon.com/web/html_email_is_evil.shtml
http://www.catb.org/jargon/html/email-style.html

Reply | Threaded
Open this post in threaded view
|

Re: Postgres on Unix/Debian - seeking "dummies guide"

Fabio Pardi
In reply to this post by Damian

Hi Damian,


given the error you posted, looks like the error is not OS related.

It says that you are connecting from/to localhost but the password is wrong.

Probably you want to run an 'ALTER USER ... WITH PASSWORD ..'

Since your postgres user is not able to connect too, I would first change pg_hba.conf to

host    all             postgres             127.0.0.1/32            trust

(and reload postgres..)


so that you do not need a password when connecting as postgres user.

Then from there, connect as user postgres to your db


psql -U postgres -h localhost postgres


change the passwords you need to change..

reconfigure your pg_hba based on your needs, reload postgres one more time and try to connect again.


regards,


fabio pardi





On 11/06/18 07:04, Damian Carey wrote:
Hi all,

Utter newbie question. There will be no single answer - so if anyone could just point me in the right direction with a "recipe" I would be most grateful.

In summary ...
===========
- for last 10 years been Windows based with a java desktop accessing Postres with no issues at all
- currently trying to port the app to Debian
- it's 20+ years since I last used *nix
- After several days - I still can't get the java app on (say) Debian user "damian" to access the db that is running as Debian user "postgres". PgAdmin3 works fine from "damian", but not the java app with the same user & pwds.
- seeking any beginner clues / recipie for the most basic setup to get it working
- not looking for "best practice", just an effective guide to what would work for initial dev phase.


In more detail ...
=============
Our distributed Java desktop app uses Postgres as its DB, and has been on Windows for many years, and that process (dev to deployment) is completely solid. In the target industry all customers must be on Windows, hence so is our dev environment.

My current task is porting to Debian - but it's my first Unix since 1993 when I played on Solaris - so my familiarity has long gone and I'm fumbling the basics. For the life of me I can't get the app to get access to the DB.

For these trials I'm using Debian on VirtualBox. I don't think that would be relevant.

I *think* it is only a matter of users, permissions, roles, groups, pwds etc - but after following 10+ tutorials and 2 days of trying I am officially struggling. Particularly roles and groups, these are in the tutorials, but 

I just want to get the most basic case working, and I can scale from there.

2 Debian users / passwords
- postgres / pgpwd
- damian / mypwd

DB added to PG
- db name: mydb
- db owner: postgres


PostgreSQL installed for user postgres - all good

for user damian
1. PgAdmin3 installed and connects to DB nicely
2. Java app installed and running properly in every way except it is denied access to the DB

Even if I use dbuser: "postgres" and dbpwd: "pgpwd" the logs say ...
<logs>
Connection matched pg_hba.conf line 92: "host all all 127.0.0.1/32 md5"
postgres@qdb FATAL:  password authentication failed for user "postgres"
postgres@qdb DETAIL:  Password does not match for user "postgres".
</logs>

- What username and password is PG expecting?
- is this expecting a role to be created or something?

Any clues would be much appreciated.

Thanks,
-Damian






Reply | Threaded
Open this post in threaded view
|

Re: Postgres on Unix/Debian - seeking "dummies guide"

Damian
Jayadevan, Andrej, Fabio

Sincere thanks to all of you for your suggestions ...  I will fumble around for the next few hours and see how I go.

It has been bizarre being competent in the Windows universe - but an utter novice in Unix. Reduced to a complete novice.

The postgresql.conf is stock standard out of the box.
I might change ...
#listen_addresses = 'localhost'
listen_addresses = '*'

pg_hba.conf
- currently as follows, so I will change these to trust ....
# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

I'm just surprised that pgAdmin is connecting from "damian", but the java app is not.

I'm sure I'l be back seeking clarification soon!

Cheers,
-Damian




On 11 June 2018 at 17:25, Fabio Pardi <[hidden email]> wrote:

Hi Damian,


given the error you posted, looks like the error is not OS related.

It says that you are connecting from/to localhost but the password is wrong.

Probably you want to run an 'ALTER USER ... WITH PASSWORD ..'

Since your postgres user is not able to connect too, I would first change pg_hba.conf to

host    all             postgres             127.0.0.1/32            trust

(and reload postgres..)


so that you do not need a password when connecting as postgres user.

Then from there, connect as user postgres to your db


psql -U postgres -h localhost postgres


change the passwords you need to change..

reconfigure your pg_hba based on your needs, reload postgres one more time and try to connect again.


regards,


fabio pardi





On 11/06/18 07:04, Damian Carey wrote:
Hi all,

Utter newbie question. There will be no single answer - so if anyone could just point me in the right direction with a "recipe" I would be most grateful.

In summary ...
===========
- for last 10 years been Windows based with a java desktop accessing Postres with no issues at all
- currently trying to port the app to Debian
- it's 20+ years since I last used *nix
- After several days - I still can't get the java app on (say) Debian user "damian" to access the db that is running as Debian user "postgres". PgAdmin3 works fine from "damian", but not the java app with the same user & pwds.
- seeking any beginner clues / recipie for the most basic setup to get it working
- not looking for "best practice", just an effective guide to what would work for initial dev phase.


In more detail ...
=============
Our distributed Java desktop app uses Postgres as its DB, and has been on Windows for many years, and that process (dev to deployment) is completely solid. In the target industry all customers must be on Windows, hence so is our dev environment.

My current task is porting to Debian - but it's my first Unix since 1993 when I played on Solaris - so my familiarity has long gone and I'm fumbling the basics. For the life of me I can't get the app to get access to the DB.

For these trials I'm using Debian on VirtualBox. I don't think that would be relevant.

I *think* it is only a matter of users, permissions, roles, groups, pwds etc - but after following 10+ tutorials and 2 days of trying I am officially struggling. Particularly roles and groups, these are in the tutorials, but 

I just want to get the most basic case working, and I can scale from there.

2 Debian users / passwords
- postgres / pgpwd
- damian / mypwd

DB added to PG
- db name: mydb
- db owner: postgres


PostgreSQL installed for user postgres - all good

for user damian
1. PgAdmin3 installed and connects to DB nicely
2. Java app installed and running properly in every way except it is denied access to the DB

Even if I use dbuser: "postgres" and dbpwd: "pgpwd" the logs say ...
<logs>
Connection matched pg_hba.conf line 92: "host all all 127.0.0.1/32 md5"
postgres@qdb FATAL:  password authentication failed for user "postgres"
postgres@qdb DETAIL:  Password does not match for user "postgres".
</logs>

- What username and password is PG expecting?
- is this expecting a role to be created or something?

Any clues would be much appreciated.

Thanks,
-Damian