Authenticate PostgreSQL user and password without database

Use the system table pg_roles

Postgres always installs a database called "postgres". postgres is the database you connect to when you are not connecting to a database. In there is a table called pg_roles.

Use this command:

psql -U pgadmin -d postgres -c 'select * from pg_roles'

Which returns this:

 rolname  | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolconfig |  oid
----------+----------+------------+---------------+-------------+--------------+-------------+----------------+--------------+-------------+---------------+-----------+-------
 postgres | t        | t          | t             | t           | t            | t           | t              |           -1 | ********    |               |           |    10
 pgadmin  | t        | t          | t             | t           | t            | t           | t              |           -1 | ********    |               |           | 16384

(2 rows)

See this answer: How to check if a postgres user exists?

Test it by logging in

Just try logging in with the supplied username/password and surround it with a try/catch, and if you can login then it is valid, otherwise it is not valid.

Altering the user

You might be able to catch an error message if you alter a user that doesn't exist: http://www.postgresql.org/docs/8.0/static/sql-alteruser.html

ALTER USER postgres WITH PASSWORD 'tmppassword';

Delete and re-add user

You might be able to catch an error message if you try to delete and re-add a user. So if it was invalid then it would have thrown an error when you try to delete a non user. http://www.postgresql.org/docs/8.0/static/sql-createuser.html

Here's a handy way to check if a username/password combo is valid from the command line. This is pulled from the puppetlabs-postgresql module:

su - username
env PGPASSWORD=somepass psql -h localhost -c 'select 1'
echo $?

... where 'somepass' is the password being tested and 'username' is the account being tested against (typically the postgres user itself).

If $? is 0, the user/password combination is valid. If $? is 1, the password is wrong or the user does not exist. Either way, there's a problem.

That's the Linux/bash way. The Windows way is something along the lines of:

(run under the user account you're interested in)

set PGPASSWORD=somepass
psql -h localhost -c 'select 1'

if errorlevel 1 (
    echo Failure Reason Given is %errorlevel%
    exit /b %errorlevel%
)

The -h on psql forces a local TCP-based connection, which by default (from pg_hba.conf) forces a password check with no possibility that passwordless users can bypass.

My apologies for not posting a fully tested Windows solution, but there's a good chance the code above will either work directly or will with just a little fixing.

Simply use this command in your terminal:

psql postgres