Bugtraq: Having Fun With PostgreSQL

A message entitled "Having Fun With PostgreSQL" was posted to Bugtraq
today. I haven't read through the paper yet so I don't know if the
author discusses security problems that need attention or if the
article is more like a compilation of "Stupid PostgreSQL Tricks."

http://www.securityfocus.com/archive/1/471541/30/0/threaded

--
Michael Fuhr

Michael Fuhr <mike(at)fuhr(dot)org> writes:
> A message entitled "Having Fun With PostgreSQL" was posted to Bugtraq
> today. I haven't read through the paper yet so I don't know if the
> author discusses security problems that need attention or if the
> article is more like a compilation of "Stupid PostgreSQL Tricks."
> http://www.securityfocus.com/archive/1/471541/30/0/threaded

It appears he's discovered the astonishing facts that

1. The out-of-the-box authentication setup is "trust".
2. A superuser can make the database do whatever he wants (within
the OS privilege limits of the postgres user).

We've debated #1 before, and a lot of repackagers change it, but I
don't really feel a strong urge to change it in the source distro.
As for #2, that's not a bug, it's intended behavior.

regards, tom lane

PS: I skimmed the paper pretty fast, so it's possible I missed
something interesting, but it sure looked like "what else is new?"

On Sat, 16 Jun 2007, Michael Fuhr wrote:

> A message entitled "Having Fun With PostgreSQL" was posted to Bugtraq
> today. I haven't read through the paper yet so I don't know if the
> author discusses security problems that need attention or if the
> article is more like a compilation of "Stupid PostgreSQL Tricks."
>
> http://www.securityfocus.com/archive/1/471541/30/0/threaded

The crux of this seems to be two-fold:
1. If dblink is installed, an untrusted user could use it to gain
privileges, either using trust/ident auth (you have a superuser named
after the account the postmaster is runing as), or can be scripted to
brute force passwords.
2. If you are a superuser, you can gain access to the external system, ie,
by creating C language functions.

Neither of these are news to me, but maybe some new postgres admin will
read it and figure out to disable trust auth and not to let untrusted
users call dblink (either not install it or REVOKE the rights to call it).

--
Around computers it is difficult to find the correct unit of time to
measure progress. Some cathedrals took a century to complete. Can you
imagine the grandeur and scope of a program that would take as long?
-- Epigrams in Programming, ACM SIGPLAN Sept. 1982

Tom Lane wrote:
> Michael Fuhr <mike(at)fuhr(dot)org> writes:
>> A message entitled "Having Fun With PostgreSQL" was posted to Bugtraq
>> today. I haven't read through the paper yet so I don't know if the
>> author discusses security problems that need attention or if the
>> article is more like a compilation of "Stupid PostgreSQL Tricks."
>> http://www.securityfocus.com/archive/1/471541/30/0/threaded
>
> It appears he's discovered the astonishing facts that
>
> 1. The out-of-the-box authentication setup is "trust".
> 2. A superuser can make the database do whatever he wants (within
> the OS privilege limits of the postgres user).
>
> We've debated #1 before, and a lot of repackagers change it, but I
> don't really feel a strong urge to change it in the source distro.
> As for #2, that's not a bug, it's intended behavior.

On #1, the fact that we allow trust as default is embarrassing. It would
be just as bad as having the default root password be password on a
linux box. We should be using md5 and force passing the password with
initdb.

Sincerely,

Joshua D. Drake

>
> regards, tom lane
>
> PS: I skimmed the paper pretty fast, so it's possible I missed
> something interesting, but it sure looked like "what else is new?"
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
>

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

The world rejoiced as jd(at)commandprompt(dot)com ("Joshua D. Drake") wrote:
> Tom Lane wrote:
>> Michael Fuhr <mike(at)fuhr(dot)org> writes:
>>> A message entitled "Having Fun With PostgreSQL" was posted to Bugtraq
>>> today. I haven't read through the paper yet so I don't know if the
>>> author discusses security problems that need attention or if the
>>> article is more like a compilation of "Stupid PostgreSQL Tricks."
>>> http://www.securityfocus.com/archive/1/471541/30/0/threaded
>> It appears he's discovered the astonishing facts that
>> 1. The out-of-the-box authentication setup is "trust".
>> 2. A superuser can make the database do whatever he wants (within
>> the OS privilege limits of the postgres user).
>> We've debated #1 before, and a lot of repackagers change it, but I
>> don't really feel a strong urge to change it in the source distro.
>> As for #2, that's not a bug, it's intended behavior.
>
> On #1, the fact that we allow trust as default is embarrassing. It
> would be just as bad as having the default root password be password
> on a linux box. We should be using md5 and force passing the password
> with initdb.

That won't help; that would introduce the "embarrassment" of having a
known default password.

This is a case where it takes careful thought to grasp whether there
is a problem or not.

If all we do is to shift the embarrassment around, that's not much
help.
--
output = reverse("moc.liamg" "@" "enworbbc")
http://linuxfinances.info/info/slony.html
"If all you can see is vast masses of end-users chewing their cud and
running Win95 on Gateways, then what good is platform independence?"
-- David LeBlanc (dleblanc(at)mindspring(dot)com)

Christopher Browne wrote:
> The world rejoiced as jd(at)commandprompt(dot)com ("Joshua D. Drake") wrote:
>> Tom Lane wrote:

>>> We've debated #1 before, and a lot of repackagers change it, but I
>>> don't really feel a strong urge to change it in the source distro.
>>> As for #2, that's not a bug, it's intended behavior.
>> On #1, the fact that we allow trust as default is embarrassing. It
>> would be just as bad as having the default root password be password
>> on a linux box. We should be using md5 and force passing the password
>> with initdb.
>
> That won't help; that would introduce the "embarrassment" of having a
> known default password.

