Thread

TODO list updated

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-01-12T15:41:53Z

I have updated the TODO list to mark all the items that are completed
for 7.0.

Are there any additional ones?  Are there some names I have forgotten to
attribute to items?

Let me know.

-- 
  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

Re: [HACKERS] TODO list updated

Peter Eisentraut <peter_e@gmx.net> — 2000-01-12T23:29:45Z

On 2000-01-12, Bruce Momjian mentioned:

> I have updated the TODO list to mark all the items that are completed
> for 7.0.
> 

Wow, we're at 32% done!

> Are there any additional ones?  Are there some names I have forgotten to
> attribute to items?

* Better interface for adding to pg_group

It's de facto done.

* Make postgres user have a password by default

There's an initdb switch.

* User who can create databases can modify pg_database table

is on the hit list. I believe the reason this had to be allowed is
createdb() using an actual insert statement to do its thing, which it
won't do any longer once I get all my code together. Some please correct
me if I'm wrong, otherwise I'll yank that code. (Yes, there is code that
specifically _allows_ this.)

-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden

Re: [HACKERS] TODO list updated

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-01-12T23:50:09Z

[Charset ISO-8859-1 unsupported, filtering to ASCII...]
> On 2000-01-12, Bruce Momjian mentioned:
> 
> > I have updated the TODO list to mark all the items that are completed
> > for 7.0.
> > 
> 
> Wow, we're at 32% done!

Actually, there are tons of _done_ items on the list.  I mentioned only
the big undone ones.

> 
> > Are there any additional ones?  Are there some names I have forgotten to
> > attribute to items?
> 
> * Better interface for adding to pg_group
> 
> It's de facto done.

Great.

> 
> * Make postgres user have a password by default
> 
> There's an initdb switch.

OK, now we have to decide if we are going to require this be done as
part of initdb.  I am inclined to say the user _has_ to be _prompted_ in
a secure matter for the password as part of initdb.  Have a command-line
switch for the password is not secure, IMHO, though it is better than
nothing.

Let's get people's opinions on this, and we can mark it as done.

> 
> * User who can create databases can modify pg_database table
> 
> is on the hit list. I believe the reason this had to be allowed is
> createdb() using an actual insert statement to do its thing, which it
> won't do any longer once I get all my code together. Some please correct
> me if I'm wrong, otherwise I'll yank that code. (Yes, there is code that
> specifically _allows_ this.)

Great.  Also dropping a database required this too.

-- 
  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

Re: [HACKERS] TODO list updated

Marc G. Fournier <scrappy@hub.org> — 2000-01-13T01:02:38Z

On Wed, 12 Jan 2000, Bruce Momjian wrote:

> OK, now we have to decide if we are going to require this be done as
> part of initdb.  I am inclined to say the user _has_ to be _prompted_ in
> a secure matter for the password as part of initdb.  Have a command-line
> switch for the password is not secure, IMHO, though it is better than
> nothing.

If we do a 'CREATE USER <user> WITH PASSWORD <pass>', its no more secure
then using a command line switch for password ... 

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org

Re: [HACKERS] TODO list updated

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-01-13T01:12:54Z

> On Wed, 12 Jan 2000, Bruce Momjian wrote:
> 
> > OK, now we have to decide if we are going to require this be done as
> > part of initdb.  I am inclined to say the user _has_ to be _prompted_ in
> > a secure matter for the password as part of initdb.  Have a command-line
> > switch for the password is not secure, IMHO, though it is better than
> > nothing.
> 
> If we do a 'CREATE USER <user> WITH PASSWORD <pass>', its no more secure
> then using a command line switch for password ... 

Why is that?  ps shows command args, righ?


-- 
  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

RE: [HACKERS] TODO list updated

Hiroshi Inoue <inoue@tpf.co.jp> — 2000-01-13T01:16:15Z

> -----Original Message-----
> From: owner-pgsql-hackers@postgreSQL.org
> [mailto:owner-pgsql-hackers@postgreSQL.org]On Behalf Of Bruce Momjian

>
> I have updated the TODO list to mark all the items that are completed
> for 7.0.
>
> Are there any additional ones?  Are there some names I have forgotten to
> attribute to items?
>
> Let me know.
>

Hmmm,who solved ????
* -spinlock stuck problem when elog(FATAL) and elog(ERROR) inside bufmgr

And I have felt that the followings are almost same.
* Allow LIMIT ability on single-table queries that have no ORDER BY to use
  a matching index [limit]
* Improve LIMIT processing by using index to limit rows processed [limit]
* Have optimizer take LIMIT into account when considering index scans
[limit]

And Isn't it preferable to omit 'in ORDER BY' from
* Use indexes in ORDER BY for restrictive data sets, min(), max()
?

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

Re: [HACKERS] TODO list updated

Tom Lane <tgl@sss.pgh.pa.us> — 2000-01-13T01:26:54Z

The Hermit Hacker <scrappy@hub.org> writes:
> On Wed, 12 Jan 2000, Bruce Momjian wrote:
>> OK, now we have to decide if we are going to require this be done as
>> part of initdb.  I am inclined to say the user _has_ to be _prompted_ in
>> a secure matter for the password as part of initdb.  Have a command-line
>> switch for the password is not secure, IMHO, though it is better than
>> nothing.

> If we do a 'CREATE USER <user> WITH PASSWORD <pass>', its no more secure
> then using a command line switch for password ... 

Yes it is --- if you have a shell script that is invoked by
	initdb --password pgsqlPassword ...
then anyone else on the same machine who happens to be doing a "ps"
meanwhile will see your password.

Note that if initdb is a shell script, then it still has to be very
careful what it does with the password; put it in any command line
for a program invoked by the script, and the leak is back with you.
A C-program version of initdb would be a lot safer.  But in theory you
can pass the password to the backend without exposing it in any command
line (put it in a data file instead, say).

			regards, tom lane

Re: [HACKERS] TODO list updated

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-01-13T01:52:22Z

> Hmmm,who solved ????
> * -spinlock stuck problem when elog(FATAL) and elog(ERROR) inside bufmgr

I thought you or Tatsuo fixed that.  I will remove the mark.

> 
> And I have felt that the followings are almost same.
> * Allow LIMIT ability on single-table queries that have no ORDER BY to use
>   a matching index [limit]
> * Improve LIMIT processing by using index to limit rows processed [limit]
> * Have optimizer take LIMIT into account when considering index scans
> [limit]
> 
> And Isn't it preferable to omit 'in ORDER BY' from
> * Use indexes in ORDER BY for restrictive data sets, min(), max()
> ?

