Re: [HACKERS] OOP real life example (was Re: Why is MySQL more
> Summary: > > 1. The current implementation is broken. > > 2. We have no proper description of how a "fixed" implementation > should work. > > 3. It's hard to fix the current implementation without such a > description. > > 4. Thus, we are in other messages here trying to work out the > model and come up with such a description. > > 5. The people working this out at the moment appear to be me, > Greg Copeland and Hannu Krosing. > > cjs I've been following the thread on and off, but maybe we should come up with a list of specifically what is broken... I have used the oo feature in the past and the only thing I dont care for about it is the lack of documentation/examples/etc of how it really works and the fact that constraints/indicies/etc are not inherited by child tables. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Open 7.3 items
> OK, the vote is not shifting from '.' to '@'. Is that how we want to > go? I like the pg_user enhancement. Marc, comments? This was your > baby. > Would it be hard to setup an internal PG variable for the actual character to be used? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Open 7.3 items
> > > > Well, they aren't separate fields so you can't ORDER BY domain. The dot
> > > > was used so it looks like a schema based on dbname.
>
> IMHO it should look like an user in domain ;)
Agreed, but there is something to be said for doing a sort of users
per domain. This wouldn't be an issue, I don't think, if there was a
split_before() and split_after() like functions.
# SELECT split_before('[EMAIL PROTECTED]','@'), split_after('[EMAIL PROTECTED]', '@');
?column? | ?column?
--+
user | domain.com
What would you guys say to submissions for a patch that would add the
function listed above? Maybe just a function called get_user(text)
and get_domain(text)? ::shrug:: Just some thoughts since there is
validity to being able to parse/operate on this data efficiently. If
those functions existed, then I think everyone would be able to have
their pie as they want it. -sc
--
Sean Chittenden
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Open 7.3 items
On Wed, 2002-08-14 at 12:45, Sean Chittenden wrote:
> > > > > Well, they aren't separate fields so you can't ORDER BY domain. The dot
> > > > > was used so it looks like a schema based on dbname.
> >
> > IMHO it should look like an user in domain ;)
>
> Agreed, but there is something to be said for doing a sort of users
> per domain. This wouldn't be an issue, I don't think, if there was a
> split_before() and split_after() like functions.
>
> # SELECT split_before('[EMAIL PROTECTED]','@'), split_after('[EMAIL PROTECTED]', '@');
> ?column? | ?column?
> --+
> user | domain.com
>
> What would you guys say to submissions for a patch that would add the
> function listed above?
create function split_before(text,text) returns text as '
select case when (strpos($1,$2) > 0)
then substr($1,1,strpos($1,$2)-1)
else $1
end as usename
' language 'SQL';
create function split_after(text,text) returns text as '
select case when (strpos($1,$2) > 0)
then substr($1,strpos($1,$2)+1)
else
end as usedomain
' language 'SQL' ;
hannu=# select split_before('me@somewhere','@'),
split_after('me@somewhere','@');
split_before | split_after
--+-
me | somewhere
(1 row)
-
Hannu
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[HACKERS] contrib Makefiles
Hi guys, The fulltextindex Makefile looks like this: subdir = contrib/fulltextindex top_builddir = ../.. include $(top_builddir)/src/Makefile.global MODULE_big = fti OBJS = list.o chtbl.o fti.o DATA_built = fti.sql DOCS = README.fti SCRIPTS = fti.pl include $(top_srcdir)/contrib/contrib-global.mk How can I modify it to build two different C files into two different .so's? I want to have fti.so and fti2.so sort of thing... Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] cvs probs
Damn - I'm getting it too: P src/backend/utils/fmgr/fmgr.c P src/backend/utils/mb/conv.c P src/backend/utils/mb/mbutils.c P src/backend/utils/mb/conversion_procs/Makefile cvs server: failed to create lock directory for `/projects/cvsroot/pgsql-server/src/backend/utils/mb/conversion_procs/ascii_ and_mic' (/projects/cvsroot/pgsql-server/src/backend/utils/mb/conversion_procs/ascii_ and_mic/#cvs.lock): Permission denied cvs server: failed to obtain dir lock in repository `/projects/cvsroot/pgsql-server/src/backend/utils/mb/conversion_procs/ascii_ and_mic' cvs [server aborted]: read lock failed - giving up Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] anoncvs - here we go again!
On Wed, 2002-08-14 at 07:51, Oliver Elphick wrote: > > cvs server: Updating src/backend/utils/mb/conversion_procs/ascii_and_mic > cvs server: failed to create lock directory for Marc, can you set up a cron job to set the permissions automatically? This seems to happen any time someone adds a new directory. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Let us therefore come boldly unto the throne of grace, that we may obtain mercy, and find grace to help in time of need." Hebrews 4:16 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Open 7.3 items
On Wed, Aug 14, 2002 at 12:11:10AM -0400, Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > I have no personal preference between period and @ or whatever. See if > > you can get some other votes for @ because most left @ when the ORDER BY > > idea came up from Marc. > > FWIW, I still lean to username@database, so I think we're roughly at a > tie. It would be good to get more votes ... My non-coding vote goes to user@database, too. A -- Andrew Sullivan 87 Mowat Avenue Liberty RMS Toronto, Ontario Canada <[EMAIL PROTECTED]> M6K 3E3 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Open 7.3 items
> > > > > > Well, they aren't separate fields so you can't ORDER BY domain. The dot
> > > > > > was used so it looks like a schema based on dbname.
> > >
> > > IMHO it should look like an user in domain ;)
> >
> > Agreed, but there is something to be said for doing a sort of users
> > per domain. This wouldn't be an issue, I don't think, if there was a
> > split_before() and split_after() like functions.
> >
> > # SELECT split_before('[EMAIL PROTECTED]','@'), split_after('[EMAIL PROTECTED]', '@');
> > ?column? | ?column?
> > --+
> > user | domain.com
> >
> > What would you guys say to submissions for a patch that would add the
> > function listed above?
>
> create function split_before(text,text) returns text as '
> select case when (strpos($1,$2) > 0)
> then substr($1,1,strpos($1,$2)-1)
> else $1
> end as usename
> ' language 'SQL';
>
> create function split_after(text,text) returns text as '
> select case when (strpos($1,$2) > 0)
> then substr($1,strpos($1,$2)+1)
> else
> end as usedomain
> ' language 'SQL' ;
>
> hannu=# select split_before('me@somewhere','@'),
> split_after('me@somewhere','@');
> split_before | split_after
> --+-
> me | somewhere
> (1 row)
Oh that was handy and fast! I didn't know of strpos(). Cool, who
says 'ya can't learn something every day? :~) Now with an alias or
subselect, it should be very easy to order users in a domain in any
way that SQL allows. :~) Thanks Hannu. -sc
--
Sean Chittenden
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Domains and Indexes
Sorry Bruce, this was included as a part of the patch of the below subject: Re: [PATCHES] Dump serials as serial -- not a sequence Patch may be smart enough to say 'already applied'. On Wed, 2002-08-14 at 01:29, Bruce Momjian wrote: > > Your patch has been added to the PostgreSQL unapplied patches list at: > > http://candle.pha.pa.us/cgi-bin/pgpatches > > I will try to apply it within the next 48 hours. > > --- > > > Rod Taylor wrote: > > Appears there is a problem finding the opclass when indexing a domain. > > > > CREATE DOMAIN newint as int4; > > CREATE TABLE tab (col newint unique); > > ERROR: data type newint has no default operator class for access method > > "btree" > > You must specify an operator class for the index or define a > > default operator class for the data type > > > > > > Specifically, GetDefaultOpClass() finds 0 exact matches and 3 binary > > compatible matches. Fetching getBaseType() of the attribute fixes the > > problem for domains (see attachment). > > > > However, I have to wonder why GetDefaultOpClass doesn't simply use the > > first Binary Compatible opclass. When there is more than one usable it > > doesn't do anything useful. > > > > > > [ Attachment, skipping... ] > > > > > ---(end of broadcast)--- > > TIP 4: Don't 'kill -9' the postmaster > > -- > Bruce Momjian| http://candle.pha.pa.us > [EMAIL PROTECTED] | (610) 359-1001 > + If your life is a hard drive, | 13 Roberts Road > + Christ can be your backup.| Newtown Square, Pennsylvania 19073 > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] CLUSTER all tables at once?
> Added to TODO: > > o Cluster all tables at once using pg_index.indisclustered or primary key > > > > And what happens with those tables that do not have any such index? > > > > Nothing, would be my vote. You'd just re-CLUSTER all tables that have > > been clustered before, the same way they were last clustered. I second Tom's opinion. If the table was not clustered before leave it as is. Thus the TODO should imho (if at all :-) read: o Cluster all tables at once that have a pg_index.indisclustered Andreas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Open 7.3 items
I'm going to vote for either @ or %. On Wed, 2002-08-14 at 00:11, Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > I have no personal preference between period and @ or whatever. See if > > you can get some other votes for @ because most left @ when the ORDER BY > > idea came up from Marc. > > FWIW, I still lean to username@database, so I think we're roughly at a > tie. It would be good to get more votes ... ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] OOP real life example (was Re: Why is MySQL more
Hannu Krosing wrote: > I guess what he meant was that you were arguing for arguments sake (mine > is better than yours! Yes it is! Yes it is! ...) That's the dictionary definition of the phrase. > and not to get to some > solution, and that's the source of the frustration. I only re-subscribed to the list because we at OpenACS had examined PG's OO extensions quite thoroughly before rejecting the current implementation as being not useful for our work, and I thought our reasoning might be of interest. > dismissing perfectly good arguments with a simple"not true" > statements and suggesting people to read heavy books with the claim that > the truth is somewhere in there ;) and that's what's I mean when I say he's been arguing from authority. -- Don Baccus Portland, OR http://donb.photo.net, http://birdnotes.net, http://openacs.org ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Inheritance
Bruce Momjian wrote: > Christopher Kings-Lynne wrote: > >>>1. The current implementation is broken. >>> >>>2. We have no proper description of how a "fixed" implementation >>>should work. >> >>Surely 99% of the implementation problems could be solved with an index type >>that can span tables? > > > Right. Instead of talking in circles, let's figure out how to do it. > If the issue is only sequence numbers, can we force a column to _only_ > get values from the sequence counter, Even if primary keys were forced to be generated from a sequence (a very artificial restriction), unique constraints are also implemented by index. And people also join on columns other than their primary key so will want indexes on these columns to span tables, also. -- Don Baccus Portland, OR http://donb.photo.net, http://birdnotes.net, http://openacs.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Inheritance
"Christopher Kings-Lynne" <[EMAIL PROTECTED]> writes: > Is it theoretically possible to add support to btree for storing table along > with the indexed value? That's what we need, all right. > This would obviously add overhead, so it would only > be done for spanning indexes. The index would also take up more space on > disk I guess. > When a new inherited table is created, all parent indices would be dropped > and recreated as spanning indices and vice versa. Seems like the hard way. Instead use a t_infomask bit in indextuples to indicate that the index entry points to a table other than the one its index is nominally associated with; if and only if this bit is set, the table OID follows the indextuple header. This way, you don't have to reindex just to create a child table, and you also don't pay any extra space cost for index entries that in fact point at the parent. There are a veritable ton of other issues to be resolved --- like how do we (efficiently) find all the indexes relevant to a given child table --- but the physical storage doesn't seem too complicated. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Open 7.3 items
Sean Chittenden wrote:
> Agreed, but there is something to be said for doing a sort of users
> per domain. This wouldn't be an issue, I don't think, if there was a
> split_before() and split_after() like functions.
>
> # SELECT split_before('[EMAIL PROTECTED]','@'), split_after('[EMAIL PROTECTED]', '@');
> ?column? | ?column?
> --+
> user | domain.com
>
> What would you guys say to submissions for a patch that would add the
> function listed above? Maybe just a function called get_user(text)
> and get_domain(text)? ::shrug:: Just some thoughts since there is
> validity to being able to parse/operate on this data efficiently. If
> those functions existed, then I think everyone would be able to have
> their pie as they want it. -sc
>
I already have a function in contrib/dblink, currently called
dblink_strtok(), which I was going to turn into a builtin function per
recent discussion (renamed of course). It would work for this but is
more general:
dblink_strtok(text inputstring, text delimiter, int posn) RETURNS text
Inputs
inputstring
any string you want to parse a token out of;
e.g. 'f=1&g=3&h=4'
delimiter
a single character to use as the delimiter;
e.g. '&' or '='
posn
the position of the token of interest, 0 based;
e.g. 1
Should it be called splitstr() (similar to substr())?
Joe
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] OOP real life example (was Re: Why is MySQL more
On Tue, 2002-08-13 at 23:42, Bruce Momjian wrote: > Curt Sampson wrote: > > On Tue, 13 Aug 2002, Bruce Momjian wrote: > > > > > Yea, you have to question what value the discussion has, really. We > > > have users of inheritance that like it. If we can get a TODO item out > > > of the disucssion, great, but there doesn't seem to be any direction of > > > where the discussion is heading. > > > > Summary: > > > > 1. The current implementation is broken. > > > > 2. We have no proper description of how a "fixed" implementation > > should work. > > > > 3. It's hard to fix the current implementation without such a > > description. > > > > 4. Thus, we are in other messages here trying to work out the > > model and come up with such a description. > > > > 5. The people working this out at the moment appear to be me, > > Greg Copeland and Hannu Krosing. > > OK, great summary. Isn't the bottom-line issue the limitation of not > being able to create an index that spans tables? Is there any way to > implement that? We have sequences that can span tables. Can that help > us? > Actually, I'm not sure that is the bottom line. One of the reasons I ask so many questions is because I'm trying to understand what the "is" case is. For me, that is important before I can understand, not only what the "to-be" picture should be, but what needs to be done to get there. Because of that, I tend to agree with Curt. We need to fill in 1, 2, and 3. As for item number 4, I was hoping that other references would at least help us understand a "defacto" implementation. Long story short, for me, it's easy to superficially agree that we need indexes that span tables but I still have no idea if that really constitutes "the bottom-line". Regards, Greg Copeland signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Open 7.3 items
On Wed, 2002-08-14 at 16:08, Joe Conway wrote: > I already have a function in contrib/dblink, currently called > dblink_strtok(), which I was going to turn into a builtin function per > recent discussion (renamed of course). It would work for this but is > more general: > > dblink_strtok(text inputstring, text delimiter, int posn) RETURNS text > > Inputs >inputstring > any string you want to parse a token out of; > e.g. 'f=1&g=3&h=4' >delimiter > a single character to use as the delimiter; > e.g. '&' or '=' >posn > the position of the token of interest, 0 based; > e.g. 1 > > Should it be called splitstr() (similar to substr())? What about functions 1. split(text,text,int) returns text 2. split(text,text) returns text[] and why not 3. split(text,text,text) returns text which returns text from $1 delimited by $2 and $3 - Hannu ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Better handling of parse errors
On Wed, 14 Aug 2002, Tom Lane wrote: > Gavin Sherry <[EMAIL PROTECTED]> writes: > > ... do we want to modify every 7.2 error message? > > Nyet ... but I don't think tacking an offset onto the end of > "parse error at or near foo" messages is likely to cause the > sort of generalized havoc you suggest ... In that case, attached is a patch which locates the beginning of the offending token more efficiently (per your suggestion of using scanbuf). The new patch does the same as before: template1=# select * frum pg_class; ERROR: parser: parse error at or near "frum" at character 10 It also implement's Tom's suggestion: template1=# select * from pg_class where\g ERROR: parse: parse error at end of input Gavin scanner2.diff.gz Description: GNU Zip compressed data ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Inheritance
On Wed, 2002-08-14 at 08:59, Tom Lane wrote:
> There are a veritable ton of other issues to be resolved --- like how do
> we (efficiently) find all the indexes relevant to a given child table
> --- but the physical storage doesn't seem too complicated.
Tom, seems we have yet another false start. Thanks for offering your
comments on the topic at hand. Since you seem to have a good grasp on
the the "is" case is, would you be willing to offer up some additional
details on what you feel the ("veritable ton of") outstanding issues
are?
Seems everyone clearly wants a cure and is itching to get there, yet I
don't fully understand the disease. I suspect that there are others in
the same boat. I feel that this is important for us all of understand.
I think we need to understand what our "to-be" picture is as well as
what points need to be addressed before we can say we've arrived.
Willing to help spell this out?
Regards,
Greg Copeland
signature.asc
Description: This is a digitally signed message part
Re: [HACKERS] Inheritance
On Tue, 2002-08-13 at 23:43, Curt Sampson wrote: > Just my opinion of course, but I think it would be best to have a > detailed description of how everything in inheritance is supposed to > work, write a set of tests from that, and then fix the implementation to > conform to the tests. > > And I think a detailed description comes most easily when you have > a logical model to work from. I completely agree. This is why I want/wanted to pursue the theory and existing implementations angle. Seems like everyone trying to jump on "index spanning" is premature. Doesn't Oracle have table inheritance? Hmmm...I might have to go do some reading to find out one way or anther... ;) Sign, Greg Copeland signature.asc Description: This is a digitally signed message part
Re: [HACKERS] journaling in contrib ...
Gavin Sherry <[EMAIL PROTECTED]> writes: > On Wed, 14 Aug 2002, Bruce Momjian wrote: >> I think this belongs on gborg. Would you create a project there? > A number of people at OSCON did consider this to be a nice contrib > feature. Out of curiousity, what makes it more suitable for gborg? I think Bruce is unhappy with the size of the tarball (220K!), and I was too when I first saw it. But it turns out that nearly all of it is a copy of Hans' slides from his OSCON talk, which I don't think is appropriate to include in contrib anyway. (For one thing, not everyone can read .sxi format. I can't at the moment.) I'd suggest dropping the talk slides (and you might as well flatten the thing into one directory). Perhaps instead the README could include a pointer to where to find the talk slides on-line. That'd bring it down to half a dozen K which is a more appropriate size for a contrib item (and hopefully will not trigger Marc's wrath ;-)). regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] OOP real life example (was Re: Why is MySQL more
Hannu Krosing <[EMAIL PROTECTED]> writes: > Agreed. Most of this would be easy to implement for curent > implementation (but perhaps no more efficient than when done by manually > added rules/triggers) if constraints could contain subqueries. I don't understand what a constraint containing a subquery means. Does it constrain the table(s) referenced by the subquery too? If not, what's the point --- adding, dropping or altering rows in the referenced table might make the constraint condition false. If it does constrain the referenced tables, how the heck are you going to implement that in a reasonable fashion? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Inheritance
On Wed, Aug 14, 2002 at 09:39:06AM -0500, Greg Copeland wrote: > On Tue, 2002-08-13 at 23:43, Curt Sampson wrote: > > Just my opinion of course, but I think it would be best to have a > > detailed description of how everything in inheritance is supposed to > > work, write a set of tests from that, and then fix the implementation to > > conform to the tests. > > > > And I think a detailed description comes most easily when you have > > a logical model to work from. > > I completely agree. This is why I want/wanted to pursue the theory and > existing implementations angle. In theory, it sounds like a good idea. In practice ... ;-) > Seems like everyone trying to jump on "index spanning" is premature. Seems like some people haven't looked at the history of the OO implementation in PostgreSQL. Actually, I think you'll find that once a PostgreSQL DBA gets to the point of designing a sufficently complex schema that inheritance might be useful, they quickly bump up against the lack of index and constraint spanning (most notably, referential integrity), and stop right there. This means that there is little community experience with the existing implementation, beyond the OO die hards. ;-) I'm not sure, but Bruce's suggestion of getting index spanning working first might move the existing implementation over the hump from 'interesting toy' to 'less than perfect implementation'. Then, the community can get some real world experience. Bruce has archived some of the emails - check your local pgsql source tree, under <$PGSQLHOME>/doc/TODO.detail/inheritance There was also some theoretical OO discussion, back when the change for default SELECT behavior on an inhertiance tree was made. (You used to have to say: SELECT foo from parent* to get foo from the parent and all children) Take a look at the archives and see if there's anything in that discussion that interests you: providing summary posts of old discussions is often a good way to restart and move an unresolved topic along. Ross ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Inheritance
Ross J. Reedstrom wrote: > Actually, I think you'll find that once a PostgreSQL DBA gets to > the point of designing a sufficently complex schema that inheritance > might be useful, they quickly bump up against the lack of index and > constraint spanning (most notably, referential integrity), and stop > right there. This means that there is little community experience with > the existing implementation, beyond the OO die hards. ;-) I'd have to agree wholeheartedly with this, because this was exactly my experience the one time I wanted to use inherited tables. FWIW, one thought I've had before related to inheritance (but pretty much orthognal to this discussion) is this: if inheritance included shared indexes and constraints, we would be not too far from having Oracle style table partitioning. Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] another multibyte question
Do any of the encodings with encoding max length > 1 have a constant character size (e.g. unicode?). If so, how hard would it be to add another member to pg_wchar_tbl, say: bool mblen_is_const; /* all chars = max bytes this charset */ Then those character sets code gain back much of the same speed advantages as single byte character sets when it comes to string processing. Joe ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Inheritance
On Wed, 2002-08-14 at 10:17, Ross J. Reedstrom wrote: > On Wed, Aug 14, 2002 at 09:39:06AM -0500, Greg Copeland wrote: > > I completely agree. This is why I want/wanted to pursue the theory and > > existing implementations angle. > > In theory, it sounds like a good idea. In practice ... ;-) > LOL. :) > > Seems like everyone trying to jump on "index spanning" is premature. > > Seems like some people haven't looked at the history of the OO > implementation in PostgreSQL. [waving hand...] > > Bruce has archived some of the emails - check your local pgsql source tree, > under <$PGSQLHOME>/doc/TODO.detail/inheritance > > There was also some theoretical OO discussion, back when the change for > default SELECT behavior on an inhertiance tree was made. (You used to > have to say: SELECT foo from parent* to get foo from the parent and all > children) Take a look at the archives and see if there's anything in that > discussion that interests you: providing summary posts of old discussions > is often a good way to restart and move an unresolved topic along. Thanks! I briefly read something about that in the archives. Excellent pointers. I'll check that out. If I have time, I'll try to summarize and post. Greg Copeland signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Open 7.3 items
[EMAIL PROTECTED] dijo: > > OK, the vote is not shifting from '.' to '@'. Is that how we want to > > go? I like the pg_user enhancement. Marc, comments? This was your > > baby. > > Would it be hard to setup an internal PG variable for the actual character > to be used? That'd be good, because almost any character people wants to use as delimiter is actually valid in database and user names. So giving people a choice is a good thing. For example someone may want to use email address as usernames, and that messes up the splitting on @. -- Alvaro Herrera () "Cuando miro a alguien, mas me atrae como cambia que quien es" (J. Binoche) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] OOP real life example (was Re: Why is MySQL more
On Tuesday 13 August 2002 08:07 pm, Curt Sampson wrote: > On Tue, 13 Aug 2002, Lamar Owen wrote: > > > Curt, I think his reply stems from his frustration of chosen content in > > > many emails that originate from you. We all pretty well understand > > > postgres has a broken feature. We all understand you see zero value in > > Knowing Don to some extent, I can say with some assurance that his > > 'attacks' are never unprovoked. > Sorry; I'm not aware of the circumstances under which one is supposed > to call someone a "dick-waver" and other such things on a technical > mailing list. Perhaps you can explain to me when one should be > doing this, so I too can do it at the appropriate times. I never said I agreed with his wording; in fact I don't agree with his wording. But that's not the point. The point is that the discussion was going absolutely nowhere, quickly. Don's colorful metaphors (for lack of a better term) aren't ones I would use, by any means -- but they had the desired effect, didn't they? The discussion has since progressed from 'the feature is broken because I say it is' to 'how can we fix the broken feature' -- which is where Don, Hannu, and Greg, unless I am mistaken, were all going towards. If you, Curt, were just trying to play devil's advocate you went just a little too far, too vehemently, and were flamed in the old alt.flame tradition. Had the words 'Hitler' or 'Nazi' shown up we would have known it had gone the next step -- and I'm just relating Usenet tradition here -- I'm not a party to that tradition, but I certainly have seen enough flamewars to know what they disintegrate into. I for one am glad you toned down the 'devil's advocate' point of view so that a useful discussion arises (which has indeed happened). And I just stated my experience with Don -- no agreement (or judgment) was implied or stated. I've just developed code beside him before. I wish I had more time to develop code on OpenACS, in fact -- but that's even further off-topic. Don Baccus is well-mannered and even tempered until provoked. When provoked; well, you see what happens. Now, let's see the constructive discussion continue, without authoritarian posturing (for lack of a more technical term for Don's colorful metaphor). -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Inheritance
Greg Copeland <[EMAIL PROTECTED]> writes: > On Tue, 2002-08-13 at 23:43, Curt Sampson wrote: >> And I think a detailed description comes most easily when you have >> a logical model to work from. > I completely agree. This is why I want/wanted to pursue the theory and > existing implementations angle. > Seems like everyone trying to jump on "index spanning" is premature. I agree. Table-spanning indexes would be a large, complex, difficult-to-get-right feature. Before diving into that we should get some idea of just how we'd actually use them, and whether that's the only big chunk of work standing between us and a more useful inheritance feature. I'm afraid we might do all that effort and then discover there are other showstoppers. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] regression test failure
On Tuesday 13 August 2002 03:52 pm, Peter Eisentraut wrote: > Tatsuo Ishii writes: > > The $libdir variable is defined at the compile time and it points to > > $prefix/lib. Apparently it points to different place while doing > > regression tests. One idea is replacing $lindir with the absolute path > > to $prefix/lib. However I wonder this would break some installations, > > for example RPM. > You can replace the string '$libdir' in the conversions_create.sql file > with an absolute directory name during the standalone regression test run. > This could be done in the regression test driver, where the correct path > is available as $pkglibdir. Other, less messy solutions don't occur to me > offhand. The RPM's patch the regression tests to work -- in a somewhat broken way, but enough to get useful results. IIRC, I'm already subbing $libdir out in them. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Open 7.3 items
On Tuesday 13 August 2002 07:21 pm, Sander Steffann wrote: > I think choosing . as the delimiter is a dangerous choice... People have > not expected it to be special until now, so maybe another character can be > chosen? I would suggest a colon if possible, so you would get dbname:user. > I don't expect that a lot of people use a colon as the dbname or username, > but I could be very wrong here. The choices have been enumerated as . and @. I personally vote for either: user@db OR db!user (sorry, having been a UUCP node admin shows at times...) To my eyes the bang notation is more of a 'divider' than the @. Unless there is some _really_ good reason to not use !, that is. :-) -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] SF moving to DB2...
I know this is a off topic. I found this in my mailbox not long ago. I'm sharing because I thought it might be of some interest. While it's obviously a PR move by IBM, it certainly was nice to have something of scale like SF to tout in Postgres' favor as a success story. Here's a snippet from what I got: Today we have announced that we are moving SourceForge.net to DB2, a powerful relational database by IBM. We are doing this because the site continues to grow at a rapid rate, with 700 new users and 70 new projects a day, and we need a database that can handle this growth. We feel that DB2 can do this for us, and IBM is giving us the resources to make this transition successful. You can read the press release here: http://www.vasoftware.com/news/press.php/2002/1070.html Sign, Greg Copeland signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Inheritance
On Wed, 2002-08-14 at 11:17, Ross J. Reedstrom wrote: > On Wed, Aug 14, 2002 at 09:39:06AM -0500, Greg Copeland wrote: > > On Tue, 2002-08-13 at 23:43, Curt Sampson wrote: > > > Just my opinion of course, but I think it would be best to have a > > > detailed description of how everything in inheritance is supposed to > > > work, write a set of tests from that, and then fix the implementation to > > > conform to the tests. > > > > > > And I think a detailed description comes most easily when you have > > > a logical model to work from. > > > > I completely agree. This is why I want/wanted to pursue the theory and > > existing implementations angle. > > In theory, it sounds like a good idea. In practice ... ;-) > > > Seems like everyone trying to jump on "index spanning" is premature. > > Seems like some people haven't looked at the history of the OO > implementation in PostgreSQL. > > Actually, I think you'll find that once a PostgreSQL DBA gets to > the point of designing a sufficently complex schema that inheritance > might be useful, they quickly bump up against the lack of index and > constraint spanning (most notably, referential integrity), and stop Only took a few minutes to write a couple of triggers to manage most of my needs. Not very generic, but gives me cross table uniqueness ;) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Open 7.3 items
We are clearly going for user@db now. --- Lamar Owen wrote: > On Tuesday 13 August 2002 07:21 pm, Sander Steffann wrote: > > I think choosing . as the delimiter is a dangerous choice... People have > > not expected it to be special until now, so maybe another character can be > > chosen? I would suggest a colon if possible, so you would get dbname:user. > > I don't expect that a lot of people use a colon as the dbname or username, > > but I could be very wrong here. > > The choices have been enumerated as . and @. I personally vote for either: > user@db > OR > db!user > (sorry, having been a UUCP node admin shows at times...) To my eyes the bang > notation is more of a 'divider' than the @. Unless there is some _really_ > good reason to not use !, that is. :-) > -- > Lamar Owen > WGCR Internet Radio > 1 Peter 4:11 > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] CREATE CONSTRAINT TRIGGER appears to be a security hole
While the REFERENCES privilege controls who can create foreign keys
referring to one's tables, it seems you can evade it by using CREATE
CONSTRAINT TRIGGER directly.
This is the "slave" portion of a FK constraint I got from pg_dump:
CREATE CONSTRAINT TRIGGER "$1"
AFTER INSERT OR UPDATE ON "slave"
FROM master
NOT DEFERRABLE INITIALLY IMMEDIATE
FOR EACH ROW
EXECUTE PROCEDURE "RI_FKey_check_ins" ('$1', 'slave', 'master', 'UNSPECIFIED',
'x', 'a');
To create this you only need to have a privilege on "slave", but it
creates a fully functional way to "query" the primary key of the master
table by brute force, and probably also to lock the table up, although I
haven't checked that.
It seems we need to check the privilege on the table mentioned in the FROM
"foo" clause as well. Is that correct and sufficient?
--
Peter Eisentraut [EMAIL PROTECTED]
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Open 7.3 items
On Wed, 14 Aug 2002, Tom Lane wrote: > Lamar Owen <[EMAIL PROTECTED]> writes: > > Appending '@template1' to unadorned usernames, and giving inherited rights > > across the installation to users with template1 rights? Then you have the > > unadorned 'lowen' becomes 'lowen@template1' -- but lowen@pari wouldn't have > > access to template1, right? > > If not, standard things like "psql -l" won't work for lowen@pari. I don't > think we can get away with a scheme that depends on disallowing access > to template1 for most people. > > It should also be noted that the whole point of this little project was > to do something *simple* ... checking access to some other database to > decide what we will allow is getting a bit far afield from simple. Hate to complicate things more, but back to a global username, say you have user "lowen" that should have access to all databases. What happens if there's already a lowen@somedb that's an unprivileged user. Assuming lowen is a db superuser, what happens in somedb? If there's a global user "lowen" and you try to create a lowen@somedb later, will it be allowed? One possible simplification would be to make the username the full username "lowen@somedb", "lowen", ... Right now we can create a "lowen@somedb" and it's a different user than "lowen" and we can already restrict a user to one database, can't we? Hmmm. Just checked and I guess not - I thought we had a record type of "user". Vince. -- == Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking http://www.camping-usa.com http://www.cloudninegifts.com http://www.meanstreamradio.com http://www.unknown-artists.com == ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Open 7.3 items
On Wednesday 14 August 2002 03:29 pm, Vince Vielhaber wrote: > Hate to complicate things more, but back to a global username, say > you have user "lowen" that should have access to all databases. What > happens if there's already a lowen@somedb that's an unprivileged user. > Assuming lowen is a db superuser, what happens in somedb? If there's > a global user "lowen" and you try to create a lowen@somedb later, will > it be allowed? If the user 'lowen' is then expanded to 'lowen@template1' it would be stored that way -- and lowen@template1 is different from lowen@pari, for instance. The lowen@template1 user could be a superuser and lowen@pari might not -- but they become distinct users. Although I do understand the difficulty if the FQDU isn't stored in full in the appropriate places. So I guess the solution is that wherever a user name is to be stored, the fully qualified form must be used and checked against, with @template1 being a 'this user is everywhere' shorthand. But maybe I'm just misunderstanding the implementation. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Open 7.3 items
On Wed, 14 Aug 2002, Lamar Owen wrote: > On Wednesday 14 August 2002 03:29 pm, Vince Vielhaber wrote: > > Hate to complicate things more, but back to a global username, say > > you have user "lowen" that should have access to all databases. What > > happens if there's already a lowen@somedb that's an unprivileged user. > > Assuming lowen is a db superuser, what happens in somedb? If there's > > a global user "lowen" and you try to create a lowen@somedb later, will > > it be allowed? > > If the user 'lowen' is then expanded to 'lowen@template1' it would be stored > that way -- and lowen@template1 is different from lowen@pari, for instance. > The lowen@template1 user could be a superuser and lowen@pari might not -- but > they become distinct users. Although I do understand the difficulty if the > FQDU isn't stored in full in the appropriate places. So I guess the solution > is that wherever a user name is to be stored, the fully qualified form must > be used and checked against, with @template1 being a 'this user is > everywhere' shorthand. > > But maybe I'm just misunderstanding the implementation. I may be too, but what's wrong with just "lowen" being shorthand for 'this user is everywhere'? Does it also mean that we'd have a user postgres@template1? Vince. -- == Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking http://www.camping-usa.com http://www.cloudninegifts.com http://www.meanstreamradio.com http://www.unknown-artists.com == ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] pg_dump output portability
I needed to move a PostgreSQL database to another product but I noticed that the pg_dump output contains a few artifacts that make the output nonportable. Most of these should be relatively easy to fix. Here's my list: * Boolean values should be dumped as true and false (rather than 't' and 'f') in INSERT-style output. * Numeric and int8 should be dumped without quotes, except in cases like 'NaN'. * Date, time, and timestamp literals should use standard prefixed syntax like DATE '-mm-dd'. * Identifier quoting seems to be inconsistent. The -n option gives you portable behaviour (quoted only if mixed case or funny characters), but the default -N doesn't actually quote some things that are generated by the backend, including rule and index creation commands. Is there a point in having the -n behavior at all? * Nonprintable characters in string literals are currently output as octal escape sequences (e.g., \012). It would be more portable to just print out the characters as is. This should be an option -- any opinions on which might be a better default? * The expression reverse-engineering code outputs ::text and similar casts in many cases. These should be CAST(). * It was once proposed to make SET SESSION AUTHORIZATION the default in pg_dump. What became of that? * Is anyone working on using standard foreign key creation commands instead of CREATE CONSTRAINT TRIGGER? -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] journaling in contrib ...
On Wed, 2002-08-14 at 12:47, Marc G. Fournier wrote: > On Wed, 14 Aug 2002, Bruce Momjian wrote: > > > Marc G. Fournier wrote: > > > > They are moving pgaccess more into the admin role, and pgmonitor fit in > > > > with that. > > > > > > Personally, I kinda like to be able to run admin modularized ... they > > > *should* be looking at stuff like webmin, where you can plug-n-play admin > > > functions as required, or horde (http://www.horde.org) ... or http://jfontain.free.fr/moodss/index.html > > > why would I > > > install pgaccess if all I want to do is monitor? Now, to be able to > > > install pgaccess and have pgmonitor tie into *that* would be cool ... > > > > > > 'bigger is better' is MicroSloth's philosophy ... sounds like the PgAccess > > > guys are adopting it too? :( > > > > I assume pgmonitor will just be a new tab in the pgaccess window. It is > > integrated only in that it is part of the tcl scripts supplied. > > Right, but, if its 'integrated', then I have to download the whole thing > ... I only want pgmonitor, so how can I get that now? > > Again, if they do it *properly*, it should be a seperate module you can > download, enable in a config file for pgaccess and have show up ... but it > should be runnable standalone, with all the extras ... > Ok, this is a little off topic for this thread, but maybe I can clear this up really quick, so things can move on. We wanted to have capabilities that PGMonitor provides, in PGAccess. So, we asked Bruce if we could just integrate PGMonitor, and he said yes. To me, it makes sense. Of course you can argue forever on which is better: one big app that contains all the functionality, or several small apps that spread the functionality (ala unix utils). You will get valid arguments on both sides...it is more of a preference thing I believe. Currently, I am integrating it into PGAccess, and I did have to make some modifications for it to work (sorry Bruce...not too many though). However, it is my intention to make this more of a plugin, and also be able to run standalone. So, if you *don't* want PGMonitor, then you don't have to have it, but you will also be able to call PGMonitor by itself (I really haven't decided on a best method yet). So, to answer your question, in the future, you will be able to use PGMonitor standalone. I assumed this from the beginning, since existing users of PGMonitor may not want to use PGAccess (like yourself). --brett > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) -- Brett Schwarz brett_schwarz AT yahoo.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Open 7.3 items
On Wednesday 14 August 2002 03:55 pm, Vince Vielhaber wrote: > On Wed, 14 Aug 2002, Lamar Owen wrote: > > If the user 'lowen' is then expanded to 'lowen@template1' it would be > > stored that way -- and lowen@template1 is different from lowen@pari, for > > But maybe I'm just misunderstanding the implementation. > > I may be too, but what's wrong with just "lowen" being shorthand for > 'this user is everywhere'? Does it also mean that we'd have a user > postgres@template1? WE could still use the form without @template1, but the backend would assume the @template1 user was being meant when the unqualified shorthand was used. So the former plain 'postgres' user could still be such to us, to client programs, etc, but the backend would assume that that meant postgres@template1 -- no namespace collision, and the special case is that anyone@template1 has the behavior the unadorned plain user now has. I do see Bruce's points, however. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Open 7.3 items
Bruce Momjian writes: > I had to add to initdb to create a file /data/PG_INSTALLER and have the > postmaster read that on startup to determine the installing user. I object to treating one user specially. There should be a general mechanism, such as a separate column in pg_shadow. I also object to fixing the name during initdb. We just got rid of that requirement. If it mattered, I would also object to the choice of the file name. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] journaling in contrib ...
On Wed, 14 Aug 2002, Tom Lane wrote: > Gavin Sherry <[EMAIL PROTECTED]> writes: > > On Wed, 14 Aug 2002, Bruce Momjian wrote: > >> I think this belongs on gborg. Would you create a project there? > > > A number of people at OSCON did consider this to be a nice contrib > > feature. Out of curiousity, what makes it more suitable for gborg? > > I think Bruce is unhappy with the size of the tarball (220K!), and > I was too when I first saw it. But it turns out that nearly all of > it is a copy of Hans' slides from his OSCON talk, which I don't think > is appropriate to include in contrib anyway. (For one thing, not > everyone can read .sxi format. I can't at the moment.) > > I'd suggest dropping the talk slides (and you might as well flatten the > thing into one directory). Perhaps instead the README could include a > pointer to where to find the talk slides on-line. That'd bring it down > to half a dozen K which is a more appropriate size for a contrib item > (and hopefully will not trigger Marc's wrath ;-)). S'alright, I figured I'd read all the posts on the subject before I responded in agreement with Bruce ... *If* we start referring ppl and projects to GBorg, more ppl will go see what is available ... how many ppl *look* in contrib to see what is avalable in there? I know that unless I'm searching for something specific, I never check out what is in contrib, but more oft search places like freshmeat to see if someone has done it already ... The original reason for contrib was *if* someone submit'd a patch to extend the server, but we weren't quite sure whether it should go in, we'd give it one release in contrib to see if it would be useful before either trashing it, or incorporating it ... the reality of the situation: we've never trashed anything, and rarely incorporated ... Anything in contrib that can be built seperately from the server code, that just requires libpq and headers, should be pulled and distributed as seperate modules, which has the added benefit that, if listed on GBorg, search engines will pick up the modules ... And the whole arg that someone threw out about 'nobody maintaining them if they aren't part of the distribution' ... so? if nobody is maintaining, then who is using?? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Open 7.3 items
OK, what I didn't want to do we to over-complexify something that is for only a few users. In a way that user has to be special for this case because of the requirement that at least one person be able to connect when you flip that flag. Also, I don't want to add a column to pg_shadow. Seems like overkill. Please suggest another name for the file. Basically, I am not going to stop working on something when one person objects or this will never get done, and I think we have had enough feedback on this that people do want this done. --- Peter Eisentraut wrote: > Bruce Momjian writes: > > > I had to add to initdb to create a file /data/PG_INSTALLER and have the > > postmaster read that on startup to determine the installing user. > > I object to treating one user specially. There should be a general > mechanism, such as a separate column in pg_shadow. > > I also object to fixing the name during initdb. We just got rid of that > requirement. > > If it mattered, I would also object to the choice of the file name. > > -- > Peter Eisentraut [EMAIL PROTECTED] > > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] CREATE CONSTRAINT TRIGGER appears to be a security hole
Peter Eisentraut <[EMAIL PROTECTED]> writes: > While the REFERENCES privilege controls who can create foreign keys > referring to one's tables, it seems you can evade it by using CREATE > CONSTRAINT TRIGGER directly. Good point. > It seems we need to check the privilege on the table mentioned in the FROM > "foo" clause as well. Is that correct and sufficient? It is if we assume that every CREATE CONSTRAINT TRIGGER is used for something that should require REFERENCES privilege. Given that the command is not really intended for user use anyway, this is probably okay to assume. One might try to evade the check by mentioning something different in FROM than is mentioned in the trigger arguments, but as of CVS tip that doesn't work --- the RI triggers don't look at the relation-name arguments anymore, only at the FROM link. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Open 7.3 items
This email brings up another issue I have seen recently. The use of the word "object", "strongly object", or "*object*" with stars is a very confrontational way to express things. It does not foster discussion; it really puts your heal in the ground and presents a very unswerving attitude when it really isn't necessary nor valuable. It is not just this email, but several people on this list who are doing that now, and it is making for more negative discussions. Thomas has mentioned it too. As I have said before, everyone gets one vote. It doesn't matter how hard to "object" to something. It is the force of your argument that affects the votes, not how strongly you express your dislike of something. One effect of this environment is that you end up coding to avoid "objections" rather than coding to meet users needs. Certainly the people who express objections are providing valuable feedback to help improve patches/features, but it should be done in a way that doesn't give the impression they are in a courtroom and when you post something incorrect, some lawyer is going to jump up and yell "object". --- Peter Eisentraut wrote: > Bruce Momjian writes: > > > I had to add to initdb to create a file /data/PG_INSTALLER and have the > > postmaster read that on startup to determine the installing user. > > I object to treating one user specially. There should be a general > mechanism, such as a separate column in pg_shadow. > > I also object to fixing the name during initdb. We just got rid of that > requirement. > > If it mattered, I would also object to the choice of the file name. > > -- > Peter Eisentraut [EMAIL PROTECTED] > > > ---(end of broadcast)--- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Domains and Indexes
Thanks. I will keep it in the queue for CVS commit message sake. --- Rod Taylor wrote: > Sorry Bruce, this was included as a part of the patch of the below > subject: > > Re: [PATCHES] Dump serials as serial -- not a sequence > > > Patch may be smart enough to say 'already applied'. > > > On Wed, 2002-08-14 at 01:29, Bruce Momjian wrote: > > > > Your patch has been added to the PostgreSQL unapplied patches list at: > > > > http://candle.pha.pa.us/cgi-bin/pgpatches > > > > I will try to apply it within the next 48 hours. > > > > --- > > > > > > Rod Taylor wrote: > > > Appears there is a problem finding the opclass when indexing a domain. > > > > > > CREATE DOMAIN newint as int4; > > > CREATE TABLE tab (col newint unique); > > > ERROR: data type newint has no default operator class for access method > > > "btree" > > > You must specify an operator class for the index or define a > > > default operator class for the data type > > > > > > > > > Specifically, GetDefaultOpClass() finds 0 exact matches and 3 binary > > > compatible matches. Fetching getBaseType() of the attribute fixes the > > > problem for domains (see attachment). > > > > > > However, I have to wonder why GetDefaultOpClass doesn't simply use the > > > first Binary Compatible opclass. When there is more than one usable it > > > doesn't do anything useful. > > > > > > > > > > [ Attachment, skipping... ] > > > > > > > > ---(end of broadcast)--- > > > TIP 4: Don't 'kill -9' the postmaster > > > > -- > > Bruce Momjian| http://candle.pha.pa.us > > [EMAIL PROTECTED] | (610) 359-1001 > > + If your life is a hard drive, | 13 Roberts Road > > + Christ can be your backup.| Newtown Square, Pennsylvania 19073 > > > > ---(end of broadcast)--- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org > > > > > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Open 7.3 items
Lamar Owen <[EMAIL PROTECTED]> writes: > So the former plain 'postgres' user could still be such to us, to client > programs, etc, but the backend would assume that that meant > postgres@template1 -- no namespace collision, and the special case is that > anyone@template1 has the behavior the unadorned plain user now has. The trouble with that scheme is that there is zero interoperability between the plain-vanilla mode (postgres is postgres in pg_shadow) and the @-mode (postgres is postgres@template1 in pg_shadow). Flip the configuration switch, in either direction, and you can't log in anymore. We'd almost have to make it a frozen-at-initdb setting so that initdb would know which form to put into pg_shadow for the superuser, and so that entry wouldn't break thereafter. The reason I like the "lowen" vs "lowen@somedb" pattern is that database-global users can log in the same way whether the feature is turned on or not; this eliminates the getting-started problem, as well as the likelihood of shooting yourself in the foot. It is true that if you have a global user lowen you'd want to avoid creating any local users lowen@somedb, and that the existing code wouldn't be able to enforce that. We could possibly add a few lines to CREATE USER to warn about this mistake. (It should be a warning not an error, since if you have no intention of ever using the @-feature then there's no reason to restrict your choice of usernames.) regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Fwd: Re: [HACKERS] journaling in contrib ...
interesting. >From: Peter Eisentraut <[EMAIL PROTECTED]> >To: Bruce Momjian <[EMAIL PROTECTED]> >CC: Tom Lane <[EMAIL PROTECTED]>,Gavin Sherry <[EMAIL PROTECTED]>, ><[EMAIL PROTECTED]>,<[EMAIL PROTECTED]> >Subject: Re: [HACKERS] journaling in contrib ... >Date: Thu, 15 Aug 2002 00:01:41 +0200 (CEST) >MIME-Version: 1.0 >Received: from [64.49.215.143] by hotmail.com (3.2) with ESMTP id >MHotMailBF241D7B006A4004319C4031D78F0F510; Wed, 14 Aug 2002 14:58:24 -0700 >Received: from postgresql.org (postgresql.org [64.49.215.8])by >relay2.pgsql.com (Postfix) with ESMTPid F0ED2EDFC30; Wed, 14 Aug 2002 >17:58:05 -0400 (EDT) >Received: from localhost (postgresql.org [64.49.215.8])by postgresql.org >(Postfix) with ESMTP id B278F47583Efor <[EMAIL PROTECTED]>; Wed, >14 Aug 2002 17:58:00 -0400 (EDT) >Received: from mail.gmx.net (mail.gmx.net [213.165.64.20])by postgresql.org >(Postfix) with SMTP id A91A14754A3for <[EMAIL PROTECTED]>; Wed, >14 Aug 2002 17:57:59 -0400 (EDT) >Received: (qmail 26637 invoked by uid 0); 14 Aug 2002 21:58:00 - >Received: from pd902f0d4.dip0.t-ipconnect.de (217.2.240.212) by >mail.gmx.net (mp002-rz3) with SMTP; 14 Aug 2002 21:58:00 - >From pgsql-hackers-owner Wed, 14 Aug 2002 14:59:23 -0700 >X-X-Sender: [EMAIL PROTECTED] >In-Reply-To: <[EMAIL PROTECTED]> >Message-ID: ><[EMAIL PROTECTED]> >X-Virus-Scanned: by AMaViS new-20020517 >Precedence: bulk >Sender: [EMAIL PROTECTED] > >Bruce Momjian writes: > > > OK, we got _that_ answer. Looks like gborg. Marc really wants to pump > > that up. > >I think if gborg had a different name and looked more like the main site, >more people would consider using it without feeling "kicked out". > >-- >Peter Eisentraut [EMAIL PROTECTED] > > >---(end of broadcast)--- >TIP 3: if posting/reading through Usenet, please send an appropriate >subscribe-nomail command to [EMAIL PROTECTED] so that your >message can get through to the mailing list cleanly _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] CLUSTER all tables at once?
Sounds good to me. TODO updated: o Cluster all tables at once using pg_index.indisclustered set during previous CLUSTER --- Zeugswetter Andreas SB SD wrote: > > > Added to TODO: > > > > o Cluster all tables at once using pg_index.indisclustered or primary key > > > > > > And what happens with those tables that do not have any such index? > > > > > > Nothing, would be my vote. You'd just re-CLUSTER all tables that have > > > been clustered before, the same way they were last clustered. > > I second Tom's opinion. If the table was not clustered before leave it as is. > > Thus the TODO should imho (if at all :-) read: > o Cluster all tables at once that have a pg_index.indisclustered > > Andreas > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] pg_dump output portability
Peter Eisentraut <[EMAIL PROTECTED]> writes: > I needed to move a PostgreSQL database to another product but I noticed > that the pg_dump output contains a few artifacts that make the output > nonportable. Most of these should be relatively easy to fix. Most of these look like they would break a lot of people --- for example, we can't just arbitrarily change the results of bool_out. > * Identifier quoting seems to be inconsistent. The -n option gives you > portable behaviour (quoted only if mixed case or funny characters), but > the default -N doesn't actually quote some things that are generated by > the backend, including rule and index creation commands. Is there a point > in having the -n behavior at all? You mean you'd rather eliminate the -N behavior, no? I'd vote for that. > * Nonprintable characters in string literals are currently output as octal > escape sequences (e.g., \012). It would be more portable to just print > out the characters as is. This should be an option -- any opinions on > which might be a better default? Again, I'm fairly suspicious of this; it seems likely to result in failures to read in the data. You can't just leave data newlines as-is for example. > * The expression reverse-engineering code outputs ::text and similar casts > in many cases. These should be CAST(). I will vote against this as being a major loss of legibility. Perhaps we could compromise on controlling it by a GUC variable, though. > * It was once proposed to make SET SESSION AUTHORIZATION the default in > pg_dump. What became of that? I think this is a good idea, and was meaning to do it but hadn't got round to it. > * Is anyone working on using standard foreign key creation commands > instead of CREATE CONSTRAINT TRIGGER? Rod Taylor submitted a patch for that, which I was planning to review and apply shortly. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Open 7.3 items
Bruce Momjian <[EMAIL PROTECTED]> writes: > In a way that user has to be special for this case > because of the requirement that at least one person be able to connect > when you flip that flag. Why does anyone need to be special? The behavior should be to try the given user name, and if that's not found then to try user@db. I see no need to special-case any user. > Basically, I am not going to stop working on something when one person > objects or this will never get done, He didn't say to stop working on it. He said to fix the misdesigned parts. And I quite agree that those parts are misdesigned. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] journaling in contrib ...
Tom Lane wrote: > I'd suggest dropping the talk slides (and you might as well flatten the > thing into one directory). Perhaps instead the README could include a > pointer to where to find the talk slides on-line. That'd bring it down > to half a dozen K which is a more appropriate size for a contrib item > (and hopefully will not trigger Marc's wrath ;-)). OK, we got _that_ answer. Looks like gborg. Marc really wants to pump that up. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] another multibyte question
> Do any of the encodings with encoding max length > 1 have a constant > character size (e.g. unicode?). If so, how hard would it be to add > another member to pg_wchar_tbl, say: > > bool mblen_is_const; /* all chars = max bytes this charset */ > > Then those character sets code gain back much of the same speed > advantages as single byte character sets when it comes to string processing. Sounds interesting idea, but none of encodings currently PostgreSQL supports has fixed length character size. UCS-2/UCS-4 is such an encoding, we do not support it however. -- Tatsuo Ishii ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Open 7.3 items
I believe the dictionary meaning of 'object' in this context would be 'a cause for concern or attention'. Each of Peters uses of the word is highly appropriate, as he was concerned and I'd agree with the sentiments that those concepts needed attention. Anyway, object with stars and strongly object are definitely leaning towards abuse of the word. On Wed, 2002-08-14 at 13:35, Bruce Momjian wrote: > > This email brings up another issue I have seen recently. The use of the > word "object", "strongly object", or "*object*" with stars is a very > > > I had to add to initdb to create a file /data/PG_INSTALLER and have the > > > postmaster read that on startup to determine the installing user. > > > > I object to treating one user specially. There should be a general > > mechanism, such as a separate column in pg_shadow. > > > > I also object to fixing the name during initdb. We just got rid of that > > requirement. > > > > If it mattered, I would also object to the choice of the file name. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] journaling in contrib ...
Marc G. Fournier wrote: > Anything in contrib that can be built seperately from the server code, > that just requires libpq and headers, should be pulled and distributed as > seperate modules, which has the added benefit that, if listed on GBorg, > search engines will pick up the modules ... > > And the whole arg that someone threw out about 'nobody maintaining them if > they aren't part of the distribution' ... so? if nobody is maintaining, > then who is using?? Want to hear something funny? They are moving my pgmonitor off gborg and into the pgaccess. When the move is final, I will add a link on that gborg page. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Open 7.3 items
[EMAIL PROTECTED] (Bruce Momjian) wrote: > Tom Lane wrote: >> Bruce Momjian <[EMAIL PROTECTED]> writes: >> > I don't know where else to go with the patch at this point. I >> > think increasing the number of 'global' users is polluting the >> > namespace too much, >> >> Why? If the installation needs N global users, then it needs N >> global users; who are you to make that value judgment for them? >> >> In practice I think an installation that's using this feature is >> going to have a pretty small number of global users, and so the issue >> of collisions with local usernames isn't really as big as it's been >> painted in this thread. We could ignore that issue (except for >> documenting it) and have a perfectly serviceable feature. > > The original idea was that Marc wanted people who could create their > own users for their own databases. If we make the creation of global > users too easy, all of a sudden people don't have control over their > db usernames because they have to avoid all the global user names > already defined. By adding multiple global users, it is diluting the > usefulness of the feature. > Maybe I am missing something here but shouldnt db access really be part of the privileges system? If all we are talking about is a quick hack until this can be implemented correctly, what is the concern with having so much functionality in the hack? Why does it matter what the actual usernames can or cant be? For example you could just make everyone with a username NN@dbname (where N's are int) local accounts and then leave everything else alone. The only issue I could see with something like this would be that someone trying to use this hack wont be able to give their users names like pudgy@dbname, but who cares? I mean if you are giving access to a bunch of developers, how is it going to affect them if you tell them to login with 123456@yourdb instead of jsmith@yourdb? If they cant remember it or something maybe they can write it down? I dunno... ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_dump output portability
> * Is anyone working on using standard foreign key creation commands > instead of CREATE CONSTRAINT TRIGGER? Submitted with the pg_constraint patch, and more recently updated to match cvs tip. I believe Tom wishes to review this prior to application. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] pg_dump output portability
Peter Eisentraut wrote: > I needed to move a PostgreSQL database to another product but I noticed ^^ Surely this is a misprint. ;-) > that the pg_dump output contains a few artifacts that make the output > nonportable. Most of these should be relatively easy to fix. Here's my > list: Maybe we need a "maximum portability" flag for pg_dump that will do some of the things outlined below. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Open 7.3 items
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > In a way that user has to be special for this case > > because of the requirement that at least one person be able to connect > > when you flip that flag. > > Why does anyone need to be special? The behavior should be to try the > given user name, and if that's not found then to try user@db. I see no > need to special-case any user. Oh, so try it with and without. I can do that, but it seems more of a security problem where you were trying two names instead of one. Do people like that? It is easy to do, except for the fact we have to match pg_hba.conf with a username, though we could do the double-test there too, if that isn't too weird. > > Basically, I am not going to stop working on something when one person > > objects or this will never get done, > > He didn't say to stop working on it. He said to fix the misdesigned > parts. And I quite agree that those parts are misdesigned. I will fix them as long as the fixes don't generate new objections, like adding a new column to pg_shadow. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Open 7.3 items
Bruce Momjian <[EMAIL PROTECTED]> writes: > Oh, so try it with and without. I can do that, but it seems more of a > security problem where you were trying two names instead of one. Do > people like that? The nice thing about it is you can have any combination of people with installation-wide access (create them as joeblow) and people with one-database access (create them as joeblow@joesdatabase). A special case for only the postgres user is much less flexible. > It is easy to do, except for the fact we have to > match pg_hba.conf with a username, though we could do the double-test > there too, if that isn't too weird. It'd probably be better to first look at the flat-file copy of pg_shadow to determine whether user or user@database is the form to use, and then run through pg_hba.conf only once using the correct form. Otherwise there are going to be all sorts of weird corner cases: user might match a different pg_hba row than user@database does. Also, if you do it this way then the substitution only has to be done in one place: you can pass down the correct form to the backend, which'd otherwise have to repeat the test to see which username is found. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Open 7.3 items
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > Oh, so try it with and without. I can do that, but it seems more of a > > security problem where you were trying two names instead of one. Do > > people like that? > > The nice thing about it is you can have any combination of people with > installation-wide access (create them as joeblow) and people with > one-database access (create them as joeblow@joesdatabase). A special > case for only the postgres user is much less flexible. Oh, yes, clearly a nice addition, but see below. > > It is easy to do, except for the fact we have to > > match pg_hba.conf with a username, though we could do the double-test > > there too, if that isn't too weird. > > It'd probably be better to first look at the flat-file copy of pg_shadow > to determine whether user or user@database is the form to use, and then > run through pg_hba.conf only once using the correct form. Otherwise > there are going to be all sorts of weird corner cases: user might match > a different pg_hba row than user@database does. Problem is that pg_shadow flat file _only_ has users with passwords. I do a btree search of that file, but I am not sure I want to add a dump of _all_ users just to allow this. Do we? > Also, if you do it this way then the substitution only has to be done in > one place: you can pass down the correct form to the backend, which'd > otherwise have to repeat the test to see which username is found. Yes, certainly a big win. What we _could_ do is to allow connections to template1 be unsuffixed by the dbname, but that makes everyone connecting to template1 have problems, and just seemed too weird. Ideas? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Open 7.3 items
On Wed, 2002-08-14 at 14:34, Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > Oh, so try it with and without. I can do that, but it seems more of a > > security problem where you were trying two names instead of one. Do > > people like that? > > The nice thing about it is you can have any combination of people with > installation-wide access (create them as joeblow) and people with > one-database access (create them as joeblow@joesdatabase). A special > case for only the postgres user is much less flexible. > > > It is easy to do, except for the fact we have to > > match pg_hba.conf with a username, though we could do the double-test > > there too, if that isn't too weird. > > It'd probably be better to first look at the flat-file copy of pg_shadow > to determine whether user or user@database is the form to use, and then > run through pg_hba.conf only once using the correct form. Otherwise > there are going to be all sorts of weird corner cases: user might match > a different pg_hba row than user@database does. > > Also, if you do it this way then the substitution only has to be done in > one place: you can pass down the correct form to the backend, which'd > otherwise have to repeat the test to see which username is found. If there is a global 'user', then a database specific 'user@database' should be rejected shouldn't it? Otherwise we wind up with two potential 'user@database' users (globals users are really user@) but with a single ID. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] More CVS Problems
I have been getting this for at least two days: [matthew@zeut src]$ cvs -v Concurrent Versions System (CVS) 1.11.2 (client/server) [matthew@zeut src]$ cvs -z3 -d :pserver:[EMAIL PROTECTED]:/projects/cvsroot co -P pgsql [...] cvs server: Updating pgsql/src/backend/utils/mb/conversion_procs/ascii_and_mic cvs server: failed to create lock directory for `/projects/cvsroot/pgsql-server/src/backend/utils/mb/conversion_procs/ascii_and_mic' (/projects/cvsroot/pgsql-server/src/backend/utils/mb/conversion_procs/ascii_and_mic/#cvs.lock): Permission denied cvs server: failed to obtain dir lock in repository `/projects/cvsroot/pgsql-server/src/backend/utils/mb/conversion_procs/ascii_and_mic' cvs [server aborted]: read lock failed - giving up ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Open 7.3 items
On Wednesday 14 August 2002 02:38 pm, Bruce Momjian wrote: > Tom Lane wrote: > > The nice thing about it is you can have any combination of people with > > installation-wide access (create them as joeblow) and people with > > one-database access (create them as joeblow@joesdatabase). A special > > case for only the postgres user is much less flexible. > > Also, if you do it this way then the substitution only has to be done in > > one place: you can pass down the correct form to the backend, which'd > > otherwise have to repeat the test to see which username is found. > Yes, certainly a big win. What we _could_ do is to allow connections to > template1 be unsuffixed by the dbname, but that makes everyone > connecting to template1 have problems, and just seemed too weird. > Ideas? Appending '@template1' to unadorned usernames, and giving inherited rights across the installation to users with template1 rights? Then you have the unadorned 'lowen' becomes 'lowen@template1' -- but lowen@pari wouldn't have access to template1, right? Or am I misunderstanding the feature? -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] journaling in contrib ...
On Wed, 14 Aug 2002, Bruce Momjian wrote: > Marc G. Fournier wrote: > > Anything in contrib that can be built seperately from the server code, > > that just requires libpq and headers, should be pulled and distributed as > > seperate modules, which has the added benefit that, if listed on GBorg, > > search engines will pick up the modules ... > > > > And the whole arg that someone threw out about 'nobody maintaining them if > > they aren't part of the distribution' ... so? if nobody is maintaining, > > then who is using?? > > Want to hear something funny? They are moving my pgmonitor off gborg > and into the pgaccess. When the move is final, I will add a link on > that gborg page. Ah, so now if I want to use pgmonitor, I have to use pgaccess? guess that is one way to sell pgaccess to the masses *shrug* ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] journaling in contrib ...
Marc G. Fournier wrote: > On Wed, 14 Aug 2002, Bruce Momjian wrote: > > > Marc G. Fournier wrote: > > > Anything in contrib that can be built seperately from the server code, > > > that just requires libpq and headers, should be pulled and distributed as > > > seperate modules, which has the added benefit that, if listed on GBorg, > > > search engines will pick up the modules ... > > > > > > And the whole arg that someone threw out about 'nobody maintaining them if > > > they aren't part of the distribution' ... so? if nobody is maintaining, > > > then who is using?? > > > > Want to hear something funny? They are moving my pgmonitor off gborg > > and into the pgaccess. When the move is final, I will add a link on > > that gborg page. > > Ah, so now if I want to use pgmonitor, I have to use pgaccess? guess that > is one way to sell pgaccess to the masses *shrug* They are moving pgaccess more into the admin role, and pgmonitor fit in with that. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Documentation DTD
Anyone mind if we bump the DTD version to Docbook 4.2? This consists on all users who wish to build docs on installing the 4.2 DTD set, and updating some depreciated tags within the sgml files. comment -> remark docinfo -> appendixinfo, chapterinfo, bookinfo, etc. What it buys is a number of useful tags, SVGs and probably more importantly for the future, xsl and fop support which will probably be important in the future. OpenJade hasn't had a new release in quite a long time -- not to say work isn't needed. Yes, after updating docs to the newer DTD I intend to make them XML compliant to ensure they work with v5 of docbook in the future. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] Standard replication interface?
Reading about the pgmonitor thread and mention of gborg made me wonder about replication and ready ability to uniformly monitor it. Just as pg_stat* tables exist to allow for statistic gathering and monitoring in a uniform fashion, it occurred to me that a predefined set of views and/or tables for all replication implementations may be worthwhile. That way, no matter what replication method/tool is being used, as long as it conforms to the defined replication interfaces, generic monitoring tools can be used to keep an eye on things. Think this has any merit? Greg Copeland signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Open 7.3 items
Lamar Owen <[EMAIL PROTECTED]> writes: > Appending '@template1' to unadorned usernames, and giving inherited rights > across the installation to users with template1 rights? Then you have the > unadorned 'lowen' becomes 'lowen@template1' -- but lowen@pari wouldn't have > access to template1, right? If not, standard things like "psql -l" won't work for lowen@pari. I don't think we can get away with a scheme that depends on disallowing access to template1 for most people. It should also be noted that the whole point of this little project was to do something *simple* ... checking access to some other database to decide what we will allow is getting a bit far afield from simple. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Open 7.3 items
Bruce Momjian <[EMAIL PROTECTED]> writes: > Problem is that pg_shadow flat file _only_ has users with passwords. I > do a btree search of that file, but I am not sure I want to add a dump > of _all_ users just to allow this. Do we? Why not? Doesn't seem like a big penalty ... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] journaling in contrib ...
On Wed, 14 Aug 2002, Bruce Momjian wrote: > Marc G. Fournier wrote: > > On Wed, 14 Aug 2002, Bruce Momjian wrote: > > > > > Marc G. Fournier wrote: > > > > Anything in contrib that can be built seperately from the server code, > > > > that just requires libpq and headers, should be pulled and distributed as > > > > seperate modules, which has the added benefit that, if listed on GBorg, > > > > search engines will pick up the modules ... > > > > > > > > And the whole arg that someone threw out about 'nobody maintaining them if > > > > they aren't part of the distribution' ... so? if nobody is maintaining, > > > > then who is using?? > > > > > > Want to hear something funny? They are moving my pgmonitor off gborg > > > and into the pgaccess. When the move is final, I will add a link on > > > that gborg page. > > > > Ah, so now if I want to use pgmonitor, I have to use pgaccess? guess that > > is one way to sell pgaccess to the masses *shrug* > > They are moving pgaccess more into the admin role, and pgmonitor fit in > with that. Personally, I kinda like to be able to run admin modularized ... they *should* be looking at stuff like webmin, where you can plug-n-play admin functions as required, or horde (http://www.horde.org) ... why would I install pgaccess if all I want to do is monitor? Now, to be able to install pgaccess and have pgmonitor tie into *that* would be cool ... 'bigger is better' is MicroSloth's philosophy ... sounds like the PgAccess guys are adopting it too? :( ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Open 7.3 items
On Wednesday 14 August 2002 03:04 pm, Tom Lane wrote: > Lamar Owen <[EMAIL PROTECTED]> writes: > > Appending '@template1' to unadorned usernames, and giving inherited > > rights across the installation to users with template1 rights? Then you > > have the unadorned 'lowen' becomes 'lowen@template1' -- but lowen@pari > > wouldn't have access to template1, right? > If not, standard things like "psql -l" won't work for lowen@pari. I don't > think we can get away with a scheme that depends on disallowing access > to template1 for most people. Ok, maybe I'm really off base, but if I connect to database pari as lowen@pari, isn't pg_database present there? I just tried here: createdb pari psql pari Welcome to psql, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit pari=# select datname from pg_database; datname acs-test maillabels testing2 template1 template0 pari (6 rows) So AFAICT if I were psql I would parse the unadorned lowen as 'lowen@template1' and connect to template1 if not otherwise specified. If the fully qualified database user (FQDU) is present, parse the database name out and connect to that database, then issue the SQL to do the -l or whatever. The @pari would just override the normal default of template1, right? So a 'psql -U lowen@pari -l ' would connect to database pari (subject to permissions) and select datname from pg_database there. What else am I missing, Tom? ISTM I don't need access to template1 -- although I wasn't necessarily suggesting eliminating that. I was more suggesting: lowen@pari has read access to those parts of template1 necessary for normal functioning, full access (subject ot GRANT/REVOKE) of pari, and no access to other databases; lowen@template1 has access across the install (subject to GRANT/REVOKE, of course). lowen@template1 = lowen (unadorned). That was the answer, I thought, to the question Bruce had. There would be NO unadorned usernames then, and no special handling EXCEPT of the template1 database, which is already a special case. Now, can we support the idea of 'postgres@pari' being a superuser for pari but not for the rest of the install? Meaning no CREATE DATABASE right, as that would require write access to template1? That's OK I believe, as I would assume a 'tied to a database' superuser shouldn't be allowed to create a new database to which he isn't going to have access. The full ramifications of this structure could prove interesting. The supersuperuser 'postgres' becomes postgres@template1 -- template1 becoming the consistent default database (for connecting as well as user membership). As anything added to template1 becomes part of any subsequently added databases, being a user in template1 becomes an installation-wide user. And the user never really has to explicitly state @template1 -- they could just leave off the @template1 and everything works as it does now. Yes, there are complications, but not great ones, no? -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Open 7.3 items
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > Problem is that pg_shadow flat file _only_ has users with passwords. I > > do a btree search of that file, but I am not sure I want to add a dump > > of _all_ users just to allow this. Do we? > > Why not? Doesn't seem like a big penalty ... Well, in most cases pg_pwd doesn't even get created unless someone has a password. We would be creating that file in all cases, or at least in all cases wher db_user_namespace is set, and again, that is a SIGHUP param, so you would need to make sure pg_pwd has the right contents if it was enabled during a sighup. Frankly, I would recommend a new file that just contains user names and is always created. We are basically heading down the road to complexity here. In fact, pg_hba.conf is just a microcosm of how we are going to handle pg_shadow matching. If we create dave@db1, then when dave tries to connect to db1, he comes in as dave@db1, but when he goes to connect to db2, if there is a plain 'dave', he will connect as 'dave' to db2, if possible. If people are OK with that, then I can easily push the double-testing down into the authentication system. It merely means testing the new pg_hba.conf USER column for two values, and pg_shadow for two values, but I would test with @db first. The double testing just seems strange to me because it splits the user namespace into two parts one with @ and one without, and conflicting user parts in the two namespaces do interact when @db does not match. That seems strange, but hey, if no one else thinks it is strange, it is easy to code. It is basically the same as testing pg_pwd, just doing it later in the code. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] journaling in contrib ...
Marc G. Fournier wrote: > > They are moving pgaccess more into the admin role, and pgmonitor fit in > > with that. > > Personally, I kinda like to be able to run admin modularized ... they > *should* be looking at stuff like webmin, where you can plug-n-play admin > functions as required, or horde (http://www.horde.org) ... why would I > install pgaccess if all I want to do is monitor? Now, to be able to > install pgaccess and have pgmonitor tie into *that* would be cool ... > > 'bigger is better' is MicroSloth's philosophy ... sounds like the PgAccess > guys are adopting it too? :( I assume pgmonitor will just be a new tab in the pgaccess window. It is integrated only in that it is part of the tcl scripts supplied. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Standard replication interface?
Greg Copeland <[EMAIL PROTECTED]> writes: > ... it occurred to me that a predefined set of views > and/or tables for all replication implementations may be worthwhile. Do we understand replication well enough to define such a set of views? I sure don't ... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] journaling in contrib ...
On Wed, 14 Aug 2002, Bruce Momjian wrote: > Marc G. Fournier wrote: > > > They are moving pgaccess more into the admin role, and pgmonitor fit in > > > with that. > > > > Personally, I kinda like to be able to run admin modularized ... they > > *should* be looking at stuff like webmin, where you can plug-n-play admin > > functions as required, or horde (http://www.horde.org) ... why would I > > install pgaccess if all I want to do is monitor? Now, to be able to > > install pgaccess and have pgmonitor tie into *that* would be cool ... > > > > 'bigger is better' is MicroSloth's philosophy ... sounds like the PgAccess > > guys are adopting it too? :( > > I assume pgmonitor will just be a new tab in the pgaccess window. It is > integrated only in that it is part of the tcl scripts supplied. Right, but, if its 'integrated', then I have to download the whole thing ... I only want pgmonitor, so how can I get that now? Again, if they do it *properly*, it should be a seperate module you can download, enable in a config file for pgaccess and have show up ... but it should be runnable standalone, with all the extras ... ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Open 7.3 items
Lamar Owen wrote: > On Wednesday 14 August 2002 03:29 pm, Vince Vielhaber wrote: > > Hate to complicate things more, but back to a global username, say > > you have user "lowen" that should have access to all databases. What > > happens if there's already a lowen@somedb that's an unprivileged user. > > Assuming lowen is a db superuser, what happens in somedb? If there's > > a global user "lowen" and you try to create a lowen@somedb later, will > > it be allowed? > > If the user 'lowen' is then expanded to 'lowen@template1' it would be stored > that way -- and lowen@template1 is different from lowen@pari, for instance. > The lowen@template1 user could be a superuser and lowen@pari might not -- but > they become distinct users. Although I do understand the difficulty if the > FQDU isn't stored in full in the appropriate places. So I guess the solution > is that wherever a user name is to be stored, the fully qualified form must > be used and checked against, with @template1 being a 'this user is > everywhere' shorthand. Yes, Vince is on to something with his quote above. If we have users with and without @, we get into the situation where users without @ may become users with @ when their usernames collide with existing user/db combinations already created. The point is that those two namespaces do collide and will cause confusion. Then you start to get into the situation where you always add @ and make @template1 a special case. However, remember that this flag can be turned on and off after initdb, so you need to be able to get in to set things up without great complexity _and_ the @template1 would not be passed in from the client, if for no other reason that the username is only 32 characters. It is the backend doing the flagging, and therefore the user can't say 'I am dave@templatge1' vs 'I am dave@connectdb'. This is how I got to the installuser hack in the first place. In fact, even the install user, typically 'postgres' has a problem because if you create 'postgres@db1', 'postgres' will have trouble connecing to db1 as themselves. I think we can live with one user who is special/global, but not more than one because of the confusion it would create. I can change the way this works, but we need a solution without holes. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] journaling in contrib ...
Marc G. Fournier wrote: > On Wed, 14 Aug 2002, Bruce Momjian wrote: > > > Marc G. Fournier wrote: > > > > They are moving pgaccess more into the admin role, and pgmonitor fit in > > > > with that. > > > > > > Personally, I kinda like to be able to run admin modularized ... they > > > *should* be looking at stuff like webmin, where you can plug-n-play admin > > > functions as required, or horde (http://www.horde.org) ... why would I > > > install pgaccess if all I want to do is monitor? Now, to be able to > > > install pgaccess and have pgmonitor tie into *that* would be cool ... > > > > > > 'bigger is better' is MicroSloth's philosophy ... sounds like the PgAccess > > > guys are adopting it too? :( > > > > I assume pgmonitor will just be a new tab in the pgaccess window. It is > > integrated only in that it is part of the tcl scripts supplied. > > Right, but, if its 'integrated', then I have to download the whole thing > ... I only want pgmonitor, so how can I get that now? > > Again, if they do it *properly*, it should be a seperate module you can > download, enable in a config file for pgaccess and have show up ... but it > should be runnable standalone, with all the extras ... My guess is that it will be integrated and not stand-alone, though tcl apps are so small, you may never notice. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] encrypted passwords
A couple questions regarding encrypted passwords: (1) There was talk of changing the default value of the 'password_encryption' GUC variable for 7.3; AFAIK, this hasn't happened yet. Should this be done? (2) What is the reasoning behind the current storage format of MD5-encrypted passwords? At the moment, we "determine" that a password is stored pre-hashed in pg_shadow by checking if it begins with "md5" and is 35 characters long (the isMD5() macro in libpq/crypt.h). This seems problematic, for a couple reasons: (a) it needlessly overloads the password field: that field should store the password or the digest itself, not meta-data about the authentication process. (b) it makes it difficult to determine if the password is *actually* encrypted, or whether the user just happened to specify an (unencrypted) password of that form. (c) it limits us to using the MD5 algorithm. MD5 is not looking as invincible as it once did, and having the capability to support SHA1 or another algorithm without too much pain would be nice. (3) (Related to 2b above) Shouldn't we reject an attempt by the user to specify an un-encrypted password that matches the isMD5() test? For example: nconway=# create user foo encrypted password 'md5'; CREATE USER nconway=# create user foo2 encrypted password 'somethingelse'; CREATE USER nconway=# select usename, passwd from pg_shadow where usename like 'foo%'; usename | passwd -+- foo | md5 foo2| md51b80a20a1b6cd86eb369f01009b739d3 (The first password is stored "as-is", the second is hashed before being stored.) I don't see a need for the ability to specify pre-hashed passwords, and it makes the whole process of determining the type of password being used more complicated. (4) The naming standard for system catalogs would dictate that the 'passwd' field of pg_shadow actually be named 'usepasswd' or something similar, wouldn't it? The same applies to the 'valuntil field. Cheers, Neil -- Neil Conway <[EMAIL PROTECTED]> PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Open 7.3 items
On Wednesday 14 August 2002 03:49 pm, Bruce Momjian wrote: > Lamar Owen wrote: > > On Wednesday 14 August 2002 03:29 pm, Vince Vielhaber wrote: > > > Hate to complicate things more, but back to a global username, say > > > you have user "lowen" that should have access to all databases. What > > places. So I guess the solution is that wherever a user name is to be > > stored, the fully qualified form must be used and checked against, with > > @template1 being a 'this user is everywhere' shorthand. > Yes, Vince is on to something with his quote above. > If we have users with and without @, we get into the situation where > users without @ may become users with @ when their usernames collide > with existing user/db combinations already created. The point is that > those two namespaces do collide and will cause confusion. But that's the exact problem I was trying to address -- as far as the backend is concerned, there isn't a user without @ -- the incoming connection from a user without @ is translated into a connection coming from user@template1. > Then you start to get into the situation where you always add @ and make > @template1 a special case. However, remember that this flag can be > turned on and off after initdb, so you need to be able to get in to set > things up without great complexity _and_ the @template1 would not be > passed in from the client, if for no other reason that the username is > only 32 characters. It is the backend doing the flagging, and therefore > the user can't say 'I am dave@templatge1' vs 'I am dave@connectdb'. Ok, how do I as a client specify the @dbname for the user? By the database I'm connecting to? That IS a wrinkle. But it does make sense, as lowen@pari won't be able to connect to any other database, right? So, where's this new notation going to get used, again? I must have misunderstood something. So, if we have a namespace collision -- then we have to make the implementation have the restriction that a global username can't exist as a database-specific username -- but two or more database-specific usernames can be the same. So, have a trigger on insertion of a user that checks for an existing user attached to template1 (again, for consistency -- installation wide templates are in template1 -- installation-wide users should be too) -- and then aborts the CREATE USER if so. > This is how I got to the installuser hack in the first place. In fact, > even the install user, typically 'postgres' has a problem because if you > create 'postgres@db1', 'postgres' will have trouble connecing to db1 as > themselves. I think we can live with one user who is special/global, but > not more than one because of the confusion it would create. If you say CREATE USER lowen@pari for the syntax, the create user trips the trigger, which checks for lowen@template1 and aborts if so. CREATE USER lowen@template1 does the same, checking for ANY user lowen. Namespace collision averted? CREATE USER lowen would be the same as CREATE USER lowen@connecteddb, so that the subsuperuser for connecteddb can just CREATE USER without qualifying -- the command line createdb could take the @dbname argument, splitting it out and connecting to the proper database. This has ramifications, I admit. And just saying that unqualified CREATE USER's should create the user@template1 introduces its own problems. > I can change the way this works, but we need a solution without holes. Trigger on the holes. But if I can't (or shouldn't) be able to specify the @dbname from the client, there is GOING to be a namespace collision if installation-wide users of ANY name are allowed (which is what you've already said -- just repeating for emphasis). Or we will have to forbid the postgres user from being reused -- trigger on CREATE USER and abort if user=postgres, I guess. Now as to the toggling of the feature -- what happens when you have lowen@pari and lowen@wgcr coexisting, and you turn off the feature? Which password becomes valid for the resultant singular user lowen? IMHO, if two or more users of the same name occurs, then you shouldn't be able to turn the feature off. I know you've already put alot of work into this, Bruce. But what if the feature isn't toggled, but always there, just waiting to be exploited by CREATE USER user@db, with the default CREATE USER always putting the user into association with the currently connected database? Is there bad overhead involved? Is it something that could break installations not using the feature? Or should CREATE USER with an unqualified username default to @template1 (what I originally thought it should). -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] encrypted passwords
On Wed, 2002-08-14 at 16:32, Neil Conway wrote: > A couple questions regarding encrypted passwords: > > (1) There was talk of changing the default value of the > 'password_encryption' GUC variable for 7.3; AFAIK, this hasn't > happened yet. Should this be done? Since ODBC is capable of using the encryption and I presume JDBC also is, what reason is there for not enforcing it's use? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] journaling in contrib ...
Bruce Momjian writes: > OK, we got _that_ answer. Looks like gborg. Marc really wants to pump > that up. I think if gborg had a different name and looked more like the main site, more people would consider using it without feeling "kicked out". -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] contrib Makefiles
Christopher Kings-Lynne writes: > How can I modify it to build two different C files into two different .so's? That is next to impossible in the current setup. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Open 7.3 items
Bruce Momjian writes: > OK, what I didn't want to do we to over-complexify That's reasonable, but not when you break other things along the way that were themselves meant to decomplexify things. > something that is for only a few users. If it's only for a few users, please send private patches to them. Face it, it's not going to happen. It's going to be in the release notes, everyone's going to see it, and there's going to be a Slashdot thread about how "they" broke the password files. So let's design a feature for everyone. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] pg_dump output portability
Tom Lane writes:
> Most of these look like they would break a lot of people --- for
> example, we can't just arbitrarily change the results of bool_out.
That wouldn't help anyway. I meant to add code in pg_dump (and possibly
the rule recompiler). That doesn't break anything.
> You mean you'd rather eliminate the -N behavior, no? I'd vote for that.
Yes. Or at least switch the default to "portable and readable".
> Again, I'm fairly suspicious of this; it seems likely to result in
> failures to read in the data. You can't just leave data newlines as-is
> for example.
Why not? You'd end up with
INSERT ... VALUES ('multi
line
literal', 'more data');
This is accepted by PostgreSQL now, is legal SQL, and is arguably at least
as readable as octal escape sequences. (Note I'm not talking about doing
this in COPY, which is not portable anyway.)
> > * The expression reverse-engineering code outputs ::text and similar casts
> > in many cases. These should be CAST().
>
> I will vote against this as being a major loss of legibility. Perhaps
> we could compromise on controlling it by a GUC variable, though.
I was afraid of that, but to pick up the theme of the day, I'm not sure if
I want to overcomplexify things that much. ;-)
--
Peter Eisentraut [EMAIL PROTECTED]
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] encrypted passwords
Neil Conway wrote: > A couple questions regarding encrypted passwords: > > (1) There was talk of changing the default value of the > 'password_encryption' GUC variable for 7.3; AFAIK, this hasn't > happened yet. Should this be done? Strange. I had updated the docs and postgresql.conf, but not guc.c, where the default it set. Fixed now. > (2) What is the reasoning behind the current storage format of > MD5-encrypted passwords? At the moment, we "determine" that a > password is stored pre-hashed in pg_shadow by checking if it > begins with "md5" and is 35 characters long (the isMD5() macro in > libpq/crypt.h). This seems problematic, for a couple reasons: > > (a) it needlessly overloads the password field: that field > should store the password or the digest itself, not > meta-data about the authentication process. Yep. That is how FreeBSD handles the password string, and I just followed that. > (b) it makes it difficult to determine if the password is > *actually* encrypted, or whether the user just happened to > specify an (unencrypted) password of that form. Yep, good point. > (c) it limits us to using the MD5 algorithm. MD5 is not > looking as invincible as it once did, and having the > capability to support SHA1 or another algorithm without > too much pain would be nice. > > (3) (Related to 2b above) Shouldn't we reject an attempt by the user > to specify an un-encrypted password that matches the isMD5() test? > For example: > > nconway=# create user foo encrypted password > 'md5'; > CREATE USER > nconway=# create user foo2 encrypted password 'somethingelse'; > CREATE USER > nconway=# select usename, passwd from pg_shadow > where usename like 'foo%'; > usename | passwd > -+- > foo | md5 > foo2| md51b80a20a1b6cd86eb369f01009b739d3 > > (The first password is stored "as-is", the second is hashed before > being stored.) > > I don't see a need for the ability to specify pre-hashed passwords, > and it makes the whole process of determining the type of password > being used more complicated. Well, pg_dump actually loads in the encrypted passwords in that format, so yea, we do need to allow that. Basically, if you want to split out the encryption type from the encryption string, you will need a new pg_shadow column to handle that, and an update to CREATE USER to pass that flag in for pg_dump to use when reloading. > (4) The naming standard for system catalogs would dictate that the > 'passwd' field of pg_shadow actually be named 'usepasswd' or > something similar, wouldn't it? The same applies to the 'valuntil > field. Yes, not sure what other apps access that, but clearly it is inconsistent. Will it cause hardship to fix that? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] encrypted passwords
Rod Taylor wrote: > On Wed, 2002-08-14 at 16:32, Neil Conway wrote: > > A couple questions regarding encrypted passwords: > > > > (1) There was talk of changing the default value of the > > 'password_encryption' GUC variable for 7.3; AFAIK, this hasn't > > happened yet. Should this be done? > > Since ODBC is capable of using the encryption and I presume JDBC also > is, what reason is there for not enforcing it's use? It was delayed until 7.3 so we had 7.2 client apps that understood it so an upgraded would continue to work with older clients. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] pg_dump output portability
Peter Eisentraut wrote: > > I will vote against this as being a major loss of legibility. Perhaps > > we could compromise on controlling it by a GUC variable, though. > > I was afraid of that, but to pick up the theme of the day, I'm not sure if > I want to overcomplexify things that much. ;-) Tomorrow's theme is "sharing". :-) -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Open 7.3 items
OK, I have a new idea. Seems most don't like that 'postgres' is a special user in this context. How about if we just document that they have to create a postgres@template1 user before flipping the switch. That way, there is no special user, no PG_INSTALLER file, and no double-tests for user names. It doesn't give us a global user, but frankly, it seems that such a system is never going to work reliably. Trying to prevent namespace conflicts by checking for users without @ that may match will make @ a special character in the user namespace, and people won't like that. --- Tom Lane wrote: > Lamar Owen <[EMAIL PROTECTED]> writes: > > So the former plain 'postgres' user could still be such to us, to client > > programs, etc, but the backend would assume that that meant > > postgres@template1 -- no namespace collision, and the special case is that > > anyone@template1 has the behavior the unadorned plain user now has. > > The trouble with that scheme is that there is zero interoperability > between the plain-vanilla mode (postgres is postgres in pg_shadow) and > the @-mode (postgres is postgres@template1 in pg_shadow). Flip the > configuration switch, in either direction, and you can't log in anymore. > We'd almost have to make it a frozen-at-initdb setting so that initdb > would know which form to put into pg_shadow for the superuser, and so > that entry wouldn't break thereafter. > > The reason I like the "lowen" vs "lowen@somedb" pattern is that > database-global users can log in the same way whether the feature is > turned on or not; this eliminates the getting-started problem, as well > as the likelihood of shooting yourself in the foot. > > It is true that if you have a global user lowen you'd want to avoid > creating any local users lowen@somedb, and that the existing code > wouldn't be able to enforce that. We could possibly add a few lines > to CREATE USER to warn about this mistake. (It should be a warning not > an error, since if you have no intention of ever using the @-feature > then there's no reason to restrict your choice of usernames.) > > regards, tom lane > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] encrypted passwords
Neil Conway <[EMAIL PROTECTED]> writes: > A couple questions regarding encrypted passwords: > (1) There was talk of changing the default value of the > 'password_encryption' GUC variable for 7.3; AFAIK, this hasn't > happened yet. Should this be done? Hmm. I thought it *was* done, but it looks like Bruce forgot to change the actual guc.c value? The docs and postgresql.conf.sample claim the default is true... 2002-06-14 21:29 momjian * doc/src/sgml/runtime.sgml, src/backend/utils/misc/postgresql.conf.sample: Make encryption of stored passwords the default, as discussed months ago. Seem to be one file short on that commit ... > (2) What is the reasoning behind the current storage format of > MD5-encrypted passwords? The reasoning for the apparent leakage between encrypted and unencrypted formats is it allows pg_dumpall to reload an already-encrypted password, or an admin to copy-and-paste an encrypted password without knowing exactly what the password is. See the archives when this mechanism was being designed (about a year ago I think), if you want the full story. > (b) it makes it difficult to determine if the password is > *actually* encrypted, or whether the user just happened to > specify an (unencrypted) password of that form. By definition, if it looks like that then it's encrypted. I really doubt anyone will want to use a 35-character plaintext password... the apparent conflict is not going to happen in practice AFAICS. > (c) it limits us to using the MD5 algorithm. Nonsense. If we want another method, we just use another prefix. > (3) (Related to 2b above) Shouldn't we reject an attempt by the user > to specify an un-encrypted password that matches the isMD5() test? No, see above. There are actually three cases here: entering a previously encrypted password (in which case do nothing to it regardless of the "encrypted" option), entering an uncrypted password with the "encrypted" option (apply MD5 transform), or entering an uncrypted password with the "unencrypted" option (do nothing). I suppose we could have instead invented an ALREADY_CRYPTED option instead, but we didn't, for reasons I don't recall at the moment; but I think it had something to do with making life easier for pg_dumpall. > (4) The naming standard for system catalogs would dictate that the > 'passwd' field of pg_shadow actually be named 'usepasswd' or > something similar, wouldn't it? The same applies to the 'valuntil > field. Yeah, they are both ancient mistakes. It's not worth trying to fix now however; we'd just break client queries. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] encrypted passwords
Tom Lane wrote: > Hmm. I thought it *was* done, but it looks like Bruce forgot to change > the actual guc.c value? The docs and postgresql.conf.sample claim the > default is true... > > 2002-06-14 21:29 momjian > > * doc/src/sgml/runtime.sgml, > src/backend/utils/misc/postgresql.conf.sample: Make encryption of > stored passwords the default, as discussed months ago. > > Seem to be one file short on that commit ... Fixed. > > (3) (Related to 2b above) Shouldn't we reject an attempt by the user > > to specify an un-encrypted password that matches the isMD5() test? > > No, see above. There are actually three cases here: entering a > previously encrypted password (in which case do nothing to it regardless > of the "encrypted" option), entering an uncrypted password with the > "encrypted" option (apply MD5 transform), or entering an uncrypted > password with the "unencrypted" option (do nothing). > > I suppose we could have instead invented an ALREADY_CRYPTED option > instead, but we didn't, for reasons I don't recall at the moment; > but I think it had something to do with making life easier for > pg_dumpall. I think there wasn't a reason to make the distinction because it could be detected automatically, and an admin copying a password from somewhere else could easily accidentally double-encrypt the password, which then wouldn't work. It also allowed auto-migration to encrypted passwords from an old dump file. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] pg_dump output portability
Peter Eisentraut <[EMAIL PROTECTED]> writes:
> Tom Lane writes:
>> Most of these look like they would break a lot of people --- for
>> example, we can't just arbitrarily change the results of bool_out.
> That wouldn't help anyway. I meant to add code in pg_dump (and possibly
> the rule recompiler). That doesn't break anything.
Ah. But where exactly will you substitute true for 't'? I don't think
pg_dump necessarily knows enough to apply that transformation.
ruleutils could and probably should do it for bool constants, but that's
only a small part of pg_dump output.
>> You mean you'd rather eliminate the -N behavior, no? I'd vote for that.
> Yes. Or at least switch the default to "portable and readable".
Switching the default is definitely fine with me, but I'd lean towards
ripping it out entirely, given that the backend-supplied chunks of stuff
are not going to have extra quotes. We always tell people "always quote
or never quote" a given identifier; pg_dump scripts ought to follow that
rule.
>> Again, I'm fairly suspicious of this; it seems likely to result in
>> failures to read in the data. You can't just leave data newlines as-is
>> for example.
> Why not? You'd end up with
> INSERT ... VALUES ('multi
> line
> literal', 'more data');
> This is accepted by PostgreSQL now, is legal SQL, and is arguably at least
> as readable as octal escape sequences. (Note I'm not talking about doing
> this in COPY, which is not portable anyway.)
Okay, I missed that context; I was thinking of COPY. Yeah, in string
literals in INSERT it seems fairly reasonable to do nothing to the data
except double ' and \. I am a little worried however about
character-set-encoding gotchas. Hiroshi or Tatsuo might have more
insight here.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Open 7.3 items
Bruce Momjian <[EMAIL PROTECTED]> writes: > How about if we just document that they have to create a > postgres@template1 user before flipping the switch. That way, there is > no special user, no PG_INSTALLER file, and no double-tests for user > names. ... and no useful superuser account; if you can't connect to anything except template1 then you ain't much of a superuser. To get around that you'd have to create postgres@db1, postgres@db2, postgres@db3, etc etc. This would be a huge pain in the neck; I think it'd render the scheme impractical. (Keep in mind that anybody who'd be interested in this feature at all has probably got quite a number of databases to contend with.) regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] journaling in contrib ...
On Thu, 15 Aug 2002, Peter Eisentraut wrote: > Bruce Momjian writes: > > > OK, we got _that_ answer. Looks like gborg. Marc really wants to pump > > that up. > > I think if gborg had a different name and looked more like the main site, > more people would consider using it without feeling "kicked out". Well, that's the first I've heard of anything like that, but several points to make here ... Chris Ryan has been actively working with the www group working on the web towards addresssing issues with GBorg, and be, the 'main site' is currently in the process of getting totally overhauled by said group ... Nobody is being kicked out ... we now have an effective method of managing projects without them being part of the centrali distribution ... 'being kicked out', to me, would mean pushing them over to DB2+Sourceforge ;) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] encrypted passwords
Bruce Momjian <[EMAIL PROTECTED]> writes: > It also allowed auto-migration to encrypted passwords from an old dump > file. Ah, right, that was it: we wanted to be able to have a pg_dumpall script containing a mix of crypted and noncrypted passwords in CREATE USER commands be loaded either as-is, or have all the passwords forced to crypted form, depending on the setting of password_encryption. So we didn't really want the CREATE USER commands in the script to say exactly what to do. Therefore, in the design as released the CREATE USER commands emitted by pg_dumpall don't actually say either ENCRYPTED or UNENCRYPTED. We didn't see a need for ALREADY_CRYPTED either, figuring that it would actually be more reliable to deduce that by looking at the data than by having a separate flag for it. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] anoncvs - here we go again!
On 14 Aug 2002, Oliver Elphick wrote: > > cvs server: Updating src/backend/utils/mb/conversion_procs/ascii_and_mic > cvs server: failed to create lock directory for > `/projects/cvsroot/pgsql-server/src/backend/utils/mb/conversion_procs/ascii_and_mic' >(/projects/cvsroot/pgsql-server/src/backend/utils/mb/conversion_procs/ascii_and_mic/#cvs.lock): > Permission denied > cvs server: failed to obtain dir lock in repository > `/projects/cvsroot/pgsql-server/src/backend/utils/mb/conversion_procs/ascii_and_mic' > cvs [server aborted]: read lock failed - giving up Damn, thought I had added a chown at the end of that command ... both are now fixed ... ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_dump output portability
On Wed, 2002-08-14 at 18:20, Bruce Momjian wrote: > Peter Eisentraut wrote: > > > I will vote against this as being a major loss of legibility. Perhaps > > > we could compromise on controlling it by a GUC variable, though. > > > > I was afraid of that, but to pick up the theme of the day, I'm not sure if > > I want to overcomplexify things that much. ;-) > > Tomorrow's theme is "sharing". :-) Brought to you by the letters S, Q, L and the number 99. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Open 7.3 items
On Wed, 14 Aug 2002, Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > I have no personal preference between period and @ or whatever. See if > > you can get some other votes for @ because most left @ when the ORDER BY > > idea came up from Marc. > > FWIW, I still lean to username@database, so I think we're roughly at a > tie. It would be good to get more votes ... Seeing as this is rumbling on I'll throw in my fraction of a vote. I too like the user@database form, partly because it 'reads'. On the other hand I can see the the reasons to like database.user and it does match the style of database.schema.object. Unfortunately for this second form, as '.' is a valid character in a database name then I can see this causing problems, especially with the behind the scenes combination of the two names. I don't see this problem with the '@' form because I can't see that character being used in a 'unqualified' user name. Hmmm...not sure that makes a terribly good arguement for my vote for 'user@db', is there a third choice for us confused folks to go for? A compromise: database@username ? [BTW, I did check and '@' seems to be a valid character in database and user names.] -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