No it wouldn't unless the packagers set it up to do that. My point is
that when a packager (or source) runs initdb, it would prompt for the
postgres user password. Just like when you create a ssh key or cert etc...

Joshua D. Drake

>
> This is a case where it takes careful thought to grasp whether there
> is a problem or not.
>
> If all we do is to shift the embarrassment around, that's not much
> help.

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

Christopher Browne <cbbrowne(at)acm(dot)org> writes:
> The world rejoiced as jd(at)commandprompt(dot)com ("Joshua D. Drake") wrote:
>> On #1, the fact that we allow trust as default is embarrassing.

> This is a case where it takes careful thought to grasp whether there
> is a problem or not.
> If all we do is to shift the embarrassment around, that's not much
> help.

We've argued this in considerable detail in the past. The bottom line
is that the trust default is the worst possible choice, except for all
the others. One of the more obvious problems with the others is that
a newbie who can't get into his database at all will not become a
Postgres adopter...

regards, tom lane

"Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
> Christopher Browne wrote:
>> That won't help; that would introduce the "embarrassment" of having a
>> known default password.

> No it wouldn't unless the packagers set it up to do that. My point is
> that when a packager (or source) runs initdb, it would prompt for the
> postgres user password.

Practically every existing packaging of PG tries to run initdb as a
hidden, behind-the-scenes, definitely not-interactive procedure.

regards, tom lane

> >> That won't help; that would introduce the "embarrassment" of having a
> >> known default password.
>
> > No it wouldn't unless the packagers set it up to do that. My point is
> > that when a packager (or source) runs initdb, it would prompt for the
> > postgres user password.
>
> Practically every existing packaging of PG tries to run initdb as a
> hidden, behind-the-scenes, definitely not-interactive procedure.
>

afaik, practically every existing packaging of pg has *already* solved the problem and does not set trust as default anyway. ident sameuser I think is the most
common.

One thing I've thought about doing is to remove the default in initdb completely and *force* the user to choose auth type. Packagers can then just use that to
set ident or whatever. and interactive users can pick trust if they really need it, but it will be a known choice.

/Magnus

Hi,

On Mon, 2007-06-18 at 01:58 -0400, Tom Lane wrote:

> Practically every existing packaging of PG tries to run initdb as a
> hidden, behind-the-scenes, definitely not-interactive procedure.

Also, from RPM perspective: RPMs are *not* interactive, and will *never*
be. So we cannot ask user a password during initdb, or such.

Regards,
--
Devrim GÜNDÜZ
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/

* Jeremy Drake (pgsql(at)jdrake(dot)com) wrote:
> The crux of this seems to be two-fold:
> 1. If dblink is installed, an untrusted user could use it to gain
> privileges, either using trust/ident auth (you have a superuser named
> after the account the postmaster is runing as), or can be scripted to
> brute force passwords.

The dblink w/ ident case is at least somewhat interesting since, iirc
anyway, if you install dblink it comes with permissions for anyone to
run it. That's pretty ugly if your PG superuser is the same user
PostgreSQL runs as and you're using ident (which is quite common, esp.
over unix sockets). The answer here being, don't allow just anyone to
run dblink.

> 2. If you are a superuser, you can gain access to the external system, ie,
> by creating C language functions.

Which, as an issue, is pretty much resolved in 8.2 anyway... You'd have
to be able to compile and/or upload new libraries to the system w/ 8.2
since the PG_MODULE_MAGIC is required now.

> Neither of these are news to me, but maybe some new postgres admin will
> read it and figure out to disable trust auth and not to let untrusted
> users call dblink (either not install it or REVOKE the rights to call it).

I'm strongly tempted to say this should be set up as the default for
dblink, if it's not too hard to implement (I'd expect there's already a
.sql which does the in-db create function and whatnot, just revoke all
from it after it's created and tell people to create views using it
instead as superuser).

Thanks,

Stephen

On Jun 18, 2007, at 12:58 AM, Tom Lane wrote:
> "Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
>> Christopher Browne wrote:
>>> That won't help; that would introduce the "embarrassment" of
>>> having a
>>> known default password.
>
>> No it wouldn't unless the packagers set it up to do that. My point is
>> that when a packager (or source) runs initdb, it would prompt for the
>> postgres user password.
>
> Practically every existing packaging of PG tries to run initdb as a
> hidden, behind-the-scenes, definitely not-interactive procedure.

I know there's issues with using ident sameuser via TCP, but what
about for filesystem socket connections?

