Debian setup: postgres problem?

Jens_Porup · June 25, 2004, 6:48am

G’day,

I am trying to install Request Tracker on Debian testing.

root@request-tracker:~# psql -h 127.0.0.1 -d template1 -U rtuser -W
Password:
psql: could not connect to server: Connection refused
Is the server running on host "127.0.0.1" and accepting
TCP/IP connections on port 5432?
root@request-tracker:~#

I’ve read the postgresql manual, which helpfully informs me that " A
common mistake is to forget to configure the server to allow TCP/IP
connections."

…But then fails to tell me how to do that!

In my fiddling, at other times, I have managed to get manual login to
the database as rtuser to work, but then when I try to run the
/usr/sbin/rt-setup-database file, it fails on line 110. (The dbi
connect line)

A test perl script shows that specifying “host:localhost” on the dsn
causes the $dbi->connect to fail, and if I remove that bit I can
connect fine–in my test.pl script. But I don’t want to have to go
hacking RT code!

I’m an experienced Perl programmer, but postgres is brand new to me, and
so is request tracker.

Thanks,

Jens

Jens_Porup · June 25, 2004, 6:56am

More info:

If I don’t specify a host with the psql -h flag, this works fine:

root@request-tracker:~# psql -d template1 -U rtuser -W
Password:
Welcome to psql 7.4.2, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

template1=>

And my /etc/postgresql/postgresql.conf file includes the uncommented lines:

tcpip_socket = true
port = 5432

The tcpip_socket is the same as the postmaster -i flag which “Allows clients
to connect via TCP/IP”.

The server is running using /etc/init.d/postgresql start, although nmap
localhost does not list 5432 as being open.

I’m up against a brick wall here, people. Please help?

Thanks,

Jens

Stephen_Quinney · June 25, 2004, 12:31pm

G’day,

I am trying to install Request Tracker on Debian testing.

root@request-tracker:~# psql -h 127.0.0.1 -d template1 -U rtuser -W
Password:
psql: could not connect to server: Connection refused
Is the server running on host “127.0.0.1” and accepting
TCP/IP connections on port 5432?
root@request-tracker:~#

Have you read all the information in the README.Debian.gz and
INSTALL.Debian.gz located in the /usr/share/doc/request-tracker3/?

They are the best place to start and may well already answer your
questions.

Stephen Quinney

signature.asc (189 Bytes)

Karl_Hegbloom · June 25, 2004, 3:32pm

I am trying to install Request Tracker on Debian testing.

What’s in your /etc/postgresql/pg_hba.conf file? You need:

host template1 rtuser 127.0.0.1 255.255.255.255 md5

local template1 rtuser md5

host rtdb rtuser 127.0.0.1 255.255.255.255 md5
local rtdb rtuser md5

… right after where it says “put your actual configuration here”. The
lines I have commented off should be uncommented until after you have
loaded the initial RT database. After you edit this file, you must run
“/etc/init.d/postgresql restart”. The IP address and mask that
connections are allowed from can be for another host, if your database
and RT are on separate boxes.

Rather than waiting for “nmap” to see what ports are open, why not just
use “netstat”? “netstat --tcp -lp” will show you open TCP ports
programs are listening on, along with what programs they are.

Karl Hegbloom hegbloom@pdx.edu

Jens_Porup · June 28, 2004, 2:07am

G’day,

I am trying to install Request Tracker on Debian testing.

Have you read all the information in the README.Debian.gz and
INSTALL.Debian.gz located in the /usr/share/doc/request-tracker3/?

They are the best place to start and may well already answer your
questions.

Stephen Quinney

Yes!

I have read, re-read, and re-read INSTALL.Debian and README.Debian
backwards, forwards, and upside down, and I still can’t get RT
installed!

To recap my troubles:

I can successfully connect to the freshly installed postgres
installation like so:

    root@request-tracker:~# psql -d template1 -U rtuser -W
    Password:
    Welcome to psql 7.4.2, the PostgreSQL interactive terminal.

    Type:  \copyright for distribution terms
    \h for help with SQL commands
    \? for help on internal slash commands
    \g or terminate with semicolon to execute query
    \q to quit

    template1=> \q

But when I try and run the RT setup script for the RT database, I get:

    root@request-tracker:~# /usr/sbin/rt-setup-database --action init --dba rtuser
    --prompt-for-dba-password
    In order to create a new database and grant RT access to that database,
    this script needs to connect to your Pg instance on localhost as rtuser.
    Please specify that user's database password below. If the user has no database
    password, just press return.

    Password: DBI connect('dbname=template1;host=localhost','rtuser',...) failed:
    could not connect to server: Connection refused at /usr/sbin/rt-setup-database
    line 110
    Failed to connect to dbi:Pg:dbname=template1;host=localhost as rtuser: could not
    connect to server: Connection refused at /usr/sbin/rt-setup-database line 110,
    <STDIN> line 1.

So let’s make sure postgres is running:

    root@request-tracker:~# /etc/init.d/postgresql restart
    Stopping PostgreSQL database server: autovacuumNo pg_autovacuum found running;
    none killed.
    postmaster.
    Starting PostgreSQL database server: postmaster autovacuum.
    root@request-tracker:~#

But if I tail the logs during the server restart, this line pops up just after
the restart finishes:

    root@request-tracker:~# tail -f /var/log/postgresql/postgres.log
    ...<snip>...
    LOG:  unexpected EOF on client connection

But a simple Perl script connects to the database without dying, like so:

    use DBI;
    my $dsn = "dbi:Pg:dbname=template1";

    ### But this dsn fails!!
    ### my $dsn = "dbi:Pg:dbname=template1:host:localhost";

	### For some reason adding host:localhost makes everything die!!!

    my $dbh = DBI->connect($dsn, "rtuser", "wibble") or die "doh!\n";

And yes, I do have the correct lines in my /etc/postgresql/pg_hba.conf
– except for that didn’t work, for some reason postgres was actually
reading my /var/lib/postgres/data/pg_hba.conf instead, so I’ve symlinked
the /etc conf file to the /var/lib file.

Please, please. I HAVE rtfm’ed, and I’m absolutely stumped on this one. A
thoughtful ponder of what’s wrong would be very, very much appreciated.

Thanks,

Jens

Jens_Porup · June 28, 2004, 4:16am

curiouser and curiouser.

RT installs perfectly out of the box on my desktop, running Debian unstable.

I’m trying to install RT on a virtual server, a user-mode linux “machine”.
Could this have anything to do with my problem?

Jens

Stephen_Quinney · June 28, 2004, 8:52am

But if I tail the logs during the server restart, this line pops up just after
the restart finishes:
    root@request-tracker:~# tail -f /var/log/postgresql/postgres.log
    ...<snip>...
    LOG:  unexpected EOF on client connection
But a simple Perl script connects to the database without dying, like so:
    use DBI;
    my $dsn = "dbi:Pg:dbname=template1";

    ###
    ### But this dsn fails!!
    ### my $dsn = "dbi:Pg:dbname=template1:host:localhost";

  ###
  ### For some reason adding host:localhost makes everything die!!!

    my $dbh = DBI->connect($dsn, "rtuser", "wibble") or die "doh!\n";
And yes, I do have the correct lines in my /etc/postgresql/pg_hba.conf
– except for that didn’t work, for some reason postgres was actually
reading my /var/lib/postgres/data/pg_hba.conf instead, so I’ve symlinked
the /etc conf file to the /var/lib file.

Which version of the Debian postgresql package do you have installed?
If this is the case it is a bug in the packaging of postgres.

It sounds to me from your description of the problem that there is
something odd going on with your pg_hba.conf. Could you reply with all
the non-comment lines so I can see what you’ve got there at the
moment.

Stephen Quinney

