[GENERAL] Mixed client_encoding database: what to do with..
Hi, Please advise. We have a product with a PostgreSQLdatabase (UTF-8, PG 8.0 and 8.3) It has a (legacy) client app that uses client_encoding = win1521 on the connection since it uses win1252 internally. The legacy app will be around for several years. It cannot change to unicode. There is a new client app too, written in java. It uses client_encoding = unicode. Nice. The problem: if someone inputs a non-win1252 character via the new java app, the legacy app fails. PostgreSQL returns an error: character [..] of encodig UTF-8 has no equivalent in WIN1252. What are my options to stop the legacy app from failing? -Is there a setting to tell PostgreSQL to fail the character gracefully by substuting it? -Should i filter out all non win1252 charachters at the new java app and substitute them? (not so nice, i'd like to have the chars in the db) -Change the DB encoding to WIN1252 (no!) -?any ideas? Greetings, Joek -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Mixed client_encoding database question
Hi all, We have a product with a PostgreSQLdatabase (unicode) It has a (legacy) client app that uses client_encoding = win1521 The legacy app will be around for several years. It cannot change to unicode. There also is a new client app, written in java. It uses client_encoding = unicode. The problem: if someone inputs a non-win1252 character via the new java app, the legacy app fails. PostgreSQL returns an error: character blabla of encodig UTF-8 has no equivalent in WIN1252. What are my options to stop the legacy app from failing? -Is there a setting to tell PostgreSQL to fail the character gracefully by substuting it? -Should i filter out all non win1252 charachters at the new java app and substitute them? (not so nice, i'd like to have the chars in the db) -?more? Greetings, Joek -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Mixed client_encoding database question
On 01/11/2011 02:32 AM, J. Hondius wrote: What are my options to stop the legacy app from failing? IMO there aren't any good ones except fix the legacy app not to be brain-dead about character encodings. -Is there a setting to tell PostgreSQL to fail the character gracefully by substuting it? The problem there is that a round-trip via the client is no longer a safe no-change operation. UPDATE tablename SET x = x WHERE ; no longer means the same as: SELECT x FROM tablename WHERE ; -- client reads value of 'x' UPDATE tablename SET x = 'value-from-client'; This is particularly critical in cases where the client may issue an UPDATE that lists values for all fields, changed and not, when making a change to a field. This is common in ORM systems, which usually list all fields mapped by the object without caring which ones might actually be different from the DB. If you can verify that the legacy app never UPDATEs fields that might contain textual data containing unicode chars inserted/updated via the Java app, you could probably get away with this approach. Alas, I don't think you can do it via Pg settings, you'd have to modify your legacy app's SQL to remap the chars, which you probably can't do. Maybe you could modify the Pg ODBC driver to do the remapping you desire - assuming your legacy app talks to the database via ODBC. -Should i filter out all non win1252 charachters at the new java app and ... thus prolonging your pain. Once the legacy app is gone, you'll be stuck with the limitations of the Java app, which by then you probably won't able to or allowed to change to permit unicode for one reason or another. If you *do* decide to filter out unsupported chars, make sure it's configurable in the app without source code changes and that both modes get covered by your unit tests. Be sure it's ALWAYS ready to switch to unicode as soon as the legacy stuff is gone. This is probably what I'd do in your situation, if I couldn't fix or retire the legacy app. I'd just make really, really sure I could always enable full char encoding support later. substitute them? (not so nice, i'd like to have the chars in the db) Ick. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] database is growing... 1GB per day basis
On 01/11/2011 02:03 PM, Karayappalli, Raghunath (Raghu) wrote: 1.We observe that database is growing very fast – many sets of files with size more than 1 GB. Please see some sample snippets of the file lists: What version of Pg are you using? Got lots of connections in 'IDLE IN TRANSACTION' state as listed in SELECT * FROM pg_stat_activity; ? Is autovacuum enabled? Have you done anything like tuned autovacuum to hardly ever run? What're the results of running 'VACUUM ANALYZE VERBOSE;' in the problem database? -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] migrate hashname function from 8.1.x to 8.4
On 01/11/2011 03:02 PM, nicolas.garfink...@genesis-manlab.com.ar wrote:
well, what i mean is that hashname() function would return a different value
depending the pgsql version.
Yep. It seems to be an undocumented function with no particular defined
behaviour. This isn't especially surprising. I personally wish such
functions were in a different schema or were prefixed with something
like pg_int_ ... but they're not.
SELECT password FROM user WHERE password = hashname('AZALEA')
!!WTF!!
I hope you're not trying to use this as a secure one-way hash function.
If so, find whoever decided to do so, and smack them. This function is
intended for fast hashing for clustering things into random buckets, and
isn't in any way intended to be even vaguely suitable for security use.
See:
http://www.postgresql.org/docs/current/static/pgcrypto.html
Unlike the hashname() function, crypt() etc are well-defined functions
with well-understood, stable results compatible not only between Pg
versions but also between Pg and other software.
Just a side note: our goal with this upgrade is to improve our system's
performance
and stability (which 8.1 is not) while not embarking in any kind of refactoring,
as plans are underway to develop a new system. what we are looking for is
just a quick fix, if there's such thing out there!
Fix your app to use pgcrypto. Use a digest function from pgcrypto,
storing both password digest and password salt. For that matter even
unsalted use of md5() would be better, or crypt() with hardcoded salt,
though both are pretty shocking.
Failing that, extract hashname() from 8.1 and bundle it into a loadable
C function module compatible with 8.1 and 8.4. Call your function
myhash() or something, and use it instead of hashname() in both versions.
A look at at hashname() in 9.0 shows it living in
src/backend/access/hash/hashfunc.c . It calls hash_any(...) in the same
file to do the real work. As it seems pretty well contained, assuming
the 8.1 implementation is similar it should be easy to adapt to use as a
loadable module. I haven't checked 8.1's code to check.
See the tutorial for help on how to implement C extension modules.
Personally I think you're way better off fixing your app to use pgcrypto
and a decent hash function.
--
Craig Ringer
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] migrate hashname function from 8.1.x to 8.4
Indeed Craig, I would like to smack the guy as well, for this and many many
other creepy crawlers in my everyday job!
Violence aside, thanks for your answer. The C module is what I was planning to
do, but was not sure if there is another way. Of course using crypt would be
the right thing to do, but I cannot afford it, as users from our system can
barely remember their password, let alone collect them and recreate them! Of
course I could reset their pwds, but that's gonna be a hard sell to my boss.
Cheers and thanks again!
On 11/01/2011, at 07:29, Craig Ringer cr...@postnewspapers.com.au wrote:
On 01/11/2011 03:02 PM, nicolas.garfink...@genesis-manlab.com.ar wrote:
well, what i mean is that hashname() function would return a different value
depending the pgsql version.
Yep. It seems to be an undocumented function with no particular defined
behaviour. This isn't especially surprising. I personally wish such functions
were in a different schema or were prefixed with something like pg_int_ ...
but they're not.
SELECT password FROM user WHERE password = hashname('AZALEA')
!!WTF!!
I hope you're not trying to use this as a secure one-way hash function. If
so, find whoever decided to do so, and smack them. This function is intended
for fast hashing for clustering things into random buckets, and isn't in any
way intended to be even vaguely suitable for security use.
See:
http://www.postgresql.org/docs/current/static/pgcrypto.html
Unlike the hashname() function, crypt() etc are well-defined functions with
well-understood, stable results compatible not only between Pg versions but
also between Pg and other software.
Just a side note: our goal with this upgrade is to improve our system's
performance
and stability (which 8.1 is not) while not embarking in any kind of
refactoring,
as plans are underway to develop a new system. what we are looking for is
just a quick fix, if there's such thing out there!
Fix your app to use pgcrypto. Use a digest function from pgcrypto, storing
both password digest and password salt. For that matter even unsalted use of
md5() would be better, or crypt() with hardcoded salt, though both are pretty
shocking.
Failing that, extract hashname() from 8.1 and bundle it into a loadable C
function module compatible with 8.1 and 8.4. Call your function myhash() or
something, and use it instead of hashname() in both versions.
A look at at hashname() in 9.0 shows it living in
src/backend/access/hash/hashfunc.c . It calls hash_any(...) in the same file
to do the real work. As it seems pretty well contained, assuming the 8.1
implementation is similar it should be easy to adapt to use as a loadable
module. I haven't checked 8.1's code to check.
See the tutorial for help on how to implement C extension modules.
Personally I think you're way better off fixing your app to use pgcrypto and
a decent hash function.
--
Craig Ringer
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: [pgsql-es-ayuda] Para participantes extranjeros en el Tercer PGDay Latinoamericano.
Yunior, En Argentina el tramite de visado es personal y se realiza en la Embajada de Cuba, incluso tiene un costo fijo. Se realiza personalmente y no se aceptan gestores por el tramite. La visa es un sello en el pasaporte, previo al viaje. Como piensan resolver este aspecto? El visado seria por turismo o trabajo? Saludos cordiales, Guido Barosio 2011/1/10 Ing. Yunior Mesa Reyes ymre...@uci.cu: IMPORTANTE!!!: Para participantes extranjeros en el Tercer PGDay Latinoamericano, a desarrollarse en la Universidad de las Ciencias Informáticas, La Habana, en febrero del 2011. Estimados, luego de realizar todas las coordinaciones previas le relacionamos la información referente a su participación en nuestro evento: ** El evento estará acargo de la gestión de las visas. ** El pasaje deberá pagarlo el interesado. ** El hospedaje y la alimentación durante los días de desarrollo del evento se garantizará por nuestra parte. **Necesitamos antes del miércoles 12 de enero del 2011 los siguientes datos para la confección de las visas de trabajo. DATOS A ENVIAR A NUESTROS CORREOS DE CONTACTO ** Nombre completo. ** # de pasaporte y/o cédula de indetidad. ** Nacionalidad y país de residencia. ** Fecha de entrada y salida del país. (ES NECESARIO QUE TODOS LOS INTERESADOS EN PARTICIPAR EN NUESTRO EVENTO SE INSCRIBAN EN LA PÁGINA DEL EVENTO COMO PARTICIPANTES) Vínculo directo: http://postgresql.uci.cu/node/1 Saludos, Ing.Yunior Mesa Reyes Postgre-SQL Empresarial. DATEC Universidad de las Ciencias Informáticas.Ciudad de la Habana. Cuba. «Se tu el cambio que quieres ver en el mundo...El éxito es el fracaso superado por la perseverancia» -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Mixed client_encoding database ques tion
Hi, I suggest you to recreate database in WIN1521 encoding, so Java will not write unsupported characters. As well you can configure JDBC in way it will allow You to change client encoding, so you can change it to WIN. In both of above You will get error when you will try to write unsupported characters. You may also do http://www.postgresql.org/docs/8.4/interactive/sql-createconversion.html Radosław Smogura, http://softperience.eu On Mon, 10 Jan 2011 16:32:13 +0100, J. Hondius wrote: Hi all, We have a product with a PostgreSQLdatabase (unicode) It has a (legacy) client app that uses client_encoding = win1521 The legacy app will be around for several years. It cannot change to unicode. There also is a new client app, written in java. It uses client_encoding = unicode. The problem: if someone inputs a non-win1252 character via the new java app, the legacy app fails. PostgreSQL returns an error: character blabla of encodig UTF-8 has no equivalent in WIN1252. What are my options to stop the legacy app from failing? -Is there a setting to tell PostgreSQL to fail the character gracefully by substuting it? -Should i filter out all non win1252 charachters at the new java app and substitute them? (not so nice, i'd like to have the chars in the db) -?more? Greetings, Joek -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] migrate hashname function from 8.1.x to 8.4
Nicolas Garfinkiel wrote: I'm trying to upgrade our pgsql from 8.1 to 8.4, but our system's login uses the hashname() function in order to get the proper password validation. Now pgsql's 8.4 hashname function is not compatible with 8.1's function. Do you have any ideas how I can reproduce 8.1 function in 8.4? https://github.com/petere/pgvihash provides the function you're looking for. I agree with Craig's concerns here, but this may let you convert toward a better long-term format more easily. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us PostgreSQL 9.0 High Performance: http://www.2ndQuadrant.com/books
Re: [GENERAL] database is growing... 1GB per day basis
I am using the version 8.2. I have enabled auto vacuum (assuming that you are referring to the autovacuum = on configuration). thanks/ regards, raghu -Original Message- From: Craig Ringer [mailto:cr...@postnewspapers.com.au] Sent: Tuesday, January 11, 2011 2:08 AM To: Karayappalli, Raghunath (Raghu) Cc: pgsql-questi...@postgresql.org Subject: Re: [GENERAL] database is growing... 1GB per day basis On 01/11/2011 02:03 PM, Karayappalli, Raghunath (Raghu) wrote: 1.We observe that database is growing very fast - many sets of files with size more than 1 GB. Please see some sample snippets of the file lists: What version of Pg are you using? Got lots of connections in 'IDLE IN TRANSACTION' state as listed in SELECT * FROM pg_stat_activity; ? Is autovacuum enabled? Have you done anything like tuned autovacuum to hardly ever run? What're the results of running 'VACUUM ANALYZE VERBOSE;' in the problem database? -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Mixed client_encoding database question
On 10 Jan 2011, at 16:32, J. Hondius wrote: Hi all, We have a product with a PostgreSQLdatabase (unicode) It has a (legacy) client app that uses client_encoding = win1521 The legacy app will be around for several years. It cannot change to unicode. There also is a new client app, written in java. It uses client_encoding = unicode. I think you may be able to set the client encoding at the user level. In that case you could use a different user for the legacy app than you use for the java app and have the appropriate client encoding for both cases. try: ALTER ROLE foo SET client_encoding TO 'win1251'; Note: This is just off the top of my head, I haven't actually verified any of this. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4d2c9b0a11871070112136! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] database is growing... 1GB per day basis
I am missing some of your questions: what are the tuning parameters are you referring here that would prevent running auto vacuum? thanks/ regards, raghu -Original Message- From: Karayappalli, Raghunath (Raghu) Sent: Tuesday, January 11, 2011 9:38 AM To: 'Craig Ringer' Cc: 'pgsql-questi...@postgresql.org' Subject: RE: [GENERAL] database is growing... 1GB per day basis I am using the version 8.2. I have enabled auto vacuum (assuming that you are referring to the autovacuum = on configuration). thanks/ regards, raghu -Original Message- From: Craig Ringer [mailto:cr...@postnewspapers.com.au] Sent: Tuesday, January 11, 2011 2:08 AM To: Karayappalli, Raghunath (Raghu) Cc: pgsql-questi...@postgresql.org Subject: Re: [GENERAL] database is growing... 1GB per day basis On 01/11/2011 02:03 PM, Karayappalli, Raghunath (Raghu) wrote: 1.We observe that database is growing very fast - many sets of files with size more than 1 GB. Please see some sample snippets of the file lists: What version of Pg are you using? Got lots of connections in 'IDLE IN TRANSACTION' state as listed in SELECT * FROM pg_stat_activity; ? Is autovacuum enabled? Have you done anything like tuned autovacuum to hardly ever run? What're the results of running 'VACUUM ANALYZE VERBOSE;' in the problem database? -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] migrate hashname function from 8.1.x to 8.4
Thanks a lot Greg! On 11/01/2011, at 12:41, Greg Smith g...@2ndquadrant.com wrote: Nicolas Garfinkiel wrote: I’m trying to upgrade our pgsql from 8.1 to 8.4, but our system’s login uses the hashname() function in order to get the proper password validation. Now pgsql’s 8.4 hashname function is not compatible with 8.1’s function. Do you have any ideas how I can reproduce 8.1 function in 8.4? https://github.com/petere/pgvihash provides the function you're looking for. I agree with Craig's concerns here, but this may let you convert toward a better long-term format more easily. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us PostgreSQL 9.0 High Performance: http://www.2ndQuadrant.com/books
Re: [GENERAL] Mixed client_encoding database: what to do with..
On Tue, Jan 11, 2011 at 3:23 AM, J. Hondius jhond...@rem.nl wrote: -Should i filter out all non win1252 charachters at the new java app and substitute them? (not so nice, i'd like to have the chars in the db) If one of your client's cannot handle the data stored in the DB, then you should not store that data in the DB. So I think this is your best option. This way you are in control of how those characters are mapped. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] database is growing... 1GB per day basis
Dne 11.1.2011 19:11, Karayappalli, Raghunath (Raghu) napsal(a): I am missing some of your questions: what are the tuning parameters are you referring here that would prevent running auto vacuum? There is a bunch of parameters influencing autovacuum - how often it runs, how much work it does, etc. Especially those beginning with 'vacuum' and 'autovacuum' - what values are set for those parameters? Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgres in FIPS
Hello, My organization is in the process of getting a FIPS certification. I was wondering if anyone who has experience with getting their application FIPS certified using postgres. I have read a little bit about this and saw that you need to compile postgres manually using a FIPS capable version of openssl. However, I would like to know how you handled the startup self test of postgres and how you handled errors in the crypto module. I have started investigating compiling postgres using openssl-fips. However, I run into issues when I try to run the make scripts using the fipsld linker. The error I get is: fipsld -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -DDEF_PGPORT=5432 -I../../../src /interfaces/libpq -I../../../src/include -D_GNU_SOURCE -I/usr/local/ssl/fips/include -I/usr/local/include -c -o pg_ctl.o pg_ctl.c fipsld -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv pg_ctl.o -L../../../src/port -l pgport -L../../../src/interfaces/libpq -lpq -L../../../src/port -L/usr/local/ssl /fips/lib -L/usr/local/lib -Wl,--as-needed -Wl,-rpath,'/usr/local/pgsql/lib' -lpgport -lssl -lcrypto -lcrypt -ldl -lm -o pg_ctl ./pg_ctl: error while loading shared libraries: libpq.so.5: cannot open shared object file: No such file or directory Just as a FYI, I am running configure and make in the following manner: ./configure --with-includes=/usr/local/ssl/fips/include:/usr/local/include --with-libraries=/usr/local/ssl/fips/lib:/usr/local/lib --enable-shared -with-openssl --without-readline --without-zlib make CC=fipsld FIPSLD_CC=gcc Thanks for any help
Re: [GENERAL] Postgres in FIPS
Something to think about heredoes your database actually require encryption? Or is the encryption handled between remote user and application? If your database is shared locally on the same server as your application, then you shouldn't need FIPS encryption since the communication between database and application never leaves the system. Unless you have really strict application requirements. Ken On Tue, Jan 11, 2011 at 4:03 PM, M Sabin postg...@sabes.net wrote: Hello, My organization is in the process of getting a FIPS certification. I was wondering if anyone who has experience with getting their application FIPS certified using postgres. I have read a little bit about this and saw that you need to compile postgres manually using a FIPS capable version of openssl. However, I would like to know how you handled the startup self test of postgres and how you handled errors in the crypto module. I have started investigating compiling postgres using openssl-fips. However, I run into issues when I try to run the make scripts using the fipsld linker. The error I get is: fipsld -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -DDEF_PGPORT=5432 -I../../../src /interfaces/libpq -I../../../src/include -D_GNU_SOURCE -I/usr/local/ssl/fips/include -I/usr/local/include -c -o pg_ctl.o pg_ctl.c fipsld -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv pg_ctl.o -L../../../src/port -l pgport -L../../../src/interfaces/libpq -lpq -L../../../src/port -L/usr/local/ssl /fips/lib -L/usr/local/lib -Wl,--as-needed -Wl,-rpath,'/usr/local/pgsql/lib' -lpgport -lssl -lcrypto -lcrypt -ldl -lm -o pg_ctl ./pg_ctl: error while loading shared libraries: libpq.so.5: cannot open shared object file: No such file or directory Just as a FYI, I am running configure and make in the following manner: ./configure --with-includes=/usr/local/ssl/fips/include:/usr/local/include --with-libraries=/usr/local/ssl/fips/lib:/usr/local/lib --enable-shared -with-openssl --without-readline --without-zlib make CC=fipsld FIPSLD_CC=gcc Thanks for any help -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Question about concurrent synchronous and asynchronous commits
I notice on the documentation page about Asynchronous Commit ( http://www.postgresql.org/docs/8.3/static/wal-async-commit.html*)*, it says the follow The user can select the commit mode of each transaction, so that it is possible to have both synchronous and asynchronous commit transactions running concurrently. Now out of curiously, I have a couple questions about the details of this. If I commit asynchronously and then follow that with a synchronous commit, does that flush the asynchronous commit as well? Is the WAL strictly linear in that commit order must always replay back in the order that transactions return on the server, regardless of whether they are asynchronous or synchronous? Thanks, Dan
