Running PostGre on DVD

Why do you need to run PostgreSQL as admin? There shouldn't be any need
for this.

Someone has done a PostgreSQL demo CD, I believe based on Knoppix.
The list archives will probably have more info.

On Mon, Nov 14, 2005 at 11:29:10AM +0100, eric.leguillier@mpsa.com wrote:

Hi everybody,

My questions may seem kind of odd.

I would like to run PostGreSQL on a DVD (database on the DVD and if
possible executable on DVD too) on windows.
I want no installation at all, so I took the no install package.

The problem is the need of creating a non-admin user to run PostGre, I
would like to know if there is an option to parameter PostGre to accept
WILLINGLY that an administrator user can run it. If there isn't, it would
be a great idea to add such a parameter.

Secondly, I would like to run PostGre having only read permission on the
data directory (which would be on the DVD...). Is it possible? If not, can
it be added (add of a 'read-only' option).

Thanks in advance for your help.

Regards,

Eric LEGUILLIER

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

--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

On Tuesday 15 November 2005 12:29 am, Jim C. Nasby wrote:

Why do you need to run PostgreSQL as admin? There shouldn't be any need
for this.

Actually I've run into a scenario where this was needed. I'm not a Windows
expert, so there might be some way to get around this:

I have a localadmin account on the workstation(which is a member of a domain).
As this localadmin(with full local administrative privileges) I created a
local user "postgres" to run PostgreSQL as. The problem was that the policy
for the domain the machine was a member of(which obviously overrides local
settings) prevented this new local user to have "local login" privileges.
Therefore I couldn't create a user to run the postmaster as. I was "stuck"
with my admin-user, which I was not able to start PG as. This was quite
frustrating as I really wanted to install Tomcat+PG to run a demo-webapp for
a customer on one of their machines. There really should be an option for
"Yes, I really want to run PG as a user with Administrator-privileges on
Windows. I promiss not to bug -hacker about any potential security-problems I
might experience".

--
Andreas Joseph Krogh <andreak@officenet.no>
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
------------------------+---------------------------------------------+
OfficeNet AS | The most difficult thing in the world is to |
Hoffsveien 17 | know how to do a thing and to watch |
PO. Box 425 Skøyen | somebody else doing it wrong, without |
0213 Oslo | comment. |
NORWAY | |
Phone : +47 22 13 01 00 | |
Direct: +47 22 13 10 03 | |
Mobile: +47 909 56 963 | |
------------------------+---------------------------------------------+

I explain myself about running PostGre as admin.

In fact I don't want specifically run PostGre as admin. The problem is, on
the computers the application including PostGre will run, I'm not sure that
the user won't have any admin or power user rights. Furthermore, I've
noticed that on certain domains, any user created is automatically added to
a default group having power user rights (that is actually happening to
me).
It causes I cannot run PostGre because on my domain, because any user
created is added to such a default group. That's why adding a parameter for
willingly authorize an user with special rights to run the application
would be great for me.

Regards,

Eric LEGUILLIER

Why do you need to run PostgreSQL as admin? There shouldn't be any need
for this.

Someone has done a PostgreSQL demo CD, I believe based on Knoppix.
The list archives will probably have more info.

On Mon, Nov 14, 2005 at 11:29:10AM +0100, eric.leguillier@mpsa.com wrote:

Hi everybody,

My questions may seem kind of odd.

I would like to run PostGreSQL on a DVD (database on the DVD and if
possible executable on DVD too) on windows.
I want no installation at all, so I took the no install package.

The problem is the need of creating a non-admin user to run PostGre, I
would like to know if there is an option to parameter PostGre to accept
WILLINGLY that an administrator user can run it. If there isn't, it would
be a great idea to add such a parameter.

Secondly, I would like to run PostGre having only read permission on the
data directory (which would be on the DVD...). Is it possible? If not,

can

it be added (add of a 'read-only' option).

Thanks in advance for your help.

Regards,

Eric LEGUILLIER

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

--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

On Tue, Nov 15, 2005 at 09:19:23AM +0100, Andreas Joseph Krogh wrote:

On Tuesday 15 November 2005 12:29 am, Jim C. Nasby wrote:

Why do you need to run PostgreSQL as admin? There shouldn't be any need
for this.

Actually I've run into a scenario where this was needed. I'm not a Windows
expert, so there might be some way to get around this:

I have a localadmin account on the workstation(which is a member of a domain).
As this localadmin(with full local administrative privileges) I created a
local user "postgres" to run PostgreSQL as. The problem was that the policy
for the domain the machine was a member of(which obviously overrides local
settings) prevented this new local user to have "local login" privileges.

Typical windows, can't give up admin priveliges even if you want to.

All jokes aside, doesn't "runas" allow you to start a program as
another user? Although the web seems to imply you have to be running a
special service to have multiple accounts running simultaneously. Talk
about bolt-on security.

<snip>

There really should be an option for
"Yes, I really want to run PG as a user with Administrator-privileges on
Windows. I promiss not to bug -hacker about any potential security-problems I
might experience".

This is free software. Nothing is stopping you from downloading the
source, disabling the check and posting it as:

Safety Free PostgreSQL - The PostgreSQL that runs everywhere and lets
you do anything, including trash your machine on demand.

There's just no reason for it to be an official PostgreSQL Development
Group product.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Why do you need to run PostgreSQL as admin? There

shouldn't be any

need for this.

Actually I've run into a scenario where this was needed. I'm not a
Windows expert, so there might be some way to get around this:

I have a localadmin account on the workstation(which is a

member of a domain).

As this localadmin(with full local administrative privileges) I
created a local user "postgres" to run PostgreSQL as. The