As for the interactive/non-interactive, we could just leave that as
an option to initdb, and make the default to ask for a password.
Packagers would just need to feed the right option to initdb.
--
Jim Nasby jim(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

Jim,

> I know there's issues with using ident sameuser via TCP, but what
> about for filesystem socket connections?

Not all OSes support ident ... Solaris and OpenBSD for two, don't, because
they see ident as insecure.

We switched the default to "trust localhost only" after many, many complaints
by newbies that they had installed PostgreSQL but couldn't log into it.
Other than maybe putting more emphasis on setting up the superuser password
in the tutorial, I really don't think that we should change the behavior.

--
Josh Berkus
PostgreSQL @ Sun
San Francisco

On Jun 19, 2007, at 1:27 PM, Josh Berkus wrote:
>> I know there's issues with using ident sameuser via TCP, but what
>> about for filesystem socket connections?
>
> Not all OSes support ident ... Solaris and OpenBSD for two, don't,
> because
> they see ident as insecure.

What about the unix domain socket, though? AFAIK that doesn't rely on
ident but some other method...
--
Jim Nasby jim(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

Jim Nasby <decibel(at)decibel(dot)org> writes:
> On Jun 19, 2007, at 1:27 PM, Josh Berkus wrote:
>> Not all OSes support ident ... Solaris and OpenBSD for two, don't,
>> because they see ident as insecure.

> What about the unix domain socket, though? AFAIK that doesn't rely on
> ident but some other method...

On OpenBSD we use getpeereid() for unix sockets, and there are
equivalent things on some other Unixen. We could never go over to
ident as the standard default, though, because not all platforms
have these sorts of features (if indeed they have unix sockets at
all ...); and in any case it's not very secure for TCP.

regards, tom lane

Magnus Hagander wrote:
>>>> That won't help; that would introduce the "embarrassment" of
>>>> having a known default password.
>>> No it wouldn't unless the packagers set it up to do that. My
>>> point is that when a packager (or source) runs initdb, it would
>>> prompt for the postgres user password.
>> Practically every existing packaging of PG tries to run initdb as a
>> hidden, behind-the-scenes, definitely not-interactive procedure.
>>
>
> afaik, practically every existing packaging of pg has *already*
> solved the problem and does not set trust as default anyway. ident
> sameuser I think is the most common.
>
> One thing I've thought about doing is to remove the default in initdb
> completely and *force* the user to choose auth type. Packagers can
> then just use that to set ident or whatever. and interactive users
> can pick trust if they really need it, but it will be a known choice.
>
>

Since nobody comemnted on this, let me turn it around and ask: Does
anybody have any reason *not* to do this?

If not, I'll just make it happen... (that should at least make people
speak up :P)

//Magnus

Magnus Hagander wrote:
> Magnus Hagander wrote:
>
>>>>> That won't help; that would introduce the "embarrassment" of
>>>>> having a known default password.
>>>>>
>>>> No it wouldn't unless the packagers set it up to do that. My
>>>> point is that when a packager (or source) runs initdb, it would
>>>> prompt for the postgres user password.
>>>>
>>> Practically every existing packaging of PG tries to run initdb as a
>>> hidden, behind-the-scenes, definitely not-interactive procedure.
>>>
>>>
>> afaik, practically every existing packaging of pg has *already*
>> solved the problem and does not set trust as default anyway. ident
>> sameuser I think is the most common.
>>
>> One thing I've thought about doing is to remove the default in initdb
>> completely and *force* the user to choose auth type. Packagers can
>> then just use that to set ident or whatever. and interactive users
>> can pick trust if they really need it, but it will be a known choice.
>>
>>
>>
>
> Since nobody comemnted on this, let me turn it around and ask: Does
> anybody have any reason *not* to do this?
>
> If not, I'll just make it happen... (that should at least make people
> speak up :P)
>
>
>

It will break the buildfarm. Of course I can unbreak it by adding
"--auth=trust" to the initdb args (and if we go this route we'll need to
do that for the regression temp installs too), but we'd need every
buildfarm member to have upgraded before we put it in. Is that really
the sort of disruption you want right now?

cheers

andrew

Andrew Dunstan wrote:
>
>
> Magnus Hagander wrote:
>> Magnus Hagander wrote:
>>
>>>>>> That won't help; that would introduce the "embarrassment" of
>>>>>> having a known default password.
>>>>>>
>>>>> No it wouldn't unless the packagers set it up to do that. My
>>>>> point is that when a packager (or source) runs initdb, it would
>>>>> prompt for the postgres user password.
>>>>>
>>>> Practically every existing packaging of PG tries to run initdb as a
>>>> hidden, behind-the-scenes, definitely not-interactive procedure.
>>>>
>>>>
>>> afaik, practically every existing packaging of pg has *already*
>>> solved the problem and does not set trust as default anyway. ident
>>> sameuser I think is the most common.
>>>
>>> One thing I've thought about doing is to remove the default in initdb
>>> completely and *force* the user to choose auth type. Packagers can
>>> then just use that to set ident or whatever. and interactive users
>>> can pick trust if they really need it, but it will be a known choice.
>>>
>>>
>>>
>>
>> Since nobody comemnted on this, let me turn it around and ask: Does
>> anybody have any reason *not* to do this?
>>
>> If not, I'll just make it happen... (that should at least make people
>> speak up :P)
>>
>>
>>
>
> It will break the buildfarm. Of course I can unbreak it by adding
> "--auth=trust" to the initdb args (and if we go this route we'll need to
> do that for the regression temp installs too), but we'd need every
> buildfarm member to have upgraded before we put it in. Is that really
> the sort of disruption you want right now?

Ouch. No. Didn't think of that.

If we want to do that, we should probably do it right at the start of
the 8.4 cycle then?

//Magnus

Magnus Hagander <magnus(at)hagander(dot)net> writes:
>> One thing I've thought about doing is to remove the default in initdb
>> completely and *force* the user to choose auth type. Packagers can
>> then just use that to set ident or whatever. and interactive users
>> can pick trust if they really need it, but it will be a known choice.

> Since nobody comemnted on this, let me turn it around and ask: Does
> anybody have any reason *not* to do this?

I'll object if no one else does: this will break existing installation
habits and processes to no real benefit.

regards, tom lane

Tom Lane wrote:
> Magnus Hagander <magnus(at)hagander(dot)net> writes:
>>> One thing I've thought about doing is to remove the default in initdb
>>> completely and *force* the user to choose auth type. Packagers can
>>> then just use that to set ident or whatever. and interactive users
>>> can pick trust if they really need it, but it will be a known choice.
>
>> Since nobody comemnted on this, let me turn it around and ask: Does
>> anybody have any reason *not* to do this?
>
> I'll object if no one else does: this will break existing installation
> habits and processes to no real benefit.

The benefit would be that PostgreSQL would be "secure by default". Which
we are *not* today.

As a comparison, that's been one of the most common complaints against
Windows earlier - stuff is installed and enabled by default, and only if
you already know the system do you know that you should disable it. The
same thing applies here - if you don't already know how PostgreSQL
works, you will by default install a database that's completely without
authentication.

//Magnus

