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ö
– Simo Kivistö, Commented Mar 19, 2015 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
– brunch875, Commented Mar 19, 2015 at 10:46
@brunch875: changing pg_hba.conf doesn't require a restart, just a reload of the config ( pg_ctl ... reload) — user330315
– user330315, Commented Mar 19, 2015 at 10:49

Community · Accepted Answer · 2017-05-23 12:01:33Z

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, 2017 at 12:01

CommunityBot

11 silver badge

answered Mar 19, 2015 at 11:56

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Anton · Accepted Answer · 2015-03-19 10:47:52Z

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, 2015 at 10:47

community wiki

Anton

1 Comment

Serge Tarkovski Over a year ago

Seems it's set, see attached screenshot.

Collectives™ on Stack Overflow

Setting password for PostgreSQL user doesn't work

2 Answers 2

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Linked

Related