problem was

that the policy for the domain the machine was a member of(which
obviously overrides local
settings) prevented this new local user to have "local

login" privileges.

Typical windows, can't give up admin priveliges even if you want to.

Huh. The stated problem is that the low privilege account does *not*
have the required privilege (to log in).
Note that PostgreSQL doesn't really require "log on locally" for
anything other than initdb. So if you can initdb on a different box and
copy it there, or somehow get the permissions temporarily, the server
will workf ine. The server only requires "Log in as a service".

The best way to fix it is of course if you can have the domain guys
grant your local account the login locally right. If not, perhaps they
can set you up with a low-priv domain account to run the service under?
(I assume you are not the domain admin guy, or this would have already
been fixed...)

If the security is set up so that you can use a local *admin* acconut
but not a local *nonadmin* accuont, then your domain people really need
to look over their security policies, because they are very very broken
indeed.

All jokes aside, doesn't "runas" allow you to start a program
as another user?

It does, but this still requires that this user have the right to log
in, which is the problem in this case it seems.

/Magnus

On Tue, Nov 15, 2005 at 01:51:04PM +0100, Magnus Hagander wrote:

Huh. The stated problem is that the low privilege account does *not*
have the required privilege (to log in).
Note that PostgreSQL doesn't really require "log on locally" for
anything other than initdb. So if you can initdb on a different box and
copy it there, or somehow get the permissions temporarily, the server
will workf ine. The server only requires "Log in as a service".

Sorry, my understanding of Windows permissions is hazy at times. You
have permission to create users, but not permission to run programs as
the user you created (because you need to "login"). And there is a
distinction between running as a service and running as a program(?!).

So I think my statement is correct that the above user cannot run
programs as anything other than administrator privelidges. Like you
said, if he could, this discussion would be moot.

If the security is set up so that you can use a local *admin* acconut
but not a local *nonadmin* accuont, then your domain people really need
to look over their security policies, because they are very very broken
indeed.

That was the way I read it and I agree, that's a very broken way to set
things up.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

I explain myself about running PostGre as admin.

In fact I don't want specifically run PostGre as admin. The problem

is, on

the computers the application including PostGre will run, I'm not sure
that
the user won't have any admin or power user rights. Furthermore, I've
noticed that on certain domains, any user created is automatically

added

to
a default group having power user rights (that is actually happening

to

me).

To be honest, the fact that Postgres forces you to run as a non-admin
user has given me nothing but headaches. (yes, I know, the problem is
defaulting everyone to admin rights is the problem. But that's where I
am). I have been kicking around the idea of posting a change to allow
you to run as admin, but in the meanwhile if you can build Postgres on
your machine, the fix is very easy. Go into src/backend/main/main.c and
find the line

if (pgwin32_is_admin())

and change it to

if (false && pgwin32_is_admin())

Mike Pollard
SUPRA Server SQL Engineering and Support
Cincom Systems, Inc

Huh. The stated problem is that the low privilege account

does *not*

have the required privilege (to log in).
Note that PostgreSQL doesn't really require "log on locally" for
anything other than initdb. So if you can initdb on a different box
and copy it there, or somehow get the permissions temporarily, the
server will workf ine. The server only requires "Log in as

a service".

Sorry, my understanding of Windows permissions is hazy at
times. You have permission to create users, but not
permission to run programs as the user you created (because
you need to "login").

Yes. If you set up your permissions in a really weird way, you can have
that.

And there is a distinction between
running as a service and running as a program(?!).

Yes. And this is a good thing! :-)
There is no reason a normal user should be able to run a service
process. And services should normally have dedicated accounts, and there
is no reason you should ever need to log in as that account
interactively.

//Magnus

On Tuesday 15 November 2005 02:07 pm, Martijn van Oosterhout wrote:

On Tue, Nov 15, 2005 at 01:51:04PM +0100, Magnus Hagander wrote:

Huh. The stated problem is that the low privilege account does *not*
have the required privilege (to log in).
Note that PostgreSQL doesn't really require "log on locally" for
anything other than initdb. So if you can initdb on a different box and
copy it there, or somehow get the permissions temporarily, the server
will workf ine. The server only requires "Log in as a service".

Sorry, my understanding of Windows permissions is hazy at times. You
have permission to create users, but not permission to run programs as
the user you created (because you need to "login"). And there is a
distinction between running as a service and running as a program(?!).

So I think my statement is correct that the above user cannot run
programs as anything other than administrator privelidges. Like you
said, if he could, this discussion would be moot.

If the security is set up so that you can use a local *admin* acconut
but not a local *nonadmin* accuont, then your domain people really need
to look over their security policies, because they are very very broken
indeed.

That was the way I read it and I agree, that's a very broken way to set
things up.

Have a nice day,

