Hi.

The entries entered in pg_shadow haven't ever worked for me. I've tried a
number of times without success. If I update a user in there and set a
password for them:
postgres=> select * from pg_shadow;
usename  |usesysid|usecreatedb|usetrace|usesuper|usecatupd|passwd|valuntil                    
---------+--------+-----------+--------+--------+---------+-------+----------------------------
postgres |     100|t          |t       |t       |t        |       |Sat Jan31 01:00:00 2037 EST
user1    |    1001|f          |t       |f       |t        |       |                            
equipment|    1004|f          |t       |f       |t        | MYPASS|                            
(3 rows)

This example assumes I've set my password to 'MYPASS'.
Now I change pg_hba.conf to have a:
host         equipment   123.123.123.123  255.255.0.0     password 

Assuming my IP is 123.123.123.123 and the database I need to connect to is
called equipment and the user is of course equipment...

I've restarted the server and...

Now I run off to my remote machine and try to connect...

psql -u -h test.mypostgresserverdomain.com equipment
Username: equipment
Password: 

Connection to database 'equipment' failed.
Password authentication failed for user 'equipment'

Any ideas on what the heck I might be forgetting to do or not doing
properly?

I'm starting postgres up as:
  su -l postgres -c 'exec /usr/local/pgsql/bin/postmaster
-D/dr/raid0/postgres/pgdata -d 1 -i -o "-E -F -S 16384 -o
/usr/local/pgsql/home/logfile" -s >> /usr/local/pgsql/home/errlog 2>&1
/usr/local/pgsql/home/errlog1 &' 

In the server's errlog file I find:
Password authentication failed for user 'equipment'

It would be really nice if I'd see something like:
Sat Aug 28 21:43:39 EDT 1999 - Password authentication failed from
123.123.123.123 on database 'equipment'

-Michael


Sat, 28 Aug 1999 21:40:29 -0300 (ADT)
Michael Richards <miker@scifair.acadiau.ca>
entries in pg_shadow
Michael Richards <miker@scifair.acadiau.ca> writes:
> The entries entered in pg_shadow haven't ever worked for me. I've tried a
> number of times without success. If I update a user in there and set a
> password for them:

IIRC, the only way to set a password that actually works is ALTER USER.

The reason direct SQL hacking on pg_shadow doesn't work is that
pg_shadow isn't what the postmaster looks at (the PM itself can't do
database operations without getting into possible deadlock situations).
There's a flat text file somewhere that contains the Real Info.  ALTER
USER and friends know to rewrite the flat file after updating pg_shadow.

This is documented somewhere, I think, but not nearly prominently
enough...

			regards, tom lane

Sat, 28 Aug 1999 21:00:46 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] entries in pg_shadow 
> Hi.
> 
> The entries entered in pg_shadow haven't ever worked for me. I've tried a
> number of times without success. If I update a user in there and set a
> password for them:
> postgres=> select * from pg_shadow;
> usename  |usesysid|usecreatedb|usetrace|usesuper|usecatupd|passwd|valuntil                    
> ---------+--------+-----------+--------+--------+---------+-------+----------------------------
> postgres |     100|t          |t       |t       |t        |       |Sat Jan31 01:00:00 2037 EST
> user1    |    1001|f          |t       |f       |t        |       |                            
> equipment|    1004|f          |t       |f       |t        | MYPASS|                            
> (3 rows)
> 
> This example assumes I've set my password to 'MYPASS'.
> Now I change pg_hba.conf to have a:
> host         equipment   123.123.123.123  255.255.0.0     password 
> 
> Assuming my IP is 123.123.123.123 and the database I need to connect to is
> called equipment and the user is of course equipment...
> 
> I've restarted the server and...

You may need to restart the postmaster, or do a dummy change to a user. 
There is a flat file that contains the pg_shadow contents that gets
updated with normal USER commands, but SQL commands don't update it.  It
is on our TODO list.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 1 Sep 1999 19:23:28 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [SQL] entries in pg_shadow