Run-as-admin warning for win32

1. You forgot to check "localsystem", as well as "domain admins". These
two have even higher permissions than the ones you test for, and one of
them is the default if Postgre ever makes it to become a service.
2. Are you sure "Powerusers" is such a good idea? It's the default for
all non-admin users. When Postgres becomes a service, it's going to be
relatively easy to configure it to run as a low-priv user. Until then,
however, isn't it too difficult for admins to set up the system for it
to run as a different user?

Shachar

Magnus Hagander wrote:

For review, comments and possible application to HEAD.

This code implements a warning when the postmaster is started as a
high-privilege account on win32 (administrator or power users).
Previously, postgresql has exited out on Unix when running as root -
this is a similar check, with the following differences:

* We do a ereport(WARNING) instead of exitting out. The reason for this
is that we can expect there are win32 admins that will want to run the
server with a high privilege account. Just sending a warning will permit
this (say, when debugging etc, or if people are just too lazy to care),
while clearly stating it's not a recommended way to do it.

* The Unix check is directly in main.c. We cannot do this on win32,
because at this stage we can only printf and exit. Win32 needs ereport.
Consider when runinng as a service - before we have loaded up
postgresql.conf and noticed we should write to the eventlog, we cannot
inform the user in any way (stderr = /dev/null from a service by
default). Therefor, the win32 check is in PostmasterMain. There might be
a slightly better place to put it, not 100% sure about that..

The win32 specific code is mainly in the file security.c to go in
src/backend/port/win32.

//Magnus

<<security.c>> <<admin_warning.patch>>

------------------------------------------------------------------------

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

--
Shachar Shemesh
Lingnu Open Source Consulting
http://www.lingnu.com/

"Magnus Hagander" <mha@sollentuna.net> writes:

Previously, postgresql has exited out on Unix when running as root -
this is a similar check, with the following differences:

* We do a ereport(WARNING) instead of exitting out.

Why? If we refuse to run as root on Unix, I do not see an argument for
being more forgiving on Windows.

The reason for this
is that we can expect there are win32 admins that will want to run the
server with a high privilege account.

Translated: "we can expect a higher proportion of Windows admins who
will refuse to be force-fed a clue"? Not a lot of sympathy here.

* The Unix check is directly in main.c. We cannot do this on win32,
because at this stage we can only printf and exit. Win32 needs ereport.

We could move the Unix check later without any problem. I agree with
keeping both checks in the same place.

regards, tom lane

Tom Lane wrote:

"Magnus Hagander" <mha@sollentuna.net> writes:

Previously, postgresql has exited out on Unix when running as root -
this is a similar check, with the following differences:

* We do a ereport(WARNING) instead of exitting out.

Why? If we refuse to run as root on Unix, I do not see an argument for
being more forgiving on Windows.

I am not sure it is as easy to run as non-admin on Win32 as it is to run
as non-root on Unix. Is it?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Tom Lane wrote:

Why? If we refuse to run as root on Unix, I do not see an argument for
being more forgiving on Windows.

I am not sure it is as easy to run as non-admin on Win32 as it is to run
as non-root on Unix. Is it?

Ease of use has nothing to do with this. Given the demonstrated
security weaknesses of Windows, we would be completely irresponsible
to allow Postgres to be started in an obviously-insecure way on that
platform.

In other words, I do not wish to be the author of code that could become
the vector for the next SQL Slammer worm.

I am already deathly afraid of what the Windows port is likely to do
to Postgres' reputation for reliability and security. Do *not* get
me started by proposing that we insert obvious security holes on lame
"ease of use" grounds. Haven't the boys in Redmond already proven
the wrongness of those priorities many times over?

regards, tom lane

Tom Lane said:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Tom Lane wrote:

Why? If we refuse to run as root on Unix, I do not see an argument
for being more forgiving on Windows.

I am not sure it is as easy to run as non-admin on Win32 as it is to
run as non-root on Unix. Is it?

Ease of use has nothing to do with this. Given the demonstrated
security weaknesses of Windows, we would be completely irresponsible to
allow Postgres to be started in an obviously-insecure way on that
platform.

In other words, I do not wish to be the author of code that could
become the vector for the next SQL Slammer worm.

Me either :-)

I am already deathly afraid of what the Windows port is likely to do to
Postgres' reputation for reliability and security. Do *not* get me
started by proposing that we insert obvious security holes on lame
"ease of use" grounds. Haven't the boys in Redmond already proven the
wrongness of those priorities many times over?

If we are going to enforce the 'must be non-privileged user' on Windows,
there are some things we need to do, I think:

. enforce the rule in initdb (currently it does not, on Windows).
. if the installer is running as Administrator, it should create a
Postgres user
. if the installer is going to install the service, it should run initdb
as the postgres user (is that possible?) and install the service to run as
that user.

IOW, we need to make it as easy as possible to be secure.

cheers

andrew

2. Are you sure "Powerusers" is such a good idea? It's the default for
all non-admin users. When Postgres becomes a service, it's going to be
relatively easy to configure it to run as a low-priv user. Until then,
however, isn't it too difficult for admins to set up the system for it
to run as a different user?

Found this document on the net. It gives you a good overview of what
different levels of users can and cannot do. I think the heading "What can a
power user do that a user can't" contains a couple of very good reasons to
prevent that PostgreSQL runs with Powerusers rights.

http://download.microsoft.com/download/1/b/8/1b8fc001-6f67-4ea1-b0f2-8add1da8cbc0/_Toc42414596

Exerpt:

Unfortunately, these permissions are also the same permissions that allow
power users to:
� Introduce Trojan horses that, if executed by administrators or
other users, can compromise system and data security
� Make system-wide operating system and application changes
that affect other users of the system

Kind regards,

Thomas Hallgren

Thomas Hallgren wrote:

http://download.microsoft.com/download/1/b/8/1b8fc001-6f67-4ea1-b0f2-8add1da8cbc0/_Toc42414596

Link does not work.

Exerpt:

Unfortunately, these permissions are also the same permissions that allow
power users to:
? Introduce Trojan horses that, if executed by administrators or
other users, can compromise system and data security
? Make system-wide operating system and application changes
that affect other users of the system

Kind regards,

Thomas Hallgren

<rant>
That pathetic thing called "Windows security" is getting to me. It is
close to impossible to create a "user", and once created, this user will
not be capable of actually doing anything.

Very flexible, very granular permissions system result in making it
impossible for someone, us in this case, to find out whether we are
over-priveleged.

Well meaning, but horrible system, with even more horrible results.
</rant>

Shachar

--
Shachar Shemesh
Lingnu Open Source Consulting
http://www.lingnu.com/

Sorry. That link was internal to the document. This one should work.

http://download.microsoft.com/download/1/b/8/1b8fc001-6f67-4ea1-b0f2-8add1da8cbc0/SecDefs2003.doc

Regards,

Thomas Hallgren

"Shachar Shemesh" <psql@shemesh.biz> wrote in message
news:40975492.8030407@shemesh.biz...

Thomas Hallgren wrote:

http://download.microsoft.com/download/1/b/8/1b8fc001-6f67-4ea1-b0f2-8add1d

a8cbc0/_Toc42414596

1. You forgot to check "localsystem", as well as "domain
admins". These two have even higher permissions than the ones
you test for, and one of them is the default if Postgre ever
makes it to become a service.

Not at all. Local System is a member of the Administrators group (no, it
doesn't show up as such, but if you examine the token on any local
system process, you will see it has the administrators group sid in it).

As is Domain Admins, if the machine is in a domain. And if it's not in a
domain, Domain Admins simply does not apply.

Domain admins have equal permissions on the local machine as
Administrators, not more. It has more permissions on the network, but we
can't really dig into that - there are a lot of other groups we need to
check for as well in that case.

2. Are you sure "Powerusers" is such a good idea? It's the
default for all non-admin users.

In what installation is that the default? The default is "Users" and not
"Power Users" in all my installations (which I haven't tweaked in that
respect from what I know).

When Postgres becomes a
service, it's going to be relatively easy to configure it to
run as a low-priv user. Until then, however, isn't it too
difficult for admins to set up the system for it to run as a
different user?

First, this is one of the reasons I go with warning and not error out.

Second, I run it as a low-priv account myself using:
runas /user:postgres c:\pgdev\bin\startpg.bat

If you want to start it using an icon, it has the same possibilities. Or
just start a commandprompt as a different user and use that one.

(RunAs requires Windows2000+, that's the only limitation)

//Magnus

Why? If we refuse to run as root on Unix, I do not see an

argument

for being more forgiving on Windows.

I am not sure it is as easy to run as non-admin on Win32 as

it is to

run as non-root on Unix. Is it?

It is a little bit more tricky, but not much. I'd say it's more a factor
of what people are used to. People think of their windows machine as a
single user system, and their unix machine as a multi user system.

On Windows 2000+ you can have multiple windows on the same windowstation
running as different users (using runas). In <= NT4 you can only have
services running as a different user than the one logged on to the
console.

Ease of use has nothing to do with this. Given the
demonstrated security weaknesses of Windows, we would be
completely irresponsible to allow Postgres to be started in
an obviously-insecure way on that platform.

Certainly a good argument. I was more into emulating the behaviour of
the other available databases for windows. They normally *let* you run
with high-priv accounts, but they *recommend* you don't. This is pretty
much standard for most "respectable" windows products.

In production, you certainly wouldn't do it. But it helps if you're
setting it up in a lab for testing, for example. When you *know* what
you are doing.

But it can certainly be changed to an ERROR if we want. I'd still argue
it should go at this place and not earlier, because we want to get the
output to go where we want it. See below.

In other words, I do not wish to be the author of code that
could become the vector for the next SQL Slammer worm.

While I fully agree with that statement, it is an incorrect comparison.
The Slammer worm was because of a hole in SQL Server that allowed
arbitrary code execution as whatever user account SQLServer was running
as. It spread equally on server running as Local System, with admin
privileges or (as recommended) with a low-privilege account. You don't
have to be an administrator to send out UDP packets...

A similar worm could just as easily hit postgresql on Unix today (eh,
assuming such a security hole existed, which I'm fairly sure it doesn't,
but hypothetically). It's not a factor of the platform. It's a factor of
the product.

Local admin privs will let a security hole in *postgresql*
change/destroy parts of the *local operating system*. No more, no less.
(Unless you give it domain admins, in which case in can reach other
machines in the domain, of course)

I am already deathly afraid of what the Windows port is
likely to do to Postgres' reputation for reliability and
security. Do *not* get me started by proposing that we
insert obvious security holes on lame "ease of use" grounds.
Haven't the boys in Redmond already proven the wrongness of
those priorities many times over?

They certainly have. In some products (like SQL Server), they've now
moved to recommending against it. They're not outright preventing it.

From other mail:

We could move the Unix check later without any problem. I agree with

keeping both checks

in the same place.

I read this as that you'd prefer to see the Unix "check-for-root"-code
moved to the same place? If so, I'll update the patch with that,
depending the outcome of the rest of the discussion (for example, need
for ereport)

//Magnus

"Andrew Dunstan" <andrew@dunslane.net> writes:

. if the installer is running as Administrator, it should create a
Postgres user

IOW, we need to make it as easy as possible to be secure.

No objection to that idea ...

regards, tom lane

. if the installer is running as Administrator, it should create a
Postgres user

IOW, we need to make it as easy as possible to be secure.

No objection to that idea ...

I don't think we should create a postgres user. We should tell the guy
who installs it to do that, and have him tell us what it is, but we
should not actually go create the user for him. That is sure to annoy a
lot of people. Most shops I know have standard naming conventinos, and
would not want a user called "postgres" without any qualifications.

I know, small point, but I think significant.

The installer-skeleton I have right now permits installation as local
system but recommends a user account. But that's just functionality to
remove, so that's easily done. In the other case, it prompts for
username and password to run as.

//Magnus

Magnus Hagander wrote:

. if the installer is running as Administrator, it should create a
Postgres user

IOW, we need to make it as easy as possible to be secure.

No objection to that idea ...

I don't think we should create a postgres user. We should tell the guy
who installs it to do that, and have him tell us what it is, but we
should not actually go create the user for him. That is sure to annoy a
lot of people. Most shops I know have standard naming conventinos, and
would not want a user called "postgres" without any qualifications.

I know, small point, but I think significant.

Then how about we ask them what user it should run as, and offer them
the option of an exisiting user (they pick from a list) or a new user
(which we would create for them), with their choice of new user name,
defaulting to Postgres?

cheers

andrew

Magnus Hagander wrote:

. if the installer is running as Administrator, it should create a
Postgres user

IOW, we need to make it as easy as possible to be secure.

No objection to that idea ...

I don't think we should create a postgres user. We should tell the guy
who installs it to do that, and have him tell us what it is, but we
should not actually go create the user for him. That is sure to annoy a
lot of people. Most shops I know have standard naming conventinos, and
would not want a user called "postgres" without any qualifications.

I know, small point, but I think significant.

The installer-skeleton I have right now permits installation as local
system but recommends a user account. But that's just functionality to
remove, so that's easily done. In the other case, it prompts for
username and password to run as.

How would it install on an XP laptop? If I am logged in as myself and
I am listed as a "Computer Administrator", do I need to create another
user, and how do I do the install as that other user, and start/stop the
server, and stuff like that?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

The installer-skeleton I have right now permits

installation as local

system but recommends a user account. But that's just

functionality to

remove, so that's easily done. In the other case, it prompts for
username and password to run as.

How would it install on an XP laptop? If I am logged in as
myself and I am listed as a "Computer Administrator", do I
need to create another user, and how do I do the install as
that other user, and start/stop the server, and stuff like that?

Yes, you need to create another user.
When running as a service, just tell the installer. It should set up
required permissions. Then start the service as normal using the Service
Control Manager.

When running manually, you will have to grant the postgres user the
required permissions on the PGDATA directory. Then you can start the
server using "runas".

//Magnus

Magnus Hagander wrote:

The installer-skeleton I have right now permits

installation as local

system but recommends a user account. But that's just

functionality to

remove, so that's easily done. In the other case, it prompts for
username and password to run as.

How would it install on an XP laptop? If I am logged in as
myself and I am listed as a "Computer Administrator", do I
need to create another user, and how do I do the install as
that other user, and start/stop the server, and stuff like that?

Yes, you need to create another user.
When running as a service, just tell the installer. It should set up
required permissions. Then start the service as normal using the Service
Control Manager.

When running manually, you will have to grant the postgres user the
required permissions on the PGDATA directory. Then you can start the
server using "runas".

Ewe, big on security, small on ease-of-use. :-)