Broken or not, it's a setup I'm not in control over. And I'm certainly not the
guy to hack the "disable admin-security-check on windows" feature:-(

--
Andreas Joseph Krogh <andreak@officenet.no>
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
------------------------+---------------------------------------------+
OfficeNet AS | The most difficult thing in the world is to |
Hoffsveien 17 | know how to do a thing and to watch |
PO. Box 425 Skøyen | somebody else doing it wrong, without |
0213 Oslo | comment. |
NORWAY | |
Phone : +47 22 13 01 00 | |
Direct: +47 22 13 10 03 | |
Mobile: +47 909 56 963 | |
------------------------+---------------------------------------------+

On Tuesday 15 November 2005 02:16 pm, Pollard, Mike wrote:

I explain myself about running PostGre as admin.

In fact I don't want specifically run PostGre as admin. The problem

is, on

the computers the application including PostGre will run, I'm not sure
that
the user won't have any admin or power user rights. Furthermore, I've
noticed that on certain domains, any user created is automatically

added

to
a default group having power user rights (that is actually happening

to

me).

To be honest, the fact that Postgres forces you to run as a non-admin
user has given me nothing but headaches. (yes, I know, the problem is
defaulting everyone to admin rights is the problem. But that's where I
am). I have been kicking around the idea of posting a change to allow
you to run as admin, but in the meanwhile if you can build Postgres on
your machine, the fix is very easy. Go into src/backend/main/main.c and
find the line

if (pgwin32_is_admin())

and change it to

if (false && pgwin32_is_admin())

Thanks, I'll see if I can build PG on Windows now.

--
Andreas Joseph Krogh <andreak@officenet.no>
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
------------------------+---------------------------------------------+
OfficeNet AS | The most difficult thing in the world is to |
Hoffsveien 17 | know how to do a thing and to watch |
PO. Box 425 Skøyen | somebody else doing it wrong, without |
0213 Oslo | comment. |
NORWAY | |
Phone : +47 22 13 01 00 | |
Direct: +47 22 13 10 03 | |
Mobile: +47 909 56 963 | |
------------------------+---------------------------------------------+

-----Original Message-----
From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of
Magnus Hagander
Sent: 15 November 2005 13:31
To: Martijn van Oosterhout
Cc: Andreas Joseph Krogh; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Running PostGre on DVD

Yes. And this is a good thing! :-)
There is no reason a normal user should be able to run a service
process. And services should normally have dedicated
accounts, and there
is no reason you should ever need to log in as that account
interactively.

Yes there is, to setup a MAPI profile for the service to use.

However I'd welcome it if you could prove that wrong with an easy way to
create a profile for a different user :-)

Regards, Dave.

Yes. And this is a good thing! :-)
There is no reason a normal user should be able to run a service
process. And services should normally have dedicated accounts, and
there is no reason you should ever need to log in as that account
interactively.

Yes there is, to setup a MAPI profile for the service to use.

However I'd welcome it if you could prove that wrong with an
easy way to create a profile for a different user :-)

Just don't use MAPI from a service. It was *NOT* made for doing that.
MAPI was created for a single user running a single-threaded app on a
single console.

There are plenty of other ways to get to your mail, that will actually
work :-)

//Magnus

-----Original Message-----
From: Magnus Hagander [mailto:mha@sollentuna.net]
Sent: 15 November 2005 13:45
To: Dave Page; Martijn van Oosterhout
Cc: Andreas Joseph Krogh; pgsql-hackers@postgresql.org
Subject: RE: [HACKERS] Running PostGre on DVD

Yes. And this is a good thing! :-)
There is no reason a normal user should be able to run a service
process. And services should normally have dedicated

accounts, and

there is no reason you should ever need to log in as that account
interactively.

Yes there is, to setup a MAPI profile for the service to use.

However I'd welcome it if you could prove that wrong with an
easy way to create a profile for a different user :-)

Just don't use MAPI from a service. It was *NOT* made for doing that.
MAPI was created for a single user running a single-threaded app on a
single console.

There are plenty of other ways to get to your mail, that will actually
work :-)

Better tell that to the SQL Server team then 'cos that's exactly how the
SQL Agent sends mail :-)

/D

I explain myself about running PostGre as admin.

In fact I don't want specifically run PostGre as admin. The problem

is, on

the computers the application including PostGre will run,

I'm not sure

that the user won't have any admin or power user rights.

Furthermore,

I've noticed that on certain domains, any user created is
automatically

added

to
a default group having power user rights (that is actually happening

to

me).