Magnus Hagander <magnus(at)hagander(dot)net> writes:
> Tom Lane wrote:
>> Magnus Hagander <magnus(at)hagander(dot)net> writes:
>>> One thing I've thought about doing is to remove the default in initdb
>>> completely and *force* the user to choose auth type.

>> I'll object if no one else does: this will break existing installation
>> habits and processes to no real benefit.

> The benefit would be that PostgreSQL would be "secure by default". Which
> we are *not* today.

No, we would NOT be "secure by default". We'd only be secure by default
if we forced the user to pick a secure auth method, for whatever value
of "secure" is politically correct.

A change like this will make exactly 0 difference to users of
prepackaged installations, since AFAIK all packages make their own
decisions about what default auth method to use (and since they are
targeting specific platforms, they have more context to make this choice
than we do). It will also be useless and annoying to experienced users
of the source code, since they already know what to do. The only group
that will be affected will be newbie installers-from-source (which I
bet is a mighty small group these days). And those people will find
that Postgres' first demand on them is to read and understand some
rather complex documentation to try to pick a default auth method.
You really think that this group is particularly likely to get it right?
If it were an open-and-shut decision we'd probably just move to a
non-trust default.

I would also argue that trust auth is not such an evil option that we
mustn't allow it to be the default. On a single-user machine it's
actually perfectly sane, seeing that we don't allow TCP connections
by default.

I think what this change would mostly do is put still another barrier
in the way of people trying out Postgres. There's already a darn steep
learning curve; we don't need a cliff at the very start.

regards, tom lane

Tom Lane wrote:
> Magnus Hagander <magnus(at)hagander(dot)net> writes:
>> Tom Lane wrote:
>>> Magnus Hagander <magnus(at)hagander(dot)net> writes:
>>>> One thing I've thought about doing is to remove the default in initdb
>>>> completely and *force* the user to choose auth type.
>
>>> I'll object if no one else does: this will break existing installation
>>> habits and processes to no real benefit.
>
>> The benefit would be that PostgreSQL would be "secure by default". Which
>> we are *not* today.
>
> No, we would NOT be "secure by default". We'd only be secure by default
> if we forced the user to pick a secure auth method, for whatever value
> of "secure" is politically correct.

Good point. But we'd at least force the user to read up on things. (Or
guess, but there's nothing we can do about that at all)

> A change like this will make exactly 0 difference to users of
> prepackaged installations, since AFAIK all packages make their own
> decisions about what default auth method to use (and since they are
> targeting specific platforms, they have more context to make this choice
> than we do).

Agreed.

> It will also be useless and annoying to experienced users
> of the source code, since they already know what to do.

Well, it would help them not to forget an important step, but agreed in
principle.

> The only group
> that will be affected will be newbie installers-from-source (which I
> bet is a mighty small group these days). And those people will find
> that Postgres' first demand on them is to read and understand some
> rather complex documentation to try to pick a default auth method.
> You really think that this group is particularly likely to get it right?
> If it were an open-and-shut decision we'd probably just move to a
> non-trust default.

In that case, md5 would seem to be the reasonable choice - unless we
want something platform specific somehow set by autoconf or so (I'd say
ident sameuser over unix sockets make a lot of sense on platforms that
support it).

> I would also argue that trust auth is not such an evil option that we
> mustn't allow it to be the default. On a single-user machine it's
> actually perfectly sane, seeing that we don't allow TCP connections
> by default.

Is there really such a thing as a single-user machine running
PostgreSQL? Maybe single "human user", but if you're not running some
other services on it (webserver, mailserver, whatever) it's not very
likely to be running pg, I think.

> I think what this change would mostly do is put still another barrier
> in the way of people trying out Postgres. There's already a darn steep
> learning curve; we don't need a cliff at the very start.

Didn't we just say that these users are most likely running a
pre-packaged version anyway?

Either way, it's not critical, given that the majority of our users use
some kind of pre-packaged deal, but I still think we could do better.

Out of curiosity, how do other databases deal with this? The only one
I've been installing recently is MSSQL which basically lets you chose
between SSPI
(ident-sameuser-over-sockets-and-kerberos-over-network-equivalent) or
password (md5 equivalent, AFAIK). It does *not* let you use empty
passwords, which is what would be equivalent with our "trust".

//Magnus

On Jun 23, 2007, at 11:03 AM, Magnus Hagander wrote:

>
>> I would also argue that trust auth is not such an evil option that we
>> mustn't allow it to be the default. On a single-user machine it's
>> actually perfectly sane, seeing that we don't allow TCP connections
>> by default.
>
> Is there really such a thing as a single-user machine running
> PostgreSQL? Maybe single "human user", but if you're not running some
> other services on it (webserver, mailserver, whatever) it's not very
> likely to be running pg, I think.

My laptop and dev boxes are all single user (and all do trust
on unix sockets).

I have several webserver boxes that use local postgresql
installations. Again, trust works just fine for that. There's
no security issue unless someone has compromised the
box (probably via the webapp), and if they've done that
they already have the keys to the castle. Mailserver, ditto.

We deploy CRM systems, running on dedicated boxes,
to customers. While they tend to get configured with
md5 access, just because it's tidy and easy to explain
to IT security folks, they'd actually be just as secure with
trust auth.

> Out of curiosity, how do other databases deal with this? The only one
> I've been installing recently is MSSQL which basically lets you chose
> between SSPI
> (ident-sameuser-over-sockets-and-kerberos-over-network-equivalent) or
> password (md5 equivalent, AFAIK). It does *not* let you use empty
> passwords, which is what would be equivalent with our "trust".

But that's not an installation from source. That's a packaged
installation, provided by the distribution owner. It's much more
comparable to, say, a Debian package.

MySQL installs with an empty root password for access from
localhost or the machines own IP address. It also installs an
account with network access to any database beginning with
"test" and possibly some more ill-defined accounts with local
access.

Part of the suggested post-install process for mysql involves
deleting some of those accounts and setting password for root.

Cheers,
Steve

On Sat, Jun 23, 2007 at 12:02:43PM -0400, Tom Lane wrote:
> Magnus Hagander <magnus(at)hagander(dot)net> writes:
> >> One thing I've thought about doing is to remove the default in initdb
> >> completely and *force* the user to choose auth type. Packagers can
> >> then just use that to set ident or whatever. and interactive users
> >> can pick trust if they really need it, but it will be a known choice.
>
> > Since nobody comemnted on this, let me turn it around and ask: Does
> > anybody have any reason *not* to do this?
>
> I'll object if no one else does: this will break existing installation
> habits and processes to no real benefit.
>
> regards, tom lane
>
I agree with Tom on this. We have a number of install and provisioning
scripts that would become ridiculously convoluted and problematic if the
default is changed from trust. It is not unreasonable to expect a certain
degree of competence from anyone running a database server. Our default
postgresql install script asks for a system password and sets everything
to md5. If the user knows enough to stray from the default install, they
are assumed to know enough to keep out of trouble.

Cheers,
Ken

Steve Atkins <steve(at)blighty(dot)com> writes:
> On Jun 23, 2007, at 11:03 AM, Magnus Hagander wrote:
>> Out of curiosity, how do other databases deal with this?

> MySQL installs with an empty root password for access from
> localhost or the machines own IP address. It also installs an
> account with network access to any database beginning with
> "test" and possibly some more ill-defined accounts with local
> access.

FWIW, on mysql 5.0.42 I see only "root(at)localhost" and "root(at)127(dot)0(dot)0(dot)1"
in a fresh-out-of-the-box installation; not sure where you got these
other accounts, maybe a distro-specific modification?

But the bottom line is that mysql's out-of-the-box behavior is
*exactly* like our trust-for-local-connections behavior. Anyone
on the box can do "mysql -u root ..." and the server will accept
them as being superuser (they don't even have to know to enter an
empty password, in my experience).

regards, tom lane

Tom Lane wrote:
> Steve Atkins <steve(at)blighty(dot)com> writes:
>
>> On Jun 23, 2007, at 11:03 AM, Magnus Hagander wrote:
>>
>>> Out of curiosity, how do other databases deal with this?
>>>
>
>
>> MySQL installs with an empty root password for access from
>> localhost or the machines own IP address. It also installs an
>> account with network access to any database beginning with
>> "test" and possibly some more ill-defined accounts with local
>> access.
>>
>
> FWIW, on mysql 5.0.42 I see only "root(at)localhost" and "root(at)127(dot)0(dot)0(dot)1"
> in a fresh-out-of-the-box installation; not sure where you got these
> other accounts, maybe a distro-specific modification?
>
> But the bottom line is that mysql's out-of-the-box behavior is
> *exactly* like our trust-for-local-connections behavior. Anyone
> on the box can do "mysql -u root ..." and the server will accept
> them as being superuser (they don't even have to know to enter an
> empty password, in my experience).
>

This is all documented. For 5.1.x see:
http://dev.mysql.com/doc/refman/5.1/en/default-privileges.html

Perhaps we should add a section to our docs on securing the database.

cheers

andredw

On Sat, Jun 23, 2007 at 06:14:23PM +0200, Magnus Hagander wrote:
> The benefit would be that PostgreSQL would be "secure by default". Which
> we are *not* today.

To achieve the "secure by default" feature that you want (and I like
the scare-quotes -- I agree with those that think this adds no real
security, but I think you're right to worry about the perception
angle in this case), why not have a ./configure option that sets the
default trust level for the build? The option could default to
something "secure", but experienced users' build scripts would only
have to be altered to include --default-authentication="trust" or
something like that. Using this approach, packagers can also
continue to do what they want.

A
--
Andrew Sullivan | ajs(at)crankycanuck(dot)ca
However important originality may be in some fields, restraint and
adherence to procedure emerge as the more significant virtues in a
great many others. --Alain de Botton

Andrew Sullivan <ajs(at)crankycanuck(dot)ca> writes:
> To achieve the "secure by default" feature that you want (and I like
> the scare-quotes -- I agree with those that think this adds no real
> security, but I think you're right to worry about the perception
> angle in this case), why not have a ./configure option that sets the
> default trust level for the build?

Why is that better than the initdb-time option we already have?
Locking down options earlier rather than later is usually not a win.

regards, tom lane

On Mon, Jun 25, 2007 at 01:31:52PM -0400, Tom Lane wrote:
> Why is that better than the initdb-time option we already have?
> Locking down options earlier rather than later is usually not a win.

Like I said, I don't actually think it _is_ better. But it would
solve the problem that some people think it's a bad thing that you
run superuser-type commands without reading the manual, and then get
a badly-secured system. (The idea here, incidentally, is not to
replace the initdb-time option, but to set the default of the initdb
command.)

A

--
Andrew Sullivan | ajs(at)crankycanuck(dot)ca
When my information changes, I alter my conclusions. What do you do sir?
--attr. John Maynard Keynes

Andrew Sullivan <ajs(at)crankycanuck(dot)ca> writes:
> On Mon, Jun 25, 2007 at 01:31:52PM -0400, Tom Lane wrote:
>> Why is that better than the initdb-time option we already have?
>> Locking down options earlier rather than later is usually not a win.

> Like I said, I don't actually think it _is_ better. But it would
> solve the problem that some people think it's a bad thing that you
> run superuser-type commands without reading the manual, and then get
> a badly-secured system. (The idea here, incidentally, is not to
> replace the initdb-time option, but to set the default of the initdb
> command.)

But, per previous discussion, the people that would be affected are
only the ones building from source. If they didn't read the manual
for initdb (nor notice the warning it puts out about trust auth),
they *certainly* didn't look for any nonstandard configure options.
The normal build process for any open-source package is

