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

Thanks Craig and Tom,

This is very helpful... once I troubleshoot accessing the database
with the postgres user, I plan on adding other users with more
restrictive credentials... I thought I'd start with this config just
to see if I could get access on a very basic level.

Off to google postgresql.conf and pg_hba.conf... Any suggested pages
for learning this?

Thanks again,
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