Restarting with pg_ctl, users, and passwords.

Hi,

I just installed postgresql 8.3 on Ubuntu Heron with Postgis. I've
worked with this install on XP before, but not on Linux. I'm having
trouble telling if the postmaster is started. But, maybe more root to
the problem, I cannot log onto the database via 'psql' when I supply
what I think I set as the correct password.

I've created a database and can log into it and do stuff with the
tables using psql. However, I have a CGI app that wants to call the
database (all on the same machine), but gets a 'FATAL: Ident
authentication failed for user "postgres"' error. now, while logged
onto my Linux user account 'postgres', I can psql into my database
without having to provide a password. So, while in there, I issued
the following SQL:

ALTER USER postgres WITH PASSWORD 'postgres';

now, I *thought* what that would do would be to allow me to issue the
'psql' command from my regular non-postgres Linux account and log in
as long as I would issue:

psql -d mydb -U postgres -W

and then provide 'postgres' as the password as well, as I had changed
it in mydb as previously stated. But I am denied access when I try
this from my account. This is what is confusing to me. I suspect
that this may be at the core of why I cannot connect to mydb, but I am
not sure, as I cannot even confirm that the database is running as I
thought it would, since I don't know what process to look for in the
'ps -ef' dump.

Please advise, thanks!

Matt

--
It is from the wellspring of our despair and the places that we are
broken that we come to repair the world.
-- Murray Waas

--
It is from the wellspring of our despair and the places that we are
broken that we come to repair the world.
-- Murray Waas

On Tuesday 19 August 2008 10:28:38 pm Matthew Pettis wrote:

Hi,

I just installed postgresql 8.3 on Ubuntu Heron with Postgis. I've
worked with this install on XP before, but not on Linux. I'm having
trouble telling if the postmaster is started. But, maybe more root to
the problem, I cannot log onto the database via 'psql' when I supply
what I think I set as the correct password.

I've created a database and can log into it and do stuff with the
tables using psql. However, I have a CGI app that wants to call the
database (all on the same machine), but gets a 'FATAL: Ident
authentication failed for user "postgres"' error. now, while logged
onto my Linux user account 'postgres', I can psql into my database
without having to provide a password. So, while in there, I issued
the following SQL:

ALTER USER postgres WITH PASSWORD 'postgres';

now, I *thought* what that would do would be to allow me to issue the
'psql' command from my regular non-postgres Linux account and log in
as long as I would issue:

psql -d mydb -U postgres -W

and then provide 'postgres' as the password as well, as I had changed
it in mydb as previously stated. But I am denied access when I try
this from my account. This is what is confusing to me. I suspect
that this may be at the core of why I cannot connect to mydb, but I am
not sure, as I cannot even confirm that the database is running as I
thought it would, since I don't know what process to look for in the
'ps -ef' dump.

Please advise, thanks!

Matt

I'm a NOVICE too. So take what I say with a grain of salt.

as root
ps -eaf | grep postmaster

will determine if the postgres is working.

The reason you can't connect is due to pg_hba.conf need to be setup correctly.
Since, I don't really understand how to get postgres to use the standard
linux user password file maybe to can post the answer if you get it working.

--
John Fabiani

"Matthew Pettis" <matthew.pettis@gmail.com> writes:

I've created a database and can log into it and do stuff with the
tables using psql. However, I have a CGI app that wants to call the
database (all on the same machine), but gets a 'FATAL: Ident
authentication failed for user "postgres"' error. now, while logged
onto my Linux user account 'postgres', I can psql into my database
without having to provide a password.

If you want to switch to using password authentication, you'll need to
modify your postmaster's pg_hba.conf configuration file, which is
evidently currently set up for ident authentication. Read the "Client
Authentication" chapter of the manual ...

regards, tom lane

Matthew Pettis wrote:

I've created a database and can log into it and do stuff with the
tables using psql. However, I have a CGI app that wants to call the
database (all on the same machine), but gets a 'FATAL: Ident
authentication failed for user "postgres"' error.

