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

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