Thread: replacing Access/ Approach etc

Hi, a friend of mine on Windows, is attempting to convert to using
PostgreSQL (and of course, I'm helping him).

The installation gave an option to run as an application, rather than as
a service.

Turns out, my friends login account has Admin privs, and postgres.exe
will not run in an account with admin privs.

He wants to keep the "lightweight feel" and frankly I'd like that on my
Ubuntu box as well - to just fire up a local instance of postgresql
pointing at a particular "data" directory, and listing (on loopback/
localhost only) on an instance-specific port (point the exe at a local
.conf file).

Why is this not so intuitive/ easy to set up?

Is it useful goal to consider running multiple instances of pg, ala
microsoft access, lotus approach, etc?

Install as service has a "feel" of heavywieght. I (and my friend) want
to have per-project local data directories, with all db meta data etc
all local to that directory and project. This way, a simple backup of
the entire project can be made (pg data, documentation, web site files,
etc, etc). Does this make sense?

Is there a way to achieve this, on windows?

Is there a way to achieve this, on gnu/linux?

Thanks in advance
Zenaan

--
Homepage: www.SoulSound.net -- Free Australia: www.UPMART.org
Please respect the confidentiality of this email as sensibly warranted.

Zenaan Harkness wrote:
> Hi, a friend of mine on Windows, is attempting to convert to using
> PostgreSQL (and of course, I'm helping him).
>
> The installation gave an option to run as an application, rather than as
> a service.
>
> Turns out, my friends login account has Admin privs, and postgres.exe
> will not run in an account with admin privs.

Correct. Won't run as root on *nix either.

> He wants to keep the "lightweight feel" and frankly I'd like that on my
> Ubuntu box as well - to just fire up a local instance of postgresql
> pointing at a particular "data" directory, and listing (on loopback/
> localhost only) on an instance-specific port (point the exe at a local
> .conf file).
>
> Why is this not so intuitive/ easy to set up?

Apart from the windows-admin issue, what problems have you had?

> Is it useful goal to consider running multiple instances of pg, ala
> microsoft access, lotus approach, etc?

Most of the developers do. I do too. Usually different versions in
different directories and on different ports. Nothing to stop you having
multiple copies of the same version though.

Since ubuntu is debian-based, apt should help you do all this. If you've
got postgresql-common installed try "man pg_wrapper" as a start point.

> Install as service has a "feel" of heavywieght. I (and my friend) want
> to have per-project local data directories, with all db meta data etc
> all local to that directory and project. This way, a simple backup of
> the entire project can be made (pg data, documentation, web site files,
> etc, etc). Does this make sense?

PostgreSQL *is* heavier than MS-Access. What you want to do should work
fine though. Remember to stop PG before taking your backups though.

> Is there a way to achieve this, on windows?
> Is there a way to achieve this, on gnu/linux?

What problems have you had?

--
   Richard Huxton
   Archonet Ltd

> ------- Original Message -------
> From: Zenaan Harkness <zen@freedbms.net>
> To: pgsql-general@postgresql.org
> Sent: 08/09/07, 06:03:54
> Subject: [GENERAL] replacing Access/ Approach etc
>
> Is there a way to achieve this, on windows?

Yes, 8.2 will start under a admin
account just fine.

> Is there a way to achieve this, on gnu/linux?

Run under any account other than root. How the db is configured to start at boot is distro-dependent though so you'll
needto figure that out and disable it to only run your own instsnce. 

Regards, Dave

Zenaan Harkness wrote on 08.09.2007 07:03:
> Hi, a friend of mine on Windows, is attempting to convert to using
> PostgreSQL (and of course, I'm helping him).
>
> The installation gave an option to run as an application, rather than as
> a service.
>
> Turns out, my friends login account has Admin privs, and postgres.exe
> will not run in an account with admin privs.
>
> He wants to keep the "lightweight feel" and frankly I'd like that on my
> Ubuntu box as well - to just fire up a local instance of postgresql
> pointing at a particular "data" directory, and listing (on loopback/
> localhost only) on an instance-specific port (point the exe at a local
> .conf file).

When I started using Postgres for more than just testing, I had the same
feeling, but frankly the overhead of starting a PostgreSQL service is so small,
that I now happily auto-start the service at boot time (Windows XP).
You won't even notice that it's running (from a performance point of view).

> Is it useful goal to consider running multiple instances of pg, ala
> microsoft access, lotus approach, etc?

So my recommendation would be: do install it as a service (any OS), and create
multiple databases. Once done that you don't need to worry about starting or
stopping the thing. It's simply available. I'd think that starting Access has
more overhead than having a PG server sitting in the background (doing nothing)

Thomas

On Sat, Sep 08, 2007 at 08:26:04AM +0100, Richard Huxton wrote:
> Zenaan Harkness wrote:
> >Hi, a friend of mine on Windows, is attempting to convert to using
> >PostgreSQL (and of course, I'm helping him).
> >
> >The installation gave an option to run as an application, rather than as
> >a service.
> >
> >Turns out, my friends login account has Admin privs, and postgres.exe
> >will not run in an account with admin privs.
>
> Correct. Won't run as root on *nix either.
>
> >He wants to keep the "lightweight feel" and frankly I'd like that on my
> >Ubuntu box as well - to just fire up a local instance of postgresql
> >pointing at a particular "data" directory, and listing (on loopback/
> >localhost only) on an instance-specific port (point the exe at a local
> >.conf file).
> >
> >Why is this not so intuitive/ easy to set up?
>
> Apart from the windows-admin issue, what problems have you had?

He has many applications, lots of files (thousands - 10 years of work!),
and many customizations, on his primary account, which is an admin
account.

Is the only option to have to swap between users in order to run
postgresql as an application on windows?

If so, can this all be scripted in a batch file, ie. swapping user then
running postgres.exe?

I think I had a problem of not being able to get postgres.exe to load
the .conf file which I was editing, even with a command line parameter.
Unfortunately I don't have a windows box here and my friend's now in
another city. I'll have to get VMWare or something happening I guess...
the problem may simply have been getting postgres to run in his account,
the "Administrator" account ...

> >Is it useful goal to consider running multiple instances of pg, ala
> >microsoft access, lotus approach, etc?
>
> Most of the developers do. I do too. Usually different versions in
> different directories and on different ports. Nothing to stop you having
> multiple copies of the same version though.
>
> Since ubuntu is debian-based, apt should help you do all this. If you've
> got postgresql-common installed try "man pg_wrapper" as a start point.

Beautiful! That's exactly what I needed.

> >Install as service has a "feel" of heavywieght. I (and my friend) want
> >to have per-project local data directories, with all db meta data etc
> >all local to that directory and project. This way, a simple backup of
> >the entire project can be made (pg data, documentation, web site files,
> >etc, etc). Does this make sense?
>
> PostgreSQL *is* heavier than MS-Access. What you want to do should work
> fine though. Remember to stop PG before taking your backups though.

Not fussed with it actually being heavier weight. It's lighter than DB2
default install, and if I can just get it to run as an application, all
will be well.

> >Is there a way to achieve this, on windows?
> >Is there a way to achieve this, on gnu/linux?
>
> What problems have you had?

Seems to consistently bomb out on Windows. I think this is just due to
admin privs.

I tried many and varied combinations, and even sequences, of command
line parameters. Turns out at least one of the parameters is position
dependent, or must be last or something...

Thanks again
Zen

--
Homepage: www.SoulSound.net -- Free Australia: www.UPMART.org
Please respect the confidentiality of this email as sensibly warranted.

On Sat, Sep 08, 2007 at 08:49:28AM +0100, Dave Page wrote:
> > From: Zenaan Harkness <zen@freedbms.net>
> > > >
      
> > Is there a way to achieve this, on windows?
  
>
  
> Yes, 8.2 will start under a admin
  
> account just fine.
  

  
Is there some special command line switch?
  

  
You see, I got an error about being not supposed to run it in an admin
  
account? How do I bypass the error?
  

  
TIA
  
Zen
  

--
Homepage: www.SoulSound.net -- Free Australia: www.UPMART.org
Please respect the confidentiality of this email as sensibly warranted.

On Sat, Sep 08, 2007 at 09:52:04AM +0200, Thomas Kellerer wrote:
> Zenaan Harkness wrote on 08.09.2007 07:03:
> >Hi, a friend of mine on Windows, is attempting to convert to using
> >PostgreSQL (and of course, I'm helping him).
> >
> >The installation gave an option to run as an application, rather than as
> >a service.
> >
> >Turns out, my friends login account has Admin privs, and postgres.exe
> >will not run in an account with admin privs.
> >
> >He wants to keep the "lightweight feel" and frankly I'd like that on my
> >Ubuntu box as well - to just fire up a local instance of postgresql
> >pointing at a particular "data" directory, and listing (on loopback/
> >localhost only) on an instance-specific port (point the exe at a local
> >.conf file).
>
> When I started using Postgres for more than just testing, I had the same
> feeling, but frankly the overhead of starting a PostgreSQL service is so
> small, that I now happily auto-start the service at boot time (Windows XP).
> You won't even notice that it's running (from a performance point of view).

Thanks. I'm confident of this.

There is a hurdle of mindset to overcome though. When DB2 only starts as
a service, and I can say "look here, postgres will start as an app, and
you can very simply target a specific instance at a specific directory",
this makes the 'sell' a lot easier.

My job as advocate, and administrator for new software installation, and
trainer for the new software, is all-up not as simple as I was hoping.
Of course, once I've solved the problem on windows once, forever and a
day it should be much easier thereafter.

The other psychological aspects are a sense of control (copy the
postgres startup batch file, change the destination 'data' directory to
the new location, give it a new port number and voila, new instance of
the application), as well as a sense of safety and simplicity ("this
instance relates to this directory, I don't have to go configuring stuff
inside the database for my new test instance location, so I won't be
clobbering data in the non-test location").

These psychological and control aspects should not be underestimated, I
say.

> >Is it useful goal to consider running multiple instances of pg, ala
> >microsoft access, lotus approach, etc?
>
> So my recommendation would be: do install it as a service (any OS), and
> create multiple databases. Once done that you don't need to worry about
> starting or stopping the thing. It's simply available. I'd think that
> starting Access has more overhead than having a PG server sitting in the
> background (doing nothing)

I don't doubt this. We're dealing with perception however. It is
satisfying to have absolute control, over location, port number, etc,
from a source (config/batch files) outside the database executable.
Especially when you want to quickly copy a whole development tree to run
some tests (new table layouts, a read-write database which testers can
use which is not the deployment database). The feel of control one gets
by being able to do all this with cp + a quick config file edit, is
empowering to the user.

A lack of a sense of control is simply one more barrier to potential
switchers. Make sense?

Thanks heaps
Zen

--
Homepage: www.SoulSound.net -- Free Australia: www.UPMART.org
Please respect the confidentiality of this email as sensibly warranted.

On 9/8/07, Zenaan Harkness <zen@freedbms.net> wrote:
> A lack of a sense of control is simply one more barrier to potential
> switchers. Make sense?

But by starting postgresql under an administrative account, the user
would, in effect, be giving up that control to PostgreSQL to simply do
as it pleased with the machine.

The real problem here is the common perception in Windows user's minds
that running as an admin account is acceptable.  It's not.  It's
dangerous.  If your friend was following the basic tenant of not
running as an admin all the time, there would be no problem.  He, and
many other windows users, have a bad habit to unlearn.  Making
PostgreSQL part of the problem is not the solution.

On Sat, Sep 08, 2007 at 09:45:21AM -0500, Scott Marlowe wrote:
> On 9/8/07, Zenaan Harkness <zen@freedbms.net> wrote:
> > A lack of a sense of control is simply one more barrier to potential
> > switchers. Make sense?
>
> But by starting postgresql under an administrative account, the user
> would, in effect, be giving up that control to PostgreSQL to simply do
> as it pleased with the machine.
>
> The real problem here is the common perception in Windows user's minds
> that running as an admin account is acceptable.  It's not.  It's
> dangerous.  If your friend was following the basic tenant of not
> running as an admin all the time, there would be no problem.  He, and
> many other windows users, have a bad habit to unlearn.  Making
> PostgreSQL part of the problem is not the solution.

I agree. And my recommendation for _next_ time he reinstalls his
windows/ sets up his machine, will be to arrange to live in a
non-privileged account. I don't know when that will be.

In the meantimem, is there any option to allow pg to run in a privileged
account on windows?

Thanks again for the feedback, it is appreciated,
Zen

I suppose you could set up a batch file to start Postgresql, create a short-cut to pg_ctl and then modify the
propertiesof the shortcut to run as under different credentials (or use runas with a batch file).  That'll require that
yourfriend enter the password for that account every time he starts Postgresql. 

Compared to that, I don't really understand follow your argument as to why installing Postgresql as a service and
stopping/startingit through the service control panel such a big deal. 

Regards,

Shelby Cain

----- Original Message ----
From: Zenaan Harkness <zen@freedbms.net>
To: pgsql-general@postgresql.org
Sent: Saturday, September 8, 2007 9:58:34 AM
Subject: Re: [GENERAL] replacing Access/ Approach etc

In the meantimem, is there any option to allow pg to run in a privileged
account on windows?

Thanks again for the feedback, it is appreciated,
Zen







____________________________________________________________________________________
Yahoo! oneSearch: Finally, mobile search
that gives answers, not web links.
http://mobile.yahoo.com/mobileweb/onesearch?refer=1ONXIC

On 9/8/07, Zenaan Harkness <zen@freedbms.net> wrote:
> On Sat, Sep 08, 2007 at 09:45:21AM -0500, Scott Marlowe wrote:
> > On 9/8/07, Zenaan Harkness <zen@freedbms.net> wrote:
> > > A lack of a sense of control is simply one more barrier to potential
> > > switchers. Make sense?
> >
> > But by starting postgresql under an administrative account, the user
> > would, in effect, be giving up that control to PostgreSQL to simply do
> > as it pleased with the machine.
> >
> > The real problem here is the common perception in Windows user's minds
> > that running as an admin account is acceptable.  It's not.  It's
> > dangerous.  If your friend was following the basic tenant of not
> > running as an admin all the time, there would be no problem.  He, and
> > many other windows users, have a bad habit to unlearn.  Making
> > PostgreSQL part of the problem is not the solution.
>
> I agree. And my recommendation for _next_ time he reinstalls his
> windows/ sets up his machine, will be to arrange to live in a
> non-privileged account. I don't know when that will be.
>
> In the meantimem, is there any option to allow pg to run in a privileged
> account on windows?

Your friend has the following choices in the matter.  He can stop
running as an administrator and then run postgresql from his account,
or he can install postgresql as a service, which really isn't a huge
inconvience / use of resources or he can hack the windows code to let
pgsql run in an unsupported and unsafe manner.

Everyone here is willing to help you figure out the first two.  The
last one no one wants to mess with because the next thing we'll here
is how postgresql doesn't care about security / is a back door to
windows.

"Scott Marlowe" <scott.marlowe@gmail.com> writes:
> On 9/8/07, Zenaan Harkness <zen@freedbms.net> wrote:
>> In the meantimem, is there any option to allow pg to run in a privileged
>> account on windows?

> Your friend has the following choices in the matter.  He can stop
> running as an administrator and then run postgresql from his account,
> or he can install postgresql as a service, which really isn't a huge
> inconvience / use of resources or he can hack the windows code to let
> pgsql run in an unsupported and unsafe manner.

I thought there was code in there since 8.2 to drop the admin privileges
at startup ... doesn't that solve the problem the way he wants?

            regards, tom lane

On 9/8/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Scott Marlowe" <scott.marlowe@gmail.com> writes:
> > On 9/8/07, Zenaan Harkness <zen@freedbms.net> wrote:
> >> In the meantimem, is there any option to allow pg to run in a privileged
> >> account on windows?
>
> > Your friend has the following choices in the matter.  He can stop
> > running as an administrator and then run postgresql from his account,
> > or he can install postgresql as a service, which really isn't a huge
> > inconvience / use of resources or he can hack the windows code to let
> > pgsql run in an unsupported and unsafe manner.
>
> I thought there was code in there since 8.2 to drop the admin privileges
> at startup ... doesn't that solve the problem the way he wants?

I think that code just lets you start it as an admin and then run it
under an unadmin account.

Hmmm.  that still might work for OP.

Shelby Cain wrote on 08.09.2007 20:57:
> Compared to that, I don't really understand follow your argument as to why
> installing Postgresql as a service and stopping/starting it through the
> service control panel such a big deal.

Or stopping/starting using a batch file (with "net start pgsql")

Thomas

Run it as a service. This is the best way.

--
___________________________________________
Andrew J. P. Maclean
Centre for Autonomous Systems
The Rose Street Building J04
The University of Sydney  2006  NSW
AUSTRALIA
Ph: +61 2 9351 3283
Fax: +61 2 9351 7474
URL: http://www.acfr.usyd.edu.au/
___________________________________________