Postgres database login problems in db.databases

[davidinosauro]

Just installed grass7 (freshly compiled) and ran into this problem: with some modules, grass seems unable to login into the Postgres db, as it tries to connect as a different user:

v.db.reconnect.all gives me a few of theese errors:

DBMI-PostgreSQL driver error:
Connection failed.
FATAL:  role "davide" does not exist

ERROR: Unable to create database <host=localhost,dbname=test,port=5432> by
    driver <pg>

Where "davide" is actually my username, while I previously ran

db.login user=postgres password=***

in fact, most modules correctly connect to db (I can run v.in.ogr and import a shapefile, which will properly create a table on db)

However, the following modules (at least) fail with that error:

• v.db.reconnect.all -cd
• db.databases (which I found was called by v.db.reconnect.all)
• grass.pygrass.vector.VectorTopo.open called from the python shell

I did't dig deep into the problem, but I guess the wrong username could be the result of calling the db client without explicitly specifying the username.

Cheers Davide Kirchner

[davidinosauro]

Update: Hoping the thing was easy enough for me to fix it, I browsed a while into the code, but I realized it is not as easy as it seems.

I realized the problem in pygrass may be distinct from the one in db.databases, because pygrass directly calls psycopg2.connect (at source:/grass/trunk/lib/python/pygrass/vector/table.py#L641​).

But even if I found the place where the fix would probably go, I couldn't work out a way of getting the db credentials without explicitly reading from ~/.grass7/dblogin or ~/.pgpass, wich does not seem a good idea in terms of avoding code replication (even if I could not find it, I bet there is some c module which does exately this).

I forgot to explitely mention the steps to reproduce:

• Set up a postgres with password authentication (just to be sure, with no username equal to the unix user GRASS runs with, and with no ~/.pgpass entry)
• db.connect and db.login to that postgres
• create a vector map with attribute table on that postgres (I used v.in.ogr)
• from the python shell:

  from grass.pygrass.vector import VectorTopo # guess would be the same with Vector
  v = VectorTopo('map_name')
  v.open()
  

• same error would be generated from db.databases

[neteler]

In order to get interactive password prompting back (removed in r32551), could we use code from here for *only* db.login?

sprompt.c: ​http://doxygen.postgresql.org/sprompt_8c_source.html

/*
 * simple_prompt
 *
 * Generalized function especially intended for reading in usernames and
 * password interactively. Reads from /dev/tty or stdin/stderr.
 *
 * prompt:      The prompt to print
 * maxlen:      How many characters to accept
 * echo:        Set to false if you want to hide what is entered (for passwords)
 *
 * Returns a malloc()'ed string with the input (w/o trailing newline).
 */

Rationale: It is "ugly" to send clear text passwords through command line.

[glynn]

Replying to neteler:

In order to get interactive password prompting back (removed in r32551), could we use code from here for *only* db.login?

Maybe. At a minimum, interactive prompting would need to be specifically requested. If db.login is spawned from the GUI, trying to interact with the user via stdin or /dev/tty isn't likely to be useful.

While the GUI can (and should) just close/redirect stdin prior to executing commands, intercepting an explicit open("/dev/tty") is somewhat harder (I'm not sure that the equivalent is even possible on Windows).

It might be better to have db.login spawn an external program for such things, with the program specified by an environment variable and/or command-line option. This would keep all of the platform-specific ugliness out of db.login itself, and would also allow the use of different programs in different environments (e.g. a dialog box for GUI environments).

Assuming that the password is being stored somewhere (presumably this is required so that subsequent db.* or v.* commands can connect automatically), it's essential that reasonable security precautions are taken.

An external program could either transmit the password back to db.login, or could persist as an agent (similar to ssh-agent) which would be accessed by subsequent modules. In the latter case, the password need only be stored in the agent's memory; however, this would necessitate the use of sockets or similar IPC mechanisms.