Re: [PATCHES] Run-as-admin warning for win32

2004-05-04 Thread Andrew Dunstan
Tom Lane said:
 Bruce Momjian [EMAIL PROTECTED] 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



---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html


Re: [PATCHES] Run-as-admin warning for win32

2004-05-04 Thread Thomas Hallgren
 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



---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html


Re: [PATCHES] Run-as-admin warning for win32

2004-05-04 Thread Shachar Shemesh
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/
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster


Re: [PATCHES] Run-as-admin warning for win32

2004-05-04 Thread Thomas Hallgren
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 [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
 Thomas Hallgren wrote:


http://download.microsoft.com/download/1/b/8/1b8fc001-6f67-4ea1-b0f2-8add1d
a8cbc0/_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/


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




---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])


Re: [PATCHES] Run-as-admin warning for win32

2004-05-04 Thread Magnus Hagander
 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


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org


Re: [PATCHES] Run-as-admin warning for win32

2004-05-04 Thread Magnus Hagander
  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


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings


Re: [PATCHES] Run-as-admin warning for win32

2004-05-04 Thread Magnus Hagander
  . 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


---(end of broadcast)---
TIP 8: explain analyze is your friend


Re: [PATCHES] Run-as-admin warning for win32

2004-05-04 Thread Andrew Dunstan
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

---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org


Re: [PATCHES] Run-as-admin warning for win32

2004-05-04 Thread Bruce Momjian
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
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match


Re: [PATCHES] Run-as-admin warning for win32

2004-05-04 Thread Bruce Momjian
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
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]


Re: [PATCHES] Run-as-admin warning for win32

2004-05-04 Thread Mark Cave-Ayland


 -Original Message-
 From: [EMAIL PROTECTED] 
 [mailto:[EMAIL PROTECTED] On Behalf Of Bruce Momjian
 Sent: 04 May 2004 16:08
 To: Magnus Hagander
 Cc: Tom Lane; Andrew Dunstan; [EMAIL PROTECTED]
 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.



---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html


Re: [PATCHES] Run-as-admin warning for win32

2004-05-04 Thread Bruce Momjian
Mark Cave-Ayland wrote:
 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.

Yea, we could ask them for a username, supply a default, then if the
username doesn't exist, ask if they want to create it.

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

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org


Re: [PATCHES] Run-as-admin warning for win32

2004-05-03 Thread Shachar Shemesh
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/
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send unregister YourEmailAddressHere to [EMAIL PROTECTED])


Re: [PATCHES] Run-as-admin warning for win32

2004-05-03 Thread Tom Lane
Magnus Hagander [EMAIL PROTECTED] 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

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match


Re: [PATCHES] Run-as-admin warning for win32

2004-05-03 Thread Tom Lane
Bruce Momjian [EMAIL PROTECTED] 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

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org