Re: [Re: Password?]

On Jul 8, 2009, at 6:19 AM, Andreas Wenk wrote:

> Jasen Betts schrieb:
>> On 2009-07-08, Andreas Wenk <a(dot)wenk(at)netzmeister-st-pauli(dot)de> wrote:
>>> Serge Fonville schrieb:
>>>>> *argh* - more detailed to avoid confusion. The auth method
>>>>> 'password' in
>>>>> pg_hba.conf means, that you will be asked for a password for the
>>>>> user you
>>>>> try to create a db with. If no user is given (with createdb -U
>>>>> [username]),
>>>>> this user is postgres ...
>>>> Wasn't it that it uses the currently logged on user is used if no
>>>> user
>>>> is specified?
>>> correct - so this will be postgres because other users are not
>>> allowed to use these
>>> programs ...

That's not true.

>>>
>>> /var/lib/postgresql/8.4/bin$ ./createdb test -p 5433
>>> createdb: could not connect to database postgres: FATAL: role
>>> "duke" does not exist
>>>
>>> $ sudo su postgres
>>> postgres(at)duke-linux:~/8.4/bin$ ./createdb test -p 5433
>>> postgres(at)duke-linux:~/8.4/bin$
>>>
>>> auth method in pg_hba.conf is trust in this case.
>> if it's "trust" any user can do
>> ~postgres/8.4/bin/createdb -U postgres -p 5433 test
>
> nope! what you did is calling createdb as system user postgres (I
> believe because of the ~ sign at the beginning) *and* giving the
> option -U postgres. That works for sure and you even don't need -U
> postgres since you are allready postgres. But leave -U postgres away
> as a system user not equal to postgres ... see my example above.

Nor is that.

Most of the postgresql client tools, including createdb, can be used
by any operating system user to connect to the database as any
database user.

If they are called with "-U foo" then they will attempt to connect to
the database as database user "foo".

If they are not called with -U then they will usually attempt to
connect to the database as the current operating system user (though
that can be overridden with the PGUSER or PGSERVICE environment
variables).

So if I'm logged in as steve and I do "createdb test" then I will try
and connect to the database as database user steve and create the test
database. If I do "createdb -U postgres test" I will try to connect to
the database as database user "postgres" and create the test database.

Whether I'm prompted for a password or not depends on the settings in
pg_hba.conf. Typically the postgres operating system user is allowed
to connect to the database as the postgres database user without a
password. Other OS user / database user combinations may or may not
need a password depending on whether pg_hba.conf is set up to ask for
one or not - how that is set up as default varies, but it's fairly
common to require a password.

Cheers,
Steve