I have now made it two items:

	* Use indexes in ORDER BY for restrictive data sets              
	* Use indexes in ORDER BY for min(), max()

We currently do not use indexes to handle ORDER BY because it is slower,
but for queries returning only a few rows, we could use the index and
skip the ORDER BY.  Not sure if this is done yet, or if it is important.

-- 
  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

Re: [HACKERS] TODO list updated

Marc G. Fournier <scrappy@hub.org> — 2000-01-13T01:54:02Z

On Wed, 12 Jan 2000, Bruce Momjian wrote:

> > On Wed, 12 Jan 2000, Bruce Momjian wrote:
> > 
> > > OK, now we have to decide if we are going to require this be done as
> > > part of initdb.  I am inclined to say the user _has_ to be _prompted_ in
> > > a secure matter for the password as part of initdb.  Have a command-line
> > > switch for the password is not secure, IMHO, though it is better than
> > > nothing.
> > 
> > If we do a 'CREATE USER <user> WITH PASSWORD <pass>', its no more secure
> > then using a command line switch for password ... 
> 
> Why is that?  ps shows command args, righ?

Point.  You won me over :)

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org

RE: [HACKERS] TODO list updated

Hiroshi Inoue <inoue@tpf.co.jp> — 2000-01-13T02:19:00Z

> -----Original Message-----
> From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
>
> > Hmmm,who solved ????
> > * -spinlock stuck problem when elog(FATAL) and elog(ERROR) inside bufmgr
>
> I thought you or Tatsuo fixed that.  I will remove the mark.
>

I have had a fix for it for 3 months but not committed because I don't
know how WAL would change it.
OK I would commit it after some checking.

> >
> > And I have felt that the followings are almost same.
> > * Allow LIMIT ability on single-table queries that have no
> ORDER BY to use
> >   a matching index [limit]
> > * Improve LIMIT processing by using index to limit rows
> processed [limit]
> > * Have optimizer take LIMIT into account when considering index scans
> > [limit]
> >
> > And Isn't it preferable to omit 'in ORDER BY' from
> > * Use indexes in ORDER BY for restrictive data sets, min(), max()
> > ?
>
> I have now made it two items:
>
> 	* Use indexes in ORDER BY for restrictive data sets
> 	* Use indexes in ORDER BY for min(), max()
>
> We currently do not use indexes to handle ORDER BY because it is slower,
> but for queries returning only a few rows, we could use the index and
> skip the ORDER BY.  Not sure if this is done yet, or if it is important.
>

Tom has changed to take IndexScan into account even when no qual exists.
* -Allow optimizer to prefer plans that match ORDER BY(Tom)
Currently optimizer is too eager to use index scan. He is planning to take
limit into account AFAIK,  He has mentioned it many times and I have been
looking forward to his change.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

Re: [HACKERS] TODO list updated

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-01-13T02:34:15Z

[Charset iso-8859-1 unsupported, filtering to ASCII...]
> > -----Original Message-----
> > From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
> >
> > > Hmmm,who solved ????
> > > * -spinlock stuck problem when elog(FATAL) and elog(ERROR) inside bufmgr
> >
> > I thought you or Tatsuo fixed that.  I will remove the mark.
> >
> 
> I have had a fix for it for 3 months but not committed because I don't
> know how WAL would change it.
> OK I would commit it after some checking.

Ah, so my memory isn't that bad.  WAL is not going into 7.0, so it
should be fine.

> > We currently do not use indexes to handle ORDER BY because it is slower,
> > but for queries returning only a few rows, we could use the index and
> > skip the ORDER BY.  Not sure if this is done yet, or if it is important.
> >
> 
> Tom has changed to take IndexScan into account even when no qual exists.
> * -Allow optimizer to prefer plans that match ORDER BY(Tom)
> Currently optimizer is too eager to use index scan. He is planning to take
> limit into account AFAIK,  He has mentioned it many times and I have been
> looking forward to his change.

OK, TODO updated.

-- 
  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

Re: [HACKERS] TODO list updated

Tom Lane <tgl@sss.pgh.pa.us> — 2000-01-13T02:41:28Z

TODO item comments:

* -SELECT foo UNION SELECT foo is incorrectly simplified to SELECT foo

The above is NOT done.

* prevent primary key that exceeds max index columns [primary]

The above is done as of yesterday.

* Fix memory leak for expressions[memory](Tom?) 

This isn't going to happen for 7.0, looks like :-(

* -Allow compression of large fields or a compressed field type

This has to be marked not-done again, unless Jan manages to squeeze
it back in via the toaster before Feb.

* Pull requested data directly from indexes, bypassing heap data

I doubt this is ever going to happen --- to make it possible, we'd
have to store tuple-commit status in index entries as well as in the
tuples themselves.  That would be a substantial space and speed penalty;
is the potential gain really worth it?

* -Convert function(constant) into a constant for index use(Tom)

Bernard Frankpitt should get the bulk of the credit for that one, not me.

* Allow LIMIT ability on single-table queries that have no ORDER BY to use
  a matching index [limit]
* Improve LIMIT processing by using index to limit rows processed [limit]
* Have optimizer take LIMIT into account when considering index scans [limit]

I agree with Hiroshi that these entries are redundant.

* -Make index creation use psort code, because it is now faster(Vadim)

I did that, not Vadim.

* -elog() flushes cache, try invalidating just entries from current xact,
  perhaps using invalidation cache

I don't think this is done?

* -Process const = const parts of OR clause in separate pass(Tom)

Again, mostly Frankpitt.


Some other things I did that aren't mentioned in TODO, but perhaps
deserve to be shown as 7.0 fixes:

* Interlock to prevent DROP DATABASE on a database with running backends

* Buffer reference counting bugfixes

