Re: [GENERAL] PostgreSQL 9.0 RPMs for RHEL 6 and Fedora 14 released
On Sun, 2010-11-21 at 12:39 +0300, Allan Kamau wrote: I am unable to obtain (using yum) a version of pgAdmin3 that can connect fruitfully to postgreSQL 9.x. My installation reports that the version I do have 1.10.5 is the latest. Should be fixed as of yesterday. Regards, -- Devrim GÜNDÜZ PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer PostgreSQL RPM Repository: http://yum.pgrpms.org Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
[GENERAL] RESET ROLE and search_path, Connection pool
Hello, We are thinking about using a (java based) connection pool. An issue is that there are many different users to connect. My idea is to only have superuser connections in the pool and change the connection role (with SET ROLE) each time a user pick a connection there. However, I'have noticed that the RESET ROLE command does not reset the search_path to its original. Could this be considered as bug (see below) ? Are there other side effects to expect, or a better approach to implement a multi user connection pool ? (I'm also using GUC variables at some places, currently bound to the pg_backend_pid(). Here I will have to add the current_user within the GUC name to better isokate them) show search_path; $user,public SET ROLE 'xxx'; show search_path; xxx,public RESET ROLE show search_path; xxx,public best regards, Marc Mamin -- 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] PG84 and SELinux
On Thu, December 2, 2010 15:32, James B. Byrne wrote: On Thu, December 2, 2010 15:23, Devrim GÜNDÜZ wrote: On Wed, 2010-12-01 at 16:54 -0500, Tom Lane wrote: AFAIK, the Red Hat RPMs work out-of-the-box with SELinux; They should -- we are using the same routines for initdb'ing. I will do a touch /.autorelabel and restart the server before I try again. If there was just something odd about the SELinux contexts on that particular host then that should clear it up. I will report whichever way it goes thereafter. I restarted the server this morning, waited for the relabel to finish ( a very long time ), and then upgraded to pg-8.4 without any problems. SSL works fine as well. I can only infer that something went seriously wrong with the SELinux context labels on that host. Thank you for the help. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] encode(bytea_value, 'escape') in PostgreSQL 9.0
The old 'escape' encoding used by PostgreSQL 8.4 and prior was pretty helpful for getting human-readable strings in psql. It seems this functionality was removed in PostgreSQL 9.0. Was this an accident or a deliberate decision? Could we get it back, please? -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- 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] could not accept SSPI security context
Passing null instead of empty string for the principal shouldn't make any difference as an empty CLR-String should be marshalled to an empty (null terminated) C-String. The problem could also be in InitializeSecurityContext which happens in respnse to AuthenticationGSSContinue. If you happen to find out what the problem is - please let me know or wrap a patch for the Npgsql development team so that we can fix it. I'm sorry that I can't be very helpful in tracking down this issue, but I don't have a Windows 2008 Server, or even any other Kerberos-Setup available to test it. Actually the SSPI-Patch was something I once put out knowing that I'll be bogged down by other stuff immediatley afterwards, and it didn't see much maintainace since then. There are some oversights like // TODO: correct exception throw new Exception(); in NpgsqlState.cs and the fact that the Continue method (in SSPIHandler.cs) returns the (opaque) secbuffer as ASCII-String (encoded by System.Text.Encoding.ASCII.GetString(Byte[])) should even be considered as a bug (even though to my knowledge it didn't cause any problems until now). In other words - I'd be willing to overhaul the wohle thing if I find a good reason to do so (and some time). Regards, Brar On Fri, 3 Dec 2010 06:32:17 +0100, Reto Schöning reto.schoen...@gmail.com wrote: thanks, and sorry for my slow responses, I'm bogged down by other stuff. I plan to get the source and try some things like - force kerberos to see if the reason that NTLM is used is that kerberos fails and hope for some hints at the cause of the failure - pass null instead of empty string for the principal - check out the principal on the calling thread etc. and post the results to the list. Could take I week or so until a get to that though. Regards, Reto 2010/11/29 Brar Piening b...@gmx.de mailto:b...@gmx.de On Mon, 29 Nov 2010 15:27:35 +0100, Reto Schöning reto.schoen...@gmail.com mailto:reto.schoen...@gmail.com wrote: I just heard back from our IT. There's nothing in the logs for this connection attempt, but they noted in the Npgsql log that the authentication was attempted using NTLM. However our domain controller no longer supports NTLM, but only LDAP(s) and kerberos (it's a Windows 2008 server). From the docs I understand that with SSPI, pg should try kerberos first and fall back to NTLM. This works when connecting from psql. Maybe Npgsql goes straight for NTLM, at least when using it the way I do? Both are using the Negotiate SSP authentication package http://msdn.microsoft.com/en-us/library/aa378748%28v=VS.85%29.aspx Npgsql (SSPIHandler.cs): int status = AcquireCredentialsHandle( , negotiate, SECPKG_CRED_OUTBOUND, IntPtr.Zero, IntPtr.Zero, IntPtr.Zero, IntPtr.Zero, ref sspicred, out expire ); libpq (fe-auth.c): /* * Send initial SSPI authentication token. * If use_negotiate is 0, use kerberos authentication package which is * compatible with Unix. If use_negotiate is 1, use the negotiate package * which supports both kerberos and NTLM, but is not compatible with Unix. */ r = AcquireCredentialsHandle(NULL, use_negotiate ? negotiate : kerberos, SECPKG_CRED_OUTBOUND, NULL, NULL, NULL, NULL, conn-sspicred, expire); It should be a one line patch to force Npgsql into using kerberos but I can't see any reason why negotiate should act differently between Npgsql and libpq. Regards, Brar
Re: [GENERAL] how can i bugfix idle in transaction lockups ?
On Fri, Dec 3, 2010 at 12:31 AM, Jonathan Vanasco postg...@2xlp.com wrote: begin w/o commit or rollback? and thanks. you've been very helpful! On Nov 30, 2010, at 2:21 PM, Merlin Moncure wrote: Begin w/o commit is a grave application error and you should consider reworking your code base so that it doesn't happen (ever). meaning, you opened a transaction (with 'begin') and didn't close it with 'commit' (or 'start/end' etc). You opened a transaction but didn't close it. Don't ever do this, and don't ever leave a transaction open waiting on indeterminate events, like user input. merlin -- 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] PG84 and SELinux
On Wed, December 1, 2010 16:54, Tom Lane wrote: James B. Byrne byrn...@harte-lyne.ca writes: Earlier today I attempted to upgrade a production server from 8.1 to 8.4 using the pgdg-84-centos.repo. I say attempted because I could never get it to support ssl connections and as that is a requirement I had to roll back to 8.1. Can't comment on that without a lot more detail. On Fri, December 3, 2010 07:40, James B. Byrne wrote: I restarted the server this morning, waited for the relabel to finish ( a very long time ), and then upgraded to pg-8.4 without any problems. SSL works fine as well. . . I wrote too soon. What I did was uncomment the ssl option. I neglected to change the setting from off to on. When I try to start the server with ssl=on it fails with this error: Auto configuration failed 29006:error:0E065068:configuration file routines:STR_COPY:variable has no value:conf_def.c:629:line 207 I have checked gpsql/data and the server.key and server.crt files are both present: -rw--- 1 postgres postgres 5213 Dec 12 2007 server.crt -rw--- 1 postgres postgres 1675 Dec 12 2007 server.key The only change made in the postgresql.conf file that triggered this was changing 'off' to 'on' for ssl. Changing it back to 'off' makes the problem disappear. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] Looking for auto starting procedures
Hello ! Yes, thanks, I am just trying to lern the difference. I am comin from Sql Server, but I am not a experienced db developer. But in Sql Server, you may hack your tests just in a direct statement and if it runs, wrap/put it in the function or procedure. Will need some time ;-) Thanks a lot! br++mabra -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Alvaro Herrera Sent: Friday, December 03, 2010 1:22 AM To: mabra Cc: pgsql-general Subject: Re: [GENERAL] Looking for auto starting procedures Excerpts from mabra's message of jue dic 02 20:04:36 -0300 2010: I've just not understood, when I have to write a function to test sql code and when I can do it interactively. In Postgres, PL/pgSQL and SQL are two different languages. There are things in PL/pgSQL that you cannot do in pure SQL. RAISE is one of them. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] RESET ROLE and search_path, Connection pool
On Fri, Dec 3, 2010 at 5:13 AM, Marc Mamin m.ma...@intershop.de wrote: Hello, We are thinking about using a (java based) connection pool. An issue is that there are many different users to connect. My idea is to only have superuser connections in the pool and change the connection role (with SET ROLE) each time a user pick a connection there. Tangential to your question, but important: Obviously each user could use RESET ROLE and become the super user. This means that every piece of code that uses this pool needs to have security appropriate for code using the super user. i.e. Whatever, it's just using a read-only role, nothing bad can happen is no longer a valid argument (if it ever was). Do you have that much faith / trust in every user? * user in quotes because I'm guessing you are referring to different portions of your application / application suite and hopefully not individual persons. Derrick
Re: [GENERAL] Looking for auto starting procedures
Le 03/12/2010 21:22, manfred.braun a écrit : [...] Yes, thanks, I am just trying to lern the difference. I am comin from Sql Server, but I am not a experienced db developer. But in Sql Server, you may hack your tests just in a direct statement and if it runs, wrap/put it in the function or procedure. You can use the DO command in 9.0 to do just that. -- Guillaume http://www.postgresql.fr http://dalibo.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Table design - postgresql solution
Hi, I have a bit of a DB design question, associated with postgresql in particular, hopefully thinking it could solve my dilemma. This is my setup of 3 tables: Table_1 id_t1 name date_of_discovery history Table_2 id_t2 name type size Table_3 id_t3 name location color I want a solution (table or groups of tables) where i can establish relations between items of every table, for example: row with id=2 from Table_1 is connected to row with id=23 from Table_3 id=9(from Table_2) is connected to id=83(from Table_1) ... ...etc Stupid Solution 1: I thought of creating a table: Relations id_1 type1 id_2 type2 where id_1 would be the id of the first item to make a connection type1 would indicate the table to where the item belongs to id_2 the otehr item to connect type2 the type of the second item How would i be able to query such a beast? I know that programatically i can make this work, but is there some easier solution? Stupid Solution 2: Crossed my mind to create a table: Things id_thing name type And each other table would have a column named id_thing that would connect to this one. This way, i would be able to list all items of every type from a single table, and the relation table would just have id_1 and id_2, pointing to this table. Depending on the column type on table Things, i could retrieve the rest of the field names of the appropriate item. Bottom line: what i need is some way where i can connect items from Table_x to Table_y. Forgive me if i cant explain my problem in a more simple way. If i am being stupid about this problem, its probably i am so darn close to it that i cant see straight (or i am just plain stupid..). Also i remember reading about table heritage (correct name?) and how several tables can work together to complement each other. Does postgresql have some inner working magic that can help on this case? Thank you. Any help or finger pointing in the right direction is highly appreciated. MV
Re: [GENERAL] encode(bytea_value, 'escape') in PostgreSQL 9.0
Florian Weimer fwei...@bfk.de writes: The old 'escape' encoding used by PostgreSQL 8.4 and prior was pretty helpful for getting human-readable strings in psql. It seems this functionality was removed in PostgreSQL 9.0. Was this an accident or a deliberate decision? Could we get it back, please? I think you're looking for set bytea_output = escape. regards, tom lane -- 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] PG84 and SELinux
James B. Byrne byrn...@harte-lyne.ca writes: I wrote too soon. What I did was uncomment the ssl option. I neglected to change the setting from off to on. When I try to start the server with ssl=on it fails with this error: Auto configuration failed 29006:error:0E065068:configuration file routines:STR_COPY:variable has no value:conf_def.c:629:line 207 AFAIK there is no place in the standard Postgres sources that could emit an error message even vaguely like that. I'm guessing that you are using some add-on code that tries to parse postgresql.conf, but I don't know what that would be. Whose init script are you using? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