Jens_Porup · June 29, 2004, 2:53am

But if I tail the logs during the server restart, this line pops up just after
the restart finishes:
    root@request-tracker:~# tail -f /var/log/postgresql/postgres.log
    ...<snip>...
    LOG:  unexpected EOF on client connection
And yes, I do have the correct lines in my /etc/postgresql/pg_hba.conf
– except for that didn’t work, for some reason postgres was actually
reading my /var/lib/postgres/data/pg_hba.conf instead, so I’ve symlinked
the /etc conf file to the /var/lib file.
Which version of the Debian postgresql package do you have installed?
If this is the case it is a bug in the packaging of postgres.

It sounds to me from your description of the problem that there is
something odd going on with your pg_hba.conf. Could you reply with all
the non-comment lines so I can see what you’ve got there at the
moment.

Version is:

root@request-tracker:~# psql --version
psql (PostgreSQL) 7.4.2
contains support for command-line editing
root@request-tracker:~#

and

root@request-tracker:~# apt-cache show postgresql
Package: postgresql
Priority: optional
Section: misc
Installed-Size: 9160
Maintainer: Oliver Elphick <Oliver.Elphick@lfix.co.uk>
Architecture: i386
Version: 7.4.2-4
<...>

And my pg_hba.conf:

host    template1   rtuser    127.0.0.1    255.255.255.255   password
local   template1   rtuser                                   password
host    rtdb        rtuser    127.0.0.1    255.255.255.255   password
local   rtdb        rtuser                                   password

local   all         postgres                                        ident sameuser

local   all         all                                             ident sameuser
host    all         all         127.0.0.1         255.255.255.255   ident sameuser
host    all         all         ::1 ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff        ident sameuser
host    all         all         ::ffff:127.0.0.1/128                ident sameuser
host    all         all         0.0.0.0           0.0.0.0           reject

Thanks for your help, Stephen.

Jens

Les_Mikesell · June 29, 2004, 3:48am

root@request-tracker:~# psql --version
psql (PostgreSQL) 7.4.2
contains support for command-line editing
root@request-tracker:~#

I don’t know about the debian packaging, but postgresql
defaults to using a unix domain socket and you have to
either start postmaster with the -i option or put
tcpip_socket=true in postgresql.conf to make it use
tcp sockets like perl/DBI want.

Les Mikesell
les@futuresource.com

Jens_Porup · June 29, 2004, 3:54am

root@request-tracker:~# psql --version
psql (PostgreSQL) 7.4.2
contains support for command-line editing
root@request-tracker:~#
I don’t know about the debian packaging, but postgresql
defaults to using a unix domain socket and you have to
either start postmaster with the -i option or put
tcpip_socket=true in postgresql.conf to make it use
tcp sockets like perl/DBI want.

Both of these lines are uncommented in my /etc/postgresql/postgresql.conf:

tcpip_socket = true
port = 5432

Jens

Stephen_Quinney · June 29, 2004, 8:41am

But a simple Perl script connects to the database without dying, like so:

    use DBI;
    my $dsn = "dbi:Pg:dbname=template1";

    ###
    ### But this dsn fails!!
    ### my $dsn = "dbi:Pg:dbname=template1:host:localhost";

  ###
  ### For some reason adding host:localhost makes everything die!!!

    my $dbh = DBI->connect($dsn, "rtuser", "wibble") or die "doh!\n";

Having looked at the perldoc for DBD::Pg I can see that the $dsn which
died on connecting in this script is wrong. You want something like:

$dsn = “dbi:Pg:dbname=template1;host=localhost;”

Try it again with that dsn and see if you can connect to the database.

Stephen

Jens_Porup · June 30, 2004, 2:59am

But a simple Perl script connects to the database without dying, like so:
    use DBI;
    my $dsn = "dbi:Pg:dbname=template1";

    ###
    ### But this dsn fails!!
    ### my $dsn = "dbi:Pg:dbname=template1:host:localhost";

