Thread: Getting a DB password to work without editing pg_hba.conf, possible?
Hi all,
I am working on an installer for my program that creates a postgres
database and user (the installer is written in perl and runs as 'root').
I want to find a way to let the user set the password on the new
database and have postgres actually ask for it without editing the
default 'pg_hba.conf' file, if at all possible.
I know how to set the password on the user:
CREATE USER "foo" WITH PASSWORD 'secret' CREATEDB NOCREATEUSER;
and from what I can tell there is no way to put a password on a
database. I create the database using:
CREATE DATABASE "bar" OWNER "foo";
From what I read in the docs, this *should* limit access to the 'bar'
database to only the 'postgres' and 'foo' (I can't find now where in the
postgres docs I read that so I may be wrong).
The problems are:
- Connections are limited to the matching system account ('foo' in
this case) which is good, but it doesn't require the password to connect.
- A normal user connected to another database can switch to the 'bar'
database using '\c foo' without requiring a password.
When I have played with the 'pg_hba.conf' file by adding the line:
# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
# Database administrative login by UNIX sockets
local bar foo md5
local all postgres ident sameuser
I find that when I try to connect to the DB 'bar' as the system user
'foo' I *do* get prompted for the password. However, when I try
connecting as another user I get in without being prompted for a
password at all.
Any help with this would be much appreciated!
Madison
--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Madison Kelly (Digimer)
TLE-BU; The Linux Experience, Back Up
Main Project Page: http://tle-bu.org
Community Forum: http://forum.tle-bu.org
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Madison Kelly <linux@alteeve.com> writes:
> I want to find a way to let the user set the password on the new
> database and have postgres actually ask for it without editing the
> default 'pg_hba.conf' file, if at all possible.
There is no such animal as a "database password" in PG. There are user
passwords. You can grant or deny a user access to a database altogether,
but you can't predicate it on him supplying a password different from
his (single) login password.
regards, tom lane
Tom Lane wrote:
> Madison Kelly <linux@alteeve.com> writes:
>
>> I want to find a way to let the user set the password on the new
>>database and have postgres actually ask for it without editing the
>>default 'pg_hba.conf' file, if at all possible.
>
>
> There is no such animal as a "database password" in PG. There are user
> passwords. You can grant or deny a user access to a database altogether,
> but you can't predicate it on him supplying a password different from
> his (single) login password.
>
> regards, tom lane
Thanks for the reply!
May I ask then? What *is* considered "best practices" for securing a
database in PostgreSQL? Assuming I leave the 'pg_hba.conf' file at it's
default values, is there any real point to having a password on a
postgresql user account? I've been reading the docs but I guess I am
overthinking the problem or missing something obvious. :p
Madison
--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Madison Kelly (Digimer)
TLE-BU; The Linux Experience, Back Up
Main Project Page: http://tle-bu.org
Community Forum: http://forum.tle-bu.org
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
On Fri, Dec 16, 2005 at 02:09:52PM -0500, Madison Kelly wrote: > May I ask then? What *is* considered "best practices" for securing a > database in PostgreSQL? Assuming I leave the 'pg_hba.conf' file at it's > default values, is there any real point to having a password on a > postgresql user account? I've been reading the docs but I guess I am > overthinking the problem or missing something obvious. :p If someone can login without being asked for a password, that generally means the system is setup not to ask. I'm not sure what you mean by "default" configuration, since you are probably using the one installed by your distro. It's very hard to see what the problem is unless you post your full pg_hba.conf and the actual command-lines you used, including which UNIX user you used. The two lines you gave would allow the postgres UNIX user to login to any database as himself without a password, and allow foo into bar with md5 authentication. If you are seeing something else you should be explicit how you're logging in. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
Madison Kelly <linux@alteeve.com> writes:
> May I ask then? What *is* considered "best practices" for securing a
> database in PostgreSQL? Assuming I leave the 'pg_hba.conf' file at it's
> default values, is there any real point to having a password on a
> postgresql user account?
Well, if there were a single "best practice" then we'd not need to offer
so many options ;-). It depends a lot on your needs and environment.
On a single-user machine where you're not allowing any remote
connections, you might as well use "trust" --- I tend to run all my
development installations that way. Ident can be pretty convenient too
for local users (I wouldn't trust it for remote connections though).
Otherwise you probably need passwords.
In any case, this just applies to whether you let someone connect or
not. What they can do after they've connected is a different
discussion. For that you use SQL privileges (GRANT/REVOKE).
regards, tom lane
Martijn van Oosterhout wrote:
> On Fri, Dec 16, 2005 at 02:09:52PM -0500, Madison Kelly wrote:
>
>>May I ask then? What *is* considered "best practices" for securing a
>>database in PostgreSQL? Assuming I leave the 'pg_hba.conf' file at it's
>>default values, is there any real point to having a password on a
>>postgresql user account? I've been reading the docs but I guess I am
>>overthinking the problem or missing something obvious. :p
>
>
> If someone can login without being asked for a password, that generally
> means the system is setup not to ask. I'm not sure what you mean by
> "default" configuration, since you are probably using the one installed
> by your distro.
>
> It's very hard to see what the problem is unless you post your full
> pg_hba.conf and the actual command-lines you used, including which UNIX
> user you used. The two lines you gave would allow the postgres UNIX
> user to login to any database as himself without a password, and allow
> foo into bar with md5 authentication. If you are seeing something else
> you should be explicit how you're logging in.
>
> Have a nice day,
Oh shoot, I really wasn't very verbose, was I? Sorry about that.
I am running Debian Sarge with the debian-provided PostgreSQL 7.4 deb
pakage. The 'pg_hba.conf' file I am using (unedited from the one that
was installed with most comments removed) is:
# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
# Database administrative login by UNIX sockets
local all postgres
ident sameuser
#
# All other connections by UNIX sockets
local all all
ident sameuser
#
# All IPv4 connections from localhost
host all all 127.0.0.1 255.255.255.255
ident sameuser
#
# All IPv6 localhost connections
host all all ::1
ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff ident sameuser
host all all ::ffff:127.0.0.1/128
ident sameuser
#
# reject all other connection attempts
host all all 0.0.0.0 0.0.0.0 reject
That is without the line I added there anymore.
After creating the database and the user this is what I have
(connected to 'template1' as 'postgres'):
template1=# SELECT * FROM pg_database;
datname | datdba | encoding | datistemplate | datallowconn |
datlastsysoid | datvacuumxid | datfrozenxid | datpath | datconfig |
datacl
-----------+--------+----------+---------------+--------------+---------------+--------------+--------------+---------+-----------+--------------------------
tle-bu | 100 | 8 | f | t |
17140 | 735 | 3221226208 | | |
template1 | 1 | 8 | t | t |
17140 | 735 | 3221226208 | | |
{postgres=C*T*/postgres}
template0 | 1 | 8 | t | f |
17140 | 464 | 464 | | |
{postgres=C*T*/postgres}
(3 rows)
template1=# SELECT * FROM pg_shadow;
usename | usesysid | usecreatedb | usesuper | usecatupd |
passwd | valuntil | useconfig
----------+----------+-------------+----------+-----------+-------------------------------------+----------+-----------
postgres | 1 | t | t | t |
| |
tle-bu | 100 | t | f | f |
md562c7c93e482292a88903ac6b65cdb34c | |
(2 rows)
You can see that I have created a password for the 'tle-bu' user. Now
when I try to connect I get the "psql: FATAL: IDENT authentication
failed for user "tle-bu"" error when I try to connect from the 'madison'
shell account using:
$ psql tle-bu -U tle-bu
Which is good. Though, if I add the user 'madison' to the database as
a user and create a database owned by her:
template1=# CREATE USER madison;
CREATE USER
template1=# CREATE DATABASE "test" OWNER "madison";
CREATE DATABASE
And then connect to the 'test' database as the user 'madison' I can
then use '\c' to connect to the 'tle-bu' database:
$ psql test -U madison
Welcome to psql 7.4.7, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
test=> \c tle-bu
You are now connected to database "tle-bu".
tle-bu=>
So ultimately my question becomes; How can I prevent other valid
postgres database users from connecting to the 'tle-bu' database
('postgres' being the obvious exception)? Can I do this with some
combination of GRANT and/or REVOKE? If so, does 'GRANT...' restrict
access to only the user(s) mentioned once it is used or do I need to
'REVOKE...' other users first and then 'GRANT...' the 'tle-bu' user?
Or am I missing a design of postgresql (always likely. :P )?
Thanks!!
Madison
--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Madison Kelly (Digimer)
TLE-BU; The Linux Experience, Back Up
Main Project Page: http://tle-bu.org
Community Forum: http://forum.tle-bu.org
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Tom Lane wrote:
> Madison Kelly <linux@alteeve.com> writes:
>
>>May I ask then? What *is* considered "best practices" for securing a
>>database in PostgreSQL? Assuming I leave the 'pg_hba.conf' file at it's
>>default values, is there any real point to having a password on a
>>postgresql user account?
>
>
> Well, if there were a single "best practice" then we'd not need to offer
> so many options ;-). It depends a lot on your needs and environment.
> On a single-user machine where you're not allowing any remote
> connections, you might as well use "trust" --- I tend to run all my
> development installations that way. Ident can be pretty convenient too
> for local users (I wouldn't trust it for remote connections though).
> Otherwise you probably need passwords.
>
> In any case, this just applies to whether you let someone connect or
> not. What they can do after they've connected is a different
> discussion. For that you use SQL privileges (GRANT/REVOKE).
>
> regards, tom lane
In this case I can't predict what a given install's postgresql will
be used for (outside of my program) because it is meant for general
distribution (it's a backup program). This obviously makes things a lot
more complicated. :p
While I developed the program that is what I did, just changed from
'ident' to 'trust'. Now though I am trying to keep what the end user
needs to do to a minimum because I've aimed the backup program at more
novice users (though not excluively). That is the biggest reason why I
am trying to work with the stock 'pg_hba.conf' file (understanding that
it can change from one distro to the next). Generally though I've only
seen the same 'local...' settings.
In my program the database needs to reside on the local machine so as
far as I am concerned my only worry is the 'local...' settings. There
may be issues with connections coming in over 'host...' connections but
I want to deal with one issue at a time. :p
So what purpose does the password on the user account accomplish? Is
it essentially useless in my scenario?
Again, thanks for your help/time!
Madison
PS - I read about 'GRANT/REVOKE' but I have to admit the postgres docs
on the topic didn't help me much. I need something a little more
"junior". :p
--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Madison Kelly (Digimer)
TLE-BU; The Linux Experience, Back Up
Main Project Page: http://tle-bu.org
Community Forum: http://forum.tle-bu.org
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Madison Kelly <linux@alteeve.com> writes: > Oh shoot, I really wasn't very verbose, was I? Sorry about that. > [ default pg_hba.conf with only "ident" lines ] Ah, that explains your question about whether passwords were good for anything at all. With this pg_hba.conf they aren't --- the server will never ask for one. You'd want to replace some of the "ident sameuser" entries with "password" (or more likely "md5") if you want password challenges instead of checks on the user's Unix login identity. See the PG administrator docs at http://www.postgresql.org/docs/8.1/static/client-authentication.html (adjust version as needed) > So ultimately my question becomes; How can I prevent other valid > postgres database users from connecting to the 'tle-bu' database > ('postgres' being the obvious exception)? Can I do this with some > combination of GRANT and/or REVOKE? At the moment you have to do that by adjusting the pg_hba.conf entries. One possibility is to use "sameuser" in the database field, eg, # TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD # Database administrative login by UNIX sockets local all postgres ident sameuser # # All other connections by UNIX sockets local sameuser all ident sameuser This will let "postgres" connect to anything but other users can only connect to the database named after them. If you need more flexibility that that, consider setting up groups named for databases and using "samegroup" --- then you grant or revoke group membership to let people into databases or not. It'd be an obvious extension to provide a direct "LOGIN" privilege on databases and grant or revoke that, but given the samegroup workaround it's not a real high-priority feature ... regards, tom lane
Tom Lane wrote: > Madison Kelly <linux@alteeve.com> writes: > >>Oh shoot, I really wasn't very verbose, was I? Sorry about that. >>[ default pg_hba.conf with only "ident" lines ] > > > Ah, that explains your question about whether passwords were good for > anything at all. With this pg_hba.conf they aren't --- the server will > never ask for one. You'd want to replace some of the "ident sameuser" > entries with "password" (or more likely "md5") if you want password > challenges instead of checks on the user's Unix login identity. See > the PG administrator docs at > http://www.postgresql.org/docs/8.1/static/client-authentication.html > (adjust version as needed) I've played with the MD5 and I think I will write a little howto or something similar to explain the options to a user who wants more security but for now I will default to leaving things as-is. >> So ultimately my question becomes; How can I prevent other valid >>postgres database users from connecting to the 'tle-bu' database >>('postgres' being the obvious exception)? Can I do this with some >>combination of GRANT and/or REVOKE? > > > At the moment you have to do that by adjusting the pg_hba.conf entries. > One possibility is to use "sameuser" in the database field, eg, > > # TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD > # Database administrative login by UNIX sockets > local all postgres ident sameuser > # > # All other connections by UNIX sockets > local sameuser all ident sameuser > > This will let "postgres" connect to anything but other users can only > connect to the database named after them. If you need more flexibility > that that, consider setting up groups named for databases and using > "samegroup" --- then you grant or revoke group membership to let people > into databases or not. > > It'd be an obvious extension to provide a direct "LOGIN" privilege > on databases and grant or revoke that, but given the samegroup > workaround it's not a real high-priority feature ... > > regards, tom lane Many thanks for your help clearing that up! If I can vote for the extension being created, consider this it. Mainly for the reasons I've mentioned; trying to handle security programatically instead of relying on the end-user (who may be less technically enclined) doing it. I know that I could have my program handle the editing of the 'pg_hba.conf' file but I don't trust myself with doing that write given that order is important and the wide number of possible configurations. Madison -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Madison Kelly (Digimer) TLE-BU; The Linux Experience, Back Up Main Project Page: http://tle-bu.org Community Forum: http://forum.tle-bu.org -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
linux@alteeve.com (Madison Kelly) writes: > In this case I can't predict what a given install's postgresql > will be used for (outside of my program) because it is meant for > general distribution (it's a backup program). This obviously makes > things a lot more complicated. :p No, it oughtn't. You shouldn't try to impose anything about this onto the users. There are really only two options you need to concern yourself about: 1. Perhaps a password may be needed in your configuration. 2. Perhaps it won't. Any number of possible causes: - Your user may be considered "trusted"; - The password may be stored in ~/.pgpass - Perhaps in future, authentication may come as some form of SSH key, stored in a directory somewhere... > While I developed the program that is what I did, just changed > from 'ident' to 'trust'. Now though I am trying to keep what the end > user needs to do to a minimum because I've aimed the backup program > at more novice users (though not excluively). That is the biggest > reason why I am trying to work with the stock 'pg_hba.conf' file > (understanding that it can change from one distro to the > next). Generally though I've only seen the same 'local...' settings. What you may want to do, then is to provide _documentation_ to suggest how they might manage pg_hba.conf. If people think you're telling them how to manage security, and their ideas aren't the same as yours, that'll point people away from your software. With Slony-I, that was one of the "design choices." It requires having a database superuser around, but Slony-I does NOT attempt to impose anything about what authentication methods you prefer to use. To run Slony-I, you have to use authentication conforming with what the environment requires. I believe Jan Wieck's preferences are to be able to use TRUST; the thought is that you should only be running replication in an environment that you already know to be secured, where you can trust anyone that has access to the database hosts. I can mostly go along with that. In our production environments, however, we use md5 authentication, because there are others setting security policy that don't think the same way about it as Jan does. Fortunately, Slony-I wasn't designed to require Jan's policy preferences. It can conform to various kinds of policies. Your program ought to do the same. > In my program the database needs to reside on the local machine > so as far as I am concerned my only worry is the 'local...' > settings. There may be issues with connections coming in over > 'host...' connections but I want to deal with one issue at a > time. :p It shouldn't much matter where the database is. > So what purpose does the password on the user account accomplish? > Is it essentially useless in my scenario? The password is an authentication token that *may* be required. For *your* purposes, it doesn't matter if it "accomplishes" anything; it doesn't matter if it is "useful." It only ought to matter that you *may* need a password to pass in as part of the DSN used to connect to the database. -- select 'cbbrowne' || '@' || 'acm.org'; http://cbbrowne.com/info/nonrdbms.html Signs of a Klingon Programmer #7: "Klingon function calls do not have 'parameters' -- they have 'arguments' -- and they ALWAYS WIN THEM."