Thread: How to enable automatic md5 password prompting when connecting to PostgreSQL

How can I automatically have PostgreSQL prompt for a
password everytime I do the following to connect to my
sample databaase 'movies'?

I installed the database correctly to exact
instructions in the INSTALL doc in the PostgreSQL
source directory in /usr/local and it is now
registered as a daemon in the LINUX run level scripts
started when the system boots.


I assigned a password to the Administrator account
which is by default 'postgres' and to a second user (
the second was created with 'createuser tomwallick'
command which successfully echoed with the CREATE USER
message.

However, no password was issued for the new user
'tomwallick' when I initially created the user.

Then in the administrator handbook, I saw that I could
create a password after the fact with the ALTER USER
command.

I used md5 password authentication, rather than the un
encryped regular password athentication when issuing
the passwords for both 'postgres' and 'tomwallick'
users.

Here is how I did this, which works but I must pass
the -W flag to the psql command to startup the
database in order to be prompted to type in a
password.

EXAMPLE:

psql -d movies -U tomwallick -W


I was simply expecting to simply just type:

EXAMPLE:

psql -d movies -U tomwallick

and as a result have the

password:

prompt request the password AUTOMATICALLY.

How can I clean/flush out the md5 passwords I
originally created and start over alter the two user
accounts 'postgres' and 'tomwallick' to simply have
regular password athentication. Keep in mind
'postgres' account is actually the administrator.

Or is it normal for my database to force me to type in
the -W flag to force me to enter in my password.





Help on this subject please.


Tom Wallick



__________________________________
Do you Yahoo!?
Yahoo! Small Business - Try our new resources site!
http://smallbusiness.yahoo.com/resources/

Tom,

On Mar 11, 2005, at 1:51 AM, Tom Wallick wrote:

> prompt request the password AUTOMATICALLY.
>
> How can I clean/flush out the md5 passwords I
> originally created and start over alter the two user
> accounts 'postgres' and 'tomwallick' to simply have
> regular password athentication. Keep in mind
> 'postgres' account is actually the administrator.
>
> Or is it normal for my database to force me to type in
> the -W flag to force me to enter in my password.
>


I think what you are seeing is that passwords are not required by
default for local users. You can change this by adding a line to your
pg_hba.conf file (located in the data directory). For example, this
will cause password prompting for all local users of the movies
database:

# TYPE  DATABASE    USER        IP-ADDRESS        IP-MASK
METHOD
local   movies      all                                             md5

After changing this be sure to signal or restart PostgreSQL.

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

Hi Tom,

On Mar 11, 2005, at 8:27 AM, Tom Wallick wrote:

> You stated to 'add' the line in this file under
> 'local'
> but there is a line there already as you can see,
> which
> has values 'local' 'all' 'all' 'trust' set already.
>
> Do I add the line you recommended right after that
> line and leave the one before it ( the 'local' 'all'
> 'all' 'trust' ) intact? Or do I remove it.

The 'trust' option here is what allows any access without providing a
password. If I recall correctly, you can add the new line before the
existing one so that the change will only impact the database 'movies'
(it uses the first line that "matches"). If you want all local
databases to require a password, change 'trust' to 'password' or 'md5'.

>
> Can I leave that line there just for safekeeping? And
> add the line you recommend after it?

Sure, keep any line by making it a comment -- start the line with #.

>
>
> Lastly, I take it when you mean signal or restart
> PostgreSQL I can just reboot my machine, because I
> have PostgreSQL 8.0.1 successfully started at boot
> time through the Sys V init scripts as stated in the
> PostgreSQL INSTALL file that came with the source
> code.
>

Restarting the machine is just fine. But in cases where you want to
keep the database operational and have configuration file changes take
effect, you signal the postmaster process. You can use the pg_ctl
program with the reload option. Here are the details for pg_ctl:

http://www.postgresql.org/docs/8.0/interactive/app-pg-ctl.html


John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL