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