dbext - keep asking user password in pgsql

[Mariano Mara]

Hi.
I created a new database today in my local copy of pgsql and added it
to my .vimrc

let g:dbext_default_profile_Otrs =
'type=PGSQL:user=mariano:passwd=mybigpasswd:host=localhost:dbname=otrs'

This is not the first connection to pgsql I use with dbext but it's
the first one that keeps asking me to enter my db password everytime I
try to run a query. If I use psql I can connect directly without
password: however I cannot do the same in dbext. Probably it's
something I set when I created the db but I would appreciate if
somebody can help me with it.

I'm using dbext 11.01 in Ubuntu JJ and connecting with psql 8.3.8

TIA,
Mariano

[David Fishburn]

Can you do the following:
1. Open up the buffer and attempt to run a query.
2. Run :DBGetOpton, then cut and paste the output provided.
3. Run :DBSetOption display_cmd_line=1, then re-run your query and
paste the full output.

That should get us started.

Dave

[Mariano Mara]

On Sep 29, 10:38 am, David Fishburn <dfishburn....@gmail.com> wrote:

> On Tue, Sep 29, 2009 at 12:17 AM, Mariano Mara <mariano.m...@gmail.com> wrote:
>
> > I created a new database today in my local copy of pgsql and added it
> > to my .vimrc
>
> > let g:dbext_default_profile_Otrs =
> > 'type=PGSQL:user=mariano:passwd=mybigpasswd:host=localhost:dbname=otrs'
>

> > This is not the first connection to pgsql I use withdbextbut it's

> > the first one that keeps asking me to enter my db password everytime I
> > try to run a query. If I use psql I can connect directly without

> > password: however I cannot do the same indbext. Probably it's

> > something I set when I created the db but I would appreciate if
> > somebody can help me with it.
>

> > I'm usingdbext11.01 in Ubuntu JJ and connecting with psql 8.3.8

>
> Can you do the following:
> 1.  Open up the buffer and attempt to run a query.
> 2.  Run :DBGetOpton, then cut and paste the output provided.
> 3.  Run :DBSetOption display_cmd_line=1, then re-run your query and
> paste the full output.
>
> That should get us started.
>
> Dave

Hi Dave, thanks for your support.
As requested here is the full output of both commands:

http://vim.pastey.net/125871
http://vim.pastey.net/125872

I used a pastey to save everyone more than 200 lines of settings. In
case a solution can be found (I really hope so) I will add to this
thread the meaningful data for completeness.

Mariano.

[Mikalai Chaly]

On Tue, Sep 29, 2009 at 9:07 PM, Mariano Mara <marian...@gmail.com> wrote:

Hi Dave, thanks for your support.
As requested here is the full output of both commands:

http://vim.pastey.net/125871
http://vim.pastey.net/125872

I used a pastey to save everyone more than 200 lines of settings. In
case a solution can be found (I really hope so) I will add to this
thread the meaningful data for completeness.

Mariano.

Probably, that's just a minor bug in a function "DB_PGSQL_execSql" - it constructs command to be executed as