* Fix libpq bug that causes it to drop backend error message sent
  just before connection closure (ie, any FATAL error message :-().

			regards, tom lane

Re: [HACKERS] TODO list updated

Tom Lane <tgl@sss.pgh.pa.us> — 2000-01-13T02:55:13Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> We currently do not use indexes to handle ORDER BY because it is slower,

Er, actually, we *do* use indexes for ORDER BY currently:

regression=# explain select * from tenk1 order by unique1;
NOTICE:  QUERY PLAN:
Index Scan using tenk1_unique1 on tenk1  (cost=760.00 rows=10000 width=148)

If you start psql with PGOPTIONS="-fi" you can see that the optimizer
believes an explicit sort would be much slower:

regression=# explain select * from tenk1 order by unique1;
NOTICE:  QUERY PLAN:
Sort  (cost=3233.91 rows=10000 width=148)
  ->  Seq Scan on tenk1  (cost=563.00 rows=10000 width=148)

but (at least on my machine) the explicit sort is marginally faster.
Evidently, the cost estimate for an explicit sort is *way* too high.

I have been poking at this and am currently thinking that the CPU-vs-
disk scaling constants (_cpu_page_weight_ and cpu_index_page_weight_)
may be drastically off for modern hardware.  This is one of the
optimizer issues that I'm hoping to resolve for 7.0.

			regards, tom lane

Re: [HACKERS] TODO list updated

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-01-13T03:01:20Z

> TODO item comments:
> 
> * -SELECT foo UNION SELECT foo is incorrectly simplified to SELECT foo
> 
> The above is NOT done.

Fixed.

> 
> * prevent primary key that exceeds max index columns [primary]
> 
> The above is done as of yesterday.

OK.

> 
> * Fix memory leak for expressions[memory](Tom?) 
> 
> This isn't going to happen for 7.0, looks like :-(

I figured.

> 
> * -Allow compression of large fields or a compressed field type
> 
> This has to be marked not-done again, unless Jan manages to squeeze
> it back in via the toaster before Feb.

I was optimistic.  I will take it off mark.

> 
> * Pull requested data directly from indexes, bypassing heap data
> 
> I doubt this is ever going to happen --- to make it possible, we'd
> have to store tuple-commit status in index entries as well as in the
> tuples themselves.  That would be a substantial space and speed penalty;
> is the potential gain really worth it?

Ingres does this.  Not sure if it worth it.  Comments?

> 
> * -Convert function(constant) into a constant for index use(Tom)
> 
> Bernard Frankpitt should get the bulk of the credit for that one, not me.

Updated.

> 
> * Allow LIMIT ability on single-table queries that have no ORDER BY to use
>   a matching index [limit]
> * Improve LIMIT processing by using index to limit rows processed [limit]
> * Have optimizer take LIMIT into account when considering index scans [limit]
> 
> I agree with Hiroshi that these entries are redundant.

Only one remains now.

> 
> * -Make index creation use psort code, because it is now faster(Vadim)
> 
> I did that, not Vadim.

Vadim had claimed it.  You did it.  Updated.

> 
> * -elog() flushes cache, try invalidating just entries from current xact,
>   perhaps using invalidation cache
> 
> I don't think this is done?

I thought we fixed this.  Hiroshi?  I could swear this came in the past
few weeks.

> 
> * -Process const = const parts of OR clause in separate pass(Tom)
> 
> Again, mostly Frankpitt.

Updated.

> 
> 
> Some other things I did that aren't mentioned in TODO, but perhaps
> deserve to be shown as 7.0 fixes:
> 
> * Interlock to prevent DROP DATABASE on a database with running backends
> 
> * Buffer reference counting bugfixes
> 
> * Fix libpq bug that causes it to drop backend error message sent
>   just before connection closure (ie, any FATAL error message :-().

All added to reliability section.

-- 
  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

Re: [HACKERS] TODO list updated

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-01-13T03:02:40Z

> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > We currently do not use indexes to handle ORDER BY because it is slower,
> 
> Er, actually, we *do* use indexes for ORDER BY currently:
> 
> regression=# explain select * from tenk1 order by unique1;
> NOTICE:  QUERY PLAN:
> Index Scan using tenk1_unique1 on tenk1  (cost=760.00 rows=10000 width=148)
> 
> If you start psql with PGOPTIONS="-fi" you can see that the optimizer
> believes an explicit sort would be much slower:
> 
> regression=# explain select * from tenk1 order by unique1;
> NOTICE:  QUERY PLAN:
> Sort  (cost=3233.91 rows=10000 width=148)
>   ->  Seq Scan on tenk1  (cost=563.00 rows=10000 width=148)
> 
> but (at least on my machine) the explicit sort is marginally faster.
> Evidently, the cost estimate for an explicit sort is *way* too high.

But it shouldn't be using the ORDER BY, except when the number of rows
processed is less than the full table, right?

> 
> I have been poking at this and am currently thinking that the CPU-vs-
> disk scaling constants (_cpu_page_weight_ and cpu_index_page_weight_)
> may be drastically off for modern hardware.  This is one of the
> optimizer issues that I'm hoping to resolve for 7.0.

Makes sense.  CPU's have gotten much faster than disk.

-- 
  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

RE: [HACKERS] TODO list updated

Hiroshi Inoue <inoue@tpf.co.jp> — 2000-01-13T03:14:54Z

> -----Original Message-----
> From: owner-pgsql-hackers@postgreSQL.org
> [mailto:owner-pgsql-hackers@postgreSQL.org]On Behalf Of Tom Lane
> 
> TODO item comments:
> 
> * Pull requested data directly from indexes, bypassing heap data
> 
> I doubt this is ever going to happen --- to make it possible, we'd
> have to store tuple-commit status in index entries as well as in the
> tuples themselves.  That would be a substantial space and speed penalty;
> is the potential gain really worth it?
>

I agree with Tom.  We could omit rows using indexes but cound't
pull data from indexes without time qualification of heap tuples now.
 
> * -elog() flushes cache, try invalidating just entries from current xact,
>   perhaps using invalidation cache
> 
> I don't think this is done?
>

If I recognize correctly this item,this was fixed by my recent changes
for cache invalidation though I had changed it without knowing this item.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

Re: [HACKERS] TODO list updated

Tom Lane <tgl@sss.pgh.pa.us> — 2000-01-13T03:19:34Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> but (at least on my machine) the explicit sort is marginally faster.
>> Evidently, the cost estimate for an explicit sort is *way* too high.

> But it shouldn't be using the ORDER BY,

Right, if the cost estimates were in line with reality it would be
choosing the explicit sort.

> ... except when the number of rows
> processed is less than the full table, right?

Now if there were *also* a LIMIT clause then the tradeoffs change again
--- the index scan wins for a small LIMIT because of its much lower
startup cost.  But the optimizer knows nothing of this and will still
estimate on the basis that all of the tuples are going to be processed.
As Hiroshi just remarked, we really need to teach the optimizer about
LIMIT.  Another thing I'm hoping to get done before 7.0.

			regards, tom lane

Re: [HACKERS] TODO list updated

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-01-13T03:34:33Z

> > -----Original Message-----
> > From: owner-pgsql-hackers@postgreSQL.org
> > [mailto:owner-pgsql-hackers@postgreSQL.org]On Behalf Of Tom Lane
> > 
> > TODO item comments:
> > 
> > * Pull requested data directly from indexes, bypassing heap data
> > 
> > I doubt this is ever going to happen --- to make it possible, we'd
> > have to store tuple-commit status in index entries as well as in the
> > tuples themselves.  That would be a substantial space and speed penalty;
> > is the potential gain really worth it?
> >
> 
> I agree with Tom.  We could omit rows using indexes but cound't
> pull data from indexes without time qualification of heap tuples now.

Removed.

>   
> > * -elog() flushes cache, try invalidating just entries from current xact,
> >   perhaps using invalidation cache
> > 
> > I don't think this is done?
> >
> 
> If I recognize correctly this item,this was fixed by my recent changes
> for cache invalidation though I had changed it without knowing this item.

Great.  I thought so.  I remember some CVS messages saying this.

-- 
  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

Re: [HACKERS] TODO list updated

Peter Eisentraut <e99re41@docs.uu.se> — 2000-01-13T11:12:27Z

On Wed, 12 Jan 2000, Bruce Momjian wrote:

> > Wow, we're at 32% done!
> 
> Actually, there are tons of _done_ items on the list.  I mentioned only
> the big undone ones.

I just do a
echo $(( `grep '^* -' TODO | wc -l` * 100 / `grep '^*' TODO | wc -l` ))
<grin>

> > * Make postgres user have a password by default
> > 
> > There's an initdb switch.
> 
> OK, now we have to decide if we are going to require this be done as
> part of initdb.  I am inclined to say the user _has_ to be _prompted_ in
> a secure matter for the password as part of initdb.  Have a command-line
> switch for the password is not secure, IMHO, though it is better than
> nothing.

Okay, a prompt it shall be. But not mandatory, since in my environment we
don't even use passwords.

-- 
Peter Eisentraut                  Sernanders vaeg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden

Re: [HACKERS] TODO list updated

Peter Eisentraut <e99re41@docs.uu.se> — 2000-01-13T11:16:09Z

On Wed, 12 Jan 2000, Tom Lane wrote:

> Note that if initdb is a shell script, then it still has to be very
> careful what it does with the password; put it in any command line
> for a program invoked by the script, and the leak is back with you.
> A C-program version of initdb would be a lot safer.  But in theory you
> can pass the password to the backend without exposing it in any command
> line (put it in a data file instead, say).

What is does is some sort of sed s/genericpassword/realpassword/ so I
guess this is not completely safe either. But something like this you'd
have to do. Can I count you in on beating Bruce into submission for an
initdb in C? ;)

-- 
Peter Eisentraut                  Sernanders vaeg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden

Re: [HACKERS] TODO list updated

Peter Eisentraut <e99re41@docs.uu.se> — 2000-01-13T11:21:27Z

On Wed, 12 Jan 2000, The Hermit Hacker wrote:

> On Wed, 12 Jan 2000, Bruce Momjian wrote:
> 
> > > If we do a 'CREATE USER <user> WITH PASSWORD <pass>', its no more secure
> > > then using a command line switch for password ... 
> > 
> > Why is that?  ps shows command args, righ?
> 
> Point.  You won me over :)

