Setting password for PostgreSQL user doesn't work

Question

I'm stuck.

The task: I need to have a certain Postgres user to own certain database with predefined password. Basically, I just don't want to maintain separate development settings for Django project apart from those present in our project's repo.

Prerequisites: PostgreSQL 9.1 on Ubuntu, database 'project' which has the owner 'project'. The 'project' user is seems to be also superuser, and should have the password 'project'. Of course there are fake names, just because of NDA. Note: I'm able to log in as 'postgres' user, that's how I use the database right now.

The problem: I tried a set of ways, mostly obvious which can be found here on Stackoverflow or in Google, to set a password to this user, but still having 'password authentication failed' message (see below).

What I've tried:

setting up password via PgAdmin
setting up password through ALTER ROLE project WITH PASSWORD 'project'
changing settings in pg_hba.conf, tried local all project peer, local all project md5

Maybe I'm missing something straightforward, please let me know.

Thanks!

UPDATE:

Here is a screenshot for this user from login roles pane -

Could you post the info from pgAdmin SQL pane, from Login roles > project (you can change confidential data of course). — Simo Kivistö, Mar 19 '15 at 10:44
Did you reset postgres after tweaking those configuration files? Otherwise the password change won't have effect. sudo service postgresql restart — brunch875, Mar 19 '15 at 10:46
@brunch875: changing `pg_hba.conf` doesn't require a restart, just a reload of the config ( `pg_ctl ... reload`) — a_horse_with_no_name, Mar 19 '15 at 10:49
Posted a screenshot from Login roles pane. Yes, I always do `sudo /etc/init.d/postgresql restart` after any settings change. Error message is `FATAL: password authentication failed for user "project"`. Yes, Django app is running on the same server, it's all on my notebook. — Serge Tarkovski, Mar 19 '15 at 11:50
"*password authentication failed*" means you supplied the wrong password when connecting — a_horse_with_no_name, Mar 19 '15 at 11:52

score 1 · Accepted Answer · edited May 23 '17 at 12:01

1

See that part at the bottom of your screenshot:

.. VALID UNTIL '1970-01-01 00:00:00:

or the "Account Expires" field above

That expiration date is wrong and explains why this account can't login.

Presumably you've been bitten by the pgAdmin bug mentioned here:

Postgres password authentication fails

TL;DR solution: ALTER USER username VALID UNTIL 'infinity';

(and of course update pgAdmin).

edited May 23 '17 at 12:01

Community

1
1

answered Mar 19 '15 at 11:56

Daniel Vérité

53,594
14
124
144

score 0 · Answer 2 · answered Mar 19 '15 at 10:47

0

Is the LOGIN attribute set in your role? CREATE ROLE does not set this attribute by default. See: http://www.postgresql.org/docs/9.1/static/role-attributes.html

answered Mar 19 '15 at 10:47

Anton

886
1
8
27

Seems it's set, see attached screenshot. – Serge Tarkovski Mar 19 '15 at 11:53

Setting password for PostgreSQL user doesn't work

2 Answers2