./configure
make
sudo make install
... now what? OK, time to read the manual ...

regards, tom lane

On 6/25/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Andrew Sullivan <ajs(at)crankycanuck(dot)ca> writes:
> > On Mon, Jun 25, 2007 at 01:31:52PM -0400, Tom Lane wrote:
> >> Why is that better than the initdb-time option we already have?
> >> Locking down options earlier rather than later is usually not a win.
>
> > Like I said, I don't actually think it _is_ better. But it would
> > solve the problem that some people think it's a bad thing that you
> > run superuser-type commands without reading the manual, and then get
> > a badly-secured system. (The idea here, incidentally, is not to
> > replace the initdb-time option, but to set the default of the initdb
> > command.)
>
> But, per previous discussion, the people that would be affected are
> only the ones building from source. If they didn't read the manual
> for initdb (nor notice the warning it puts out about trust auth),
> they *certainly* didn't look for any nonstandard configure options.
> The normal build process for any open-source package is
>
> ./configure
> make
> sudo make install
> ... now what? OK, time to read the manual ...

Since they presumably don't know about initdb yet, yeah, I figure
they'll be reading the manual. We already talk about how to initdb.
It seems reasonable to have the manual talk about how to initially
connect to your "secure by default" database and create a
non-superuser working user.

I like the idea of it being a configure flag, it seems the least
invasive way to do it.

Andrew

"Andrew Hammond" <andrew(dot)george(dot)hammond(at)gmail(dot)com> writes:
> On 6/25/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> The normal build process for any open-source package is
>>
>> ./configure
>> make
>> sudo make install
>> ... now what? OK, time to read the manual ...

> Since they presumably don't know about initdb yet, yeah, I figure
> they'll be reading the manual. We already talk about how to initdb.
> It seems reasonable to have the manual talk about how to initially
> connect to your "secure by default" database and create a
> non-superuser working user.

+1 on having such a discussion in the manual (someone else suggested
that already IIRC). But I'm not seeing what a configure flag brings
to the party.

regards, tom lane

On 6/25/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Andrew Hammond" <andrew(dot)george(dot)hammond(at)gmail(dot)com> writes:
> > On 6/25/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >> The normal build process for any open-source package is
> >>
> >> ./configure
> >> make
> >> sudo make install
> >> ... now what? OK, time to read the manual ...
>
> > Since they presumably don't know about initdb yet, yeah, I figure
> > they'll be reading the manual. We already talk about how to initdb.
> > It seems reasonable to have the manual talk about how to initially
> > connect to your "secure by default" database and create a
> > non-superuser working user.
>
> +1 on having such a discussion in the manual (someone else suggested
> that already IIRC). But I'm not seeing what a configure flag brings
> to the party.

Like Andrew Sullivan said above, if we want to achieve the dubious
goal of being "secure by default" this seems like the least invasive
way to change the process so that we can be buzzword compliant.

Andrew

"Andrew Hammond" <andrew(dot)george(dot)hammond(at)gmail(dot)com> writes:
> On 6/25/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> +1 on having such a discussion in the manual (someone else suggested
>> that already IIRC). But I'm not seeing what a configure flag brings
>> to the party.

> Like Andrew Sullivan said above, if we want to achieve the dubious
> goal of being "secure by default" this seems like the least invasive
> way to change the process so that we can be buzzword compliant.

It still wouldn't make us "secure by default". Not unless you propose
to actually change the default.

In any case, what is "secure by default"? The current default
configuration is entirely secure against outside attacks, because it
doesn't even allow outside connections. As for inside connections,
"secure" is still largely dependent on what your threat model is.
The paper that started this whole thread pointed out that "ident" auth
can expose you to problems even over a Unix socket, given the right set
of conditions. Shall we reject "ident" as not being sufficiently
secure? Better get rid of Kerberos, too, since that depends on an
outside server that could be compromised. And MD5 is certainly not good
enough, since PG doesn't force you to change to a new 12-character
not-in-the-dictionary password every week. Any of these things could be
considered to be not secure enough, depending on the user's situation.

I'm all for providing a more thorough discussion of these matters in
the manual. I'm not for changing the default behavior, nor for throwing
another roadblock into a day-zero newbie's experience with Postgres.
Ultimately it's the user's responsibility to determine what is
adequately secure for him.

regards, tom lane

On Tue, Jun 26, 2007 at 03:08:59PM -0400, Tom Lane wrote:
> It still wouldn't make us "secure by default". Not unless you propose
> to actually change the default.

Perhaps I wasn't clear enough, but the suggestion I made (for
something I actually don't care about, I hasten to add again) was
that the configure switch _would_ be set to something different by
default. So it would require that those who need trust to work would
have to enable it on purpose; but (I suggested) at a not-too-invasive
point. It'd just be another configure option, and you usually have
to reconsider those at major version upgrade anyway. Nevertheless,

> In any case, what is "secure by default"?

. . .I agree that the checkbox is a dumb one. I think the entire
topic isn't worth the amount of electrons already spilled over it. I
don't see anyone clamouring for this and I see at least one person
strongly opposed. I think it should be regarded as Dead, Jim.

A

--
Andrew Sullivan | ajs(at)crankycanuck(dot)ca
I remember when computers were frustrating because they *did* exactly what
you told them to. That actually seems sort of quaint now.
--J.D. Baldwin

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> As for inside connections, "secure" is still largely dependent on what your
> threat model is.

Indeed I think closing Postgres to access based on the unix userid of the
connecting user is the wrong threat model. That's basically saying you don't
trust the unix userid of the system in which case you're really up a creek.

On the other hand dblink allows something that most sysadmins would consider
outside the normal bounds and of concern in their threat model. It allows a
privilege escalation since it allows any user to make an arbitrary TCP unix
connection as another user. I think that's the angle this should be closed
from.

