3

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?

Eric Leschinski
  • 146,994
  • 96
  • 417
  • 335
Nelson Jarvis
  • 31
  • 1
  • 2
  • 1
    Postgres always installs a database called 'postgres'. Use that one with the answer of @Eric and you will be fine. – mistapink Apr 10 '13 at 14:47

3 Answers3

3

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

Community
  • 1
  • 1
Eric Leschinski
  • 146,994
  • 96
  • 417
  • 335
  • So you want to verify that users are valid before the postgresql system tables are setup? That seems kind of odd, but ok. – Nelson Jarvis Apr 10 '13 at 14:22
  • The automated installation is creating a new database, then running scripts to create tables and load data. A screen prompts for credentials and needs to authenticate before it calls the createdb command. There's no need to run additional commands with the wrong credentials. That is standard practice for database installations. – Nelson Jarvis Apr 10 '13 at 14:31
  • Logging in to PostgreSQL: Other db programs (like IBM 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? – Nelson Jarvis Apr 10 '13 at 14:35
  • 1
    Connect to the database: postgres. It is the database you connect to when you are not connecting to a database. – Chris Aitchison Apr 11 '13 at 11:58
1

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.

r3cgm
  • 133
  • 1
  • 7
-2

Simply use this command in your terminal:

psql postgres
Pang
  • 9,564
  • 146
  • 81
  • 122
Yashas
  • 237
  • 2
  • 3