1

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 - Login roles pane

Community
  • 1
  • 1
Serge Tarkovski
  • 1,861
  • 6
  • 19
  • 24
  • 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
  • What is the error message you get? –  Mar 19 '15 at 10:48
  • @brunch875: changing `pg_hba.conf` doesn't require a restart, just a reload of the config ( `pg_ctl ... reload`) –  Mar 19 '15 at 10:49
  • Django app is running on the same server as the database? – Burhan Khalid 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 –  Mar 19 '15 at 11:52

2 Answers2

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).

Community
  • 1
  • 1
Daniel Vérité
  • 58,074
  • 15
  • 129
  • 156
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

Anton
  • 936
  • 1
  • 8
  • 27