CODEWITHSUNDEEP
postgresqldatabase-connection
Charif DZ
Charif DZ

Reputation: 14721

Superuser expired and is the only user

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.

Upvotes: 3

Views: 640

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656231

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.

Upvotes: 3

Related Questions