But it doesn't show the complete command line, only SELECT or UPDATE, etc.
I'm not sure if it also shows create, I haven't been able to simulate
that.

What's the whole point of access control if you can happily scan your ps
output for all selects, inserts, updates, etc. going through and keep
record of it?

-- 
Peter Eisentraut                  Sernanders vaeg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden

Re: [HACKERS] TODO list updated

Marc G. Fournier <scrappy@hub.org> — 2000-01-13T12:40:30Z

On Thu, 13 Jan 2000, Peter Eisentraut wrote:

> On Wed, 12 Jan 2000, Tom Lane wrote:
> 
> > Note that if initdb is a shell script, then it still has to be very
> > careful what it does with the password; put it in any command line
> > for a program invoked by the script, and the leak is back with you.
> > A C-program version of initdb would be a lot safer.  But in theory you
> > can pass the password to the backend without exposing it in any command
> > line (put it in a data file instead, say).
> 
> What is does is some sort of sed s/genericpassword/realpassword/ so I
> guess this is not completely safe either. But something like this you'd
> have to do. Can I count you in on beating Bruce into submission for an
> initdb in C? ;)

Just a thought...since its a script, why not put the password into an
environment variable and read it from that?  

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org

Re: [HACKERS] TODO list updated

Marc G. Fournier <scrappy@hub.org> — 2000-01-13T12:41:48Z

On Thu, 13 Jan 2000, Peter Eisentraut wrote:

> On Wed, 12 Jan 2000, The Hermit Hacker wrote:
> 
> > On Wed, 12 Jan 2000, Bruce Momjian wrote:
> > 
> > > > If we do a 'CREATE USER <user> WITH PASSWORD <pass>', its no more secure
> > > > then using a command line switch for password ... 
> > > 
> > > Why is that?  ps shows command args, righ?
> > 
> > Point.  You won me over :)
> 
> But it doesn't show the complete command line, only SELECT or UPDATE, etc.
> I'm not sure if it also shows create, I haven't been able to simulate
> that.

No, that isn't the problem...the problem is that initdb, if you run it
with command line arguments, will show up in a ps listing with those
command line arguments...

if you type 'initdb --pgpasswd=passwd' it will show up in pas as exactly
that ...

its not the SELECT/UPDATE/etc that we are worried about...

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org

Re: [HACKERS] TODO list updated

Peter Eisentraut <e99re41@docs.uu.se> — 2000-01-13T12:53:50Z

On Thu, 13 Jan 2000, The Hermit Hacker wrote:

> > What is does is some sort of sed s/genericpassword/realpassword/ so I
> > guess this is not completely safe either. But something like this you'd
> > have to do. Can I count you in on beating Bruce into submission for an
> > initdb in C? ;)
> 
> Just a thought...since its a script, why not put the password into an
> environment variable and read it from that?  

That won't solve the problem. The password has to be substituted into the
catalog template and sed is the way to go for that. I guess it's a long
shot to worry about that now. And option --pwprompt should be relatively
safe until initdb is a C program.

