sql-postgresql authentication failure

sql-postgresql authentication failure

[Sebastian P. Luque]

Hi,

I'd like to use `sql.el' to connect to local postgresql databases.  I'm
accessing the server through my normal user account, although I've added
myself as a super user in the postgresql server.  In a shell, I can
connect to a 'test' database with:

---<---------------cut here---------------start-------------->---
$ psql test
Welcome to psql 8.2.4, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

test=#
---<---------------cut here---------------end---------------->---

However, when I try to do it from Emacs with `sql.el', I have:


M-x sql-postgresql

In the minibuffer:

"User: myuser"
"Database: test"
"Server: localhost"
"Password for user myuser: secret"

and the message from the server:
Password for user myuser: 
psql: FATAL:  password authentication failed for user "myuser"

Process SQL exited abnormally with code 2

Password for user sluque: 
psql: FATAL:  password authentication failed for user "myuser"

Process SQL exited abnormally with code 2


I'm using:

,-----[ psql --version ]
| psql (PostgreSQL) 8.2.4
| contains support for command-line editing
`-----

Any ideas what might be wrong?  Thanks.


Cheers,

-- 
Seb

Re: sql-postgresql authentication failure

[Tim X]

"Sebastian P. Luque" <spluque@gmail.com> writes:

> Subject: sql-postgresql authentication failure
>
> Hi,
>
> I'd like to use `sql.el' to connect to local postgresql databases.  I'm
> accessing the server through my normal user account, although I've added
> myself as a super user in the postgresql server.  In a shell, I can
> connect to a 'test' database with:
>
> ---<---------------cut here---------------start-------------->---
> $ psql test
> Welcome to psql 8.2.4, the PostgreSQL interactive terminal.
>
> Type:  \copyright for distribution terms
>        \h for help with SQL commands
>        \? for help with psql commands
>        \g or terminate with semicolon to execute query
>        \q to quit
>
> test=#
> ---<---------------cut here---------------end---------------->---
>
> However, when I try to do it from Emacs with `sql.el', I have:
>
>
> M-x sql-postgresql
>
> In the minibuffer:
>
> "User: myuser"
> "Database: test"
> "Server: localhost"
> "Password for user myuser: secret"
>
> and the message from the server:
> Password for user myuser: 
> psql: FATAL:  password authentication failed for user "myuser"
>
> Process SQL exited abnormally with code 2
>
> Password for user sluque: 
> psql: FATAL:  password authentication failed for user "myuser"
>
> Process SQL exited abnormally with code 2
>
>
> I'm using:
>
> ,-----[ psql --version ]
> | psql (PostgreSQL) 8.2.4
> | contains support for command-line editing
> `-----
>

Yep, I think so. Later versions of postgres are often installed with support
for local authentication only and disable network authentication. From memory,
you can configure postgres to use either username/password authentication for
both local and network based access or you can have it configured to use identd
based checking for local connections - essentially its assuming that if local
user x is trying to connect from local account x, and x is permitted to access
the database, then let them through without th need for passwords. 

There are two possible ways of fixing this.

The first solution will almost definitely work. Change the authentication
scheme used by postgres for the databases you want to use to be user/password
based. This is outlined in the postgres documentation. 

the second method may work and is easier, but no guarantee. It is possible that
if you set specific switches, the psql client that sql mode uses will use local
non-password based authentication. While sql-mode will still prompt you for the
username/password, this information will be ignored by the psql client and you
will get authenticated.based on the user that is running the psql client (i.e.
you). 

Personally, although the local authentication is convenient, I don't like it.
If your account is compromised, so are your databases. Then again, some would
argue that if your account is compromised, you have bigger problems anyway.

Tim

-- 
tcross (at) rapttech dot com dot au

Re: sql-postgresql authentication failure

[Sebastian P. Luque]

On Fri, 25 May 2007 15:28:56 +1000,
Tim X <timx@nospam.dev.null> wrote:

[...]

> the second method may work and is easier, but no guarantee. It is
> possible that if you set specific switches, the psql client that sql
> mode uses will use local non-password based authentication. While
> sql-mode will still prompt you for the username/password, this
> information will be ignored by the psql client and you will get
> authenticated.based on the user that is running the psql client (i.e.
> you).

Thanks for your comments Tim.  This is very much what I'd like to do,
i.e. I don't want to modify the way I access the server.  I presume you
mean one can put these switches in sql-postgres-options, but I don't see
any options in 'man psql' that would allow for this scenario.  What
particular options would you use?  Below is my
/etc/postgresql/8.2/main/pg_hba.conf file:

---<---------------cut here---------------start-------------->---
# Database administrative login by UNIX sockets
local   all         postgres                          ident sameuser

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
local   all         all                               ident sameuser
# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
# IPv6 local connections:
host    all         all         ::1/128               md5
---<---------------cut here---------------end---------------->---


Thanks again,

-- 
Seb

Re: sql-postgresql authentication failure

[Sebastian P. Luque]

Surprisingly, if I don't supply anything in the "Server: " prompt, then I
can connect.  Any clues what this means?


Cheers,

-- 
Seb

Re: sql-postgresql authentication failure

[Tim X]

"Sebastian P. Luque" <spluque@gmail.com> writes:

> Surprisingly, if I don't supply anything in the "Server: " prompt, then I
> can connect.  Any clues what this means?
>
>

No, actually, I just found that out as well. I would have to look at the sql.el
sources to know for sure. However, I suspect that if you don't provide a
username, the function just sends the database name. If you provide nothing at
all, it tries to login to a database with the same name as the user. If you
provide a database, server, login name and password, it will try to connect
using all those parameters, which will only work if the database is configured
to allow that type of connection. 

At any rate, at least you can now use sql.el to connect and interact with the
database. 

Tim

-- 
tcross (at) rapttech dot com dot au

Re: sql-postgresql authentication failure

[James Cloos]

>>>>> "Sebastian" == Sebastian P Luque <spluque@gmail.com> writes:

Sebastian> Surprisingly, if I don't supply anything in the "Server: "
Sebastian> prompt, then I can connect.  Any clues what this means?

It almost certainly means that in that case unix domain sockets are used
whereas tcp sockets are used when you specifiy a server.

psql(1) also uses unix sockets unless you specify a host with the PGHOST
environmental or with the -h or --host switches, so that is expected if
it is the case that sql.el uses psql(1) to do the heavy lifting.

-JimC
-- 
James Cloos <cloos@jhcloos.com>         OpenPGP: 1024D/ED7DAEA6