Re: Getting a DB password to work without editing pg_hba.conf,

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
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-