Thread: The pg_hba.conf file
Hi all, I recently upgraded to 7.3 on my Debian system, and found that the format of this file has changed slightly. So, since I need to revisit the file, I figured I might as well send a longish note. I've never found host based access with Postgres to be intuitive. Maybe this is good, maybe bad. I think other people have problems with it as well. I'm picking some examples from stuff on my system (which is Debian), not to complain about them, just as a source of examples. 1 ---------------------------------- First off, we have 3 different kinds of records: local, host and hostssl. local DBASE USER METHOD [OPTION] host DBASE USER IP_ADD IP_MASK METHOD [OPTION] hostssl DBASE USER IP_ADD IP_MASK METHOD [OPTION] To me, it would seem "more pleasing" if the local record looked a little more like the host and hostssl records, with something there for the IP_ADD and IP_MASK fields. Ideas I've had are: "x.x.x.x", "NotApplicable" and "NA". Something. That way field #0 is always the access method, field #1 is the DBASE name, field #2 is the user name, .... For local access, the 4th and 5th fields would just be ignored. It's an idea anyway. I can't say I've seen anything on the mailing list talking about the assymmetry of local versus host/hostssl. 2 -------------------------------- Next, authenication involving ident. From what I've been told by more security concious people than myself, that ident can not be trusted off of the local machine. And, I've seen some ident daemons which apparently can be configured to lie. Which leads me to ask the question, is ident trustworthy for local connections? Certainly these lying ident daemons could lie on a network connection, but are they also involved in local (socket) connections? Or is it the kernel which is responding with the UID of the other end of the socket? 3 ----------------------------- It's not unusual for maintainence to be done via cron jobs on the database(s). I gather this probably needs to be done by the postgres user in all cases (are there exceptions?). Debian currently suggests in do.maintainence local all ident sameuser (this looks like a pre 7.3 line which should read?) local all all ident sameuser Another "superuser" type access to PostgreSQL is PHPPGAdmin. From the Debian README, I have a choice of local all password and host all 127.0.0.1 255.255.255.255 crypt (Oh, neither line looks like a 7.3 line.) Webmin? I can't find any file under /usr/share/doc/webmin* which talks about pg_hba.conf settings. Surely it makes assumptions on what is in pg_hba.conf for it to work. To me, it sure looks like to use PHP PGAdmin I need to make sure that $cfgServers[1]['local'] = false so that I access PostgreSQL via the host 127.0.01 as I can't think of any method which allows the do.maintainence cron job to connect using ident and PGAdmin to connect using password. They are both working with all databases as the postgres user, so the first line used (either ident or password) would get used and the other ignored. Anyway, above I have 2 differing needs just from a setup point of view. I also may have other local needs. I don't have any problems with the idea of local connections from the UNIX postgres user being trusted to all databases. But why do these recommendations have such huge scope? It's not unusual for a user to have a database with the same name as his/her UID, so I guess we could allow that by policy. Pg_hba.conf allows for access to databases owned by some group. And I have some programs which do not involve a UNIX UID, and would like to allow access via passwords of some kind. Wouldn't something like: local all postgres ident sameuser local sameuser all ident sameuser local samegroup all ident sameuser local @l_other all md5 be a good, generic setup for pg_hba.conf involving local connections? (Where the file l_other, contained the names of other local databases I wanted to let people access by password.) 3.1 --------------------------------------------- In the above local ... example, I suspect having "ident sameuser" as the authentication allowing access to the database "sameuser" restricts completely, but the "all" wildcard for the user seems out of place. Something like: local sameuser sameuser ident sameuser seems to better describe the situation, that I only want these connections by UNIX UIDs to databases with the same name as the UNIX UID. And I am only guessing that the line involving samegroup works as written. 4 ------------------------------------------------- It's not unusual to see sample pg_hba.conf files, which have a host all all 0.0.0.0 0.0.0.0 reject line at the end. Should a person have similar lines for hostssl and local connections? I.e.: local all all reject host all all 0.0.0.0 0.0.0.0 reject hostssl all all 0.0.0.0 0.0.0.0 reject Sorry for the too-longish note. But, I'm a little unsure about this, and I think others are as well. Maybe it's just me. :-) Gord -- Matter Realisations http://www.materialisations.com/ Gordon Haverland, B.Sc. M.Eng. President 101 9504 182 St. NW Edmonton, AB, CA T5T 3A7 780/481-8019 ghaverla @ freenet.edmonton.ab.ca 780/993-1274 (alt.)
On Wed, Dec 18, 2002 at 22:38:59 -0700, ghaverla@freenet.edmonton.ab.ca wrote: > > 2 -------------------------------- > Next, authenication involving ident. From what I've been told by > more security concious people than myself, that ident can not be > trusted off of the local machine. And, I've seen some ident daemons That depends on whether or not you trust the admistrator of the machine. Under some circumstances it may make sense to trust ident on remote machines. > which apparently can be configured to lie. Which leads me to ask > the question, is ident trustworthy for local connections? Certainly Yes. If you can't trust the local machine, then you shouldn't be running your postgres server on it. Note that ident for "local" (this doesn't include connections to 127.0.0.1) connections does not depend on an ident server. > 3.1 --------------------------------------------- > In the above local ... example, I suspect having "ident sameuser" > as the authentication allowing access to the database "sameuser" > restricts completely, but the "all" wildcard for the user seems out > of place. Something like: > local sameuser sameuser ident sameuser > seems to better describe the situation, that I only want these > connections by UNIX UIDs to databases with the same name as > the UNIX UID. The 'all' in the users field indicates that all users can use the database matching their username. If you only want some users to be able to do this you can use a list or group there. Having 'sameuser' there wouldn't make much sense since the supplied user name is also the same as the supplied user name. > It's not unusual to see sample pg_hba.conf files, which have > a > host all all 0.0.0.0 0.0.0.0 reject > line at the end. Should a person have similar lines for hostssl > and local connections? I.e.: Well the default will be to reject connections, so they aren't really needed except to prevent accidents. However 'host' will match 'hostssl' connections (but not vice versa), so you don't need hostssl in addition to host if you do that. 'local' connections are different and need a separate entry.
On Thu, 19 Dec 2002, Bruno Wolff III wrote: > On Wed, Dec 18, 2002 at 22:38:59 -0700, > ghaverla@freenet.edmonton.ab.ca wrote: > > > > 2 -------------------------------- [ ... ] > > which apparently can be configured to lie. Which leads me to ask > > the question, is ident trustworthy for local connections? Certainly > > Yes. If you can't trust the local machine, then you shouldn't be running > your postgres server on it. Note that ident for "local" (this doesn't > include connections to 127.0.0.1) connections does not depend on an ident > server. Since I admin the local machine, I guess I can trust myself. But I was looking to see that identity for the "local" connection has nothing to do with any ident daemon I may be running (or not running, which is what I would prefer). > > 3.1 --------------------------------------------- > > In the above local ... example, I suspect having "ident sameuser" > > as the authentication allowing access to the database "sameuser" > > restricts completely, but the "all" wildcard for the user seems out > > of place. Something like: > > local sameuser sameuser ident sameuser > > seems to better describe the situation, that I only want these > > connections by UNIX UIDs to databases with the same name as > > the UNIX UID. > > The 'all' in the users field indicates that all users can use the > database matching their username. If you only want some users to > be able to do this you can use a list or group there. Having > 'sameuser' there wouldn't make much sense since the supplied user name is > also the same as the supplied user name. It never occured to me, that you would only want to put a subset of names in the USER field. I agree that having "sameuser" isn't very clear, but then I didn't think "all" was completely clear either. "All" seems to mean different things in different contexts. > > It's not unusual to see sample pg_hba.conf files, which have > > a > > host all all 0.0.0.0 0.0.0.0 reject > > line at the end. Should a person have similar lines for hostssl > > and local connections? I.e.: > > Well the default will be to reject connections, so they aren't really needed > except to prevent accidents. However 'host' will match 'hostssl' connections > (but not vice versa), so you don't need hostssl in addition to host if you > do that. 'local' connections are different and need a separate entry. I just like to be in the habit of having an explicit "Default" for any "switches" I have in code. Having an explicit reject for local seemed to be a good thing to add. But, thanks for the clairification. Gord -- Matter Realisations http://www.materialisations.com/ Gordon Haverland, B.Sc. M.Eng. President 101 9504 182 St. NW Edmonton, AB, CA T5T 3A7 780/481-8019 ghaverla @ freenet.edmonton.ab.ca 780/993-1274 (alt.)
On Thu, Dec 19, 2002 at 08:00:46 -0700, ghaverla@freenet.edmonton.ab.ca wrote: > > Since I admin the local machine, I guess I can trust myself. > But I was looking to see that identity for the "local" connection > has nothing to do with any ident daemon I may be running (or > not running, which is what I would prefer). If you use the 'local' connection it uses a domain socket and some OS' provide a getpeerid syscall to obtain the uid of the using on the other end of the connection. If you use a 'host' connection to the local host then ident works using the local machine's ident server.
I have two files below. Why does the first file not pass values to the second file? Tom First file <HTML><TITLE>Test form</TITLE> <body>Test Form<BR> <form method="POST" action="test_create_acct.php"> <input type="text" name="func" value="strtoupper"><p> First Name: <br> <input type="text" name="fname"><p> <input type="submit" name="submit" value="submit"> </body></html> file: test_create_acct.php <?php $res = $func($fname); echo "$res"; ?> Fatal error: Call to undefined function: () in /var/www/html/php_lib_login_includes/ test_create_acct.php on line 2
> I have two files below. Why does the first file not pass
> values to the second file?
I suspect you are using the old style way to pass variables and in later
versions of PHP that has changed. I'm not sure what version, I think it
changed in 7.1, 7.2 for sure.
You now find the passed variables in the $_REQUEST array, so if you wanted
to load another variable in the script you passed it to::
$func = $_REQUEST['func'];
That could be one problem.....
brew
> Tom
>
> First file
> <HTML><TITLE>Test form</TITLE>
> <body>Test Form<BR>
> <form method="POST" action="test_create_acct.php">
> <input type="text" name="func" value="strtoupper"><p>
> First Name: <br>
> <input type="text" name="fname"><p>
> <input type="submit" name="submit" value="submit">
> </body></html>
>
>
> file: test_create_acct.php
>
> <?php
> $res = $func($fname);
> echo "$res";
> ?>
>
> Fatal error: Call to undefined function: () in
> /var/www/html/php_lib_login_includes/ test_create_acct.php on line 2
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
==========================================================================
Strange Brew (brew@theMode.com)
Check out my Musician's Online Database Exchange (The MODE Pages)
http://www.TheMode.com
==========================================================================
I put
$func = $_REQUEST('func');
as my second line and got
Fatal error: Call to undefined function: array() in
/var/www/html/php_lib_login_includes/ test_create_acct.php on line 2
So I searched on $_REQUEST in the PHP manual web page
http://www.php.net/manual/en/preface.php
and found no entries.
Uh is this a joke for newbies?
Cheers, Tom
On 21 Dec 2002 at 18:41, brew@theMode.com wrote:
>
> > I have two files below. Why does the first file not pass
> > values to the second file?
>
> I suspect you are using the old style way to pass variables and in later
> versions of PHP that has changed. I'm not sure what version, I think it
> changed in 7.1, 7.2 for sure.
>
> You now find the passed variables in the $_REQUEST array, so if you wanted
> to load another variable in the script you passed it to::
>
> $func = $_REQUEST['func'];
>
> That could be one problem.....
>
> brew
>
>
> > Tom
> >
> > First file
> > <HTML><TITLE>Test form</TITLE>
> > <body>Test Form<BR>
> > <form method="POST" action="test_create_acct.php">
> > <input type="text" name="func" value="strtoupper"><p>
> > First Name: <br>
> > <input type="text" name="fname"><p>
> > <input type="submit" name="submit" value="submit">
> > </body></html>
> >
> >
> > file: test_create_acct.php
> >
> > <?php
> > $res = $func($fname);
> > echo "$res";
> > ?>
> >
> > Fatal error: Call to undefined function: () in
> > /var/www/html/php_lib_login_includes/ test_create_acct.php on line 2
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
> >
>
> ==========================================================================
> Strange Brew (brew@theMode.com)
> Check out my Musician's Online Database Exchange (The MODE Pages)
> http://www.TheMode.com
> ==========================================================================
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
I found the answer to this. Hi <?php echo $_POST["name"]; ?>. You are <?php echo $_POST["age"]; ?> years old. From the php documentation. These are autoglobals. There is a $_GET also. Tom On 21 Dec 2002 at 2:31, hodges@xprt.net wrote: > I have two files below. Why does the first file not pass > values to the second file? > > Tom > > First file > <HTML><TITLE>Test form</TITLE> > <body>Test Form<BR> > <form method="POST" action="test_create_acct.php"> > <input type="text" name="func" value="strtoupper"><p> > First Name: <br> > <input type="text" name="fname"><p> > <input type="submit" name="submit" value="submit"> > </body></html> > > > file: test_create_acct.php > > <?php > $res = $func($fname); > echo "$res"; > ?> > > Fatal error: Call to undefined function: () in > /var/www/html/php_lib_login_includes/ test_create_acct.php on line 2 > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
Tom......
> Hi <?php echo $_POST["name"]; ?>.
> You are <?php echo $_POST["age"]; ?> years old.
>
> >From the php documentation. These are autoglobals.
>
> There is a $_GET also.
... and the $_REQUEST array covers both posts and gets, so it is more
secure to use either a $_GET or $_POST depending on your situation......
Note that in your earlier post this morning you didn't put the array
subscript in square brackets, i.e. $_REQUEST['func'] , you put it in
parentheses, i.e. $_REQUEST('func), which indicates a function call, not
an array subscript.
> > Fatal error: Call to undefined function: () in
> > /var/www/html/php_lib_login_includes/ test_create_acct.php on line 2
It wasn't a joke.........
brew
There was another problem with the library I am using,
session_register() no longer works. Here is the fix
This seems to have fixed it (about line 300 in
login.inc.php):
session_start();
if (! isset ($_SESSION['gUser']))
{$gUser = '';}
else {$gUser=$_SESSION['gUser'];}
Now the php_lib_logins_include library seems to work with current
php, postgresql, and redhat 8.
Thanks for all the help and suggestions.
Tom
On 22 Dec 2002 at 8:35, hodges@xprt.net wrote:
> I found the answer to this.
>
> Hi <?php echo $_POST["name"]; ?>.
> You are <?php echo $_POST["age"]; ?> years old.
>
> >From the php documentation. These are autoglobals.
>
> There is a $_GET also.
>
> Tom
>
> On 21 Dec 2002 at 2:31, hodges@xprt.net wrote:
>
> > I have two files below. Why does the first file not pass
> > values to the second file?
> >
> > Tom
> >
> > First file
> > <HTML><TITLE>Test form</TITLE>
> > <body>Test Form<BR>
> > <form method="POST" action="test_create_acct.php">
> > <input type="text" name="func" value="strtoupper"><p>
> > First Name: <br>
> > <input type="text" name="fname"><p>
> > <input type="submit" name="submit" value="submit">
> > </body></html>
> >
> >
> > file: test_create_acct.php
> >
> > <?php
> > $res = $func($fname);
> > echo "$res";
> > ?>
> >
> > Fatal error: Call to undefined function: () in
> > /var/www/html/php_lib_login_includes/ test_create_acct.php on line 2
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
Hi, I have a Java web app that works just fine when deployed as localhost, but gives a connection error: IDENT authentication failed for user "myself" and then throws an exception: Connection could not be established. when deploying the same app as a non-localhost (e.g., www.mydomain.com), on the same server. tcpip_socket is on. And we have this in pg_hba: local all ident sameuser host all 127.0.0.1 255.0.0.0 ident sameuser host all xxx.yy.zzz.vv 255.255.255.255 ident sameuser host all 0.0.0.0 0.0.0.0 reject where xxx.yy.zzz.vv stands for the IP in the DNS record with .tv corporation. In both cases, I'm connecting with the Linux network login and password, so it probably is consistent with sameuser mode. That way I can connect both from command line and from localhost-based app, but not as a non-local-host. P.S. It has to be something simple, like: host all xxx.yy.zzz.vv 255.0.0.0 ident sameuser perhaps. I'd appreciate any feedback on this. P.P.S. The connectivity so far is straight-forward: no datasources, or jndi. P.P.P.S. The localhost-based app is running as myself on port 8080, whereas the non-localhost-based app is running as root on port 80. Do I perhaps have to have to be in the sudoers list for postgres to connect when the server runs as root? I'm not connecting to system DBs, however. I'm only connecting to my DB. Thanks, r.
"Reshat Sabiq" <sabiq@purdue.edu> writes:
> P.P.P.S. The localhost-based app is running as myself on port 8080,
> whereas the non-localhost-based app is running as root on port 80.
Well, then IDENT is going to reject the connection, because the actual
Unix login name on the client side doesn't match the postgres username
you're trying to use. (There's also theory B: you don't have a working
IDENT service on the client machine at all.)
Since I cannot imagine any good reason for running a client as root when
it doesn't absolutely have to be root, my advice is to use a saner login
setup on the client: viz, run the app as postgres, or whatever userid
you want it to be for postgres purposes. Otherwise, use some different
authentication setup than "ident sameuser".
regards, tom lane
I've been running the web app on port 8080 all this time. Only now the
conf files were updated as follows:
host all xxx.yy.zzz.vv 255.255.255.255 ident newmode
newmode root username
where xxx.yy.zzz.vv is external IP of localhost.
I'm still getting an exception when connecting from the web ap on port
80, however:
2003-01-25 14:49:44 A connection error has occurred: FATAL 1: IDENT
authentication failed for user "username"
at
org.postgresql.jdbc1.AbstractJdbc1Connection.openConnection(AbstractJdbc
1Connection.java:202)
at org.postgresql.Driver.connect(Driver.java:122)
What happens apparently is that the server runs as root, so my web app
on port 80 also runs as root, and it tries to connect as root.
These additions are now pending authorization:
local all ident
newmode
host all 127.0.0.1 255.0.0.0 ident
newmode
I'd appreciate any further suggestions. The web app runs just fine on
port 8080, when server runs as "username".
P.S. The server runs as root, and that is not likely to change. It
appears that IDENT is functioning, but how could that be checked other
than the message above?
Sincerely,
r.
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, January 01, 2003 2:31 PM
To: Reshat Sabiq
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] localhost connects, same code on other host fails
"Reshat Sabiq" <sabiq@purdue.edu> writes:
> P.P.P.S. The localhost-based app is running as myself on port 8080,
> whereas the non-localhost-based app is running as root on port 80.
Well, then IDENT is going to reject the connection, because the actual
Unix login name on the client side doesn't match the postgres username
you're trying to use. (There's also theory B: you don't have a working
IDENT service on the client machine at all.)
Since I cannot imagine any good reason for running a client as root when
it doesn't absolutely have to be root, my advice is to use a saner login
setup on the client: viz, run the app as postgres, or whatever userid
you want it to be for postgres purposes. Otherwise, use some different
authentication setup than "ident sameuser".
regards, tom lane