Обсуждение: TODO list updated
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, Pennsylvania19026
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
[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, Pennsylvania19026
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
> -----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
> 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, Pennsylvania19026
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 byinitdb --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
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
> 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, Pennsylvania19026
> -----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
[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, Pennsylvania19026
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
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
> 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, Pennsylvania19026
> 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, Pennsylvania19026
> -----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
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
> > -----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, Pennsylvania19026
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
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
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
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
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
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
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
> 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, Pennsylvania19026
> 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, Pennsylvania19026
> 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, Pennsylvania19026
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
orecho $PASSWORD > allegedly-secure-temp-file
or evenbackend <<EOF ALTER USER ... PASSWORD $PASSWORDEOF
(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
> 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 -echoread PASS stty echoecho $PASS and it worked perfectly: #$ /bjm/x <- typed test heretest -- 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, Pennsylvania19026
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
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
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
> 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
optionsto 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
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,
thenecho "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
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
> 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, Pennsylvania19026
> 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 echoecho 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, Pennsylvania19026
>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
> 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
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 whereecho "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
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
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