Re: postgresql configuration and set-up
On Wed, 2004-03-17 at 13:43, Tom Allison wrote:
> BillMoseley@hank.org wrote:
>
> > As glenn posted, it seems like you need to setup the
> > /etc/postresql/pg_hba.conf file.
> >
>
> Oliver, I CC'ed you on this because I don't have a formal bugreport
> utility working yet and thought it would helpful if you read this.
Sorry for the delay in replying; I've been away from home.
> What I ended up doing was throwing out the pg_hba.conf file as supplied
> by Debian. I personally don't like the implimentation for a variety of
> reasons:
A package default has got to cope with many possible installations. I
think this one is the best compromise beween usability and initial
security. Each DBA has to assess his own security requirements and
change the setup to suit his own needs.
> login_usernames are associated with database usernames. Means access to
> one means greater access to do damage. Security issue.
If your box is compromised by a cracked UNIX password, you have many
greater problems. Proper use of GRANT to restrict permissions to what
is necessary to each user will help to reduce the impact that any one
cracked account can have. The postgres account (DB administrator) does
not have a password at all, by default, so needs to be accessed through
root. If root is compromised, you have no hope any way.
> required ident which is not installed with postgresql and should be
> considered a bug. Ident is also an open text password process and
> should be avoided. Security issue and a Bug.
The default setup is to allow ident access for Unix sockets and
localhost only. That is, security depends on the Unix access controls.
The default setup does NOT allow access from any other machine. (Access
through localhost requires installation of an ident server on the local
machine, but that is not necessary, since you can use UNIX socket access
instead.) Therefore there is no security issue unless you install an
ident server and also allow non-root users to run packet sniffing
software on the local machine.
> It's far easier to manage and far saner to manage if you just added some
> lines to pg_hba.conf to the effect of:
>
> local all postgres trust
No! Absolutely not! That says that anyone connecting through a UNIX
socket and merely claiming to be postgres is allowed full administrator
access! That destroys all your security. Look at this, with your line:
olly@cerberus$ psql -U postgres template1
Welcome to psql 7.4.2, 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
template1=# ALTER USER payroll WITH PASSWORD 'pay me lots';
as opposed to this, with the default policy:
olly@cerberus$ psql -U postgres template1
psql: FATAL: IDENT authentication failed for user "postgres"
> local all all md5 (or trust or password)
> host all all 127.0.0.0/8 md5
By this you require every user and script to supply a password; any user
who gets fed up with this can install a ~/.pgpass file and put you back
in the security situation you had with "ident sameuser" authentication.
> host all all 192.168.0.0/24 md5 ( I have an internal LAN )
The default policy does not allow that access at all.
> host all all 0.0.0.0 0.0.0.0 reject
>
> This puts the onus of security access to postgresql on postgresql and
> requires a certain level of security by default on everything.
> The subnet 192.168.1.0/24 could be built up from a script to query
> ifconfig at package installation time if required. Otherwise skip it.
>
> I'm kind of surprised at the change. It's been years since I looked at
> postgresql in Debian and IIRC it wasn't like this. I don't know the
> history behind it. But I picked up Debian (again) because IMHO it's
> just a better distro, especially for servers, than anything else I've
> ever seen.
I think your assessment of the risks is wrong, for a default
installation. As local administrator you can assess your own risks and
impose your own policy, but I don't agree with them for the package.
> I would send a bugreport, but my email isn't working on that machine yet.
I don't agree that it is a bug.
> <chop chop chop Thanks for reading this far Oliver>
>
>
> > Now, that authenticates to use the database. But, the issue
> > I've been wondering about is this:
> >
> > I create tables as one user then I want another user to use
> > those tables (like the web server). Then I need to GRANT
> > access to every object in the database to that other user.
> >
> > I wonder if there's an easier way than having to GRANT every
> > object -- like a global grant.
Not that I know of; the best thing to do is to construct a script to set
permissions.
One way to make things easier is to assign users to groups and grant
permissions to groups rather than to users; removing or adding a user to
the appropriate group will automatically grant access to all objects for
which the group has access.
Oliver Elphick
PostgreSQL package maintainer
Reply to: