[GENERAL] Migration tool from MS SQL server 7.0
Dear Sirs, it exist some tool to convert a SQL script generated from MS SQL 7 to the syntax compatible with Postgresql v. 8.x.x? Best regards. Domenico
Re: [GENERAL] Toolkit for creating editable grid
Florian, your reply is not is not in news.postgresql.org server. How to receive all messages using news.postgresql.org server ? It is tedious to check the newsgroup archives using web interface also. > > I'm in progress of selecting development platform. >pgAdmin3 is written in C++, using the cross-plattform and free GUI-Toolkit wxwindows (www.wxwindows.org I believe). pgAdmin3 includes an editable grid-view (right-click a table, and select "Edit Data"). >> I believe pgAdmin3 is licensed under a BSD-License, so you might even be able to reuse some code. pgAdmin does not have ability to fetch next screenful on page down. It can fetch only first 100 or all rows for editing.This would me major improvement and I dont have enough knowledge to add this to pgadmin. > Including C,C++,Visual Basic, PHP and PL/SQL(!) in the _same_ ranking is just insane. They serve vastly difference purposes (Just imagine a "web-application in PL/SQL, " Why not ? I can create HTML output from pg/SQL and redirect it to browser. I hope that XHTML capabilites will be added to Postgres also. > "a kernel in Visual Basic" VB has good complier which can be used kernel also. > "GUI-Applikation in PHP" To improve imagination you can download Agata Report from http://www.agata.org.br/us/index.php This is is GUI application written in PHP which allows to create reports from Postgres database. Andrus. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Toolkit for creating editable grid
Has anyone used OpenOffice Base? Just a thought. Or Rekall - it's a bit immature, but it might do what you want. The dreaded MS Access can do what you describe in about 4 minutes... EE > I'm in progress of selecting development platform. pgAdmin3 is written in C++, using the cross-plattform and free Including C,C++,Visual Basic, PHP and PL/SQL(!) in the _same_ ranking is just insane. They serve vastly difference purposes (Just imagine a "web-application in PL/SQL, " Why not ? I can create HTML output from pg/SQL and redirect it to browser. I hope that XHTML capabilites will be added to Postgres also. This is a terrible idea. For example, how does a browser request a new page? There's a plethora of good ways to hook a browser up to a database, having a webserver built into an RDBMS is not one of them. "GUI-Applikation in PHP" To improve imagination you can download Agata Report from http://www.agata.org.br/us/index.php This is is GUI application written in PHP which allows to create reports from Postgres database. You might also want to look at PHP-Lens, which has a simple form-builder. Cheers, EE ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] How to store the time zone with a timestamp
I just realized that the type "timestamp with time zone" does NOT store the time zone! (And I just found this support list to look for help.) I am developing an application that stores power (watts) readings from meters located around the world (abridged)-- power_meter_id integer NOT NULL, "ts" timestamp with time zone NOT NULL, power_reading real NOT NULL Not storing the originating TZ is unacceptable. My search of this list finds a number of helpful discussions (including a reference to a TODO; soon I hope!) I don't know database programming and wonder if experienced users could reply with some code they've implemented. I think the best solution is along Karsten's, which I believe goes like this-- 1) "ts" timestamp with time zone NOT NULL, "tz" char( 6 ) NOT NULL, # '-12:00' to '+13:00' 2) A insert trigger that populates "tz" 3) Some function foo() that a) calls timezone( tz, ts ) and b) tacks on tz to the resulting string in a "SELECT power_meter_id, foo( ts, tz ), power_reading;" ANY snip its of code that implements anything related would be appreciated. I can probably gin the complete solution seeing enough examples. Thanks! Andrew ---(end of broadcast)--- TIP 1: 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: [GENERAL] is this a bug or I am blind?
Mage <[EMAIL PROTECTED]> writes: > Tom, I can send you the data I dumped and you can try it out with same > settings. I trust you. Thanks. After digging through it, I can exhibit the problem: in hu_HU locale and LATIN2 encoding, on a Linux box (Fedora Core 4, but probably all glibc installations will do this), I get mage=# select 'potyty'::varchar = 'potty'::varchar; ?column? -- f (1 row) mage=# select 'potyty'::varchar < 'potty'::varchar; ?column? -- f (1 row) mage=# select 'potyty'::varchar > 'potty'::varchar; ?column? -- f (1 row) Needless to say, this makes sorting and btree indexing very unhappy, as they take the trichotomy law as an article of faith ;-) I don't know anything about hu_HU comparison rules, but it appears that strcoll() believes that these two strings should be considered equal. Is that sane? The immediate cause of the problem is that texteq() and textne() have a "fast path" for unequal-length inputs: /* fast path for different-length inputs */ if (VARSIZE(arg1) != VARSIZE(arg2)) result = false; else result = (text_cmp(arg1, arg2) == 0); (text_cmp is what invokes strcoll.) Thus the = operator returns false, while the other two go to strcoll() and then return false. Perhaps the fast-path check is a bad idea, but fixing this is not just a matter of removing that. If we subscribe to strcoll's worldview then we have to conclude that *text strings are not hashable*, because strings that should be "equal" may have different hash codes. And at least in the current PG code, that's not something we can flip on and off depending on the locale --- texteq would have to be marked non hashable in the system catalogs, meaning a big performance hit for *everybody* even if their locale is not this weird. The other approach we could take is to define text comparison as yielding equality only for bitwise-equal strings. If strcoll() returns zero then ask strcmp() for a second opinion. This would mean that we'd sort according to strcoll in the main, but strings that strcoll considers equal but aren't physically identical would sort in codeset order. I can't see that this would do any harm in the context of sorting rules, but the question of what equality means is something for users to answer. Do you think that these two queries ought to yield the same rows in hu_HU locale, or not? select * from common_logins where username = 'potyty'; select * from common_logins where username = 'potty'; regards, tom lane ---(end of broadcast)--- TIP 1: 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: [GENERAL] is this a bug or I am blind?
On Fri, Dec 16, 2005 at 12:12:08PM -0500, Tom Lane wrote: > Thanks. After digging through it, I can exhibit the problem: in hu_HU > locale and LATIN2 encoding, on a Linux box (Fedora Core 4, but probably > all glibc installations will do this), I get I don't know if this is related or not, but did the following issue from a year ago ever get resolved? We were wondering then if the Hungarian locale on some platforms might be causing problems. http://archives.postgresql.org/pgsql-bugs/2004-12/msg00206.php http://archives.postgresql.org/pgsql-bugs/2004-12/msg00228.php -- Michael Fuhr ---(end of broadcast)--- TIP 1: 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: [GENERAL] is this a bug or I am blind?
On Fri, 2005-12-16 at 18:12, Tom Lane wrote: [snip] > I don't know anything about hu_HU comparison rules, but it appears that > strcoll() believes that these two strings should be considered equal. > Is that sane? It is sane in a way, as the "ty" combination is pronounced together as a single consonant in hungarian, and "tty" is the 'strong' version of it. The usual way of making a consonant strong in Hungarian is to double it, which works well for simple consonants, but for "ty" is normally written "tty". So "tyty" and "tty" could be arguably both taken as double "ty", except that the official form is "tty"... but from a pronunciation point of view they ARE equivalent in hungarian. Cheers, Csaba. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] is this a bug or I am blind?
Michael Fuhr <[EMAIL PROTECTED]> writes: > I don't know if this is related or not, but did the following issue > from a year ago ever get resolved? We were wondering then if the > Hungarian locale on some platforms might be causing problems. AFAIR we never did figure that one out. I wasn't able to reproduce it using RHEL, but the complainant was using SLES which might possibly have had different Hungarian locale rules at the time. I'm not sure how much those have changed across different releases of glibc. Now that we see the mechanism for the problem, it might well explain various odd reports we've gotten from people using a number of different locales. I've tended to write these off as locale-vs-encoding pilot error, but maybe they weren't all that. regards, tom lane ---(end of broadcast)--- TIP 1: 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: [GENERAL] is this a bug or I am blind?
Csaba Nagy <[EMAIL PROTECTED]> writes: > ... So "tyty" and "tty" could be arguably both taken as double "ty", > except that the official form is "tty"... but from a pronunciation point > of view they ARE equivalent in hungarian. That's fair enough, but the question is should they be taken as equivalent for string-comparison purposes? (English has plenty of cases where different letter combinations sound alike, but we don't consider them equal because of that. That may not be a good analogy though. Also, if there are cases in other locales where strcoll considers non-identical strings equal, the reasoning for it might be quite different.) regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] is this a bug or I am blind?
On Fri, Dec 16, 2005 at 12:12:08PM -0500, Tom Lane wrote:
> Perhaps the fast-path check is a bad idea, but fixing this is not just
> a matter of removing that. If we subscribe to strcoll's worldview then
> we have to conclude that *text strings are not hashable*, because
> strings that should be "equal" may have different hash codes. And at
> least in the current PG code, that's not something we can flip on and off
> depending on the locale --- texteq would have to be marked non hashable
> in the system catalogs, meaning a big performance hit for *everybody*
> even if their locale is not this weird.
That's true, in the sense that unconverted strings are not hashable.
This is what strxfrm was created for, to return the sorting key for a
string. A quick C program demonstrates that indeed in that locale these
two strings are equal, whereas in en_AU they are not.
$ LC_ALL=hu_HU ./strxfrm potyty potty
String 1: potyty
Strxfrm 1: " ((\x01\x02\x02\x02\x02\x01\x02\x02\x02\x02
String 2: potty
Strxfrm 2: " ((\x01\x02\x02\x02\x02\x01\x02\x02\x02\x02
$ LC_ALL=en_AU ./strxfrm potyty potty
String 1: potyty
Strxfrm 1:
\x1B\x1A\x1F$\x1F$\x01\x02\x02\x02\x02\x02\x02\x01\x02\x02\x02\x02\x02\x02
String 2: potty
Strxfrm 2: \x1B\x1A\x1F\x1F$\x01\x02\x02\x02\x02\x02\x01\x02\x02\x02\x02\x02
I think the only way to make indexes properly locale sensetive would be
to either use strcoll() in all cases, or store the result from
strxfrm() in the index. Anything else will break somewhere.
In any case, we first need to determine which answer is correct, before
we run off trying to fix it.
This is Glibc 2.3.2 on a Debian Linux system.
Have a nice day,
--
Martijn van Oosterhout http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.
#include
#include
#include
#include
void DumpString(unsigned char *s)
{
while(*s)
{
if( isprint(*s) )
printf( "%c", *s );
else
printf( "\\x%02X", *s );
s++;
}
}
int main(int argc, char *argv[])
{
char buffer[100];
int i;
setlocale(LC_ALL,"");
for( i=1; i
pgpyDjU9SPKXP.pgp
Description: PGP signature
Re: [GENERAL] is this a bug or I am blind?
On Fri, 2005-12-16 at 18:52, Tom Lane wrote: > Csaba Nagy <[EMAIL PROTECTED]> writes: > > ... So "tyty" and "tty" could be arguably both taken as double "ty", > > except that the official form is "tty"... but from a pronunciation point > > of view they ARE equivalent in hungarian. > > That's fair enough, but the question is should they be taken as > equivalent for string-comparison purposes? (English has plenty of > cases where different letter combinations sound alike, but we don't > consider them equal because of that. That may not be a good analogy > though. Also, if there are cases in other locales where strcoll > considers non-identical strings equal, the reasoning for it might be > quite different.) Well, I'm not an expert on this one. In any case, hungarian has phonetical writing as opposed to the etymological writing English has. So in hungarian there is a 1 to 1 mapping between the sounds and the signs used to depict them... so pronunciation is somewhat more relevant in sorting I guess. But I'm not a linguist so I won't know for sure. Cheers, Csaba. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] is this a bug or I am blind?
Martijn van Oosterhout writes: > On Fri, Dec 16, 2005 at 12:12:08PM -0500, Tom Lane wrote: >> Perhaps the fast-path check is a bad idea, but fixing this is not just >> a matter of removing that. If we subscribe to strcoll's worldview then >> we have to conclude that *text strings are not hashable*, because >> strings that should be "equal" may have different hash codes. > This is what strxfrm was created for, to return the sorting key for a > string. Ah. So we could redefine hashtext() to return the hash of the strxfrm value. Slow, but a lot better than giving up hash join and hash aggregation altogether... > In any case, we first need to determine which answer is correct, before > we run off trying to fix it. Agreed. regards, tom lane ---(end of broadcast)--- TIP 1: 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: [GENERAL] is this a bug or I am blind?
On Fri, Dec 16, 2005 at 06:54:15PM +0100, Martijn van Oosterhout wrote: > That's true, in the sense that unconverted strings are not hashable. > This is what strxfrm was created for, to return the sorting key for a > string. A quick C program demonstrates that indeed in that locale these > two strings are equal, whereas in en_AU they are not. FWIW, here's some links to Microsoft and MySQL dealing with the same issue, so we're not alone here. Hungarian seems to be a complex language to sort, but it seems that glibc is right in this case. http://blogs.msdn.com/michkap/archive/2005/11/13/491646.aspx http://bugs.mysql.com/bug.php?id=12519 Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgpXaYP4PXMtd.pgp Description: PGP signature
Re: [GENERAL] is this a bug or I am blind?
On Fri, Dec 16, 2005 at 01:06:58PM -0500, Tom Lane wrote: > Ah. So we could redefine hashtext() to return the hash of the strxfrm > value. Slow, but a lot better than giving up hash join and hash > aggregation altogether... Not to put too fine a point on it, but either you want locale-sensetive sorting or you don't. If you do, you need to realise the cost associated with it. Correctness above speed after all. Which reminds me, I should probably finish that COLLATE patch. Then you could choose between: 'putty' = 'putyty' COLLATE C (false) 'putty' = 'putyty' COLLATE 'hu_HU' (true) Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgp551SuWeC4R.pgp Description: PGP signature
Re: [GENERAL] Debug user lib for user define data type
OK, thanks meneer! Ale -Original Message- From: Martijn van Oosterhout [mailto:[EMAIL PROTECTED] Sent: Thursday, December 15, 2005 11:35 AM To: Ale Raza Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Debug user lib for user define data type On Wed, Dec 14, 2005 at 11:52:19AM -0800, Ale Raza wrote: > Hi, > > I am writing a user defined data type in C and want to debug the lib > I am creating for this data type. Wondering if somebody knows how to > link and debug this lib or any document which can help me to do this? > Can I debug this lib without compiling the PostgreSQL source code? > > I use prebuilt RPM package to installed PostgreSQL(7.4.6). > Platform: Red Hat Enterprise Linux AS release 3 (Taroon). Kernel 2.4.21-4.EL > on an i686 Hmm, you need the source code you make your user-defined type, and to have it work you need to compile with exactly the same options. The easiest way to do that is to compile both the server and your lib yourself. That said, it can be done, you just use GDB to attach to the backend and break on your functions. But GBD will probably irritate you unless you compile the server for debugging also. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] is this a bug or I am blind?
On Fri, 16 Dec 2005 11:59:48 -0600, Csaba Nagy <[EMAIL PROTECTED]> wrote: On Fri, 2005-12-16 at 18:52, Tom Lane wrote: Csaba Nagy <[EMAIL PROTECTED]> writes: > ... So "tyty" and "tty" could be arguably both taken as double "ty", > except that the official form is "tty"... but from a pronunciation point > of view they ARE equivalent in hungarian. That's fair enough, but the question is should they be taken as equivalent for string-comparison purposes? (English has plenty of cases where different letter combinations sound alike, but we don't consider them equal because of that. That may not be a good analogy though. Also, if there are cases in other locales where strcoll considers non-identical strings equal, the reasoning for it might be quite different.) Well, I'm not an expert on this one. In any case, hungarian has phonetical writing as opposed to the etymological writing English has. So in hungarian there is a 1 to 1 mapping between the sounds and the signs used to depict them... so pronunciation is somewhat more relevant in sorting I guess. But I'm not a linguist so I won't know for sure. Trouble is, you can never guarantee that you're dealing with actual words. What of you're comparing someone's password that happens to contain combination of letters that act in this way? Cheers, Csaba. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Russ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Fetch statements
Hi, I´m trying to discover some bad SQL statements in our environment but the logs are not helping too much... they say we´ve some very bad fetch statements but I don´t know how to discover the corresponding SELECT statement... How can I find them? Some example: <10.0.1.122(3821) 5072 2005-12-16 10:36:12 BRST FETCH>LOG: duration: 1855.818 ms statement: fetch 1 in cur65c thanks in advance! Benkendorf Yahoo! doce lar. Faça do Yahoo! sua homepage.
Re: [GENERAL] is this a bug or I am blind?
Martijn van Oosterhout writes: > FWIW, here's some links to Microsoft and MySQL dealing with the same > issue, so we're not alone here. Hungarian seems to be a complex > language to sort, but it seems that glibc is right in this case. The mysql bug link has a fairly detailed description, but it dodges the question that we need to answer here: do we want to make a finer-grain distinction than glibc does? In the test data that I got from Mage, the first clue I got was from looking at the results of an ORDER BY versus an index scan: potyos potyty potty potyty potyty potty potty6 potyos potty potyty potyty potty potyty potty6 Actually, the relative order of the "potyty"s and "potty"s is completely random at the moment. You've got to admit that this looks weird: you'd expect a database's ORDER BY output to impose at least a cosmetic ordering on these strings. Per what we've heard, it wouldn't matter much to a Hungarian speaker whether the "potyty"s come before or after the "potty"s, but it seems like it should be consistently one or the other. This argument doesn't really answer the question about whether WHERE username = 'potyty' should match a stored 'potty', however. My inclination is to say "no it shouldn't directly match --- apply a normalization function to your data if you think that tyty should be canonically spelled tty". If we had per-column locales there would be a stronger argument for allowing them to be equal, but right now this folding would occur for all text in a database ... and surely this would be considered a bug for any text that happened not to be Hungarian words. But perhaps my view is overly influenced by performance considerations. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Getting a DB password to work without editing pg_hba.conf, possible?
Hi all,
I am working on an installer for my program that creates a postgres
database and user (the installer is written in perl and runs as 'root').
I want to find a way to let the user set the password on the new
database and have postgres actually ask for it without editing the
default 'pg_hba.conf' file, if at all possible.
I know how to set the password on the user:
CREATE USER "foo" WITH PASSWORD 'secret' CREATEDB NOCREATEUSER;
and from what I can tell there is no way to put a password on a
database. I create the database using:
CREATE DATABASE "bar" OWNER "foo";
From what I read in the docs, this *should* limit access to the 'bar'
database to only the 'postgres' and 'foo' (I can't find now where in the
postgres docs I read that so I may be wrong).
The problems are:
- Connections are limited to the matching system account ('foo' in
this case) which is good, but it doesn't require the password to connect.
- A normal user connected to another database can switch to the 'bar'
database using '\c foo' without requiring a password.
When I have played with the 'pg_hba.conf' file by adding the line:
# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
# Database administrative login by UNIX sockets
local bar foomd5
local all postgres ident sameuser
I find that when I try to connect to the DB 'bar' as the system user
'foo' I *do* get prompted for the password. However, when I try
connecting as another user I get in without being prompted for a
password at all.
Any help with this would be much appreciated!
Madison
--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Madison Kelly (Digimer)
TLE-BU; The Linux Experience, Back Up
Main Project Page: http://tle-bu.org
Community Forum:http://forum.tle-bu.org
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] is this a bug or I am blind?
Martijn van Oosterhout writes:
> On Fri, Dec 16, 2005 at 01:06:58PM -0500, Tom Lane wrote:
>> Ah. So we could redefine hashtext() to return the hash of the strxfrm
>> value. Slow, but a lot better than giving up hash join and hash
>> aggregation altogether...
> Not to put too fine a point on it, but either you want locale-sensetive
> sorting or you don't.
Nobody's said anything about giving up locale-sensitive sorting. The
question is about locale-sensitive equality: does it really make sense
that 'tty' = 'tyty'? Would your answer change in the context
'/dev/tty' = '/dev/tyty'? Are you willing to *not have access* to a
text comparison operator that will make the distinction?
I'm inclined to think that this is more like the occasional need for
accent-insensitive comparisons. It seems generally agreed that you want
something like smash('ab') = smash('áb') rather than making the
strings equal in all contexts.
Of course, not being a native speaker of any of the affected languages,
my opinion shouldn't be taken too seriously ...
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Fetch statements
On Fri, Dec 16, 2005 at 06:28:03PM +, Carlos Benkendorf wrote: > I´m trying to discover some bad SQL statements in our environment > but the logs are not helping too much... they say we´ve some very > bad fetch statements but I don´t know how to discover the > corresponding SELECT statement... How can I find them? What do you mean by "bad"? Are the statements causing errors, or are they just taking longer to run than you think they should? The following log entry suggests the latter. > Some example: > <10.0.1.122(3821) 5072 2005-12-16 10:36:12 BRST FETCH>LOG: duration: > 1855.818 ms statement: fetch 1 in cur65c Do your logs contain the DECLARE statement for the cur65c cursor? If not, are you logging all statements or only those that last longer than a certain amount of time (log_min_duration_statement)? -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Getting a DB password to work without editing pg_hba.conf, possible?
Madison Kelly <[EMAIL PROTECTED]> writes: > I want to find a way to let the user set the password on the new > database and have postgres actually ask for it without editing the > default 'pg_hba.conf' file, if at all possible. There is no such animal as a "database password" in PG. There are user passwords. You can grant or deny a user access to a database altogether, but you can't predicate it on him supplying a password different from his (single) login password. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Getting a DB password to work without editing pg_hba.conf,
Tom Lane wrote: Madison Kelly <[EMAIL PROTECTED]> writes: I want to find a way to let the user set the password on the new database and have postgres actually ask for it without editing the default 'pg_hba.conf' file, if at all possible. There is no such animal as a "database password" in PG. There are user passwords. You can grant or deny a user access to a database altogether, but you can't predicate it on him supplying a password different from his (single) login password. regards, tom lane Thanks for the reply! May I ask then? What *is* considered "best practices" for securing a database in PostgreSQL? Assuming I leave the 'pg_hba.conf' file at it's default values, is there any real point to having a password on a postgresql user account? I've been reading the docs but I guess I am overthinking the problem or missing something obvious. :p Madison -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Madison Kelly (Digimer) TLE-BU; The Linux Experience, Back Up Main Project Page: http://tle-bu.org Community Forum:http://forum.tle-bu.org -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Toolkit for creating editable grid
Andrus wrote: Florian, your reply is not is not in news.postgresql.org server. How to receive all messages using news.postgresql.org server ? It is tedious to check the newsgroup archives using web interface also. Bad, but I guess there is nothing I can do about that... I'm subscriber of the mailinglist, not of the newsgroup... Sorry.. greetings, Florian Pflug smime.p7s Description: S/MIME Cryptographic Signature
[GENERAL] Transacciones Anidadas
Hola Estoy trabajando con Postgres 8.0.3 en Ubuntu. Necesito realizar transacciones anidadas, pero no logro que se aborten transacciones intermedias al abortarse una superior. Ejemplo: BEGIN WORK; INSERT INTO mitabla VALUES (1); BEGIN TRANSACTION; INSERT INTO mitabla VALUES (2); INSERT INTO mitabla VALUES (3); COMMIT TRANSACTION; INSERT INTO mitabla VALUES (4); ROLLBACK WORK; El "ROLLBACK WORK" no aborta la TRANSACTION. Resultado de la consulta: mitabla 1 2 3 (3 rows) Resultado esperado: mitabla (0 rows) Alguna idea?? Gracias. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Toolkit for creating editable grid
Florian, >> your reply is not is not in news.postgresql.org server. > > Bad, but I guess there is nothing I can do about that... I'm subscriber > of the mailinglist, not of the newsgroup... Sorry.. Your messages are digitally signed. Maybe this prevents them from distributing using news protocol ? Andrus. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Toolkit for creating editable grid
On Fri, 2005-12-16 at 13:11, Florian G. Pflug wrote: > Andrus wrote: > > Florian, > > > > your reply is not is not in news.postgresql.org server. > > How to receive all messages using news.postgresql.org server ? It is tedious > > to check the newsgroup archives using web interface also. > > Bad, but I guess there is nothing I can do about that... I'm subscriber > of the mailinglist, not of the newsgroup... Sorry.. You might want to consider setting up a gmail account. It's excellent for reading email from the web. About 1,000 times nicer than the one I've used that comes with Exchange Server (when accessing it with firefox/mozilla) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Getting a DB password to work without editing pg_hba.conf,
On Fri, Dec 16, 2005 at 02:09:52PM -0500, Madison Kelly wrote: > May I ask then? What *is* considered "best practices" for securing a > database in PostgreSQL? Assuming I leave the 'pg_hba.conf' file at it's > default values, is there any real point to having a password on a > postgresql user account? I've been reading the docs but I guess I am > overthinking the problem or missing something obvious. :p If someone can login without being asked for a password, that generally means the system is setup not to ask. I'm not sure what you mean by "default" configuration, since you are probably using the one installed by your distro. It's very hard to see what the problem is unless you post your full pg_hba.conf and the actual command-lines you used, including which UNIX user you used. The two lines you gave would allow the postgres UNIX user to login to any database as himself without a password, and allow foo into bar with md5 authentication. If you are seeing something else you should be explicit how you're logging in. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgpJlM1ACLKBd.pgp Description: PGP signature
Re: [GENERAL] Toolkit for creating editable grid
> Has anyone used OpenOffice Base? Just a thought. Or Rekall - it's a bit > immature, but it might do what you want. The dreaded MS Access > can do what you describe in about 4 minutes... Postgres lacks easy GUI frontend with report generation capabilities like Access. To fix this pgAdmin should be implemented in OpenOffice. This will create a very good Postgres management tool for both administrators and end users. This reduces the development cost since ready-made OpenOffice components can be used. >>>"GUI-Applikation in PHP" >> >>To improve imagination you can download Agata Report from >>http://www.agata.org.br/us/index.php >> >>This is is GUI application written in PHP which allows to create reports >>from Postgres database. >> > You might also want to look at PHP-Lens, which has a simple form-builder. I looked their web site. They have the following suggestion in http://phplens.com/lens/product/ : ... , you will need to ALTER TABLE phplens MODIFY id CHAR(40). MODIFY clause causes error in Postgres. Does this mean that they do'nt bother about Postgres compatibility at all ? Andrus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Getting a DB password to work without editing pg_hba.conf,
Madison Kelly <[EMAIL PROTECTED]> writes: > May I ask then? What *is* considered "best practices" for securing a > database in PostgreSQL? Assuming I leave the 'pg_hba.conf' file at it's > default values, is there any real point to having a password on a > postgresql user account? Well, if there were a single "best practice" then we'd not need to offer so many options ;-). It depends a lot on your needs and environment. On a single-user machine where you're not allowing any remote connections, you might as well use "trust" --- I tend to run all my development installations that way. Ident can be pretty convenient too for local users (I wouldn't trust it for remote connections though). Otherwise you probably need passwords. In any case, this just applies to whether you let someone connect or not. What they can do after they've connected is a different discussion. For that you use SQL privileges (GRANT/REVOKE). regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Getting a DB password to work without editing pg_hba.conf,
Martijn van Oosterhout wrote:
On Fri, Dec 16, 2005 at 02:09:52PM -0500, Madison Kelly wrote:
May I ask then? What *is* considered "best practices" for securing a
database in PostgreSQL? Assuming I leave the 'pg_hba.conf' file at it's
default values, is there any real point to having a password on a
postgresql user account? I've been reading the docs but I guess I am
overthinking the problem or missing something obvious. :p
If someone can login without being asked for a password, that generally
means the system is setup not to ask. I'm not sure what you mean by
"default" configuration, since you are probably using the one installed
by your distro.
It's very hard to see what the problem is unless you post your full
pg_hba.conf and the actual command-lines you used, including which UNIX
user you used. The two lines you gave would allow the postgres UNIX
user to login to any database as himself without a password, and allow
foo into bar with md5 authentication. If you are seeing something else
you should be explicit how you're logging in.
Have a nice day,
Oh shoot, I really wasn't very verbose, was I? Sorry about that.
I am running Debian Sarge with the debian-provided PostgreSQL 7.4 deb
pakage. The 'pg_hba.conf' file I am using (unedited from the one that
was installed with most comments removed) is:
# TYPE DATABASEUSERIP-ADDRESSIP-MASK METHOD
# Database administrative login by UNIX sockets
local all postgres
ident sameuser
#
# All other connections by UNIX sockets
local all all
ident sameuser
#
# All IPv4 connections from localhost
hostall all 127.0.0.1 255.255.255.255
ident sameuser
#
# All IPv6 localhost connections
hostall all ::1
:::::::ident sameuser
hostall all :::127.0.0.1/128
ident sameuser
#
# reject all other connection attempts
hostall all 0.0.0.0 0.0.0.0 reject
That is without the line I added there anymore.
After creating the database and the user this is what I have
(connected to 'template1' as 'postgres'):
template1=# SELECT * FROM pg_database;
datname | datdba | encoding | datistemplate | datallowconn |
datlastsysoid | datvacuumxid | datfrozenxid | datpath | datconfig |
datacl
---++--+---+--+---+--+--+-+---+--
tle-bu|100 |8 | f | t|
17140 | 735 | 3221226208 | | |
template1 | 1 |8 | t | t|
17140 | 735 | 3221226208 | | |
{postgres=C*T*/postgres}
template0 | 1 |8 | t | f|
17140 | 464 | 464 | | |
{postgres=C*T*/postgres}
(3 rows)
template1=# SELECT * FROM pg_shadow;
usename | usesysid | usecreatedb | usesuper | usecatupd |
passwd| valuntil | useconfig
--+--+-+--+---+-+--+---
postgres |1 | t | t| t |
| |
tle-bu | 100 | t | f| f |
md562c7c93e482292a88903ac6b65cdb34c | |
(2 rows)
You can see that I have created a password for the 'tle-bu' user. Now
when I try to connect I get the "psql: FATAL: IDENT authentication
failed for user "tle-bu"" error when I try to connect from the 'madison'
shell account using:
$ psql tle-bu -U tle-bu
Which is good. Though, if I add the user 'madison' to the database as
a user and create a database owned by her:
template1=# CREATE USER madison;
CREATE USER
template1=# CREATE DATABASE "test" OWNER "madison";
CREATE DATABASE
And then connect to the 'test' database as the user 'madison' I can
then use '\c' to connect to the 'tle-bu' database:
$ psql test -U madison
Welcome to psql 7.4.7, 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
test=> \c tle-bu
You are now connected to database "tle-bu".
tle-bu=>
So ultimately my question becomes; How can I prevent other valid
postgres database users from connecting to the 'tle-bu' database
('postgres' being the obvious exception)? Can I do this with some
combination of GRANT and/or REVOKE? If so, does 'GRANT...' restrict
access to only the user(s) mentioned once it is used or do I need to
'REVOKE...' other users first and then 'GRANT...' the 'tle-bu' user?
Or am I missing a design of postgresql (always likely. :P )?
Thanks!!
Madison
--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Madis
[GENERAL] 8.1 build on Solaris has LATIN9?
Hi. I am curious how/why the Pg 8.1.1 build on Solaris SunOS $hostname 5.9 Generic sun4u sparc SUNW,Ultra-250 chose LATIN9 charset as default. Of course we can override this and might if there's a good reason move to LATIN9 as default. Afraid though of having anything go flakey when we import the original DBs with LATIN1 encoding. I saw a message already during an import to the effect of "can't do conversion"... Thanks -- --- Jerry Sievers 305 854-3001 (home) WWW ECommerce Consultant 305 321-1144 (mobilehttp://www.JerrySievers.com/ ---(end of broadcast)--- TIP 1: 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: [GENERAL] Migration tool from MS SQL server 7.0
""dfx"" <[EMAIL PROTECTED]> wrote > Dear Sirs, > it exist some tool to convert a SQL script generated from MS SQL 7 to the > syntax > compatible with Postgresql v. 8.x.x? In general, I bet no - since some functionalities in SQL Server is different from ours, say TableSpace. Regards, Qingqing ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] PL/pgSQL Function Help
I've got an issue with a plpgsql function and I have not been able to find any reference to this situation in searching. I hope that someone can point me in the correct direction. I'm running v8.0.3 and assuming a test set up of: - CREATE TYPE myrec AS ( id int ); CREATE OR REPLACE FUNCTION test(x int) RETURNS myrec AS ' DECLARE output RECORD; BEGIN IF x THEN RETURN NULL; END IF; SELECT INTO output ; RETURN output; END; ' LANGUAGE plpgsql; - Testing the function yields: - xxx=# select * from test(0); id -- (1 row) xxx=# select * from test(1); id (1 row) - My dilema is that the program I'm trying to write this function for (not mine) expects that if (in this case) a 1 is sent, it should have zero rows returned (0 affected rows). It seems that any time I have a return type of a record I'll get a nice NULL record, but it still counts as something. Is there no way in Postgres that I can simply not return anything so I show zero rows? Thanks. -- David ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Getting a DB password to work without editing pg_hba.conf,
Tom Lane wrote: Madison Kelly <[EMAIL PROTECTED]> writes: May I ask then? What *is* considered "best practices" for securing a database in PostgreSQL? Assuming I leave the 'pg_hba.conf' file at it's default values, is there any real point to having a password on a postgresql user account? Well, if there were a single "best practice" then we'd not need to offer so many options ;-). It depends a lot on your needs and environment. On a single-user machine where you're not allowing any remote connections, you might as well use "trust" --- I tend to run all my development installations that way. Ident can be pretty convenient too for local users (I wouldn't trust it for remote connections though). Otherwise you probably need passwords. In any case, this just applies to whether you let someone connect or not. What they can do after they've connected is a different discussion. For that you use SQL privileges (GRANT/REVOKE). regards, tom lane In this case I can't predict what a given install's postgresql will be used for (outside of my program) because it is meant for general distribution (it's a backup program). This obviously makes things a lot more complicated. :p While I developed the program that is what I did, just changed from 'ident' to 'trust'. Now though I am trying to keep what the end user needs to do to a minimum because I've aimed the backup program at more novice users (though not excluively). That is the biggest reason why I am trying to work with the stock 'pg_hba.conf' file (understanding that it can change from one distro to the next). Generally though I've only seen the same 'local...' settings. In my program the database needs to reside on the local machine so as far as I am concerned my only worry is the 'local...' settings. There may be issues with connections coming in over 'host...' connections but I want to deal with one issue at a time. :p So what purpose does the password on the user account accomplish? Is it essentially useless in my scenario? Again, thanks for your help/time! Madison PS - I read about 'GRANT/REVOKE' but I have to admit the postgres docs on the topic didn't help me much. I need something a little more "junior". :p -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Madison Kelly (Digimer) TLE-BU; The Linux Experience, Back Up Main Project Page: http://tle-bu.org Community Forum:http://forum.tle-bu.org -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Getting a DB password to work without editing pg_hba.conf,
Madison Kelly <[EMAIL PROTECTED]> writes:
> Oh shoot, I really wasn't very verbose, was I? Sorry about that.
> [ default pg_hba.conf with only "ident" lines ]
Ah, that explains your question about whether passwords were good for
anything at all. With this pg_hba.conf they aren't --- the server will
never ask for one. You'd want to replace some of the "ident sameuser"
entries with "password" (or more likely "md5") if you want password
challenges instead of checks on the user's Unix login identity. See
the PG administrator docs at
http://www.postgresql.org/docs/8.1/static/client-authentication.html
(adjust version as needed)
>So ultimately my question becomes; How can I prevent other valid
> postgres database users from connecting to the 'tle-bu' database
> ('postgres' being the obvious exception)? Can I do this with some
> combination of GRANT and/or REVOKE?
At the moment you have to do that by adjusting the pg_hba.conf entries.
One possibility is to use "sameuser" in the database field, eg,
# TYPE DATABASEUSERIP-ADDRESSIP-MASK METHOD
# Database administrative login by UNIX sockets
local all postgresident
sameuser
#
# All other connections by UNIX sockets
local sameuserall ident
sameuser
This will let "postgres" connect to anything but other users can only
connect to the database named after them. If you need more flexibility
that that, consider setting up groups named for databases and using
"samegroup" --- then you grant or revoke group membership to let people
into databases or not.
It'd be an obvious extension to provide a direct "LOGIN" privilege
on databases and grant or revoke that, but given the samegroup
workaround it's not a real high-priority feature ...
regards, tom lane
---(end of broadcast)---
TIP 1: 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: [GENERAL] 8.1 build on Solaris has LATIN9?
Jerry Sievers <[EMAIL PROTECTED]> writes: > Hi. I am curious how/why the Pg 8.1.1 build on Solaris > SunOS $hostname 5.9 Generic sun4u sparc SUNW,Ultra-250 > chose LATIN9 charset as default. It probably deduced that from the locale setting (LANG or LC_ALL) that initdb was run in. Try "show lc_collate" if you don't remember what that was. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PL/pgSQL Function Help
"Niblett, David A" <[EMAIL PROTECTED]> writes: > Is there no way in Postgres that I can simply not return > anything so I show zero rows? Make the function return SETOF myrec not just myrec. Then you can return zero or one (or more) myrec's. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PL/pgSQL Function Help
On Fri, Dec 16, 2005 at 03:30:01PM -0500, Tom Lane wrote: > "Niblett, David A" <[EMAIL PROTECTED]> writes: > > Is there no way in Postgres that I can simply not return > > anything so I show zero rows? > > Make the function return SETOF myrec not just myrec. Then you > can return zero or one (or more) myrec's. Dunno if this indicates a possible problem, but the function as posted fails an assertion in an assert-enabled 8.0.5 server (but not in 8.1.1 or 8.2devel). test=> SELECT test(0); server closed the connection unexpectedly #2 0x001f7e30 in ExceptionalCondition (conditionName=0x220b10 "!(typeId == ( (olddata)->t_choice.t_datum.datum_typeid ))", errorType=0x2208e0 "FailedAssertion", fileName=0x220868 "tuptoaster.c", lineNumber=830) at assert.c:51 #3 0x00054ac8 in toast_flatten_tuple_attribute (value=2231056, typeId=2230496, typeMod=2230376) at tuptoaster.c:830 -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Transacciones Anidadas
On Fri, Dec 16, 2005 at 03:23:07PM -0400, Juan Garcs Bustamante wrote: > Estoy trabajando con Postgres 8.0.3 en Ubuntu. Jaime Casanova has already mentioned this list is in English and that questions in Spanish should go to pgsql-es-ayuda. > Necesito realizar transacciones anidadas, pero no logro que se aborten > transacciones intermedias al abortarse una superior. PostgreSQL 8.0 and later have savepoints; they don't support nesting transactions by using multiple BEGIN statements. http://www.postgresql.org/docs/8.0/interactive/tutorial-transactions.html http://www.postgresql.org/docs/8.0/interactive/sql-savepoint.html > Ejemplo: > > BEGIN WORK; > INSERT INTO mitabla VALUES (1); > BEGIN TRANSACTION; > INSERT INTO mitabla VALUES (2); > INSERT INTO mitabla VALUES (3); > COMMIT TRANSACTION; > INSERT INTO mitabla VALUES (4); > ROLLBACK WORK; > > El "ROLLBACK WORK" no aborta la TRANSACTION. It does abort a transaction, but not the one you're thinking of. The second BEGIN has no effect because you're already in a transaction: test=> BEGIN WORK; BEGIN test=> INSERT INTO mitabla VALUES (1); INSERT 0 1 test=> BEGIN TRANSACTION; WARNING: there is already a transaction in progress BEGIN test=> INSERT INTO mitabla VALUES (2); INSERT 0 1 test=> INSERT INTO mitabla VALUES (3); INSERT 0 1 test=> COMMIT TRANSACTION; COMMIT You've committed the transaction, so the three inserted records are in the table, as your query shows: > Resultado de la consulta: > > mitabla > > 1 > 2 > 3 > (3 rows) You didn't explicitly begin another transaction but the fourth insert does appear to have been rolled back, so I'd guess you're using a client that has autocommit disabled. After you committed the first three inserts another transaction was started automatically, and that's what was rolled back. -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Fetch statements
Sorry, I would like to say "take longer than" with "bad" statements... but I´m only logging statements that take longer than 700 ms and DECLARE statements are not logged of course. I think the only way in my case is logging all statements... Am I right? Or is there another way? Thanks! Michael Fuhr <[EMAIL PROTECTED]> escreveu: On Fri, Dec 16, 2005 at 06:28:03PM +, Carlos Benkendorf wrote:> I´m trying to discover some bad SQL statements in our environment> but the logs are not helping too much... they say we´ve some very> bad fetch statements but I don´t know how to discover the> corresponding SELECT statement... How can I find them?What do you mean by "bad"? Are the statements causing errors, orare they just taking longer to run than you think they should? Thefollowing log entry suggests the latter.> Some example:> <10.0.1.122(3821) 5072 2005-12-16 10:36:12 BRST FETCH>LOG: duration: 1855.818 ms statement: fetch 1 in cur65cDo your logs contain the DECLARE statement for the cur65c cursor?If not, are you logging all statements or only those that lastlonger than a certain amount of time (log_min_duration_statement)?-- Michael Fuhr Yahoo! doce lar. Faça do Yahoo! sua homepage.
Re: [GENERAL] Transacciones Anidadas
Juan Garcés Bustamante wrote: Hola Estoy trabajando con Postgres 8.0.3 en Ubuntu. Necesito realizar transacciones anidadas, pero no logro que se aborten transacciones intermedias al abortarse una superior. The main language spoken here is english. If you ask your questions in english, you'll have a much higher chance of someone answering. If you'd rather post in spanish, you could subscribe to a spanish postgresql list (I believe there is one). Ejemplo: BEGIN WORK; INSERT INTO mitabla VALUES (1); BEGIN TRANSACTION; INSERT INTO mitabla VALUES (2); INSERT INTO mitabla VALUES (3); COMMIT TRANSACTION; INSERT INTO mitabla VALUES (4); ROLLBACK WORK; El "ROLLBACK WORK" no aborta la TRANSACTION. You cannot nest transactions that way. Instead, start the outer transaction with "begin", and the inner transaction with "savepoint ". You can then rollback to a savepoint with "rollback to ", and rollback the whole transaction with just "rollback". Instead of commiting a savepoint, you release it. (With "release "). Your example should therefor look like this: begin; insert into mitabla values (1) ; savepoint sp1 ; insert into mitablea values (2) ; insert into mitabla values (3) ; release sp1 ; insert into mitabla values(4) ; rollback; > Resultado de la consulta: mitabla 1 2 3 (3 rows) What probably happened is that your second "begin" was ignored by postgres - with a warning like "Warning: Already inside transaction" i'd guess. Your commit then comitted everything from the first begin on, therefor comitting values 1,2,3. Additionally, I guess that you have autocommit set to "off". This causes psql to start a new transaction for the value "4", because in autocommit=off mode psql will not let you execute commands outside a transaction. Your final rollback then rolled back that transaction, removing 4 from the table, but leaving 1,2,3 in place. Resultado esperado: mitabla (0 rows) Try my corrected example, it should report "0 rows" ;-) greetings, Florian Pflug smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Getting a DB password to work without editing pg_hba.conf,
Tom Lane wrote:
Madison Kelly <[EMAIL PROTECTED]> writes:
Oh shoot, I really wasn't very verbose, was I? Sorry about that.
[ default pg_hba.conf with only "ident" lines ]
Ah, that explains your question about whether passwords were good for
anything at all. With this pg_hba.conf they aren't --- the server will
never ask for one. You'd want to replace some of the "ident sameuser"
entries with "password" (or more likely "md5") if you want password
challenges instead of checks on the user's Unix login identity. See
the PG administrator docs at
http://www.postgresql.org/docs/8.1/static/client-authentication.html
(adjust version as needed)
I've played with the MD5 and I think I will write a little howto or
something similar to explain the options to a user who wants more
security but for now I will default to leaving things as-is.
So ultimately my question becomes; How can I prevent other valid
postgres database users from connecting to the 'tle-bu' database
('postgres' being the obvious exception)? Can I do this with some
combination of GRANT and/or REVOKE?
At the moment you have to do that by adjusting the pg_hba.conf entries.
One possibility is to use "sameuser" in the database field, eg,
# TYPE DATABASEUSERIP-ADDRESSIP-MASK METHOD
# Database administrative login by UNIX sockets
local all postgresident
sameuser
#
# All other connections by UNIX sockets
local sameuserall ident
sameuser
This will let "postgres" connect to anything but other users can only
connect to the database named after them. If you need more flexibility
that that, consider setting up groups named for databases and using
"samegroup" --- then you grant or revoke group membership to let people
into databases or not.
It'd be an obvious extension to provide a direct "LOGIN" privilege
on databases and grant or revoke that, but given the samegroup
workaround it's not a real high-priority feature ...
regards, tom lane
Many thanks for your help clearing that up! If I can vote for the
extension being created, consider this it. Mainly for the reasons I've
mentioned; trying to handle security programatically instead of relying
on the end-user (who may be less technically enclined) doing it. I know
that I could have my program handle the editing of the 'pg_hba.conf'
file but I don't trust myself with doing that write given that order is
important and the wide number of possible configurations.
Madison
--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Madison Kelly (Digimer)
TLE-BU; The Linux Experience, Back Up
Main Project Page: http://tle-bu.org
Community Forum:http://forum.tle-bu.org
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Transacciones Anidadas
On Fri, Dec 16, 2005 at 03:23:07PM -0400, Juan Garcés Bustamante wrote: > Hola Guten Abend ! > Estoy trabajando con Postgres 8.0.3 en Ubuntu. Hm, ich benutze Debian, mit PostgreSQL 7.4 in einem Cluster. Wie das bei Ubuntu so funktioniert, weiß ich nicht so richtig. > Necesito realizar transacciones anidadas, pero no logro que se aborten > transacciones intermedias al abortarse una superior. Ich glaube Du mußt Savepoints benutzen, wenn Du eine Zwischentransaktion innerhalb einer anderen starten willst. Es natürlich klar, daß innerhalb *einer* Transaktion jeder Fehler die gesamte Verarbeitung abbricht ! > Ejemplo: > > BEGIN WORK; > INSERT INTO mitabla VALUES (1); > BEGIN TRANSACTION; > INSERT INTO mitabla VALUES (2); > INSERT INTO mitabla VALUES (3); > COMMIT TRANSACTION; > INSERT INTO mitabla VALUES (4); > ROLLBACK WORK; > > El "ROLLBACK WORK" no aborta la TRANSACTION. Das kann ich mir nicht vorstellen. Bist Du sicher, daß BEGIN TRANSACTION die richtige Syntax ist für das, was Du machen willst ? > Resultado de la consulta: > > mitabla > > 1 > 2 > 3 > (3 rows) Ach so, klar, mE können BEGINs geschachtelt werden, ohne daß ein Problem auftritt. Allerdings beendet dann COMMIT alle begonnenen Transaktionen auf einmal. Da das INSERT ... 4 erst nach dem COMMIT, aber vor dem ROLLBACK kommt, wird es richtig von dem ROLLBACK erfasst und erscheint nicht in der Tabelle. > Resultado esperado: > > mitabla > > > (0 rows) Nee, nee. Deine Erwartung an das Ergebnis ist falsch. > Alguna idea?? Naja, siehe oben :-) > Gracias. Kein Problem. Gern wieder. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
FW: [GENERAL] PL/pgSQL Function Help
Well that kills two birds. I completely didn't understand the difference between SETOF and just RECORD return types. That fixed it up, and for the record here is what the function looks like now. CREATE TYPE myrec AS ( id int ); CREATE OR REPLACE FUNCTION test(x int) RETURNS SETOF myrec AS ' DECLARE output RECORD; BEGIN IF x THEN RETURN; END IF; SELECT INTO output ; RETURN NEXT output; RETURN; END; ' LANGUAGE plpgsql; xxx=# select * from test(1); id (0 rows) xxx=# select * from test(0); id -- (1 row) Thanks Tom! -- David -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Friday, December 16, 2005 3:30 PM To: Niblett, David A Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] PL/pgSQL Function Help "Niblett, David A" <[EMAIL PROTECTED]> writes: > Is there no way in Postgres that I can simply not return anything so I > show zero rows? Make the function return SETOF myrec not just myrec. Then you can return zero or one (or more) myrec's. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] How to store the time zone with a timestamp
BTW, if you wanted a more integrated solution, you could build a custom type that would store the timestamp info. There's a good chance such code would end up being what's used in the server as the official type, too. On Fri, Dec 16, 2005 at 08:57:04AM -0800, Andrew B. Young wrote: > I just realized that the type "timestamp with time zone" does NOT store > the time zone! > (And I just found this support list to look for help.) > > I am developing an application that stores power (watts) readings from > meters > located around the world (abridged)-- >power_meter_id integer NOT NULL, >"ts" timestamp with time zone NOT NULL, >power_reading real NOT NULL > > Not storing the originating TZ is unacceptable. My search of this list > finds a number of > helpful discussions (including a reference to a TODO; soon I hope!) I > don't know > database programming and wonder if experienced users could reply with > some code > they've implemented. > > I think the best solution is along Karsten's, which I believe goes like > this-- > 1) "ts" timestamp with time zone NOT NULL, > "tz" char( 6 ) NOT NULL, # '-12:00' to '+13:00' > 2) A insert trigger that populates "tz" > 3) Some function foo() that >a) calls timezone( tz, ts ) and >b) tacks on tz to the resulting string > in a "SELECT power_meter_id, foo( ts, tz ), power_reading;" > > ANY snip its of code that implements anything related would be appreciated. > I can probably gin the complete solution seeing enough examples. > > Thanks! > Andrew > > ---(end of broadcast)--- > TIP 1: 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 > -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Fetch statements
On Fri, Dec 16, 2005 at 08:49:32PM +, Carlos Benkendorf wrote: > I would like to say "take longer than" with "bad" statements... > but I´m only logging statements that take longer than 700 ms > and DECLARE statements are not logged of course. > > I think the only way in my case is logging all statements... Am I > right? Or is there another way? Check the logging documentation to be sure, but I think you'll have to log all statements. -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Toolkit for creating editable grid
"Andrus" <[EMAIL PROTECTED]> writes: >> Has anyone used OpenOffice Base? Just a thought. Or Rekall - it's >> a bit immature, but it might do what you want. The dreaded MS >> Access can do what you describe in about 4 minutes... > > Postgres lacks easy GUI frontend with report generation capabilities > like Access. > > To fix this pgAdmin should be implemented in OpenOffice. This will > create a very good Postgres management tool for both administrators > and end users. This reduces the development cost since ready-made > OpenOffice components can be used. Unfortunately, we don't run OpenOffice.org *or* pgAdmin on any of our IBM pSeries clusters, so for us, that's a pretty much useless answer. The folks I'm thinking of here *are* material to the matter, as we have actually been trying to get some reasonably material budget put together to come up with a report generation "solution," albeit with a mandate that it can function in X-less environments. The above biases aside, OpenOffice.org has seemed terribly heavyweight any time I have used it for anything database-related. -- "cbbrowne","@","ntlug.org" http://cbbrowne.com/info/sgml.html "Why use Windows, since there is a door?" -- <[EMAIL PROTECTED]> Andre Fachat ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] 8.1 build on Solaris has LATIN9?
Please forgive the empty reply preceeding this note. Tom Lane <[EMAIL PROTECTED]> writes: > Jerry Sievers <[EMAIL PROTECTED]> writes: > > Hi. I am curious how/why the Pg 8.1.1 build on Solaris > > SunOS $hostname 5.9 Generic sun4u sparc SUNW,Ultra-250 > > chose LATIN9 charset as default. > > It probably deduced that from the locale setting (LANG or LC_ALL) > that initdb was run in. Try "show lc_collate" if you don't remember > what that was. Thanks for this info. Interesting, it was iso8859-15. I inidb a new cluster and this time, I get SQL_ASCII. This is without a hint of language or locale stuff in the ENV. Guessing the postgres user's env has changed since the original cluster creation. Have a great weekend. > regards, tom lane > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org > -- --- Jerry Sievers 305 854-3001 (home) WWW ECommerce Consultant 305 321-1144 (mobilehttp://www.JerrySievers.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] is this a bug or I am blind?
Tom Lane <[EMAIL PROTECTED]> writes: > The other approach we could take is to define text comparison as > yielding equality only for bitwise-equal strings. If strcoll() returns > zero then ask strcmp() for a second opinion. Fwiw this has come up before on this list and it was discovered this is effectively what Perl does, probably for similar motivations wrt to hashes. I think it's probably the least bad solution, even if it's not really the right thing. -- greg ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Getting a DB password to work without editing pg_hba.conf,
[EMAIL PROTECTED] (Madison Kelly) writes: >In this case I can't predict what a given install's postgresql > will be used for (outside of my program) because it is meant for > general distribution (it's a backup program). This obviously makes > things a lot more complicated. :p No, it oughtn't. You shouldn't try to impose anything about this onto the users. There are really only two options you need to concern yourself about: 1. Perhaps a password may be needed in your configuration. 2. Perhaps it won't. Any number of possible causes: - Your user may be considered "trusted"; - The password may be stored in ~/.pgpass - Perhaps in future, authentication may come as some form of SSH key, stored in a directory somewhere... >While I developed the program that is what I did, just changed > from 'ident' to 'trust'. Now though I am trying to keep what the end > user needs to do to a minimum because I've aimed the backup program > at more novice users (though not excluively). That is the biggest > reason why I am trying to work with the stock 'pg_hba.conf' file > (understanding that it can change from one distro to the > next). Generally though I've only seen the same 'local...' settings. What you may want to do, then is to provide _documentation_ to suggest how they might manage pg_hba.conf. If people think you're telling them how to manage security, and their ideas aren't the same as yours, that'll point people away from your software. With Slony-I, that was one of the "design choices." It requires having a database superuser around, but Slony-I does NOT attempt to impose anything about what authentication methods you prefer to use. To run Slony-I, you have to use authentication conforming with what the environment requires. I believe Jan Wieck's preferences are to be able to use TRUST; the thought is that you should only be running replication in an environment that you already know to be secured, where you can trust anyone that has access to the database hosts. I can mostly go along with that. In our production environments, however, we use md5 authentication, because there are others setting security policy that don't think the same way about it as Jan does. Fortunately, Slony-I wasn't designed to require Jan's policy preferences. It can conform to various kinds of policies. Your program ought to do the same. >In my program the database needs to reside on the local machine > so as far as I am concerned my only worry is the 'local...' > settings. There may be issues with connections coming in over > 'host...' connections but I want to deal with one issue at a > time. :p It shouldn't much matter where the database is. >So what purpose does the password on the user account accomplish? > Is it essentially useless in my scenario? The password is an authentication token that *may* be required. For *your* purposes, it doesn't matter if it "accomplishes" anything; it doesn't matter if it is "useful." It only ought to matter that you *may* need a password to pass in as part of the DSN used to connect to the database. -- select 'cbbrowne' || '@' || 'acm.org'; http://cbbrowne.com/info/nonrdbms.html Signs of a Klingon Programmer #7: "Klingon function calls do not have 'parameters' -- they have 'arguments' -- and they ALWAYS WIN THEM." ---(end of broadcast)--- TIP 1: 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: [GENERAL] How to store the time zone with a timestamp
On Fri, Dec 16, 2005 at 03:18:04PM -0600, Jim C. Nasby wrote: > BTW, if you wanted a more integrated solution, you could build a custom > type that would store the timestamp info. There's a good chance such > code would end up being what's used in the server as the official type, > too. Well, I wrote a module for storing arbitrary tags with base types. One of the uses would be for a timestamp that remembers the timezone. I havn't received all that much feedback on it which leads me to conclude timestamps-remebering-timezones are not a heavily requested feature. It's probably on the same level as the ENUM suggested a while ago. Interesting, but not enough to get it included into core. http://svana.org/kleptog/pgsql/taggedtypes.html That said, I'd love for a variation of this to be included since I beleive it has a lot of applications not thought of yet. If user-types could define their own typmod values, that'd make it complete. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgp7Xp7ZwhTHo.pgp Description: PGP signature
[GENERAL] Versioning Schema/Stored Procedures
hi all, We installed a first version (1.0.0.1) of our schema. then came a few patches we had for a few stored procedures and tables (1.0.0.2). Then even more (1.0.0.3) (1.0.0.4). Some chose to upgrade to version 1.0.0.3 and stick to it, while some others chose to upgrade to 1.0.0.4. Now when i have some more schema updates, how should i find out what (incremental) updates the client needs? One way might be to store [ 'version', 'schema', 'Date_time_change', 'User_who_Changed' ] in a table. But i want to store the versions for each table/stored procedures/views. I could create a table to store these components with similar details. But i do not want to duplicate the work. I just want to upgrade the components i need to, so as to avoid downtime for teh applications taht do not need the component. Is it possible to modify pg_class to have another 'version' column so that i can version each relation and other components? Is there a better way to do schema versioing to the level of tables, stored procedures and views? thanks, vish
Re: [GENERAL] How to store the time zone with a timestamp
On Fri, Dec 16, 2005 at 11:25:22PM +0100, Martijn van Oosterhout wrote: > On Fri, Dec 16, 2005 at 03:18:04PM -0600, Jim C. Nasby wrote: > > BTW, if you wanted a more integrated solution, you could build a custom > > type that would store the timestamp info. There's a good chance such > > code would end up being what's used in the server as the official type, > > too. > > Well, I wrote a module for storing arbitrary tags with base types. One > of the uses would be for a timestamp that remembers the timezone. I > havn't received all that much feedback on it which leads me to conclude > timestamps-remebering-timezones are not a heavily requested feature. > It's probably on the same level as the ENUM suggested a while ago. > Interesting, but not enough to get it included into core. > > http://svana.org/kleptog/pgsql/taggedtypes.html > > That said, I'd love for a variation of this to be included since I > beleive it has a lot of applications not thought of yet. If user-types > could define their own typmod values, that'd make it complete. Wow, that's damn cool and useful. You should put it on pgFoundry (at least as a project that links back to your site) so people can find it there. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 1: 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: [GENERAL] Versioning Schema/Stored Procedures
The way I handle this is to version the entire schema and have scripts that know how to upgrade from one version to another. If you think about it, you really want/need everything in the database to be designed to run together anyway. I've yet to find a case where I'd want some of the stuff in the schema to be older than other stuff. case where it makes sense to ha On Fri, Dec 16, 2005 at 02:41:58PM -0800, vishal saberwal wrote: > hi all, > > We installed a first version (1.0.0.1) of our schema. then came a few > patches we had for a few stored procedures and tables (1.0.0.2). Then even > more (1.0.0.3) (1.0.0.4). Some chose to upgrade to version 1.0.0.3 and stick > to it, while some others chose to upgrade to 1.0.0.4. > > Now when i have some more schema updates, how should i find out what > (incremental) updates the client needs? > > One way might be to store [ 'version', 'schema', 'Date_time_change', > 'User_who_Changed' ] in a table. But i want to store the versions for each > table/stored procedures/views. I could create a table to store these > components with similar details. But i do not want to duplicate the work. I > just want to upgrade the components i need to, so as to avoid downtime for > teh applications taht do not need the component. > > Is it possible to modify pg_class to have another 'version' column so that i > can version each relation and other components? > Is there a better way to do schema versioing to the level of tables, stored > procedures and views? > > thanks, > vish -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Negative offsets
CSN wrote: > I was playing around with negative offsets: > > select * from table1 order by col1 offset -5 limit 25; > select * from table1 order by col1 offset -25 limit > 25; > select * from table1 order by col1 offset -250 limit > 25; > > They all return the same resultset (offset 0). Is > there even any point in allowing negative offsets - > such as maybe someday they'll offset backwards? Actually we allow negative offsets and limits. Seems we should disallow them. Yes, it would be interesting if they did offsets relative to the end of the result set. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (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: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Negative offsets
On Fri, Dec 16, 2005 at 07:14:42PM -0500, Bruce Momjian wrote: > Actually we allow negative offsets and limits. Seems we should disallow > them. Yes, it would be interesting if they did offsets relative to the > end of the result set. Either interesting or a great way to introduce bugs... AFAIK you can always get the same behavior just by reversing the sort order, so I'd lean towards not allowing negative offsets. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] client_encoding values
wrote: > Hello! > > Please, help. I need to get complete list of 'client_encoding' > values supported by the PostgreSQL Server. Can I somehow get these > values using select query or function call? Uh, we have a pg_conversion table, but I don't know how to tell which are available as client encodiings. I hope that helps. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (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 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] is this a bug or I am blind?
At 01:40 PM 12/16/2005 -0500, Tom Lane wrote:
Nobody's said anything about giving up locale-sensitive sorting. The
question is about locale-sensitive equality: does it really make sense
that 'tty' = 'tyty'? Would your answer change in the context
'/dev/tty' = '/dev/tyty'? Are you willing to *not have access* to a
text comparison operator that will make the distinction?
I'm inclined to think that this is more like the occasional need for
accent-insensitive comparisons. It seems generally agreed that you want
something like smash('ab') = smash('áb') rather than making the
strings equal in all contexts.
I agree.
I would prefer for everything to be compared without any
collation/corruption by default, and for there to be a function to pick the
desired comparison behaviour ( Can all that functionality be done with the
collate clause?).
Because most databases are multi-locale whether the humans are aware of it
or not:
The Computer "locale", human locale #1, unknown/international locale, human
locale #2, ...
In a column for license keys, "tty" should rarely be the same as "tyty".
In a column for base64 data (crypto hashes, etc) "tty" should NEVER be the
same as "tyty".
In a column for domain names, I doubt it is clear whether you want to match
tty.ibm.hu just because tyty.ibm.hu exists.
But in a column for license owner names, one might want "tty" and "tyty" to
be the same - one might have to have a multicolumn index depending on the
owner's locale of choice.
I recommend that for these reasons initdb should always pick "no mangled"
text by default, no matter what the locale setting is. And that users
should be advised of the potential consequences of mangling or I would even
say corrupting all text in their databases by default.
Regards,
Link.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