You *REALLY* shouldn't use the `postgres' user for applications or
normal use. Create a new non-superuser, grant them the required
priveleges, and use them for the web application.

If you like you can make them the owner of a new database, so they can
do basically whatever they want with that database but not mess with
your others.

See the documentation for CREATE USER, CREATE ROLE, GRANT, and CREATE
DATABASE .

now, while logged
onto my Linux user account 'postgres', I can psql into my database
without having to provide a password.

It's using ident authentication on a UNIX socket connection. The
database knows you are logged in on the UNIX account "postgres" (or have
su'd to it) because the operating system can tell it that. So it trusts you.

The web app will be using a TCP/IP connection to localhost, which (not
being a UNIX socket) cannot use unix socket IDENT auth. It can use ident
for passwordless auth anyway if you have an ident daemon running, but
traditionally you'd use a password instead.

For this to work, the server must be listening for TCP/IP connections
(see postgresql.conf) and be set to accept password auth for at least
the user and database you wish to log in to for 127.0.0.1/32 ("localhost").

now, I *thought* what that would do would be to allow me to issue the
'psql' command from my regular non-postgres Linux account and log in
as long as I would issue:

psql -d mydb -U postgres -W

and then provide 'postgres' as the password as well, as I had changed
it in mydb as previously stated. But I am denied access when I try
this from my account.

Because you're still using ident auth, as psql defaults to a UNIX
socket. It never uses your password because it already knows you're not
authorized according to pg_hba.conf's configuration for local UNIX sockets.

Use a TCP/IP connection instead by adding the argument
"--host localhost"
to the psql command line.

I cannot even confirm that the database is running as I
thought it would, since I don't know what process to look for in the
'ps -ef' dump.

"postgres"

In any case, if the DB wasn't running you'd get connection refused
errors or similar.

$ sudo /etc/init.d/postgresql-8.3 stop
[sudo] password for craig:
* Stopping PostgreSQL 8.3 database server
[ OK ]
$ psql
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket
"/var/run/postgresql/.s.PGSQL.5432"?
$ psql -h 127.0.0.1
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?

--
Craig Ringer

Thanks Craig and Tom,

This is very helpful... once I troubleshoot accessing the database
with the postgres user, I plan on adding other users with more
restrictive credentials... I thought I'd start with this config just
to see if I could get access on a very basic level.

Off to google postgresql.conf and pg_hba.conf... Any suggested pages
for learning this?

Thanks again,
Matt

On Wed, Aug 20, 2008 at 8:18 AM, Craig Ringer
<craig@postnewspapers.com.au> wrote:

Matthew Pettis wrote:

I've created a database and can log into it and do stuff with the
tables using psql. However, I have a CGI app that wants to call the
database (all on the same machine), but gets a 'FATAL: Ident
authentication failed for user "postgres"' error.

You *REALLY* shouldn't use the `postgres' user for applications or
normal use. Create a new non-superuser, grant them the required
priveleges, and use them for the web application.

If you like you can make them the owner of a new database, so they can
do basically whatever they want with that database but not mess with
your others.

See the documentation for CREATE USER, CREATE ROLE, GRANT, and CREATE
DATABASE .

now, while logged
onto my Linux user account 'postgres', I can psql into my database
without having to provide a password.

It's using ident authentication on a UNIX socket connection. The
database knows you are logged in on the UNIX account "postgres" (or have
su'd to it) because the operating system can tell it that. So it trusts you.

The web app will be using a TCP/IP connection to localhost, which (not
being a UNIX socket) cannot use unix socket IDENT auth. It can use ident
for passwordless auth anyway if you have an ident daemon running, but
traditionally you'd use a password instead.

For this to work, the server must be listening for TCP/IP connections
(see postgresql.conf) and be set to accept password auth for at least
the user and database you wish to log in to for 127.0.0.1/32 ("localhost").

now, I *thought* what that would do would be to allow me to issue the
'psql' command from my regular non-postgres Linux account and log in
as long as I would issue:

psql -d mydb -U postgres -W

and then provide 'postgres' as the password as well, as I had changed
it in mydb as previously stated. But I am denied access when I try
this from my account.

Because you're still using ident auth, as psql defaults to a UNIX
socket. It never uses your password because it already knows you're not
authorized according to pg_hba.conf's configuration for local UNIX sockets.

Use a TCP/IP connection instead by adding the argument
"--host localhost"
to the psql command line.

I cannot even confirm that the database is running as I
thought it would, since I don't know what process to look for in the
'ps -ef' dump.

"postgres"

In any case, if the DB wasn't running you'd get connection refused
errors or similar.

$ sudo /etc/init.d/postgresql-8.3 stop
[sudo] password for craig:
* Stopping PostgreSQL 8.3 database server
[ OK ]
$ psql
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket
"/var/run/postgresql/.s.PGSQL.5432"?
$ psql -h 127.0.0.1
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?

--
Craig Ringer

--
It is from the wellspring of our despair and the places that we are
broken that we come to repair the world.
-- Murray Waas

Hi Craig,

Ok, I changed my pg_hba.conf from the commented line to the following
line to try to open things up for now (I know I will need more
security, but I'm trying to open things up wide to find the problem):

--- pg_hba.conf --------------------
# IPv4 local connections:
#host    all         all         127.0.0.1/32          md5
host    all         all         127.0.0.1/32          trust
-------------------------------------------

and made the following change to postgresql.conf (the line was
previously commented out):
--- postgresql.conf ----------------
listen_addresses = 'localhost'
-------------------------------------------

I still am getting the same error from my application (which I believe
is passing on some postgresql error info:

--- App error ----------------------------------------
Error reported was 'FATAL:  Ident authentication failed for user
"postgres"
'.

This error occured when trying to make a connection to the specified
postgresql server.

Most commonly this is caused by
(1) incorrect connection string
(2) you didnt specify a &#39;user=...&#39; in your connection string
(3) the postmaster (postgresql server) isnt running
(4) you are not allowing TCP/IP connection to the postmaster
(5) your postmaster is not running on the correct port - if its not on
5432 you must specify a &#39;port=...&#39;
(6) the security on your system does not allow the webserver (usually
user &#39;nobody&#39;) to make socket connections to the postmaster
(7) you forgot to specify a &#39;host=...&#39; if the postmaster is on
a different machine
(8) you made a typo
------------------------------------------------------------

Any suggestions? I'm using the postgres user and password in my CGI
app (though that shouldn't matter with my current config, right?)

Help is appreciated,
Matt

On Wed, Aug 20, 2008 at 8:18 AM, Craig Ringer
<craig@postnewspapers.com.au> wrote:

Matthew Pettis wrote:

I've created a database and can log into it and do stuff with the
tables using psql. However, I have a CGI app that wants to call the
database (all on the same machine), but gets a 'FATAL: Ident
authentication failed for user "postgres"' error.

You *REALLY* shouldn't use the `postgres' user for applications or
normal use. Create a new non-superuser, grant them the required
priveleges, and use them for the web application.

If you like you can make them the owner of a new database, so they can
do basically whatever they want with that database but not mess with
your others.

See the documentation for CREATE USER, CREATE ROLE, GRANT, and CREATE
DATABASE .

now, while logged
onto my Linux user account 'postgres', I can psql into my database
without having to provide a password.

It's using ident authentication on a UNIX socket connection. The
database knows you are logged in on the UNIX account "postgres" (or have
su'd to it) because the operating system can tell it that. So it trusts you.

The web app will be using a TCP/IP connection to localhost, which (not
being a UNIX socket) cannot use unix socket IDENT auth. It can use ident
for passwordless auth anyway if you have an ident daemon running, but
traditionally you'd use a password instead.

For this to work, the server must be listening for TCP/IP connections
(see postgresql.conf) and be set to accept password auth for at least
the user and database you wish to log in to for 127.0.0.1/32 ("localhost").

now, I *thought* what that would do would be to allow me to issue the
'psql' command from my regular non-postgres Linux account and log in
as long as I would issue:

psql -d mydb -U postgres -W

and then provide 'postgres' as the password as well, as I had changed
it in mydb as previously stated. But I am denied access when I try
this from my account.

Because you're still using ident auth, as psql defaults to a UNIX
socket. It never uses your password because it already knows you're not
authorized according to pg_hba.conf's configuration for local UNIX sockets.

Use a TCP/IP connection instead by adding the argument
"--host localhost"
to the psql command line.

I cannot even confirm that the database is running as I
thought it would, since I don't know what process to look for in the
'ps -ef' dump.

"postgres"

In any case, if the DB wasn't running you'd get connection refused
errors or similar.

$ sudo /etc/init.d/postgresql-8.3 stop
[sudo] password for craig:
* Stopping PostgreSQL 8.3 database server
[ OK ]
$ psql
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket
"/var/run/postgresql/.s.PGSQL.5432"?
$ psql -h 127.0.0.1
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?

--
Craig Ringer

--
It is from the wellspring of our despair and the places that we are
broken that we come to repair the world.
-- Murray Waas

On Wed, Aug 20, 2008 at 11:38 AM, Matthew Pettis
<matthew.pettis@gmail.com> wrote:

Hi Craig,

Ok, I changed my pg_hba.conf from the commented line to the following
line to try to open things up for now (I know I will need more
security, but I'm trying to open things up wide to find the problem):

--- pg_hba.conf --------------------
# IPv4 local connections:
#host    all         all         127.0.0.1/32          md5
host    all         all         127.0.0.1/32          trust
-------------------------------------------

and made the following change to postgresql.conf (the line was
previously commented out):
--- postgresql.conf ----------------
listen_addresses = 'localhost'
-------------------------------------------

I still am getting the same error from my application (which I believe
is passing on some postgresql error info:

--- App error ----------------------------------------
Error reported was 'FATAL:  Ident authentication failed for user
"postgres"
'.

You need to restart the postgresql service for the listen_addresses to
take effect, and either restarting or reloading will make the changes
you made in pg_hba.conf take effect.

SOLVED.

Yep, Restart was done.

The issue turned out not to be with Postgresql config, but the app
config. In the app, I define a connection string, which has user,
password, and databasename. When I had this same configuration on
WinXP, I did not need to specify a fourth parameter, the host, which
explicitly told the app to use host=localhost. When I added the host
param to the connection string, it all went through.

On the bright side, I learned a lot about how to restart the service
and the config files...

Curious: Any ideas why I can leave the host off my connection string
in WinXP, but not Linux? It it an idiosyncracy of my app, or of
PostgreSQL?

Thanks for all the help,
Matt

On Wed, Aug 20, 2008 at 1:27 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:

On Wed, Aug 20, 2008 at 11:38 AM, Matthew Pettis
<matthew.pettis@gmail.com> wrote:

Hi Craig,

Ok, I changed my pg_hba.conf from the commented line to the following
line to try to open things up for now (I know I will need more
security, but I'm trying to open things up wide to find the problem):

--- pg_hba.conf --------------------
# IPv4 local connections:
#host    all         all         127.0.0.1/32          md5
host    all         all         127.0.0.1/32          trust
-------------------------------------------

and made the following change to postgresql.conf (the line was
previously commented out):
--- postgresql.conf ----------------
listen_addresses = 'localhost'
-------------------------------------------

I still am getting the same error from my application (which I believe
is passing on some postgresql error info:

--- App error ----------------------------------------
Error reported was 'FATAL:  Ident authentication failed for user
"postgres"
'.

You need to restart the postgresql service for the listen_addresses to
take effect, and either restarting or reloading will make the changes
you made in pg_hba.conf take effect.

--
It is from the wellspring of our despair and the places that we are
broken that we come to repair the world.
-- Murray Waas

On Wed, Aug 20, 2008 at 01:58:37PM -0500, Matthew Pettis wrote:

Curious: Any ideas why I can leave the host off my connection string
in WinXP, but not Linux? It it an idiosyncracy of my app, or of
PostgreSQL?

No host specified on a UNIX system means "unix domain socket" which is
essentially a way of doing a socket connection on the local machine.
You can acheive the same effect by host=/path/to/socket.

Leaving off the host in windows I'm not sure what it does.

Have a ncie day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

-------------- Original message ----------------------
From: "Matthew Pettis" <matthew.pettis@gmail.com>

SOLVED.

Yep, Restart was done.

The issue turned out not to be with Postgresql config, but the app
config. In the app, I define a connection string, which has user,
password, and databasename. When I had this same configuration on
WinXP, I did not need to specify a fourth parameter, the host, which
explicitly told the app to use host=localhost. When I added the host
param to the connection string, it all went through.

On the bright side, I learned a lot about how to restart the service
and the config files...

Curious: Any ideas why I can leave the host off my connection string
in WinXP, but not Linux? It it an idiosyncracy of my app, or of
PostgreSQL?

Thanks for all the help,
Matt

Is the Linux app running on the Postgres server machine?
If so I hazard a guess that you have a line like:

local all all trust

before your host line in pg_hba.

The app connecting from the same machine would try the local socket (local) before the localhost(tcp/ip), unless localhost was specified in the connection string.

--
Adrian Klaver
aklaver@comcast.net

Below is the pg_hba.conf file exerpt (minus a lot of comments)... the
line starting '#host' was my attempt at opening up the db as wide as
possible just to see if i could get in somehow...

--- pg_hba.conf --------------------------------------
# Database administrative login by UNIX sockets
local   all         postgres                          ident sameuser

# TYPE DATABASE USER CIDR-ADDRESS METHOD

# "local" is for Unix domain socket connections only
local all all ident sameuser
# IPv4 local connections:
host all all 127.0.0.1/32 md5
#host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 md5
------------------------------------------------------------------------------------------

On Wed, Aug 20, 2008 at 3:32 PM, Adrian Klaver <aklaver@comcast.net> wrote:

-------------- Original message ----------------------
From: "Matthew Pettis" <matthew.pettis@gmail.com>

SOLVED.

Yep, Restart was done.

The issue turned out not to be with Postgresql config, but the app
config. In the app, I define a connection string, which has user,
password, and databasename. When I had this same configuration on
WinXP, I did not need to specify a fourth parameter, the host, which
explicitly told the app to use host=localhost. When I added the host
param to the connection string, it all went through.

On the bright side, I learned a lot about how to restart the service
and the config files...

Curious: Any ideas why I can leave the host off my connection string
in WinXP, but not Linux? It it an idiosyncracy of my app, or of
PostgreSQL?

Thanks for all the help,
Matt

Is the Linux app running on the Postgres server machine?
If so I hazard a guess that you have a line like:

local all all trust

before your host line in pg_hba.

The app connecting from the same machine would try the local socket (local) before the localhost(tcp/ip), unless localhost was specified in the connection string.

--
Adrian Klaver
aklaver@comcast.net

--
It is from the wellspring of our despair and the places that we are
broken that we come to repair the world.
-- Murray Waas

-------------- Original message ----------------------
From: aklaver@comcast.net (Adrian Klaver)

-------------- Original message ----------------------
From: "Matthew Pettis" <matthew.pettis@gmail.com>

SOLVED.

Yep, Restart was done.

The issue turned out not to be with Postgresql config, but the app
config. In the app, I define a connection string, which has user,
password, and databasename. When I had this same configuration on
WinXP, I did not need to specify a fourth parameter, the host, which
explicitly told the app to use host=localhost. When I added the host
param to the connection string, it all went through.

On the bright side, I learned a lot about how to restart the service
and the config files...

Curious: Any ideas why I can leave the host off my connection string
in WinXP, but not Linux? It it an idiosyncracy of my app, or of
PostgreSQL?

Thanks for all the help,
Matt

Is the Linux app running on the Postgres server machine?
If so I hazard a guess that you have a line like:

local all all trust

Should have been:

local all all some non-functional authentication method

this would cause the connection to the socket to fail assuming the authentication method selected did not work.

before your host line in pg_hba.

The app connecting from the same machine would try the local socket (local)
before the localhost(tcp/ip), unless localhost was specified in the connection
string.

--
Adrian Klaver
aklaver@comcast.net

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Adrian Klaver
aklaver@comcast.net

-------------- Original message ----------------------
From: "Matthew Pettis" <matthew.pettis@gmail.com>

Below is the pg_hba.conf file exerpt (minus a lot of comments)... the
line starting '#host' was my attempt at opening up the db as wide as
possible just to see if i could get in somehow...

--- pg_hba.conf --------------------------------------
# Database administrative login by UNIX sockets
local   all         postgres                          ident sameuser

The line above is the reason you have to specify localhost in your connection string.
From:
http://www.postgresql.org/docs/8.3/interactive/auth-pg-hba-conf.html
Each record specifies a connection type, a client IP address range (if relevant for the connection type), a database name, a user name, and the authentication method to be used for connections matching these parameters. The first record with a matching connection type, client address, requested database, and user name is used to perform authentication. There is no "fall-through" or "backup": if one record is chosen and the authentication fails, subsequent records are not considered. If no record matches, access is denied.

Unless you are logged in as the OS user postgres and connecting as such the local line will fail and the connection will be aborted. To skip over the local line and get the host line you have specify localhost in your connection string.

# TYPE DATABASE USER CIDR-ADDRESS METHOD

# "local" is for Unix domain socket connections only
local all all ident sameuser
# IPv4 local connections:
host all all 127.0.0.1/32 md5
#host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 md5
--------------------------------------------------------------------------------
----------

On Wed, Aug 20, 2008 at 3:32 PM, Adrian Klaver <aklaver@comcast.net> wrote:

-------------- Original message ----------------------
From: "Matthew Pettis" <matthew.pettis@gmail.com>

SOLVED.

Yep, Restart was done.

The issue turned out not to be with Postgresql config, but the app
config. In the app, I define a connection string, which has user,
password, and databasename. When I had this same configuration on
WinXP, I did not need to specify a fourth parameter, the host, which
explicitly told the app to use host=localhost. When I added the host
param to the connection string, it all went through.

On the bright side, I learned a lot about how to restart the service
and the config files...

Curious: Any ideas why I can leave the host off my connection string
in WinXP, but not Linux? It it an idiosyncracy of my app, or of
PostgreSQL?

Thanks for all the help,
Matt

Is the Linux app running on the Postgres server machine?
If so I hazard a guess that you have a line like:

local all all trust

before your host line in pg_hba.

The app connecting from the same machine would try the local socket (local)

before the localhost(tcp/ip), unless localhost was specified in the connection
string.

--
Adrian Klaver
aklaver@comcast.net

--
Adrian Klaver
aklaver@comcast.net

would the 'ident sameuser' entry qualify as a 'some non-functional
authentication method'?

On Wed, Aug 20, 2008 at 3:48 PM, Adrian Klaver <aklaver@comcast.net> wrote:

-------------- Original message ----------------------
From: aklaver@comcast.net (Adrian Klaver)

-------------- Original message ----------------------
From: "Matthew Pettis" <matthew.pettis@gmail.com>

SOLVED.

Yep, Restart was done.

The issue turned out not to be with Postgresql config, but the app
config. In the app, I define a connection string, which has user,
password, and databasename. When I had this same configuration on
WinXP, I did not need to specify a fourth parameter, the host, which
explicitly told the app to use host=localhost. When I added the host
param to the connection string, it all went through.

On the bright side, I learned a lot about how to restart the service
and the config files...

Curious: Any ideas why I can leave the host off my connection string
in WinXP, but not Linux? It it an idiosyncracy of my app, or of
PostgreSQL?

Thanks for all the help,
Matt

Is the Linux app running on the Postgres server machine?
If so I hazard a guess that you have a line like:

local all all trust

Should have been:

local all all some non-functional authentication method

this would cause the connection to the socket to fail assuming the authentication method selected did not work.

before your host line in pg_hba.

The app connecting from the same machine would try the local socket (local)
before the localhost(tcp/ip), unless localhost was specified in the connection
string.

--
Adrian Klaver
aklaver@comcast.net

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Adrian Klaver
aklaver@comcast.net

--
It is from the wellspring of our despair and the places that we are
broken that we come to repair the world.
-- Murray Waas

-------------- Original message ----------------------
From: "Matthew Pettis" <matthew.pettis@gmail.com>

would the 'ident sameuser' entry qualify as a 'some non-functional
authentication method'?

Yes. Basically you only get one shot at each connection to satisfy the requirements of a pg_hba line. The lines are read top to bottom, so if you have restrictive line at the top that your connection cannot satisfy then you are locked out. As has been mentioned on Linux the default action is to connect via the local socket in the absence of a host name/ip in the connection string.So in your case with no host specified the connection would attempt a socket connection. The first socket line is:
local all postgres ident sameuser

so you would need to be logged in as the Linux user postgres to make the connection.

On Wed, Aug 20, 2008 at 3:48 PM, Adrian Klaver <aklaver@comcast.net> wrote:

-------------- Original message ----------------------
From: aklaver@comcast.net (Adrian Klaver)

-------------- Original message ----------------------
From: "Matthew Pettis" <matthew.pettis@gmail.com>

SOLVED.

Yep, Restart was done.

The issue turned out not to be with Postgresql config, but the app
config. In the app, I define a connection string, which has user,
password, and databasename. When I had this same configuration on
WinXP, I did not need to specify a fourth parameter, the host, which
explicitly told the app to use host=localhost. When I added the host
param to the connection string, it all went through.

On the bright side, I learned a lot about how to restart the service
and the config files...

Curious: Any ideas why I can leave the host off my connection string
in WinXP, but not Linux? It it an idiosyncracy of my app, or of
PostgreSQL?

Thanks for all the help,
Matt

Is the Linux app running on the Postgres server machine?
If so I hazard a guess that you have a line like:

local all all trust

Should have been:

local all all some non-functional

authentication method

this would cause the connection to the socket to fail assuming the

authentication method selected did not work.

before your host line in pg_hba.

The app connecting from the same machine would try the local socket (local)
before the localhost(tcp/ip), unless localhost was specified in the

connection

string.

--
Adrian Klaver
aklaver@comcast.net

--
Adrian Klaver
aklaver@comcast.net

So, since I run my CGI under a non-'postgres' user, is that the line
that would govern my authentication, and then fail me? Because I
thought with 'postgres' listed as the 3rd spot, this line would not
apply, and would move on to a different governing rule...

On Wed, Aug 20, 2008 at 4:21 PM, Adrian Klaver <aklaver@comcast.net> wrote:

-------------- Original message ----------------------
From: "Matthew Pettis" <matthew.pettis@gmail.com>

would the 'ident sameuser' entry qualify as a 'some non-functional
authentication method'?

Yes. Basically you only get one shot at each connection to satisfy the requirements of a pg_hba line. The lines are read top to bottom, so if you have restrictive line at the top that your connection cannot satisfy then you are locked out. As has been mentioned on Linux the default action is to connect via the local socket in the absence of a host name/ip in the connection string.So in your case with no host specified the connection would attempt a socket connection. The first socket line is:
local all postgres ident sameuser

so you would need to be logged in as the Linux user postgres to make the connection.

On Wed, Aug 20, 2008 at 3:48 PM, Adrian Klaver <aklaver@comcast.net> wrote:

-------------- Original message ----------------------
From: aklaver@comcast.net (Adrian Klaver)

-------------- Original message ----------------------
From: "Matthew Pettis" <matthew.pettis@gmail.com>

SOLVED.

Yep, Restart was done.

The issue turned out not to be with Postgresql config, but the app
config. In the app, I define a connection string, which has user,
password, and databasename. When I had this same configuration on
WinXP, I did not need to specify a fourth parameter, the host, which
explicitly told the app to use host=localhost. When I added the host
param to the connection string, it all went through.

On the bright side, I learned a lot about how to restart the service
and the config files...

Curious: Any ideas why I can leave the host off my connection string
in WinXP, but not Linux? It it an idiosyncracy of my app, or of
PostgreSQL?

Thanks for all the help,
Matt

Is the Linux app running on the Postgres server machine?
If so I hazard a guess that you have a line like:

local all all trust

Should have been:

local all all some non-functional

authentication method

this would cause the connection to the socket to fail assuming the

authentication method selected did not work.

before your host line in pg_hba.

The app connecting from the same machine would try the local socket (local)
before the localhost(tcp/ip), unless localhost was specified in the

connection

string.

--
Adrian Klaver
aklaver@comcast.net

--
Adrian Klaver
aklaver@comcast.net

--
It is from the wellspring of our despair and the places that we are
broken that we come to repair the world.
-- Murray Waas

-------------- Original message ----------------------
From: "Matthew Pettis" <matthew.pettis@gmail.com>

So, since I run my CGI under a non-'postgres' user, is that the line
that would govern my authentication, and then fail me? Because I
thought with 'postgres' listed as the 3rd spot, this line would not
apply, and would move on to a different governing rule...

Sorry I was being thick. I failed to see the second local line. Yes it should pick either the next local line if no host is specified or the next host line if one is specified.

On Wed, Aug 20, 2008 at 4:21 PM, Adrian Klaver <aklaver@comcast.net> wrote:

-------------- Original message ----------------------
From: "Matthew Pettis" <matthew.pettis@gmail.com>

would the 'ident sameuser' entry qualify as a 'some non-functional
authentication method'?

Yes. Basically you only get one shot at each connection to satisfy the

requirements of a pg_hba line. The lines are read top to bottom, so if you have
restrictive line at the top that your connection cannot satisfy then you are
locked out. As has been mentioned on Linux the default action is to connect via
the local socket in the absence of a host name/ip in the connection string.So in
your case with no host specified the connection would attempt a socket
connection. The first socket line is:

local all postgres ident sameuser

so you would need to be logged in as the Linux user postgres to make the

connection.

On Wed, Aug 20, 2008 at 3:48 PM, Adrian Klaver <aklaver@comcast.net> wrote:

-------------- Original message ----------------------
From: aklaver@comcast.net (Adrian Klaver)

-------------- Original message ----------------------
From: "Matthew Pettis" <matthew.pettis@gmail.com>

SOLVED.

Yep, Restart was done.

The issue turned out not to be with Postgresql config, but the app
config. In the app, I define a connection string, which has user,
password, and databasename. When I had this same configuration on
WinXP, I did not need to specify a fourth parameter, the host, which
explicitly told the app to use host=localhost. When I added the host
param to the connection string, it all went through.

On the bright side, I learned a lot about how to restart the service
and the config files...

Curious: Any ideas why I can leave the host off my connection string
in WinXP, but not Linux? It it an idiosyncracy of my app, or of
PostgreSQL?

Thanks for all the help,
Matt

Is the Linux app running on the Postgres server machine?
If so I hazard a guess that you have a line like:

local all all trust

Should have been:

local all all some non-functional

authentication method

this would cause the connection to the socket to fail assuming the

authentication method selected did not work.

before your host line in pg_hba.

The app connecting from the same machine would try the local socket

(local)

before the localhost(tcp/ip), unless localhost was specified in the

connection

string.

--
Adrian Klaver
aklaver@comcast.net

--
Adrian Klaver
aklaver@comcast.net

--
Adrian Klaver
aklaver@comcast.net