-- 
Peter Eisentraut                  Sernanders vaeg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden

Re: [HACKERS] TODO list updated

Peter Eisentraut <e99re41@docs.uu.se> — 2000-01-13T12:55:08Z

On Thu, 13 Jan 2000, The Hermit Hacker wrote:

> No, that isn't the problem...the problem is that initdb, if you run it
> with command line arguments, will show up in a ps listing with those
> command line arguments...
> 
> if you type 'initdb --pgpasswd=passwd' it will show up in pas as exactly
> that ...

Not to mention the world readable shell history files which would make
this even more convenient ...

-- 
Peter Eisentraut                  Sernanders vaeg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden

Re: [HACKERS] TODO list updated

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-01-13T13:15:57Z

> On Wed, 12 Jan 2000, Tom Lane wrote:
> 
> > Note that if initdb is a shell script, then it still has to be very
> > careful what it does with the password; put it in any command line
> > for a program invoked by the script, and the leak is back with you.
> > A C-program version of initdb would be a lot safer.  But in theory you
> > can pass the password to the backend without exposing it in any command
> > line (put it in a data file instead, say).
> 
> What is does is some sort of sed s/genericpassword/realpassword/ so I
> guess this is not completely safe either. But something like this you'd
> have to do. Can I count you in on beating Bruce into submission for an
> initdb in C? ;)

I will be responsible to make sure the password doesn't get into a
command as an argument.  sed has a -f command that will take it's regex
input from a file.  That is the solution, though the umask has to be set
to make sure the temp file is not readable by anyone else.

Most OS vendors use shell scripts for this type of thing because it
doesn't have to be fast, and it is changed often.

-- 
  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

Re: [HACKERS] TODO list updated

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-01-13T13:16:50Z

> On Wed, 12 Jan 2000, The Hermit Hacker wrote:
> > Point.  You won me over :)
> 
> But it doesn't show the complete command line, only SELECT or UPDATE, etc.
> I'm not sure if it also shows create, I haven't been able to simulate
> that.
> 
> What's the whole point of access control if you can happily scan your ps
> output for all selects, inserts, updates, etc. going through and keep
> record of it?

It only shows the command, not the table involved or the parameters.

-- 
  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

Re: [HACKERS] TODO list updated

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-01-13T13:22:47Z

> On Thu, 13 Jan 2000, The Hermit Hacker wrote:
> 
> > No, that isn't the problem...the problem is that initdb, if you run it
> > with command line arguments, will show up in a ps listing with those
> > command line arguments...
> > 
> > if you type 'initdb --pgpasswd=passwd' it will show up in pas as exactly
> > that ...
> 
> Not to mention the world readable shell history files which would make
> this even more convenient ...

Man, why is my bash shell history world-readable.  Who's idea was that?

Also, Peter, I got you the sed -f option to use files as sed
parameters, which gets us out of this problem. Another day, another
escape from recoding it in C...  :-)


-- 
  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

Re: [HACKERS] TODO list updated

Peter Eisentraut <e99re41@docs.uu.se> — 2000-01-13T13:30:35Z

On Thu, 13 Jan 2000, Bruce Momjian wrote:

> > What is does is some sort of sed s/genericpassword/realpassword/ so I
> > guess this is not completely safe either. But something like this you'd
> > have to do. Can I count you in on beating Bruce into submission for an
> > initdb in C? ;)
> 
> I will be responsible to make sure the password doesn't get into a
> command as an argument.  sed has a -f command that will take it's regex
> input from a file.  That is the solution, though the umask has to be set
> to make sure the temp file is not readable by anyone else.

That's one more file to find and to erase! Sounds very ugly to me. Better
leave off this option altogether and user alter user. Can end users
comment on this at all?

> Most OS vendors use shell scripts for this type of thing because it
> doesn't have to be fast, and it is changed often.

So we can do it better! Also besides actual code changes (as recently),
initdb itself hardly ever changes. When I get some time I'll develop a
prototype to convince you. :)

-- 
Peter Eisentraut                  Sernanders vaeg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden

Re: [HACKERS] TODO list updated

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-01-13T13:36:01Z

> On Thu, 13 Jan 2000, Bruce Momjian wrote:
> 
> > > What is does is some sort of sed s/genericpassword/realpassword/ so I
> > > guess this is not completely safe either. But something like this you'd
> > > have to do. Can I count you in on beating Bruce into submission for an
> > > initdb in C? ;)
> > 
> > I will be responsible to make sure the password doesn't get into a
> > command as an argument.  sed has a -f command that will take it's regex
> > input from a file.  That is the solution, though the umask has to be set
> > to make sure the temp file is not readable by anyone else.
> 
> That's one more file to find and to erase! Sounds very ugly to me. Better
> leave off this option altogether and user alter user. Can end users
> comment on this at all?

Huh.  Use trap and have it automatically removed on exit:

	trap "rm -f /tmp/pgpass.$$" 0 1 2 3 15

> 
> > Most OS vendors use shell scripts for this type of thing because it
> > doesn't have to be fast, and it is changed often.
> 
> So we can do it better! Also besides actual code changes (as recently),
> initdb itself hardly ever changes. When I get some time I'll develop a
> prototype to convince you. :)

OK.

-- 
  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

Re: [HACKERS] TODO list updated

Marc G. Fournier <scrappy@hub.org> — 2000-01-13T13:38:21Z

On Thu, 13 Jan 2000, Peter Eisentraut wrote:

> On Thu, 13 Jan 2000, Bruce Momjian wrote:
> 
> > > What is does is some sort of sed s/genericpassword/realpassword/ so I
> > > guess this is not completely safe either. But something like this you'd
> > > have to do. Can I count you in on beating Bruce into submission for an
> > > initdb in C? ;)
> > 
> > I will be responsible to make sure the password doesn't get into a
> > command as an argument.  sed has a -f command that will take it's regex
> > input from a file.  That is the solution, though the umask has to be set
> > to make sure the temp file is not readable by anyone else.
> 
> That's one more file to find and to erase! Sounds very ugly to me. Better
> leave off this option altogether and user alter user. Can end users
> comment on this at all?
> 
> > Most OS vendors use shell scripts for this type of thing because it
> > doesn't have to be fast, and it is changed often.
> 
> So we can do it better! Also besides actual code changes (as recently),
> initdb itself hardly ever changes. When I get some time I'll develop a
> prototype to convince you. :)

