Installation procedure wishes

Dmitry Samersoff <dms@wplus.net> writes:

1. My standard way to install pgsql:
make
su
useradd postgres
****
mkdir ~postgres
make install
chown -R postgres ~postgres

Is it possible to include last three commands into installation procedure?

If you followed the installation instructions (ie, run "make install" as
the postgres user), you wouldn't need the chown step. The reason that
making the toplevel installation directory isn't part of what "make
install" does is that it's typically located somewhere that requires
root permission to make the directory --- but you only need to do that
once, it doesn't have to be done over each time you reinstall.

2. The most often PGDATA is ~postgres/data
and PGLIB is ~postgres/lib
Is it possible to use this as default if environment not set?

Not ~postgres necessarily, but whatever the --prefix set by configure
is. I kinda thought these defaults were compiled in already? If not,
they probably should be.

3. Next step is adding plpgsql into database template1 (or patching creatdb
script) to add plpgsql every time as I create new db

That's a one-command thing now, so I'm not seeing why it's harder to issue
the command than type a configure option ...

regards, tom lane

3. Next step is adding plpgsql into database template1 (or patching creatdb
script) to add plpgsql every time as I create new db

That's a one-command thing now, so I'm not seeing why it's harder to issue
the command than type a configure option ...

Initially I thought it would be nice to offer those
procedural languages that can be TRUSTED ones to regular
users by default. So I first added the appropriate commands
to initdb. Some complained and I moved them out again and
added the new commands (createlang and destroylang) instead.

And I agree - I was wrong. It's bad practice to install
things by default that some don't need. A database system has
some defined priorities:

1. Reliability

2. Reliability

3. Security

4. Reliability

5. Security

n. Capabilities, performance and other non-critical items.

Adding types/operators/procedural-languages by default to any
created database is easy. Add them to the template1 database.
If you forgot it during an upgrade or restore, be sure some
user will tell you soon.

But if you have choosen the conservative way of beeing a site
admin, noone will ever tell you if you forgot to DISABLE a
feature after a 50 hour restore marathon.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck@debis.com (Jan Wieck) #

But if you have choosen the conservative way of beeing a site
admin, noone will ever tell you if you forgot to DISABLE a
feature after a 50 hour restore marathon.

Yes, the same reason postmaster -i flag is required to enable tcp/ip.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

On 17-Jun-99 Tom Lane wrote:

Dmitry Samersoff <dms@wplus.net> writes:

1. My standard way to install pgsql:
make
su
useradd postgres
****
mkdir ~postgres
make install
chown -R postgres ~postgres

Is it possible to include last three commands into installation procedure?

I wrote this letter because last month I had to install/upgrade
postgress over 20 times. All this questions is not significant for
single instalation, but prove a task if I need installing postgres
every day.

If you followed the installation instructions (ie, run "make install" as
the postgres user), you wouldn't need the chown step. The reason that
making the toplevel installation directory isn't part of what "make
install" does is that it's typically located somewhere that requires
root permission to make the directory --- but you only need to do that

^^^^^^^^^^^^^^^
Yes! IMHO, If root privilege required (in most case) at least once,
it would be nice to make top-level directory and all other
by install.

(It's step back to discussion fired some monthes ago)

once, it doesn't have to be done over each time you reinstall.

2. The most often PGDATA is ~postgres/data
and PGLIB is ~postgres/lib
Is it possible to use this as default if environment not set?

Not ~postgres necessarily, but whatever the --prefix set by configure
is. I kinda thought these defaults were compiled in already? If not,
they probably should be.

AFAIK, It's not compiled already in current 6.5.
Other side, using home directory of user "postgres" provide a simple and
standard way to control postgres data and lib locations.

3. Next step is adding plpgsql into database template1 (or patching creatdb
script) to add plpgsql every time as I create new db

That's a one-command thing now, so I'm not seeing why it's harder to issue
the command than type a configure option ...

I still need remember to run this command for template1 or do it every time
when I creating db.

Configure options allow me to add it to internal-used build script
and don\'t keep it in mind.

---
Dmitry Samersoff, dms@wplus.net, ICQ:3161705
http://devnull.wplus.net
* There will come soft rains ...

Bruce Momjian wrote:

But if you have choosen the conservative way of beeing a site
admin, noone will ever tell you if you forgot to DISABLE a
feature after a 50 hour restore marathon.

Yes, the same reason postmaster -i flag is required to enable tcp/ip.

That's a detail I'm in doubt about. Our defaults for AF_UNIX
sockets is trust (and AFAIK must be because identd cannot
handle them). Thus any user who has a local shell account
could easily become db user postgres.

I think a default of host-localhost-ident-sameuser and giving
superusers the builtin right to become everyone would gain
higher security.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck@debis.com (Jan Wieck) #

Bruce Momjian wrote:

But if you have choosen the conservative way of beeing a site
admin, noone will ever tell you if you forgot to DISABLE a
feature after a 50 hour restore marathon.

Yes, the same reason postmaster -i flag is required to enable tcp/ip.

That's a detail I'm in doubt about. Our defaults for AF_UNIX
sockets is trust (and AFAIK must be because identd cannot
handle them). Thus any user who has a local shell account
could easily become db user postgres.

I think a default of host-localhost-ident-sameuser and giving
superusers the builtin right to become everyone would gain
higher security.

But can we assume ident is running. I don't think so.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

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

That's a detail I'm in doubt about. Our defaults for AF_UNIX
sockets is trust (and AFAIK must be because identd cannot
handle them). Thus any user who has a local shell account
could easily become db user postgres.

I think a default of host-localhost-ident-sameuser and giving
superusers the builtin right to become everyone would gain
higher security.

But can we assume ident is running. I don't think so.

No, we cannot make the default installation dependent on any nonstandard
software. Jan's right, though: the default setup is not secure against
local attackers.

Perhaps we ought to make the default setup use password protection?
That would at least force people to take extra steps to open themselves
to easy attack.

There is still the issue of allowing the superuser to become everyone.
Right now, a pg_dump -z script is extremely painful to run if the
protection setup requires passwords (I am not sure it even works, but
certainly having to enter a password at each ownership swap would be
no fun). It wouldn't work at all under ident authorization. I think
we need some sort of "real vs effective userid" scheme to allow a
superuser-started session to switch to any userid without requiring a
password. (Maybe that's the same thing Jan has in mind.)

Also, it's pointless to pretend we have much security against local
attackers as long as the socket file is being created in /tmp.
On a system that doesn't have "sticky bits" for directories, a local
attacker could substitute his own socket file and then spoof the
protocol to steal legitimate users' passwords... I recall we discussed
moving the socket location to a directory only writable by postgres,
but didn't get around to doing anything about it.

To run a really secure server on a machine where you didn't trust all
the local users, without the annoyance of passwords, you'd need to set
up host-localhost-ident-sameuser *and* disable access through the
AF_UNIX socket. Is that possible now? (I guess you could configure
host localhost reject ...)

regards, tom lane