I have never had to create a user to install any other software on my
laptop.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Yes, you need to create another user.
When running as a service, just tell the installer. It

should set up

required permissions. Then start the service as normal using the
Service Control Manager.

When running manually, you will have to grant the postgres user the
required permissions on the PGDATA directory. Then you can

start the

server using "runas".

Ewe, big on security, small on ease-of-use. :-)

I have never had to create a user to install any other
software on my laptop.

Yes, this is why I originally suggested making it a recommendation and
not something enforced. It will certainly take a good lot of windows
people by surprise...

//Magnus

Bruce Momjian wrote:

I have never had to create a user to install any other software on my
laptop.

How much else that you have installed runs as a service?

OTOH, I just installed apache and it is running the service as
LocalSystem :-(

cheers

andrew

-----Original Message-----
From: pgsql-patches-owner@postgresql.org
[mailto:pgsql-patches-owner@postgresql.org] On Behalf Of Bruce Momjian
Sent: 04 May 2004 16:08
To: Magnus Hagander
Cc: Tom Lane; Andrew Dunstan; pgsql-patches@postgresql.org
Subject: Re: [PATCHES] Run-as-admin warning for win32

Magnus Hagander wrote:

The installer-skeleton I have right now permits

installation as local

system but recommends a user account. But that's just

functionality to

remove, so that's easily done. In the other case, it prompts for
username and password to run as.

How would it install on an XP laptop? If I am logged in as
myself and I am listed as a "Computer Administrator", do I
need to create another user, and how do I do the install as
that other user, and start/stop the server, and stuff like that?

Yes, you need to create another user.
When running as a service, just tell the installer. It

should set up

required permissions. Then start the service as normal using the
Service Control Manager.

When running manually, you will have to grant the postgres user the
required permissions on the PGDATA directory. Then you can

start the

server using "runas".

Ewe, big on security, small on ease-of-use. :-)

I have never had to create a user to install any other
software on my laptop.

Just listening in on this thread.... I would be inclined to agree that
the Win32 PostgeSQL should run under its own user given the history of
Windows security. FWIW I know that Installshield (one of the most
popular installers) and the default settings for MSI mean that only
administrators can install software; so I would argue that there is no
reason why the PostgreSQL installer also shouldn't require administrator
privilege to run, and therefore create the postgres user account for us.

I think that most of the services installed under Win32 use a separate
username and password. The last installer I used that required a
username and password was for a backup service and then it was simply a
page in the installer that asked for a username and password to run
under; both of these were set to defaults so all I had to do was click
"Next" and the account was generated for me and the directory
permissions set up correctly. This has the balance that the novice user
will just click "Next" while the advanced user can set up a customised
account to meet his/her needs.

In the case of a stand-alone executable, I can see things being a little
more tricky; however I don't see there being any reason why when we
launch the backend we try the default username and password (postgres
and "") and if that doesn't work then we ask for a new username and
password from the user. I would see that this wouldn't be done in the
backend but in the equivalent of the /etc/init.d/postgresql script under
Win32. If people really got upset about asking for the account password
on startup then I guess they would have to go for the service
installation - I see this as being no different to the inconvenience of
asking for a PEM passphrase when we restart Apache on our Linux servers.

Finally I believe we can tighten up security from SMB/RPC-based exploits
by applying the "can only logon locally" policy to the postgres account
which means that SMB/RPC connections cannot be made with the default
PostgreSQL username and password which is always a good thing :)

Kind regards,

Mark.

---

Mark Cave-Ayland
Webbased Ltd.
Tamar Science Park
Derriford
Plymouth
PL6 8BX
England

Tel: +44 (0)1752 764445
Fax: +44 (0)1752 764446

This email and any attachments are confidential to the intended
recipient and may also be privileged. If you are not the intended
recipient please delete it from your system and notify the sender. You
should not copy it or use it for any purpose nor disclose or distribute
its contents to any other person.