Thread: 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... 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
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
> 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, Pennsylvania19026