I could be wrong here, but I don't think anyone *really* cares whether its
in script or C...just nobody wants to do the coding... :)

I personally think there have been enough solutions to the problem
provided that a C version isn't required, but if someone wants to go
through the trouble of doing it (when suitable solutions are present to
not require it), who am I to argue?

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org

Re: [HACKERS] TODO list updated

Karl DeBisschop <kdebisschop@range.infoplease.com> — 2000-01-13T14:12:12Z

> That's one more file to find and to erase! Sounds very ugly to me. Better
> leave off this option altogether and user alter user. Can end users
> comment on this at all?

As an end user, an initdb in C sounds like the best option.  

I don't really like the temp file idea - i have too many temp files
already. Nor will the average user be immediately understand that the
environment variable should be set without leaving a trace in their
history.

I suppose any of the options could be added to initdb for the novice
or lazy user.  If there is no other solution, I'd prefer a note on
initdb to `psql template1` and `ALTER USER...`

-- 
Karl DeBisschop <kdebisschop@alert.infoplease.com>
617.832.0332 (Fax: 617.956.2696)

Information Please - your source for FREE online reference
http://www.infoplease.com  - Your Ultimate Fact Finder
http://kids.infoplease.com - The Great Homework Helper

Netsaint Plugins Development
http://netsaintplug.sourceforge.net

Re: [HACKERS] TODO list updated

Tom Lane <tgl@sss.pgh.pa.us> — 2000-01-13T15:45:15Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I will be responsible to make sure the password doesn't get into a
> command as an argument.  sed has a -f command that will take it's regex
> input from a file.  That is the solution, though the umask has to be set
> to make sure the temp file is not readable by anyone else.

Another possibility is not to try to 'sed' the password into the initial
database contents, but to run an ALTER USER command (using a standalone
backend) after we've done the initial setup of template1.  As long as
this is done before a postmaster is started, it's perfectly safe ---
no one other than the postgres user will have been able to connect to
the database yet.

Doing it this way, the password would need to appear in the stdin input
of that standalone backend, but not anyplace else.

After thinking about it a little more, I wonder if I was too optimistic
to say that an initdb script could transfer the password securely.
Consider: we can get the password with

	echo "Please enter password for postgres superuser: "
	read PASSWORD

and now the password is in a shell variable of the shell running initdb,
and hasn't been exposed anywhere else.  So far so good, but now what?
You can't securely do

	echo $PASSWORD | backend

or
	echo $PASSWORD > allegedly-secure-temp-file

or even
	backend <<EOF
		ALTER USER ... PASSWORD $PASSWORD
	EOF

(the latter *looks* good, but way too many shells implement
here-documents by creating a temp file to put the data in;
do you want to trust the shell to make the here-doc secure?)

What I am starting to think is that we do need a C program.  However,
it could be very small; it shouldn't try to do all of what initdb does.
All it needs to do is fetch the password from stdin and then echo it
to stdout in an ALTER USER command.  The invocation in initdb would
look something like

	securepassword $SUPERUSERNAME | standalone-backend ...

and the code would be on the order of

	fprintf(stderr, "Please enter password for %s: ", argv[1]);
	fgets(stdin, password);
	printf("ALTER USER %s PASSWORD '%s'\n", argv[1], password);

(Actually, you'd want it to do a few more pushups: turn off tty
echoing before prompting for password, read password twice and
check it was entered the same both times, retry if not, etc.
Another reason that a pure shell script isn't really up to the
job is that AFAIR it can't easily turn off tty echoing.)

			regards, tom lane

Re: [HACKERS] TODO list updated

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-01-13T15:57:33Z

> After thinking about it a little more, I wonder if I was too optimistic
> to say that an initdb script could transfer the password securely.
> Consider: we can get the password with
> 
> 	echo "Please enter password for postgres superuser: "
> 	read PASSWORD
> 
> and now the password is in a shell variable of the shell running initdb,
> and hasn't been exposed anywhere else.  So far so good, but now what?
> You can't securely do
> 
> 	echo $PASSWORD | backend
> 
> or
> 	echo $PASSWORD > allegedly-secure-temp-file

This is secure.  echo is a shell builtin, and does not invoke a separate
process with arguments.

> (Actually, you'd want it to do a few more pushups: turn off tty
> echoing before prompting for password, read password twice and
> check it was entered the same both times, retry if not, etc.
> Another reason that a pure shell script isn't really up to the
> job is that AFAIR it can't easily turn off tty echoing.)

That is the part that is hard to do in a shell, except I think there are
stty settings for this.

I just did:
	
	stty -echo
	read PASS 
	stty echo
	echo $PASS

and it worked perfectly:

	#$ /bjm/x  
			<- typed test here
	test               

-- 
  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

initdb (Re: [HACKERS] TODO list updated)

Peter Eisentraut <e99re41@docs.uu.se> — 2000-01-13T16:02:14Z

On Thu, 13 Jan 2000, Tom Lane wrote:

> What I am starting to think is that we do need a C program.  However,
> it could be very small; it shouldn't try to do all of what initdb does.
> All it needs to do is fetch the password from stdin and then echo it
> to stdout in an ALTER USER command.  The invocation in initdb would

One more little utility lying around, not my favourite.

What I had been phantasizing about is an initdb completely in C that
a) eliminates all shell incompatibilities
b) doesn't depend on the grace of external utilities
c) doesn't need any external files