let cmd = dbext_bin .  ' ' .
                \ s:DB_option('', dbext#DB_getWType("cmd_options"), ' ') .
                \ s:DB_option('-d ', s:DB_get("dbname"), ' ') .
                \ s:DB_option('-U ', s:DB_get("user"), ' ') .
                \ s:DB_option('-h ', s:DB_get("host"), ' ') .
                \ s:DB_option('-p ', s:DB_get("port"), ' ') .
                \ s:DB_option(' ', s:DB_get("extra"), '') .
                \ ' -q -f ' . s:dbext_tempfile

In comparison to smilar DB_MYSQL_execSql or DB_SQLSRV_execSql, there is no reference to a 'passwd' option.

If that's it, you can try to patch it - add something like

          \ s:DB_option('--password', s:DB_get("passwd"), ' ') .

to the code above (I'm not good with psql, so used "--password").

Mikalai

[Mariano Mara]

On Sep 29, 5:12 pm, Mikalai Chaly <nch...@gmail.com> wrote:

Hi Mikalai, thanks for your answer. Your tip was very useful to point
me in a good direction.
Turns out there is no easy way to pass a password as a command line
parameter to psql. You can use -W switch to make psql ask for it but
you will have to type it as a second step. I finally created a
~/.pgpass file as suggested here [1] and now dbext is not asking the
pass anymore. What's funny is that this started happening yesterday
with a fresh pgsql database. I can recall at least three other pgsql
databases I had queried with dbext and none of them prompted for the
password. Don't know why.

Mariano

[1] http://www.postgresql.org/docs/current/interactive/libpq-pgpass.html

[David Fishburn]

>> Probably, that's just a minor bug in a function "DB_PGSQL_execSql" - it
>> constructs command to be executed as

...

> Hi Mikalai, thanks for your answer. Your tip was very useful to point
> me in a good direction.
> Turns out there is no easy way to pass a password as a command line
> parameter to psql. You can use -W switch to make psql ask for it but
> you will have to type it as a second step. I finally created a
> ~/.pgpass file as suggested here [1] and now dbext is not asking the
> pass anymore.

Correct. PGSQL does not allow passwords to be set from the command
line which is the reason dbext does not provide it.

> What's funny is that this started happening yesterday
> with a fresh pgsql database. I can recall at least three other pgsql
> databases I had queried with dbext and none of them prompted for the
> password. Don't  know why.

Mariano, I can only image someone had already set those settings in
the database you were connecting to.

> [1] http://www.postgresql.org/docs/current/interactive/libpq-pgpass.html

As you have pointed out, that is the only way to control access to the database.

I am glad you are connecting and running again.

Dave

[Mariano Mara]

On Sep 29, 11:10 pm, David Fishburn <dfishburn....@gmail.com> wrote:
> >> Probably, that's just a minor bug in a function "DB_PGSQL_execSql" - it
> >> constructs command to be executed as
>
> ...
>
> > Hi Mikalai, thanks for your answer. Your tip was very useful to point
> > me in a good direction.
> > Turns out there is no easy way to pass a password as a command line
> > parameter to psql. You can use -W switch to make psql ask for it but
> > you will have to type it as a second step. I finally created a
> > ~/.pgpass file as suggested here [1] and now dbext is not asking the
> > pass anymore.
>
> Correct.  PGSQL does not allow passwords to be set from the command
> line which is the reason dbext does not provide it.
>
> > What's funny is that this started happening yesterday
> > with a fresh pgsql database. I can recall at least three other pgsql
> > databases I had queried with dbext and none of them prompted for the
> > password. Don't  know why.
>
> Mariano, I can only image someone had already set those settings in
> the database you were connecting to.
>

Sounds plausible

> > [1]http://www.postgresql.org/docs/current/interactive/libpq-pgpass.html
>
> As you have pointed out, that is the only way to control access to the database.
>
> I am glad you are connecting and running again.
>

Thanks for confirming it was the best solution. And thank you very
much for such a fine plugin.

[Gregory Margo]

On Tue, Sep 29, 2009 at 10:10:46PM -0400, David Fishburn wrote:
> >> Probably, that's just a minor bug in a function "DB_PGSQL_execSql" - it
> >> constructs command to be executed as

> > Hi Mikalai, thanks for your answer. Your tip was very useful to point
> > me in a good direction.
> > Turns out there is no easy way to pass a password as a command line
> > parameter to psql. You can use -W switch to make psql ask for it but
> > you will have to type it as a second step. I finally created a
> > ~/.pgpass file as suggested here [1] and now dbext is not asking the
> > pass anymore.
>
> Correct. PGSQL does not allow passwords to be set from the command
> line which is the reason dbext does not provide it.

You can put the password in environment variable PGPASSWORD.
http://www.postgresql.org/docs/8.4/interactive/libpq-envars.html

--
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Gregory H. Margo
gmargo at yahoo/com, gmail/com, pacbell/net; greg at margofamily/org