Re: postgresql configuration and set-up

[BillMoseley@hank.org]

On Tue, Mar 16, 2004 at 09:30:27PM -0500, Tom Allison wrote:
> I'm trying to get debian's postgresql server running on a machine.

> But I'm stuck on what the passwords are for the users I create.

> createdb foo
> createuser foouser

> works just fine.
> But how do I log in to the database foo as user foouser?

Something I'm learning about, too.

A couple of different ways.  One is to su to foouser and run
psql, the other is to use psql -U foouser.

> It says I need a password in the docs,  But I have no clue what it might
> be, I was never asked for one.

You have to tell createuser that you want to supply a
password (-P).

As glenn posted, it seems like you need to setup the
/etc/postresql/pg_hba.conf file.

The pg_hba.conf file is only for authentication.  It's used
for proving that you are who you say you are.  When you
connect from an account you can use "ident" method and not
worry about username and passwords.  If you just run psql it
knows your username.  If you connect with sockets (using -h,
for example) then you would need to have a local ident
program available (like ident2) that would authenticate that
you are indeed the user you say you are.

In the pg_hba.conf file postgres looks for the first
matching line by looking at the type (local or host), what
database you want to connect to, and as what user.  And
maybe from what IP address or range.

Then the final column says how to authenticate.  Ident I
mentioned above.  I use md5 for password authentication.

So, I'd create a user with 

  createuser -P someuser

and it would prompt for a password.  And then use "md5" in
the pg_hba.conf file to say to authenticate me by a
requiring a password.

Now, that authenticates to use the database.  But, the issue
I've been wondering about is this:

I create tables as one user then I want another user to use
those tables (like the web server).  Then I need to GRANT
access to every object in the database to that other user.

I wonder if there's an easier way than having to GRANT every
object -- like a global grant.


--
Bill Moseley
moseley@hank.org