Thread: PostgreSQL Qs
Hey folks,
I've been trying to figure out the user authentication stuff in
PostgreSQL, and am finding it more confusing than mysql.
My system runs Debian, I installed postgresql from apt. It seemed to
proceed correctly (no errors were tossed my way). As user postgres, I
created the db and table structure. Again, seemed to proceed correctly.
I changed /etc/postgresql/pg_hba.conf to:
local all md5
host all 127.0.0.1 255.0.0.0 md5
host dbname 0.0.0.0 255.255.255.255 md5
Then, I went to add the DBD-Pg module for Perl (compiled from scratch).
I set the env vars:
export DBI_DSN=dbi:Pg:dbname=template1
export DBI_USER=postgres
export DBI_PASS=xxxxxx
Then I ran 'make test'. I got a lot of:
..
t/99cleanup.........DBI connect('dbname=template1','postgres',...) failed:
FATAL 1: Password authentication failed for user "postgres" at
t/99cleanup.t line 11
..
Changing AUTH_TYPE from 'md5' to 'password' gives the same error. The
only way I can get anything to run (the test scripts or the script below)
is to change the AUTH_TYPE to 'trust' (Note: I can run the script below as
user postgres if I leave the AUTH_TYPE and AUTH_ARGUMENT as the default
values [ident and sameuser], but, since I don't run an ident server, this
doesn't make sense to me.) Obviously, I don't want to leave the AUTH_TYPE
as trust, but I'm not sure what I need to do to get a username/password
combo to work (particularly within a perl script using DBI [When I tested
this using mysql, I had it working and found it much simpler].
Could someone explain or give me a link to a doc that explains what is
going on as far as user authentication is concerned? Actually, a basic
howto guide for PGSQL would be most helpful for many things.
Cheers!
Jon
Test Script (from O'Reilly's Programming the Perl DBI):
#!/usr/bin/perl -w
use DBI;
my @drivers = DBI->available_drivers();
die "No drivers.\n" unless @drivers;
foreach my $driver (@drivers) {
print "Driver: $driver\n";
my @datasources = DBI->data_sources($driver);
foreach my $datasource (@datasources) {
print "\tData source is $datasource\n";
}
print "\n";
}
exit;
On Wednesday 07 May 2003 5:35 pm, Jon Earle wrote:
> Hey folks,
>
> I've been trying to figure out the user authentication stuff in
> PostgreSQL, and am finding it more confusing than mysql.
Yep - more options than you can shake a stick at. They all have fans though.
> My system runs Debian, I installed postgresql from apt. It seemed to
> proceed correctly (no errors were tossed my way). As user postgres, I
> created the db and table structure. Again, seemed to proceed correctly.
>
> I changed /etc/postgresql/pg_hba.conf to:
>
> local all md5
> host all 127.0.0.1 255.0.0.0 md5
> host dbname 0.0.0.0 255.255.255.255 md5
What version is this? I've got something like (note the extra column):
# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
host all all 127.0.0.1 255.255.255.255 md5
> Then, I went to add the DBD-Pg module for Perl (compiled from scratch).
> I set the env vars:
>
> export DBI_DSN=dbi:Pg:dbname=template1
> export DBI_USER=postgres
> export DBI_PASS=xxxxxx
>
> Then I ran 'make test'. I got a lot of:
>
> ..
> t/99cleanup.........DBI connect('dbname=template1','postgres',...) failed:
> FATAL 1: Password authentication failed for user "postgres" at
> t/99cleanup.t line 11
I take it the password works when you use psql?
psql -Upostgres template1
If not, set your access to trust, start up psql as above and use:
ALTER USER postgres ENCRYPTED PASSWORD 'newpasshere';
Now back to md5 trust setting and see if that does it.
> Could someone explain or give me a link to a doc that explains what is
> going on as far as user authentication is concerned? Actually, a basic
> howto guide for PGSQL would be most helpful for many things.
One thing to bear in mind is that the database password has nothing to do with
your system password for user "postgres" (unless you're using PAM auth).
--
Richard Huxton
Don't have time to really answer your question, except to say that there's nothing wrong with AUTH_TYPE of trust. If the machine is a trusted machine, then anyone who breaks in will likely be able to read the username/password from a config file anyway. I usually run Postgres on a UNIX-domain socket only, with AUTH_TYPE being trust. Jon
On Wed, 7 May 2003 mallah@trade-india.com wrote: > Hi , > > shudn't "host" lines in pg_hba.conf be in format > host database user ip mask method > > in lines below u missed the user part: > > > host all 127.0.0.1 255.0.0.0 md5 > > host dbname 0.0.0.0 255.255.255.255 md5 I don't believe so. From the pg_hba.conf docs: # host # ---- # # This record identifies networked hosts that are permitted to connect # via IP connections. # # Format: # # host DBNAME IP_ADDRESS ADDRESS_MASK AUTH_TYPE [AUTH_ARGUMENT] I ended up solving the problem however. With a colleague offering thoughts, we determined that there is a system table named pg_shadow that contains the usernames and passwords. Didn't see that in the docs (I was thinking a file named pg_shadow would be magically created somewhere...). Next, I'd done two things wrong with the alter user command - first, I put quotes around the username and password. Apparently, just the password needs 'em. Next, I omitted the trailing ; and failed to notice the very subtle change in the command prompt that indicated it was waiting for input. Off now to get myself further in trouble with the beast! :) Thanks for the responses! Cheers! Jon -- Jon Earle SAVE FARSCAPE http://www.savefarscape.com/ While it's okay to disagree with your friends and family, childish insults do not express sovereignty.
On Wed, 2003-05-07 at 18:32, Richard Huxton wrote: > On Wednesday 07 May 2003 5:35 pm, Jon Earle wrote: > > My system runs Debian, I installed postgresql from apt. It seemed to > > proceed correctly (no errors were tossed my way). As user postgres, I > > created the db and table structure. Again, seemed to proceed correctly. > > > > I changed /etc/postgresql/pg_hba.conf to: > > > > local all md5 > > host all 127.0.0.1 255.0.0.0 md5 > > host dbname 0.0.0.0 255.255.255.255 md5 > > What version is this? I've got something like (note the extra column): > # TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD > host all all 127.0.0.1 255.255.255.255 md5 I assume he is using Debian stable, which has 7.2.1 (and will have for evermore, dammit!, until the next stable release.) -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "Dearly beloved, avenge not yourselves, but rather give place unto wrath. For it is written, Vengeance is mine; I will repay, saith the Lord. Therefore if thine enemy hunger, feed him; if he thirst, give him drink; for in so doing thou shalt heap coals of fire on his head. Be not overcome of evil, but overcome evil with good." Romans 12:19-21
On Wed, May 07, 2003 at 14:45:51 -0400, Jon Earle <je_pgsql@kronos.honk.org> wrote: > On Wed, 7 May 2003 mallah@trade-india.com wrote: > > > Hi , > > > > shudn't "host" lines in pg_hba.conf be in format > > host database user ip mask method Note the format changed not too long ago (between 7.1 and 7.2 I think).
> export DBI_DSN=dbi:Pg:dbname=template1 > export DBI_USER=postgres > export DBI_PASS=xxxxxx Routinely accessing your databases as postgres is akin to running your Debian system as root all the time. Create a DB user, give proper rights to that user and go from there. Also, avoid changing template1 unless you know what you are doing. Create and use another DB for testing. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On Wed, 7 May 2003, Oliver Elphick wrote: > I assume he is using Debian stable, which has 7.2.1 (and will have for > evermore, dammit!, until the next stable release.) Yes, that's what I'm using (v7.2.1-2woody2). Is it worth upgrading? Cheers! Jon -- Jon Earle SAVE FARSCAPE http://www.savefarscape.com/ While it's okay to disagree with your friends and family, childish insults do not express sovereignty.
On Wed, 2003-05-07 at 21:19, Jon Earle wrote: > On Wed, 7 May 2003, Oliver Elphick wrote: > > > I assume he is using Debian stable, which has 7.2.1 (and will have for > > evermore, dammit!, until the next stable release.) > > Yes, that's what I'm using (v7.2.1-2woody2). Is it worth upgrading? Yes. The release managers will allow only security bugs to be fixed in the stable release. If you upgrade, you get non-security bugs fixed, plus all the new features of 7.3. The downside is that there is a somewhat greater risk of packaging bugs, and of bugs introduced by 7.3, but 7.3 itself has been out for some months now. If you have real data, be sure to have good dumps before you start upgrading, then let the package attempt an automatic upgrade. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "Dearly beloved, avenge not yourselves, but rather give place unto wrath. For it is written, Vengeance is mine; I will repay, saith the Lord. Therefore if thine enemy hunger, feed him; if he thirst, give him drink; for in so doing thou shalt heap coals of fire on his head. Be not overcome of evil, but overcome evil with good." Romans 12:19-21
On Wed, May 07, 2003 at 16:19:10 -0400, Jon Earle <je_pgsql@kronos.honk.org> wrote: > On Wed, 7 May 2003, Oliver Elphick wrote: > > > I assume he is using Debian stable, which has 7.2.1 (and will have for > > evermore, dammit!, until the next stable release.) > > Yes, that's what I'm using (v7.2.1-2woody2). Is it worth upgrading? You should at least upgrade to 7.2.4 as there are important bug fixes. You won't have to do an initdb to do that upgrade. You might want to upgrade to 7.3.2 as there are some nice new features, but you will have to be more careful about the upgrade process to make sure some of the changes don't cause problems.
On Thu, 8 May 2003, Bruno Wolff III wrote: > On Wed, May 07, 2003 at 16:19:10 -0400, > Jon Earle <je_pgsql@kronos.honk.org> wrote: > > On Wed, 7 May 2003, Oliver Elphick wrote: > > > > > I assume he is using Debian stable, which has 7.2.1 (and will have for > > > evermore, dammit!, until the next stable release.) > > > > Yes, that's what I'm using (v7.2.1-2woody2). Is it worth upgrading? > > You should at least upgrade to 7.2.4 as there are important bug fixes. > You won't have to do an initdb to do that upgrade. > > You might want to upgrade to 7.3.2 as there are some nice new features, > but you will have to be more careful about the upgrade process to make > sure some of the changes don't cause problems. Well, I've just started out with postgresql, and haven't yet started into development, so perhaps I'll purge the stable package and grab the new one. -- Jon Earle SAVE FARSCAPE http://www.savefarscape.com/ While it's okay to disagree with your friends and family, childish insults do not express sovereignty.