Authenticate PostgreSQL user and password without database

Question

How do I verify the postgresql user and password are valid without the existence of a database?

I'm creating an automated installation that will create a database and run sql scripts to create tables. The install needs to authenticate the user and password before running the scripts. The automated install calls Windows batch files. First I set the password, set PGPASSWORD=mypassword.

After setting the password, what command can authenticate the user and password and return an error code or message?

Other database programs (like IBM and db2) have an attach command which lets the user attach to the server or instance, without specifying a db name. I cannot find the equivalent for PostgreSQL.

How do I login to PostgreSQL on the command line without specifying a database name?

Answer 1

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

Answer 2

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.