Thread: Fwd: 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
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 'user=...' 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 'port=...'
(6) the security on your system does not allow the webserver (usually
user 'nobody') to make socket connections to the postmaster
(7) you forgot to specify a 'host=...' 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/ > Please line up in a tree and maintain the heap invariant while > boarding. Thank you for flying nlogn airlines.
Attachment
Re: Fwd: Restarting with pg_ctl, users, and passwords.
From
aklaver@comcast.net (Adrian Klaver)
Date:
-------------- 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 localhostwas 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 localhostwas 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
Re: Fwd: Restarting with pg_ctl, users, and passwords.
From
aklaver@comcast.net (Adrian Klaver)
Date:
-------------- 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
Re: Fwd: Restarting with pg_ctl, users, and passwords.
From
aklaver@comcast.net (Adrian Klaver)
Date:
-------------- 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 witha matching connection type, client address, requested database, and user name is used to perform authentication. Thereis 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 willbe 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
Re: Fwd: Restarting with pg_ctl, users, and passwords.
From
aklaver@comcast.net (Adrian Klaver)
Date:
-------------- 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 readtop 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/ipin 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 readtop 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/ipin 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
Re: Fwd: Restarting with pg_ctl, users, and passwords.
From
aklaver@comcast.net (Adrian Klaver)
Date:
-------------- 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 hostis 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