The implemenation idea behind c) was to include all the catalog/*.h files
directly, having changed the DATA() and DESC() macros prior, thus
eliminating the need for .bki files, genbki.sh (which fortunately hadn't
had any compatibility problems), another set of files being installed that
you don't really need at runtime.

Also you wouldn't need pg_version or pg_encoding which implies you don't
need libpq, which means you don't need to set LD_LIBRARY_PATH. The idea is
that initdb should run right out of the box after make install.

I'm going to try if I can get something like this together before this
thing goes out the door. But I urge you to give the potential advantages
of this careful consideration.

-- 
Peter Eisentraut                  Sernanders vaeg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden

Re: [HACKERS] TODO list updated

Tom Lane <tgl@sss.pgh.pa.us> — 2000-01-13T16:47:56Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> You can't securely do
>> echo $PASSWORD | backend
>> or
>> echo $PASSWORD > allegedly-secure-temp-file

> This is secure.  echo is a shell builtin, and does not invoke a separate
> process with arguments.

echo is a builtin in ksh and derivatives, but I don't think it's safe
to assume it is a builtin everywhere...

			regards, tom lane

Re: [HACKERS] TODO list updated

Karl DeBisschop <kdebisschop@range.infoplease.com> — 2000-01-13T16:58:26Z

> What I am starting to think is that we do need a C program.  However,
> it could be very small; it shouldn't try to do all of what initdb does.
> All it needs to do is fetch the password from stdin and then echo it
> to stdout in an ALTER USER command.  The invocation in initdb would
> look something like
> 
>	 securepassword $SUPERUSERNAME | standalone-backend ...
> 
> and the code would be on the order of
> 
>	 fprintf(stderr, "Please enter password for %s: ", argv[1]);
>	 fgets(stdin, password);
> 	 printf("ALTER USER %s PASSWORD '%s'\n", argv[1], password);
  
Why not something like:

#include  <libpq-fe.h>
  char *pghost;
  char *pgport;
  char *pgoptions;
  char *pgtty;
  char *dbName;
  char *user;
  char *password;
  char *query;
  PGconn     *conn;
  PGresult   *res;

  fprintf(stderr, "Please enter password for %s: ", argv[1]);
  fgets(stdin, password);
  pgoptions = NULL;    /* special options to start up the backend server */
  pgtty = NULL;        /* debugging tty for the backend server */
  conn = PQsetdb(pghost, pgport, pgoptions, pgtty, dbName);
  sprintf(query,"ALTER USER postgres SET PASSWORD='%s'",password)
  PGresult= PQexec(conn,query);
  PQfinish(conn);

-- 
Karl DeBisschop <kdebisschop@alert.infoplease.com>
617.832.0332 (Fax: 617.956.2696)

Information Please - your source for FREE online reference
http://www.infoplease.com  - Your Ultimate Fact Finder
http://kids.infoplease.com - The Great Homework Helper

Netsaint Plugins Development
http://netsaintplug.sourceforge.net

Re: initdb (Re: [HACKERS] TODO list updated)

Tom Lane <tgl@sss.pgh.pa.us> — 2000-01-13T16:58:34Z

Peter Eisentraut <e99re41@DoCS.UU.SE> writes:
> What I had been phantasizing about is an initdb completely in C that
> a) eliminates all shell incompatibilities
> b) doesn't depend on the grace of external utilities

These apparent advantages won't really be realized unless you propose
to replace *all* our shell-scripts with C; so I'm not persuaded by those
arguments.  However

> c) doesn't need any external files

