Обсуждение: logic check of pg_hba.conf configuration
so rarely have problems with postgres that setup or reconfigs are few and far between. In reviewing the docs on this after a recent config error, I am asking for assistance to acid test the following config file. running version 7.0 current pg_hba.conf contains// local all trust host sameuser 127.0.0.1 255.255.255.255 password host all 123.45.678.1 255.255.255.255 trust host all 0.0.0.0 0.0.0.0 reject intended results are// 1) anyone who can ssh into the box should have admin priv anyway, as such trust them 2) anyone accessing it via PHP, Perl, or others using port 5432 but from the same physical machine will get access to the database that they provide username/pw for - expect them to be logging in as their postgres username 3) 123.45.678.1 is a secure box, and needs to access database tables and such... as such it should be able to connect to and do whatever as needed... trusted 4) no one else should from any other machine should be able to access any database resources on this server mods, misconceptions, problems? thanks Dave
"Dave" <dave@hawk-systems.com> writes:
> running version 7.0
7.0? Not even 7.0.something? You really ought to update.
But I digress...
> current pg_hba.conf contains//
> local all trust
> host sameuser 127.0.0.1 255.255.255.255 password
This strikes me as very wrong. Line 1 says that you will trust any
process running on the local host, and then in line 2 you want to be
much more paranoid simply because the connection is loopback TCP and
not Unix-socket transport. Anyone who can get at a Perl script running
on your box can surely have his choice of transport methods from there
to the database; so I don't see the percentage in closing the door to
127.0.0.1 while leaving the Unix-socket door wide open.
Also, if line 2 is intended for scripted access, password authentication
seems like a poor choice. Where's the script going to get the password?
Surely it's a bad idea to encourage people to store passwords directly
in PHP scripts ... yet that's exactly what you'll get with this.
Depending on how your scripting support is set up, you might consider
local all password
host sameuser 127.0.0.1 255.255.255.255 ident
This (assuming you are running a trustworthy ident daemon) would allow
scripts to connect without a password as long as they are running under
the Unix userid matching their database name and database user id; so
the security issue moves away from PG and into whatever you use to
authenticate the right to create/run a script.
Local admins would have to give a password, which is annoying but at
least not a security hole. If you were to update to 7.1 or 7.2 you
would have more flexibility; in particular it'd be possible to shield
the Unix-socket file with filesystem permissions, which would be a much
nicer way of restricting admin access than passwords. (You can't
realistically run pg_dump from a cron task if admin access requires
passwords :-(.)
> host all 123.45.678.1 255.255.255.255 trust
Okay as long as you really really trust all users on that machine.
You might also want to think about whether your network is firewalled
sufficiently that an attacker could not inject packets claiming to be
from that machine.
> host all 0.0.0.0 0.0.0.0 reject
You realize of course that this is redundant (dropping off the end
of pg_hba.conf is an implicit reject). Okay as documentation though.
regards, tom lane
>> running version 7.0 >7.0? Not even 7.0.something? You really ought to update. Havn't seen anyting critical requiring an upgrade... call it laziness or not wanting to fix something that isn't broke and causing problems. This security issue may end up being that reason though. >But I digress... ditto... <clip to everything else> ok, let me step back and come at it this way (at the risk of a RTFM which has been done) Authentication/Security Goals Assuming I want to allow postgres to start up unattended at startup (FreeBSD) so local machine needs to be trusted or the startup script chokes. (recent failure of boot scripts was as a result of us changing everything to password). Startup does an su to user pgsql to run the pg_ctl to start/stop the database on reboot. Can I trust a single user (like pgsql) for this purpose? Assuming that I have multiple users, all with FTP access only (no shell accounts). I do have some of these users with postgres databases, and am managing postgres users with the same ftp username/password, and restricting databases within postgres etc... Requests for these databases will be via PHP or Perl scripts and they will be running as the web server (so user nobody/apache whatever). I want to require these users to place their postgres username and password in their PHP/Perl script in order to access ANY database, and when they provide those, they should only be able to access databases that that user has permissions to access from within postgres... no automatic or passwordless access. Easy to secure the username and passwords for accessing the database with unix file permissions and keeping them out of the web root. I need access to all databases from the 123.45.678.1 server... can provide a username and password since they are scripted items so it doesn't necessarily HAVE to be trust'ed, we can secure the scripts appropriately (probably better than trusting anything anyway). This server is the ONLY server currently that needs to access any database from outside the postgres server itself. If we add others in the future it would be to specific databases and we would probably use the same password as we would with the 123.45.678.1 server since these would be exceptions to the rule. The permissions just don't seem to be designed around that sort of flexibility/restrictions, or at least not the way I am looking at it. I do appreciate the response Tom. Dave