Put another way there is a fundamental incompatibility between the default
settings for Postgres and the default settings for dblink though. If you
install both and accept the default configuration for both then one subverts
the security of the other. The side that's outside the norm here is dblink,
and in fact it not only subverts Postgres but could allow an attacker to
subvert other systems' security too.

All that really has to happen is that dblink should by default not be callable
by any user other than Postgres. DBAs should be required to manually run
"GRANT EXECUTE ON dblink_connect(text) TO public;" if that's what he wants.

It would be more obvious running that command that you're allowing any user to
make connections as Postgres, and at least whatever piece of documentation
contains that instruction would be a convenient place to put a warning about
it.

The only problem with this is that dblink provides 36 different functions (why
so many?!) My first thought is it could provide a pair of plpgsql functions to
grant or revoke access to all its functions to a specified role. But perhaps
someone else has a cleverer idea.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com

Gregory Stark <stark(at)enterprisedb(dot)com> writes:
> All that really has to happen is that dblink should by default not be
> callable by any user other than Postgres.

Yeah, that is not an unreasonable change. Someone suggested it far
upthread, but we seem to have gotten distracted :-(

> The only problem with this is that dblink provides 36 different functions

I think just having the install script revoke public execute access
on the connection-establishing functions would be sufficient. There
are only two of 'em.

regards, tom lane

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> Gregory Stark <stark(at)enterprisedb(dot)com> writes:
> > All that really has to happen is that dblink should by default not be
> > callable by any user other than Postgres.
>
> Yeah, that is not an unreasonable change. Someone suggested it far
> upthread, but we seem to have gotten distracted :-(

Indeed, I know I did, though I think there were others also. :)

> > The only problem with this is that dblink provides 36 different functions
>
> I think just having the install script revoke public execute access
> on the connection-establishing functions would be sufficient. There
> are only two of 'em.

Agreed. We might want to mention this issue in the documentation
somewhere also, though I'm not really sure where. :/ Basically:

"Be very careful with functions which allow a user to make a remote
connection (eg: dblink). When ident is used as an authentication
mechanism it may be possible for a regular user to gain superuser
priviledges. Functions run in the database backend, which runs as
the OS user the backend was started under. Therefore, connections from
the database backend will be authenticated as the OS user the backend
was started under would be- which using ident generally means a
password-less connection to the database superuser account by means of a
local filesystem socket."

Thanks,

Stephen

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Gregory Stark <stark(at)enterprisedb(dot)com> writes:
>> All that really has to happen is that dblink should by default not be
>> callable by any user other than Postgres.
>
> Yeah, that is not an unreasonable change. Someone suggested it far
> upthread, but we seem to have gotten distracted :-(

On the subject of privilege escalation in contrib modules, I just did some
quick searches. Other modules using suspect calls are: pg_adminpack, and
tsearch2 (aside from pg_standby and pgbench which are stand-alone programs).

It looks like pg_adminpack uses requireSuperuser religiously so that should be
fine.

tsearch2 looks kind of suspect though. I'm not very familiar with it but is
there any protection against an attacker specifying arbitrary files as
dictionary, thesaurus, or stemmer files, allowing the user to open a file as
the postgres user instead of his own privileges?

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com

Tom Lane wrote:
> Gregory Stark <stark(at)enterprisedb(dot)com> writes:
>
>> All that really has to happen is that dblink should by default not be
>> callable by any user other than Postgres.
>>
>
> Yeah, that is not an unreasonable change. Someone suggested it far
> upthread, but we seem to have gotten distracted :-(
>
>
>> The only problem with this is that dblink provides 36 different functions
>>
>
> I think just having the install script revoke public execute access
> on the connection-establishing functions would be sufficient. There
> are only two of 'em.
>
>
>

+1 on this.

cheers

andrew

Jeremy Drake wrote:

> 2. If you cannot tell what process is connecting on a local socket (which
> I suspect you cannot portably),

See ident_unix() in hba.c.

It might not be 100% portable but I think it's fairly close for platforms
that actually have unix sockets.

cheers

andrew

Gregory Stark wrote:
> All that really has to happen is that dblink should by default not be callable
> by any user other than Postgres. DBAs should be required to manually run
> "GRANT EXECUTE ON dblink_connect(text) TO public;" if that's what he wants.

That serves the purpose of making PG "secure by default" (whatever that means
exactly) well, and surely is a good short-term solution.
But it severely limits the usefulness of dblink on setup where PG uses
ident auth either via TCP or unix-sockets - there seems to be no way to
securely users use dblink in such a setup.

Therefore I think there should be a ToDO
"Explore how dblink can be made safe if used together with ident authentication"
or something similar.

The ideal solution would IMHO be to authenticate a user using dblink as
the user he used to connect to PG in the first place - but since ident is
handled outside of PG that might be impossible to archive without some
really bad hacks. So maybe just finding a way to disable ident auth for
connections made via dblink is sufficient.

greetings, Florian Pflug

* Florian Pflug (fgp(dot)phlo(dot)org(at)gmail(dot)com) wrote:
> Gregory Stark wrote:
> >All that really has to happen is that dblink should by default not be
> >callable
> >by any user other than Postgres. DBAs should be required to manually run
> >"GRANT EXECUTE ON dblink_connect(text) TO public;" if that's what he wants.
>
> That serves the purpose of making PG "secure by default" (whatever that
> means
> exactly) well, and surely is a good short-term solution.
> But it severely limits the usefulness of dblink on setup where PG uses
> ident auth either via TCP or unix-sockets - there seems to be no way to
> securely users use dblink in such a setup.

Uh, have the admin create appropriate views.

> Therefore I think there should be a ToDO
> "Explore how dblink can be made safe if used together with ident
> authentication"
> or something similar.

I disagree. What dblink *does* is insecure and in general *shouldn't*
be something regular users can do. That goes well and beyond just the
ident case, imv, but it's handy thing to point to atm.

> The ideal solution would IMHO be to authenticate a user using dblink as
> the user he used to connect to PG in the first place - but since ident is
> handled outside of PG that might be impossible to archive without some
> really bad hacks. So maybe just finding a way to disable ident auth for
> connections made via dblink is sufficient.

erm, this isn't dblink anymore, this is cross-database stuff that should
be done completely differently from dblink, if it's going to be done at
all.

Thanks,

Stephen

On Tue, 26 Jun 2007, Tom Lane wrote:

> Gregory Stark <stark(at)enterprisedb(dot)com> writes:
> > All that really has to happen is that dblink should by default not be
> > callable by any user other than Postgres.
>
> Yeah, that is not an unreasonable change. Someone suggested it far
> upthread, but we seem to have gotten distracted :-(

An idea came to me while thinking about this. The particular use-case
that I use dblink for is connecting to another database in the same
database cluster. ISTM (without looking at any code) that the postmaster
could keep track of who is properly authenticated in each backend, and see
if a connection is being created from that backend to allow connections as
the user in that backend.

I had a couple ideas about this:
1. If you can tell what process is connecting on a local socket, store a
mapping of pid to userid in the postmaster shmem and if a connection is
originating from a pid in this table and is attempting to authenticate as
the corresponding userid, allow it.

2. If you cannot tell what process is connecting on a local socket (which
I suspect you cannot portably), generate a random "token" and stash it in
shared memory mapping it to a userid, and then on authentication, send
this token to the postmaster to prove that you have already authenticated.
This has the downside of turning an exploit where a non-privileged user
can read arbitrary postgres memory, they could potentially gain the
privilieges of any logged on user, but the best idea is to not have that
kind of bug ;)

I know this is not the time for thinking about such things, but it may be
an idea for 8.4...

--
It's really quite a simple choice: Life, Death, or Los Angeles.

Stephen Frost wrote:
> * Florian Pflug (fgp(dot)phlo(dot)org(at)gmail(dot)com) wrote:
>> Gregory Stark wrote:
>>> All that really has to happen is that dblink should by default not be
>>> callable
>>> by any user other than Postgres. DBAs should be required to manually run
>>> "GRANT EXECUTE ON dblink_connect(text) TO public;" if that's what he wants.
>> That serves the purpose of making PG "secure by default" (whatever that
>> means
>> exactly) well, and surely is a good short-term solution.
>> But it severely limits the usefulness of dblink on setup where PG uses
>> ident auth either via TCP or unix-sockets - there seems to be no way to
>> securely users use dblink in such a setup.
>
> Uh, have the admin create appropriate views.
I meant letting them use it to connect to abitrary databases and hosts, not
executing only predefined quries. My wording wasn't clear in that regard,
though.

>> Therefore I think there should be a ToDO
>> "Explore how dblink can be made safe if used together with ident
>> authentication"
>> or something similar.
>
> I disagree. What dblink *does* is insecure and in general *shouldn't*
> be something regular users can do. That goes well and beyond just the
> ident case, imv, but it's handy thing to point to atm.
I fail to see why dblink is any more insecure than, say, plpgsql or
plperl (not plperlu). It doesn't give any more priviliges than pgsql
would. The only exception IMHO are privileges that you get because
dblink issues that connection from a specific machine as a specific user.

What other security problems does dblink impose? Maybe I'm just being blind..

greetings, Florian Pflug

* Florian Pflug (fgp(dot)phlo(dot)org(at)gmail(dot)com) wrote:
> Stephen Frost wrote:
> >Uh, have the admin create appropriate views.
> I meant letting them use it to connect to abitrary databases and hosts, not
> executing only predefined quries. My wording wasn't clear in that regard,
> though.

Perhaps I wasn't clear. My response to that is "don't.". It's not a
safe or sane thing for a user to be able to do.

> >I disagree. What dblink *does* is insecure and in general *shouldn't*
> >be something regular users can do. That goes well and beyond just the
> >ident case, imv, but it's handy thing to point to atm.

> I fail to see why dblink is any more insecure than, say, plpgsql or
> plperl (not plperlu). It doesn't give any more priviliges than pgsql
> would. The only exception IMHO are privileges that you get because
> dblink issues that connection from a specific machine as a specific user.

Trusted languages shouldn't be able to open socket connections. If
plpgsql (erm, don't think so) or plperl (I hope it can't...) can do that
then they should be marked as untrusted.

Thanks,

Stephen

On Tue, 26 Jun 2007, Andrew Dunstan wrote:

> Jeremy Drake wrote:
>
> > 2. If you cannot tell what process is connecting on a local socket (which
> > I suspect you cannot portably),
>
>
> See ident_unix() in hba.c.
>
> It might not be 100% portable but I think it's fairly close for platforms
> that actually have unix sockets.

It looks to me (looking at docs on the various functions used there) that
only Linux supports getting the PID of the connecting process. The other
various *BSD methods tend only to give the uid and gid, which will not be
helpful if the connection is coming from another backend in the same
cluster.

In the linux case, it looks like one would need to get the client pid, try
to get the PGPROC entry for it, if it exists get the roleid out of that
and allow connections as that role.

For any other case, some sort of painful protocol hack would be in order.
The best way I can see is to see if the client process is owned by the
same user as the database cluster, and if so send an auth request (like
the SCM_CRED one), which would be responded to with the pid and a random
sequence stored in the PGPROC entry. The server then proves the backend
really is the one it claims to be by looking up the PID's PGPROC entry,
and making sure the token matches.

This is all just thinking out loud, of course... I have no plans to
implement this in the short-term, but it may be an interesting project in
the future.

--
I like to believe that people in the long run are going to do more to
promote peace than our governments. Indeed, I think that people want
peace so much that one of these days governments had better get out of
the way and let them have it.
-- Dwight D. Eisenhower