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
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
