Re: Fwd: Restarting with pg_ctl, users, and passwords.

Hi Craig,

Ok, I changed my pg_hba.conf from the commented line to the following
line to try to open things up for now (I know I will need more
security, but I'm trying to open things up wide to find the problem):

--- pg_hba.conf --------------------
# IPv4 local connections:
#host all all 127.0.0.1/32 md5
host all all 127.0.0.1/32 trust
-------------------------------------------

and made the following change to postgresql.conf (the line was
previously commented out):
--- postgresql.conf ----------------
listen_addresses = 'localhost'
-------------------------------------------

I still am getting the same error from my application (which I believe
is passing on some postgresql error info:

--- App error ----------------------------------------
Error reported was 'FATAL: Ident authentication failed for user
"postgres"
'.

This error occured when trying to make a connection to the specified
postgresql server.

Most commonly this is caused by
(1) incorrect connection string
(2) you didnt specify a 'user=...' in your connection string
(3) the postmaster (postgresql server) isnt running
(4) you are not allowing TCP/IP connection to the postmaster
(5) your postmaster is not running on the correct port - if its not on
5432 you must specify a 'port=...'
(6) the security on your system does not allow the webserver (usually
user 'nobody') to make socket connections to the postmaster
(7) you forgot to specify a 'host=...' if the postmaster is on
a different machine
(8) you made a typo
------------------------------------------------------------

Any suggestions? I'm using the postgres user and password in my CGI
app (though that shouldn't matter with my current config, right?)

Help is appreciated,
Matt

On Wed, Aug 20, 2008 at 8:18 AM, Craig Ringer
<craig(at)postnewspapers(dot)com(dot)au> wrote:
> Matthew Pettis wrote:
>
>> I've created a database and can log into it and do stuff with the
>> tables using psql. However, I have a CGI app that wants to call the
>> database (all on the same machine), but gets a 'FATAL: Ident
>> authentication failed for user "postgres"' error.
>
> You *REALLY* shouldn't use the `postgres' user for applications or
> normal use. Create a new non-superuser, grant them the required
> priveleges, and use them for the web application.
>
> If you like you can make them the owner of a new database, so they can
> do basically whatever they want with that database but not mess with
> your others.
>
> See the documentation for CREATE USER, CREATE ROLE, GRANT, and CREATE
> DATABASE .
>
>> now, while logged
>> onto my Linux user account 'postgres', I can psql into my database
>> without having to provide a password.
>
> It's using ident authentication on a UNIX socket connection. The
> database knows you are logged in on the UNIX account "postgres" (or have
> su'd to it) because the operating system can tell it that. So it trusts you.
>
> The web app will be using a TCP/IP connection to localhost, which (not
> being a UNIX socket) cannot use unix socket IDENT auth. It can use ident
> for passwordless auth anyway if you have an ident daemon running, but
> traditionally you'd use a password instead.
>
> For this to work, the server must be listening for TCP/IP connections
> (see postgresql.conf) and be set to accept password auth for at least
> the user and database you wish to log in to for 127.0.0.1/32 ("localhost").
>
>> now, I *thought* what that would do would be to allow me to issue the
>> 'psql' command from my regular non-postgres Linux account and log in
>> as long as I would issue:
>>
>> psql -d mydb -U postgres -W
>>
>> and then provide 'postgres' as the password as well, as I had changed
>> it in mydb as previously stated. But I am denied access when I try
>> this from my account.
>
> Because you're still using ident auth, as psql defaults to a UNIX
> socket. It never uses your password because it already knows you're not
> authorized according to pg_hba.conf's configuration for local UNIX sockets.
>
> Use a TCP/IP connection instead by adding the argument
> "--host localhost"
> to the psql command line.
>
>> I cannot even confirm that the database is running as I
>> thought it would, since I don't know what process to look for in the
>> 'ps -ef' dump.
>
> "postgres"
>
> In any case, if the DB wasn't running you'd get connection refused
> errors or similar.
>
> $ sudo /etc/init.d/postgresql-8.3 stop
> [sudo] password for craig:
> * Stopping PostgreSQL 8.3 database server
> [ OK ]
> $ psql
> psql: could not connect to server: No such file or directory
> Is the server running locally and accepting
> connections on Unix domain socket
> "/var/run/postgresql/.s.PGSQL.5432"?
> $ psql -h 127.0.0.1
> psql: could not connect to server: Connection refused
> Is the server running on host "127.0.0.1" and accepting
> TCP/IP connections on port 5432?
>
> --
> Craig Ringer
>

--
It is from the wellspring of our despair and the places that we are
broken that we come to repair the world.
-- Murray Waas