PostgreSQL Qs

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

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 think pg_hba.conf is pretty cool otherwise.

regds
mallah.

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;

---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and
unsubscribe commands go to majordomo@postgresql.org

-----------------------------------------
Get your free web based email at trade-india.com.
"India's Leading B2B eMarketplace.!"
http://www.trade-india.com/

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, 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.

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, 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).

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

he's probably running 7.2.x

Robert Treat

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.