> The implemenation idea behind c) was to include all the catalog/*.h files
> directly, having changed the DATA() and DESC() macros prior, thus
> eliminating the need for .bki files, genbki.sh (which fortunately hadn't
> had any compatibility problems), another set of files being installed that
> you don't really need at runtime.

is very attractive indeed --- it'd eliminate the risk of incompatibility
between genbki's interpretation of the catalog .h files and the C
compiler's interpretation thereof, as well as give us more flexibility
in what we put in the .h files.  (For example, I just finished hacking
up genbki.sh to interpret "INDEX_MAX_KEYS*2" and "INDEX_MAX_KEYS*4"
correctly.  If we ever go to 8-byte oids, that code will need fixed
again.  Whole problem goes away if the tables are processed by the C
compiler...)

What I'd be inclined to think about is a compromise: leave initdb as
mostly a shell script, but replace genbki.sh and the lib template files
with something that compiles up tables equivalent to the template files
and when invoked spits out bootstrapping commands on its stdout.  It'd
be very easy to test: diff its output against the existing template files.

> Also you wouldn't need pg_version or pg_encoding which implies you don't
> need libpq, which means you don't need to set LD_LIBRARY_PATH.

Again, not very interesting, since you won't get far until you have
made libpq.so accessible...

			regards, tom lane

Re: [HACKERS] TODO list updated

Tom Lane <tgl@sss.pgh.pa.us> — 2000-01-13T17:10:29Z

Karl DeBisschop <kdebisschop@range.infoplease.com> writes:
>> What I am starting to think is that we do need a C program.  However,
>> it could be very small; it shouldn't try to do all of what initdb does.

> Why not something like:

> [ fire up a postmaster and send it an ALTER USER command ]

That's got a race condition: at the time you start the postmaster,
the postgres superuser hasn't got a password.  A bad guy could get
in there and set the password the way *he* wanted it, or less
detectably: just connect as postgres, wait for you to set the password,
then read it out (he's still connected as postgres and still has
superuser rights...)

If we thought that was acceptable, the whole issue of setting the
password in initdb (rather than doing it manually later on) wouldn't
be on the table.  The idea is to have a password in place *before*
opening the store.

If Bruce is correct that 'echo' is a shell builtin on all shells,
then
	echo "ALTER USER ..." | standalone-backend
seems like a sufficient solution.  I am a little concerned about
that "if", but it may be a close-enough answer.

			regards, tom lane

Re: [HACKERS] TODO list updated

Marc G. Fournier <scrappy@hub.org> — 2000-01-13T17:18:11Z

On Thu, 13 Jan 2000, Tom Lane wrote:

> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> >> You can't securely do
> >> echo $PASSWORD | backend
> >> or
> >> echo $PASSWORD > allegedly-secure-temp-file
> 
> > This is secure.  echo is a shell builtin, and does not invoke a separate
> > process with arguments.
> 
> echo is a builtin in ksh and derivatives, but I don't think it's safe
> to assume it is a builtin everywhere...

bash-2.03$ which echo
/usr/slocal/bin/echo

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org

Re: [HACKERS] TODO list updated

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-01-13T17:22:23Z

> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> >> You can't securely do
> >> echo $PASSWORD | backend
> >> or
> >> echo $PASSWORD > allegedly-secure-temp-file
> 
> > This is secure.  echo is a shell builtin, and does not invoke a separate
> > process with arguments.
> 
> echo is a builtin in ksh and derivatives, but I don't think it's safe
> to assume it is a builtin everywhere...

I believe it is safe.  csh and sh have it built in.  Does anyone know of
a shell that does not have echo builtin?  How do you tell?  Not sure.


-- 
  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

Re: [HACKERS] TODO list updated

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-01-13T17:25:21Z

> On Thu, 13 Jan 2000, Tom Lane wrote:
> 
> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > >> You can't securely do
> > >> echo $PASSWORD | backend
> > >> or
> > >> echo $PASSWORD > allegedly-secure-temp-file
> > 
> > > This is secure.  echo is a shell builtin, and does not invoke a separate
> > > process with arguments.
> > 
> > echo is a builtin in ksh and derivatives, but I don't think it's safe
> > to assume it is a builtin everywhere...
> 
> bash-2.03$ which echo
> /usr/slocal/bin/echo
> 

which is an external program looking for another external program.  From
bash:

	#$ type echo
	echo is a shell builtin
	#$ which which
	/usr/bin/which

-- 
  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

Re: [HACKERS] TODO list updated

Karl DeBisschop <kdebisschop@range.infoplease.com> — 2000-01-13T17:33:17Z

>That's got a race condition: at the time you start the postmaster,
>the postgres superuser hasn't got a password.  A bad guy could get
>in there and set the password the way *he* wanted it

Or could `echo "ALTER USER ..." | standalone-backend` to the backend
-- isn't that still a race condition?

>or less detectably: just connect as postgres, wait for you to set the
>password, then read it out (he's still connected as postgres and
>still has superuser rights...)

Or connect to the stanadalone backend, and create a trigger on ALTER
USER... to print the command to a file.  Seems like echo doesn't solve
this vulnerablilty either.

Obviously I'm pretty naive here, so I'll just shut up after this.  But
from what I know of how these parts all work together, the echo
approach has the same problems, but maybe to a somewaht smaller degree.

And even if echo is a builtin in all shells, an alias will override
the builtin, at least in bash.  So if you machine has been penetrated
to the point where the above race condition comes into play, you also
cannot trust echo.

Just my $0.02 worth.

-- 
Karl DeBisschop <kdebisschop@alert.infoplease.com>
617.832.0332 (Fax: 617.956.2696)

Information Please - your source for FREE online reference
http://www.infoplease.com  - Your Ultimate Fact Finder
http://kids.infoplease.com - The Great Homework Helper

Netsaint Plugins Development
http://netsaintplug.sourceforge.net

Re: [HACKERS] TODO list updated

Karl DeBisschop <kdebisschop@range.infoplease.com> — 2000-01-13T17:42:16Z

> bash-2.03$ which echo
> /usr/slocal/bin/echo

I don't think that test has bearing on whether echo is builtin.
Consider the following:

skillet.infoplease.com:/u/kdebisschop> export PATH=.:$PATH
skillet.infoplease.com:/u/kdebisschop> which echo
/usr/bin/echo
skillet.infoplease.com:/u/kdebisschop> echo '#!/bin/echo trap door'>./echo
skillet.infoplease.com:/u/kdebisschop> chmod +x echo 
skillet.infoplease.com:/u/kdebisschop> which echo
/disk/1/home/kdebisschop/echo
skillet.infoplease.com:/u/kdebisschop> ./echo foo
trap door ./echo foo
skillet.infoplease.com:/u/kdebisschop> echo foo
foo

So bash is using the builtin, but which shows the script.

BUT, for aliases (this is a totally separate shell, BTW):

skillet.infoplease.com:/u/kdebisschop> alias echo='echo tarp door'
skillet.infoplease.com:/u/kdebisschop> echo foo
tarp door foo
skillet.infoplease.com:/u/kdebisschop> which echo
/usr/bin/echo

-- 
Karl DeBisschop <kdebisschop@alert.infoplease.com>
617.832.0332 (Fax: 617.956.2696)

Information Please - your source for FREE online reference
http://www.infoplease.com  - Your Ultimate Fact Finder
http://kids.infoplease.com - The Great Homework Helper

Netsaint Plugins Development
http://netsaintplug.sourceforge.net

Re: [HACKERS] TODO list updated

Tom Lane <tgl@sss.pgh.pa.us> — 2000-01-13T18:29:11Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> echo is a builtin in ksh and derivatives, but I don't think it's safe
>> to assume it is a builtin everywhere...

> I believe it is safe.  csh and sh have it built in.  Does anyone know of
> a shell that does not have echo builtin?  How do you tell?  Not sure.

I looked at the man pages for plain old Bourne shell on the oldest
systems I have access to (SunOS 4.1.4 and HPUX 9).  They all say that
echo is a builtin.  So I guess it's probably safe enough.  There may
be a few hoary old machines where
	echo "ALTER USER ... $password ..." | backend
is a security risk, but it seems like it should be a very minimal
problem.  (Especially since even a non-builtin echo should be a live
process for only a *really* short interval, even if the backend takes
longer to process the command.)

			regards, tom lane

Re: [HACKERS] TODO list updated

Tom Lane <tgl@sss.pgh.pa.us> — 2000-01-13T18:38:23Z

Karl DeBisschop <kdebisschop@range.infoplease.com> writes:
>> That's got a race condition: at the time you start the postmaster,
>> the postgres superuser hasn't got a password.  A bad guy could get
>> in there and set the password the way *he* wanted it

> Or could `echo "ALTER USER ..." | standalone-backend` to the backend
> -- isn't that still a race condition?

No, not unless he's already either root or postgres.  Ordinary other
users can't run a standalone backend in your database (that's one reason
why the toplevel data directory must always have permissions 700).

> And even if echo is a builtin in all shells, an alias will override
> the builtin, at least in bash.  So if you machine has been penetrated
> to the point where the above race condition comes into play, you also
> cannot trust echo.

Again, if the attacker has already managed to modify your .profile,
then you've lost the game.  What we're concerned about here is other
users on your machine or any of the machines that your pg_hba file
allows connections from.  Running ps while you are doing initdb, for
example, doesn't require any special preconditions beyond a regular
user account on the same machine you are on.

			regards, tom lane

Re: [HACKERS] TODO list updated

Lamar Owen <lamar.owen@wgcr.org> — 2000-01-13T19:47:54Z

Bruce Momjian wrote:
> > * Make postgres user have a password by default

> > There's an initdb switch.

> OK, now we have to decide if we are going to require this be done as
> part of initdb.  I am inclined to say the user _has_ to be _prompted_ in
> a secure matter for the password as part of initdb.  Have a command-line
> switch for the password is not secure, IMHO, though it is better than
> nothing.

> Let's get people's opinions on this, and we can mark it as done.

As a packager, and a user, I would like the _option_ of setting a
default password using a --prompt-for-password switch.

By all means don't make it default to prompting for a password -- there
are those who do not need a password on the database superuser account,
due to other security measures and connection models (IE, backing a
webserver that is handling authentication and pooling connections under
a single (nonprivileged) user).

--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11