Re: Getting a DB password to work without editing pg_hba.conf, - Mailing list pgsql-general

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