3

I'm using Postgres 9.5 on Windows 7.

I have only one user postgres and I defined an expiry date for that user.

Now I'm trying to connect after is expired and I can't, so does anyone know how to cancel the expiry from the superuser, so I don't have to reinstall Postgres.

I tried to edit pg_hba.conf to allow trust for postgres but still the same problem.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Charif DZ
  • 14,415
  • 3
  • 21
  • 40

1 Answers1

3

Start the server in single-user mode to fix the faulty "expiry date" with:

ALTER ROLE postgres VALID UNTIL 'infinity';

The manual:

The postgres command [...] When invoked in single-user mode from the shell, the user can enter queries and the results will be printed to the screen, but in a form that is more useful for developers than end users. In the single-user mode, the session user will be set to the user with ID 1, and implicit superuser powers are granted to this user. This user does not actually have to exist, so the single-user mode can be used to manually recover from certain kinds of accidental damage to the system catalogs.

Bold emphasis mine.

Aside: It's a pretty "creative" idea to let the superuser postgres expire. IOW: don't.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Looks right i will give it a try i just tried this to see if the super user can be expired thanks – Charif DZ Jun 03 '17 at 16:11
  • just one question when i ran this using cmd window in adminstration mode didn't allow this. i used the git bash shell and it worked fine. do you have any idea – Charif DZ Jun 05 '17 at 08:54
  • 1
    ok it's fine i used with cmd without administration privelage and it worked fine. thank you – Charif DZ Jun 05 '17 at 08:56