To be honest, the fact that Postgres forces you to run as a
non-admin user has given me nothing but headaches. (yes, I
know, the problem is defaulting everyone to admin rights is
the problem. But that's where I am). I have been kicking
around the idea of posting a change to allow you to run as
admin,

This has been proposed before, and always rejected. While you're always
welcome to provide a patch, I'm very doubtful it would be accepted into
the main product.

//Magnus

On Tuesday 15 November 2005 03:05 pm, Magnus Hagander wrote:

I explain myself about running PostGre as admin.

In fact I don't want specifically run PostGre as admin. The problem

is, on

the computers the application including PostGre will run,

I'm not sure

that the user won't have any admin or power user rights.

Furthermore,

I've noticed that on certain domains, any user created is
automatically

added

to
a default group having power user rights (that is actually happening

to

me).

To be honest, the fact that Postgres forces you to run as a
non-admin user has given me nothing but headaches. (yes, I
know, the problem is defaulting everyone to admin rights is
the problem. But that's where I am). I have been kicking
around the idea of posting a change to allow you to run as
admin,

This has been proposed before, and always rejected. While you're always
welcome to provide a patch, I'm very doubtful it would be accepted into
the main product.

Oracle allows you to run it as admin... Don't know about SQL Server...
My bet is PG will some day bite the bullet and allow this too as more and more
will use PG on Windows.

--
Andreas Joseph Krogh <andreak@officenet.no>
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
------------------------+---------------------------------------------+
OfficeNet AS | The most difficult thing in the world is to |
Hoffsveien 17 | know how to do a thing and to watch |
PO. Box 425 Skøyen | somebody else doing it wrong, without |
0213 Oslo | comment. |
NORWAY | |
Phone : +47 22 13 01 00 | |
Direct: +47 22 13 10 03 | |
Mobile: +47 909 56 963 | |
------------------------+---------------------------------------------+

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

To be honest, the fact that Postgres forces you to run as a
non-admin user has given me nothing but headaches. (yes, I
know, the problem is defaulting everyone to admin rights is
the problem. But that's where I am). I have been kicking
around the idea of posting a change to allow you to run as
admin,

This has been proposed before, and always rejected. While you're always
welcome to provide a patch, I'm very doubtful it would be accepted into
the main product.

The example given in this thread certainly isn't going to change
anybody's mind. "Hi, I propose reducing everybody's security because
my local admins insist on an utterly brain-dead security policy."

regards, tom lane

This has been proposed before, and always rejected. While you're
always welcome to provide a patch, I'm very doubtful it would be
accepted into the main product.

The example given in this thread certainly isn't going to change

anybody's mind.

"Hi, I propose reducing everybody's security because my local admins

insist on an

utterly brain-dead security policy."

I think there is still need for discussion in this area for typical
Windows desktop use.

1. You can run Windows without creating users at all.
2. You may be using a Windows box where you are not allowed to create a
user

To apply unix practices to Windows is imho not really practicable.
For example a Windows developer usually uses an account with
administrative privs
and thus cannot run "make check" from his account :-(

Andreas

The example given in this thread certainly isn't going to change
anybody's mind. "Hi, I propose reducing everybody's security because
my local admins insist on an utterly brain-dead security policy."

What's wrong with that? ;)

But seriously, the proposal is not to reduce everybody's security, just
make it an option for people that want to. I am not arguing that it is
a good idea/bad idea. In fact, the best thing to do may be to leave it
in contrib, so if someone thinks it will solve a problem, it is at least
a little painful to get to it. But at least by putting it into contrib,
it may be useful to someone. Especially if the idea is to put a sample
database onto a removable device. I suspect this is for some kind of
demo (if not, it could be used for one); you go to a prospects site, pop
the CD/DVD into their machine, and show off what your product can do for
them. In that case, you may have no control over the permissions on the
machine, and you certainly do not want to have to create and switch
users for a demo; you've just lost the customers interest.

Also, in my case, I'm running the debugger and profiler against Postgres
on my Windows machine. I find it much easier to throw out the admin
restriction, so I can just use my own account. I agree that my default
account should not have had full admin rights, but that is the way the
machine came. And yes, I should have immediately created a new user and
set myself up on that one. But come on, my old laptop was so old, and I
was so excited... sorry, TMI.

Mike Pollard
SUPRA Server SQL Engineering and Support
Cincom Systems, Inc

On Tuesday 15 November 2005 03:37 pm, Tom Lane wrote:

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

To be honest, the fact that Postgres forces you to run as a
non-admin user has given me nothing but headaches. (yes, I
know, the problem is defaulting everyone to admin rights is
the problem. But that's where I am). I have been kicking
around the idea of posting a change to allow you to run as
admin,

This has been proposed before, and always rejected. While you're always
welcome to provide a patch, I'm very doubtful it would be accepted into
the main product.

The example given in this thread certainly isn't going to change
anybody's mind. "Hi, I propose reducing everybody's security because
my local admins insist on an utterly brain-dead security policy."

Tom, nobody wants to reduce everybody's security, and nobody is proposing
changes leading to such. I just believe more than me agree that having this
as an option on Windows wouldn't hurt anybody, but would rather make life
simpler for some Windows people. Anyway, I don't use Windows on a regular
basis, so it's not that important to me...

--
Andreas Joseph Krogh <andreak@officenet.no>
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
------------------------+---------------------------------------------+
OfficeNet AS | The most difficult thing in the world is to |
Hoffsveien 17 | know how to do a thing and to watch |
PO. Box 425 Skøyen | somebody else doing it wrong, without |
0213 Oslo | comment. |
NORWAY | |
Phone : +47 22 13 01 00 | |
Direct: +47 22 13 10 03 | |
Mobile: +47 909 56 963 | |
------------------------+---------------------------------------------+

I don't understand why an user can't WILLINGLY (by EXPLICITLY setting an
OPTION) allow a privileged administrator to run PostGre.

It is a MAJOR problem for me, that will force me to use another database
because my database will be on a DVD and I'm not sure that on the PC on
which it will be executed, the user isn't an admin or that I can create an
unprivileged user. To resume, I don't want my user to be unable to run my
application for that.

The persons specifying this option would know perfectly well the risks
linked to it.

I'm starting to think the PostGre developpers think the users are children.

I'm deeply disappointed to be forced to compile my own PostGre and I will
not.

Eric LEGUILLIER

I explain myself about running PostGre as admin.

In fact I don't want specifically run PostGre as admin. The problem

is, on

the computers the application including PostGre will run,

I'm not sure

that the user won't have any admin or power user rights.

Furthermore,

I've noticed that on certain domains, any user created is
automatically

added

to
a default group having power user rights (that is actually happening

to

me).

To be honest, the fact that Postgres forces you to run as a
non-admin user has given me nothing but headaches. (yes, I
know, the problem is defaulting everyone to admin rights is
the problem. But that's where I am). I have been kicking
around the idea of posting a change to allow you to run as
admin,

This has been proposed before, and always rejected. While you're always
welcome to provide a patch, I'm very doubtful it would be accepted into
the main product.

//Magnus

On Tue, Nov 15, 2005 at 04:01:24PM +0100, Andreas Joseph Krogh wrote:

The example given in this thread certainly isn't going to change
anybody's mind. "Hi, I propose reducing everybody's security because
my local admins insist on an utterly brain-dead security policy."

Tom, nobody wants to reduce everybody's security, and nobody is proposing
changes leading to such. I just believe more than me agree that having this
as an option on Windows wouldn't hurt anybody, but would rather make life
simpler for some Windows people. Anyway, I don't use Windows on a regular
basis, so it's not that important to me...

So get the source code and change it and put it on a website for others
to use. What's missing is an argument that it should be supported by
the default installation...

This is free software, if you don't like something, change it. You just
can't require other people to go along with it.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

NO, it won't reduce everybody's security.

You obviously don't understand what I'm trying to say.

It would NOT be the default option. The user could just choose by
SPECIFYING it, that PostGre don't control the privileged he has.

This discussion is amazing. Without this option, I CANNOT use PostGre, and
I think I'm not the only one...

Eric LEGUILLIER
Projet BriqueBackup

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

To be honest, the fact that Postgres forces you to run as a
non-admin user has given me nothing but headaches. (yes, I
know, the problem is defaulting everyone to admin rights is
the problem. But that's where I am). I have been kicking
around the idea of posting a change to allow you to run as
admin,

This has been proposed before, and always rejected. While you're always
welcome to provide a patch, I'm very doubtful it would be accepted into
the main product.

The example given in this thread certainly isn't going to change
anybody's mind. "Hi, I propose reducing everybody's security because
my local admins insist on an utterly brain-dead security policy."

regards, tom lane

On 11/15/05, eric.leguillier@mpsa.com <eric.leguillier@mpsa.com> wrote:

I don't understand why an user can't WILLINGLY (by EXPLICITLY setting an
OPTION) allow a privileged administrator to run PostGre.
It is a MAJOR problem for me, that will force me to use another database
because my database will be on a DVD and I'm not sure that on the PC on
which it will be executed, the user isn't an admin or that I can create an
unprivileged user. To resume, I don't want my user to be unable to run my
application for that.
The persons specifying this option would know perfectly well the risks
linked to it.
I'm starting to think the PostGre developpers think the users are children.
I'm deeply disappointed to be forced to compile my own PostGre and I will
not.

You can do it.
Modify the source, it's a one line change. Be grateful that you have
this privilege that you would lack with a proprietary database.

Running as an administrator isn't a matter of taste, it's
fundamentally broken from a security perspective. Just as you are
(usually) asked to jump through hoops to break the normal promises
that the database provide, you will be asked to do so on this one.

If you are unable to make a one line change to the source and rebuild
the application then you probably are unable to understand the
security implications of your decision. I wouldn't call this treating
you like a child, I'd call this expecting you to be an adult.

Well, first, you ought to learn the name of the product. It's Postgres
or PostgreSQL, but not PostGre.

I suspect that you will find other issues anyway in running from a
datadir on a read-only medium. I suggest you see if you can do it
regardless of this issue. If not, then some other product might suit you
better anyway (I believe Firebird has specific support for this, for
example.) We have never pretended that Postgres is a perfect fit for
every situation.

Finally, learn to chill a little. Getting angry doesn't help you or
anyone else.

cheers

andrew

eric.leguillier@mpsa.com wrote:

On Tue, 15 Nov 2005 eric.leguillier@mpsa.com wrote:

I don't understand why an user can't WILLINGLY (by EXPLICITLY setting an
OPTION) allow a privileged administrator to run PostGre.

Well, to start with, it increases the support costs of the product as a
whole to the community. Adding an option with severe security implications
is not free, at least not if you want to be reasonably diligent about
minimizing and documenting the risks. Generally the community tries to
take that seriously, so IMHO just assuming that anyone who sets it knows
the risks isn't acceptable.

Why don't we actually start looking at the actual implications and see
what we can do about them, rather than either assuming they're too great
or too minimal. Maybe we'll come up with solutions to current problems as
well.

I'm deeply disappointed to be forced to compile my own PostGre and I will
not.

Well, given that such an option isn't likely to go in before 8.2 given the
policy on dot version changes, I don't think you can get out of compiling
a copy unless you have a year before shipping.

Andrew, I'm getting a bit angry (and I'm sorry for that) because I think
the performances of Postgres are better than Firebird and I'm frustrated to
have to compile it whereas it would be simpler for everybody to have an
option.

It seem to be impossible though, I will use Firebird.

Thanks for your patience.

Eric LEGUILLIER

Well, first, you ought to learn the name of the product. It's Postgres
or PostgreSQL, but not PostGre.

I suspect that you will find other issues anyway in running from a
datadir on a read-only medium. I suggest you see if you can do it
regardless of this issue. If not, then some other product might suit you
better anyway (I believe Firebird has specific support for this, for
example.) We have never pretended that Postgres is a perfect fit for
every situation.

Finally, learn to chill a little. Getting angry doesn't help you or
anyone else.

cheers

andrew

eric.leguillier@mpsa.com wrote:

-----Original Message-----
From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of
eric.leguillier@mpsa.com
Sent: 15 November 2005 15:15
To: Magnus Hagander
Cc: pgsql-hackers@postgresql.org
Subject: Réf. : RE: [HACKERS] Running PostGre on DVD

I don't understand why an user can't WILLINGLY (by EXPLICITLY
setting an
OPTION) allow a privileged administrator to run PostGre.

Dunno about PostGre :-), but on PostgreSQL (otherwise known as Postgres) because we chose not to allow people the same footgun that could easily have clobbered users of another well known database not long ago.

Regards, Dave.

On Tue, Nov 15, 2005 at 08:10:40AM -0800, Stephan Szabo wrote:

On Tue, 15 Nov 2005 eric.leguillier@mpsa.com wrote:

I don't understand why an user can't WILLINGLY (by EXPLICITLY setting an
OPTION) allow a privileged administrator to run PostGre.

Well, to start with, it increases the support costs of the product as a
whole to the community. Adding an option with severe security implications
is not free, at least not if you want to be reasonably diligent about
minimizing and documenting the risks. Generally the community tries to
take that seriously, so IMHO just assuming that anyone who sets it knows
the risks isn't acceptable.

Why don't we actually start looking at the actual implications and see
what we can do about them, rather than either assuming they're too great
or too minimal. Maybe we'll come up with solutions to current problems as
well.

To expand on that, someone has suggested the use of runas, so it would
be good to see how that works.

The problem here isn't that PostgreSQL refuses to run with admin
privledges, it's that the Windows security model is brain-dead. IF it
can be shown that there is no reasonable way around Windows 'security'
and IF there is enough demand from users then the community might
consider a hack that allows running PostgreSQL from an admin account.
But as it stands right now, neither of those has been shown.

So as Stephan suggested, let's try looking at the root problem and see
if there's some way to fix that.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

On Tue, Nov 15, 2005 at 09:56:03AM -0500, Pollard, Mike wrote:

a little painful to get to it. But at least by putting it into contrib,
it may be useful to someone. Especially if the idea is to put a sample

Keep in mind that compiling something on windows is extremely painful
for most people. Unlike unix, the vast majority of windows users don't
have a compiler laying around.

Also, in my case, I'm running the debugger and profiler against Postgres
on my Windows machine. I find it much easier to throw out the admin
restriction, so I can just use my own account. I agree that my default
account should not have had full admin rights, but that is the way the
machine came. And yes, I should have immediately created a new user and
set myself up on that one. But come on, my old laptop was so old, and I
was so excited... sorry, TMI.

Well, a bigger issue is that windows makes things a lot more difficult
to do if you don't have admin on your account. Yes, there is runas, but
windows doesn't exactly foster people working from the command line. And
IIRC runas isn't nearly as nice to use as sudo.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

I don't understand why an user can't WILLINGLY (by EXPLICITLY
setting an
OPTION) allow a privileged administrator to run PostGre.

Well, to start with, it increases the support costs of the

product as

a whole to the community. Adding an option with severe security
implications is not free, at least not if you want to be reasonably
diligent about minimizing and documenting the risks. Generally the
community tries to take that seriously, so IMHO just assuming that
anyone who sets it knows the risks isn't acceptable.

Why don't we actually start looking at the actual

implications and see

what we can do about them, rather than either assuming they're too
great or too minimal. Maybe we'll come up with solutions to current
problems as well.

To expand on that, someone has suggested the use of runas, so
it would be good to see how that works.

The problem here isn't that PostgreSQL refuses to run with
admin privledges, it's that the Windows security model is
brain-dead. IF it can be shown that there is no reasonable
way around Windows 'security'
and IF there is enough demand from users then the community
might consider a hack that allows running PostgreSQL from an
admin account.

There is *NOTHING* wrong with the model in this case. It's the specific
implementation of the mdoel that is broken.
If you assign every user uid "0" in Unix, I beleive you'd get the same
problem as when you assign every user an admin on windows... Both are
equally stupid. There's just more software on windows that is designed
for such stupid environments, but it's not in the security model itself.
If it was in the actual security model, we'd have to do something.

//Magnus

Well, a bigger issue is that windows makes things a lot more difficult
to do if you don't have admin on your account. Yes, there is runas, but
windows doesn't exactly foster people working from the command line. And
IIRC runas isn't nearly as nice to use as sudo.

Couldn't the installer create a handy dandy icon on the desktop with the
correct runas command to start/stop it for a given user or even have a
graphical pg_ctl type interface with Start, Stop and Restart buttons
that does the right thing behind the scenes?

On unix I get a startup script that hides the su and other logic and
safeties behind the scenes.
--

-----Original Message-----
From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Rod Taylor
Sent: 15 November 2005 16:40
To: Jim C. Nasby
Cc: Pollard, Mike; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Running PostGre on DVD

Well, a bigger issue is that windows makes things a lot

more difficult

to do if you don't have admin on your account. Yes, there

is runas, but

windows doesn't exactly foster people working from the

command line. And

IIRC runas isn't nearly as nice to use as sudo.

Couldn't the installer create a handy dandy icon on the
desktop with the
correct runas command to start/stop it for a given user or even have a
graphical pg_ctl type interface with Start, Stop and Restart buttons
that does the right thing behind the scenes?

We do. You can't run from the command line as an admin, but when
installed as a service you can start/stop it etc. as an admin, even
though the service actually runs under a low privilege account.

You can start/stop etc from the command line using 'net start', from the
services control panel applet, or using shortcuts we provide on the
start menu.

Regards, Dave

On Tue, Nov 15, 2005 at 05:33:38PM +0100, Magnus Hagander wrote:

There is *NOTHING* wrong with the model in this case. It's the specific
implementation of the mdoel that is broken.
If you assign every user uid "0" in Unix, I beleive you'd get the same
problem as when you assign every user an admin on windows... Both are
equally stupid. There's just more software on windows that is designed
for such stupid environments, but it's not in the security model itself.
If it was in the actual security model, we'd have to do something.

Actually, no. In UNIX is you are running as user 0, you can su to any
other user ID, even if they don't exist. You can set it up so you can
never go back, a trapdoor basically. Under linux you can even give up
all sorts of priveledges without changing your UID.

The difference with Windows appears to be that you can't willingly
restrict your own priveledges without creating another user and
switching to them.

For example, does the windows model allow you to say (without creating
a new user): I irrevocably restrict my access to files owned by user X
for this process *only*. Or to files under subdirectory Y. Or I
irrevocably restrict my access to open new network sockets. Or
irrevocably restrict my access to create new users.

If this is possible then a patch might be accepted that would allow you
to run as "admin" but only after giving up all the rights that aren't
actually needed.

If you can't do this, I'd call the model flawed.

Have a ncie day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

On Tue, Nov 15, 2005 at 11:39:37AM -0500, Rod Taylor wrote:

Well, a bigger issue is that windows makes things a lot more difficult
to do if you don't have admin on your account. Yes, there is runas, but
windows doesn't exactly foster people working from the command line. And
IIRC runas isn't nearly as nice to use as sudo.

Couldn't the installer create a handy dandy icon on the desktop with the
correct runas command to start/stop it for a given user or even have a
graphical pg_ctl type interface with Start, Stop and Restart buttons
that does the right thing behind the scenes?

On unix I get a startup script that hides the su and other logic and
safeties behind the scenes.

Well, I think the normal windows installer goes and installs PostgreSQL
as a service, which eliminates all these problems; but that doesn't help
for the case of trying to run a demo.

BTW, my point was that the reason many windows users run with admin
rights is because windows doesn't provide a viable alternative (unlike
OS X).
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

On Tue, Nov 15, 2005 at 10:58:31AM -0600, Jim C. Nasby wrote:

BTW, my point was that the reason many windows users run with admin
rights is because windows doesn't provide a viable alternative (unlike
OS X).

Err, sorry, hit send too soon. My point about OS X isn't meant to start
a flame war, only to point out that there are ways to make this work in
a GUI environment. Maybe in the future Windows will pick one of those
ways up.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

There is *NOTHING* wrong with the model in this case. It's the
specific implementation of the mdoel that is broken.
If you assign every user uid "0" in Unix, I beleive you'd

get the same

problem as when you assign every user an admin on

windows... Both are

equally stupid. There's just more software on windows that

is designed

for such stupid environments, but it's not in the security

model itself.

If it was in the actual security model, we'd have to do something.

Actually, no. In UNIX is you are running as user 0, you can
su to any other user ID, even if they don't exist. You can
set it up so you can never go back, a trapdoor basically.

Ok. Didn't know that part about nonexistant ids.
As for su, you can su to a different user on windows as well. Either
using runas, or by replacing your process token. The second way requires
a specific user right to do it (which for example Local System always
has, which is why procucts like IIS can do it all the time), runas
doesn't.

Under linux you can even give up all sorts of priveledges
without changing your UID.

You can remove stuff from your token in Windows as well. Don't know many
that do, but you can (again, I *think* IIS is an example of this, but
I'm not sure).

The difference with Windows appears to be that you can't
willingly restrict your own priveledges without creating
another user and switching to them.

You can, but it may be a bit harder than in *nix. It's just a whole lot
easier to switch to another user.

For example, does the windows model allow you to say (without
creating a new user): I irrevocably restrict my access to
files owned by user X for this process *only*. Or to files
under subdirectory Y. Or I irrevocably restrict my access to
open new network sockets. Or irrevocably restrict my access
to create new users.

Not entirely sure. You can get rid of privileges, and you can get rid of
group memberships. Don't think you can do it for a specific file,
because that's driven by the ACL on the file and not on the token. (You
can get rid of the group that had permissions on it which would give you
the same effect, but if someone granted your account direct permissions
on it, you'd still be able to access it).

If this is possible then a patch might be accepted that would
allow you to run as "admin" but only after giving up all the
rights that aren't actually needed.

Hmm. I guess we could try the approach of dropping groups in pg_ctl
before we even call postmaster... Should be doable, if someone wants to
do the lifting. Tha way we could keep the admin check in the postmaster,
because we'd get rid of admin before we got there...

//Magnus

On Tue, Nov 15, 2005 at 08:43:06PM +0100, Magnus Hagander wrote:

Ok. Didn't know that part about nonexistant ids.

Usernames are implementation details, if you ask to become user 38587,
the kernel doesn't check whether they exist. You just might not be able
to open any files anymore :)

For example, does the windows model allow you to say (without
creating a new user): I irrevocably restrict my access to
files owned by user X for this process *only*. Or to files
under subdirectory Y. Or I irrevocably restrict my access to
open new network sockets. Or irrevocably restrict my access
to create new users.

Not entirely sure. You can get rid of privileges, and you can get rid of
group memberships. Don't think you can do it for a specific file,
because that's driven by the ACL on the file and not on the token. (You
can get rid of the group that had permissions on it which would give you
the same effect, but if someone granted your account direct permissions
on it, you'd still be able to access it).

Ah, now we are making progress. If there was a way to give up file
access permissions so you could no longer write files to, say, the
Windows System directory, this would go a long way to solving the
issue. Currently, if the Postmaster runs as admin, anyone with access
to the database could use COPY to read and write any file the backend
can access.

Hmm. I guess we could try the approach of dropping groups in pg_ctl
before we even call postmaster... Should be doable, if someone wants to
do the lifting. Tha way we could keep the admin check in the postmaster,
because we'd get rid of admin before we got there...

Actually, it could possibly be acceptable to do it inside the
postmaster itself. It doesn't really matter where it's done, as long as
it permanently restricts the access of the postmaster from then on.

Quickly looking, I found a site [1]http://www.winterdom.com/dev/security/tokens.html that refers to OpenProcessToken()
and AdjustTokenPrivileges() which appears to allow you to drop
permissions you have. There is also something called
CreateRestrictedToken() which can then to passed to
CreateProcessAsUser().

Maybe one of the Win32 hackers want to look into this to see what can
be done.

[1]: http://www.winterdom.com/dev/security/tokens.html

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

For example, does the windows model allow you to say (without
creating a new user): I irrevocably restrict my access to files
owned by user X for this process *only*. Or to files under
subdirectory Y. Or I irrevocably restrict my access to open new
network sockets. Or irrevocably restrict my access to create new
users.

Not entirely sure. You can get rid of privileges, and you

can get rid

of group memberships. Don't think you can do it for a

specific file,

because that's driven by the ACL on the file and not on the token.
(You can get rid of the group that had permissions on it

which would

give you the same effect, but if someone granted your

account direct

permissions on it, you'd still be able to access it).

Ah, now we are making progress. If there was a way to give up
file access permissions so you could no longer write files
to, say, the Windows System directory, this would go a long
way to solving the issue. Currently, if the Postmaster runs
as admin, anyone with access to the database could use COPY
to read and write any file the backend can access.

Getting rid of the admin and powerusers group should do that, I think.
But you can still get caught in a nested group scenario.
You could drop *all* groups except "Users", which will take care of all
scenarios except when the admin has configured the machine so that
"Users" is a member of "Administrators". Not sure if there is anything
we can do about that.

But I'm not sure that's what we really care aobut. If we want to solve
the "able to run under the normal user that's an admin", we don't. If we
want to solve "deal with completely broken policy setups", we do.

Hmm. I guess we could try the approach of dropping groups in pg_ctl
before we even call postmaster... Should be doable, if

someone wants

to do the lifting. Tha way we could keep the admin check in the
postmaster, because we'd get rid of admin before we got there...

Actually, it could possibly be acceptable to do it inside the
postmaster itself. It doesn't really matter where it's done,
as long as it permanently restricts the access of the
postmaster from then on.

Right. but it's easier if you can create a new token for a new process.

Quickly looking, I found a site [1] that refers to
OpenProcessToken() and AdjustTokenPrivileges() which appears
to allow you to drop permissions you have. There is also
something called
CreateRestrictedToken() which can then to passed to
CreateProcessAsUser().

CreateRestrictedToken() is most likely the one you want. It's well
documented on msdn.microsoft.com. Point with that one is that it doesn't
require any privileges in order to drop privileges :)

//Magnus

On Tue, Nov 15, 2005 at 10:15:01PM +0100, Magnus Hagander wrote:

Ah, now we are making progress. If there was a way to give up
file access permissions so you could no longer write files
to, say, the Windows System directory, this would go a long
way to solving the issue. Currently, if the Postmaster runs
as admin, anyone with access to the database could use COPY
to read and write any file the backend can access.

Getting rid of the admin and powerusers group should do that, I think.

Look at pgwin32_is_admin(), it just checks if the user is member of one
of those two groups. I think we have your solution right here...

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Ah, now we are making progress. If there was a way to

give up file

access permissions so you could no longer write files to,

say, the

Windows System directory, this would go a long way to solving the
issue. Currently, if the Postmaster runs as admin, anyone with
access to the database could use COPY to read and write

any file the

backend can access.

Getting rid of the admin and powerusers group should do

that, I think.

Look at pgwin32_is_admin(), it just checks if the user is
member of one of those two groups. I think we have your
solution right here...

Oh, I know - I wrote it :-)

You still lose in the nested group scenario.

And whlie a privilege like backup/restore can be used to overwrite any
file on the system, you must be able to execute arbitrary API calls to
do that. Whereas with admin/powerusers you can just use COPY or
whatever.

Bottom line is that pgwin32_is_admin() is far from perfect, it just
catches the most common scenarios.

//Magnus

On Tue, Nov 15, 2005 at 10:29:34PM +0100, Magnus Hagander wrote:

You still lose in the nested group scenario.

And whlie a privilege like backup/restore can be used to overwrite any
file on the system, you must be able to execute arbitrary API calls to
do that. Whereas with admin/powerusers you can just use COPY or
whatever.

Well, like you said, what's the problem we're trying to solve. It seems
to me that Windows doesn't have a clearly defined concept of
"superuser" and hence it can't be tested for. Having seperate API
points to access files that require different priviledges just makes it
more complicated.

There isn't a simple way to say, limit my access to this directory tree
(like chroot())? Surely as admin you could create tokens and add them
to the PGDATA directory and then allow only access to directories with
that token. Since PostgreSQL doesn't actually require priviledges,
couldn't you just enumerate and drop them all?

Maybe allow a cripple mode where you refuse to load objects or allow
COPY. Or run any functions in untrusted languages. Still seems to me to
be a failure in the OS that you can't just say "drop everything except
this".

Bottom line is that pgwin32_is_admin() is far from perfect, it just
catches the most common scenarios.

My method would be: try to open file for writing in the Windows System
directory. If that works you've got too much priviledge...

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

We were initially logging out of the Windows GUI environment and back in
again to do the Windows builds. Discovering runas made the whole
process MUCH less painful. So far I haven't needed to use any advanced
features of sudo or runas; in my view either is easy to use for the
common
cases. I'll admit it gets a little messy getting into the msys/mingw
environment as another user. I gave an example of how we used it this
way recently:

http://archives.postgresql.org/pgsql-hackers/2005-11/msg00750.php

This wouldn't help with the "run from DVD" situation without having
a user to runas.

-Kevin

"Jim C. Nasby" <jnasby@pervasive.com> >>>

Yes, there is runas, but
windows doesn't exactly foster people working from the command line. And
IIRC runas isn't nearly as nice to use as sudo.

NO, it won't reduce everybody's security.

You obviously don't understand what I'm trying to say.

It would NOT be the default option. The user could just choose by
SPECIFYING it, that PostGre don't control the privileged he has.

This discussion is amazing. Without this option, I CANNOT use PostGre, and
I think I'm not the only one...

Eric LEGUILLIER
Projet BriqueBackup

It's been fine for 15 years on Unix.

Chris