| View previous topic :: View next topic |
| Author |
Message |
Ast0r
Guru
Joined: 11 Apr 2006
Posts: 404
Location: Dallas, Tx - USA
|
 Posted: Mon Oct 09, 2006 6:42 pm Post subject: PostgreSQL security strategy |
 |
|
I have a machine that is running PostgreSQL and serving numerous databases, each with a unique user that is used to access that customer's account information. It's very important that the users be isolated an unable to change data in other user's databases.
I have a decent grasp of TCP/IP and database permissions, but one issue is still nagging at me. I have set up pg_hba.conf to require password authentication for all users connecting locally (php scripts read their configuration information from flat text files in their respective directories and use that information to log in) and to only allow a few select users to connect remotely (myself, my boss, and a couple of developers at our company). However, I have some maintenance scripts that need to perform superuser actions and I currently have them using the default user (postgres) to accomplish these ends (creating databases, using pg_dump/pg_restore, fixing ownership, etc). In order to script pg_dump and pg_restore I have to use -U postgres, because running pg_dump through an exec statement doesn't allow for the script to send it a password, it just dies.
My question is: what is the best way to secure this so that I don't have to connect with a known superuser. I was thinking of creating an obscure user-name with superuser privledges and letting that user connect locally and then setting a password for the postgres user. Right now, anyone who has a shell account or FTP access could execute commands as the postgres superuser (I know this is very dangerous) and I want to make sure there is no chance of being compromised. However, I still need to be able to execute superuser commands in my scripts (note: the scripts all do data validation and they are not in a publicly accessable area of our site, so I'm not really worried about people exploiting them ... just worried about if someone got FTP access and ran a malicious script ... or worse yet, shell access). |
|
| Back to top |
|
 |
dga
n00b
Joined: 10 Apr 2007
Posts: 2
|
| Posted: Tue Apr 10, 2007 5:23 pm Post subject: postgres local high security |
|
|
If your database names are the same as the client login names, then this should accomplish the seperation.
on the local line in pg_hba.conf use the ident method with 'sameuser' i.e.
| Code: | local sameuser all ident sameuser
local all postgres ident sameuser |
The first line means that sessions accessing the database server via the unix domain socket are their system account name in the database and may only use the database called their system account name.
i.e. user account 'bob' can only log into the database as 'bob' and can only connect to the database named 'bob'
Your postgres security is equivalent to your system security at this point. This means that any user on your system that can get a different user id ( via su. sudo etc ) can log in to postgres at those users, and, effectively the entry in the password file for the system controls postgres logins as well. However, remember that postgres has its own user list and access to database resources is controlled by that (this concept is expanded below).
The second config line allows postres to access databases which are not called 'postgres' (i.e. the 'bob' database from above).
Anyone who needs access to a database not named as their system login name will need a similar line. (i.e. you and your dba's)
To do your backups, add a line to postgres's crontab to run the backup scripts ( the system runs postgres's crontab as the postgres user so it gets into the database ) or a 'su postgres ... ' in the root crontab which has the same effect. The scripts could be run manually by a user with su or sudo permission (your administrative staff for example).
Also, since the postgres user doesn't have to have a usable system login ( i.e. there is a system account for postgres who owns the database etc. but the password is locked so that no one can login as postgres ) , only people with root access on the server can do anything as postgres ( su, sudo, crontab etc. )
The ftp user shouldnt need a database account so it is totally locked out. There are no passwords to know (or guess).
Users created in postgres via the createuser (shell command) or CREATE USER (sql command) can use the database only with the permissions they have been granted for the database/tables. Your clients should not have 'create user' or 'database superuser' permission and probabaly not even 'create database' permission. If you use sameuser in the database field in pg_hba.conf then the user can only connect to a database bearing their system login name and may not change databases thereafter. This disables passing a database name on the command line and effectively makes the \c psql command a noop etc.
For remote logins, you have to use some other method, because ident is totally unsecure from remote hosts. If you can get your remote functionality by, for example, tunneling a psql session over ssh, the local ... ident method still works since you are logged in to a system account via the ssh tunnel.
I use this system for my web server to get access to postgres. It can only be the web server user and has strict controls about which tables and databases it can use. No one can log into the system as the web server, and so the only access as the web server user to the database is via the web site interface. (Me runnning sudo -u webserver psql excluded of course). Also, I completely disallow all non local access to the database because I can, so to use the database one must login to the system first.
If you have 8.1 or later, you can have your dba's inherit ( or use set role interactively ) the client role names so that they can administrate the databases without having to be the database superuser. This helps to prevent the 'big oopses' ( like DROP DATABASE ... ) etc. There are many other ways roles can make life easier for everyone as well.
if you use a pg_hba.conf line like
| Code: | | local all all ident sameuser |
This will allow accounts to change databases and potentially look at the table names but they cannot do select, insert, update etc in any area without explicit permissions. This is probably not as secure as the more restrictive set above but it is more secure than passwords. It does allow any account the potential (via GRANT) to see more than one database which may be desirable in certian situations. But, this type of access can be granted account by account via the 'local all <user> ident sameuser' line though, without the security impliications of an 'all all' type of global setting, since the default behaviour for restricted users then remains that they can't even connect to other databases at all.
Also look at the comments in pg_hba.conf as they have some other interesting notes as well. |
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
Networking & Security