This was a typo in my original post, it did fail with host=localhost
(That stray colon was a typo in my post, but not in my test script)

  ###
  ### For some reason adding host:localhost makes everything die!!!

    my $dbh = DBI->connect($dsn, "rtuser", "wibble") or die "doh!\n";
Having looked at the perldoc for DBD::Pg I can see that the $dsn which
died on connecting in this script is wrong. You want something like:

$dsn = “dbi:Pg:dbname=template1;host=localhost;”

Try it again with that dsn and see if you can connect to the database.

Here is the nub of the problem.

I can connect with:

$dsn = "dbi:Pg:dbname=template1;";

but not with

$dsn = "dbi:Pg:dbname=template1;host=localhost;";

(or with the following, I assumed that semicolon was a typo of yours)
	$dsn = "dbi:Pg:dbname=template1:host=localhost;";

And of course it is the latter that rt-setup-database wants to use.

The database error I get on my DBI->connect() or die is:

root@request-tracker:~# perl test.pl
DBI connect('dbname=template1;host=localhost;','rtuser',...) failed: could not
connect to server: Connection refused at test.pl line 13

and also:

root@request-tracker:~# perl test.pl
DBI connect('dbname=template1:host=localhost;','rtuser',...) failed: FATAL:
IDENT authentication failed for user "rtuser" at test.pl line 13

By the way, there was a typo in my previous post. That extra colon was,
in fact an = in my script. I have thoroughly double checked everything.
It connects without the host specified, and dies if host=localhost is
in the $dsn.

Jens

Jens_Porup · June 30, 2004, 3:30am

Worth mentioning:

An apt-get upgrade on my Debian testing server brought down a new version of
request-tracker3 and rt3-clients. This has had no effect on my problem:

root@request-tracker:~# /usr/sbin/rt-setup-database --action init --dba rtuser \
>     --prompt-for-dba-password
In order to create a new database and grant RT access to that database,
this script needs to connect to your Pg instance on localhost as rtuser.
Please specify that user's database password below. If the user has no database
password, just press return.

Password: DBI connect('dbname=template1;host=localhost','rtuser',...) failed:
could not connect to server: Connection refused at /usr/sbin/rt-setup-database
line 110
Failed to connect to dbi:Pg:dbname=template1;host=localhost as rtuser: could not
connect to server: Connection refused at /usr/sbin/rt-setup-database line 110,
<STDIN> line 1.

Thanks for your help,

Jens

Stephen_Quinney · June 30, 2004, 8:23am

and also:

root@request-tracker:~# perl test.pl
DBI connect(‘dbname=template1:host=localhost;’,‘rtuser’,…) failed: FATAL:
IDENT authentication failed for user “rtuser” at test.pl line 13

ok, the problem is definitely something to do with your pg_hba.conf
file, I do not know what though as it looked ok to me before. The
IDENT failure shows that it is trying to use ident authentication when
a host is specified, it is working when you connect locally as that is
allowing password authentication.

The key to this problem is that the order of the lines in the
pg_hba.conf does matter, it will take the first one that matches and
use that, if it fails there is no drop through to other lines. It
should be picking up the lines at the top mentioning the database
template1 with user rtuser but for some reason it isn’t. Maybe those
IPV6 lines lower down are confusing matters?

Stephen

Sebastian_Flothow1 · June 30, 2004, 11:17am

Here is the nub of the problem.

I can connect with:

$dsn = “dbi:Pg:dbname=template1;”;

but not with

$dsn = “dbi:Pg:dbname=template1;host=localhost;”;

(or with the following, I assumed that semicolon was a typo of yours)
$dsn = “dbi:Pg:dbname=template1:host=localhost;”;

And of course it is the latter that rt-setup-database wants to use.

What happens when you set the hostname to “” (empty string) instead of
“localhost”?

Sebastian

Sebastian Flothow
sebastian@flothow.de

Because it reverses the logical flow of conversation.