Re: [BUGS] BUG #1858: setting search path in select doesn't (always) work

2005-09-03 Thread Dick Kniep 
Hi list,

I reported the bug because I was convinced it was something fishy, however, 
the bug I reported proved to be something completely else. I don't even know 
if it is a bug at all.  If you do a set search_path; the path is set 
correctly according to the rules of autocommit and everything else. 

If however you are not authorized to use the schema, the next query on a table 
within the requested schema results in a table not found condition, which 
makes sense, because it actually cannot find the table, because it cannot 
enter the schema. 

I have checked the way psycopg handles this, and if a select is done on the 
table this is working correctly,

 File /home/dick/CVix/CVix/src/ObjectBuilder.py, line 516, in __BldTabDef
cur.execute(Sqlstmt)
psycopg.ProgrammingError: ERROR:  permission denied for schema adeuxproductie

Select * from adeuxproductie.parameters WHERE 1=0

so the problem must be within Zope and I will report this problem to the Zope 
community.

Thanks for all your help.

Op vrijdag 2 september 2005 17:12, schreef Michael Fuhr:
 On Fri, Sep 02, 2005 at 10:58:12AM -0400, Tom Lane wrote:
  D.J. Kniep [EMAIL PROTECTED] writes:
   After setting the search path the query
   Select * from vwexternetoegang
   produces the required results in the first installation, but in the new
   installation, it cannot find the view. However, if I do an explicit
   Set search_path to testschema;
   it works as expected.
 
  What does show search_path report in the failing and non-failing
  states? Also try select current_schemas(true).

 This thread came up in pgsql-general yesterday; Dick says the real
 problem was something else.

 http://archives.postgresql.org/pgsql-general/2005-09/msg00074.php

Cheers,
Dick Kniep

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[BUGS] BUG #1858: setting search path in select doesn't (always) work

2005-09-02 Thread D.J. Kniep 

The following bug has been logged online:

Bug reference:  1858
Logged by:  D.J. Kniep
Email address:  [EMAIL PROTECTED]
PostgreSQL version: 7.4.8
Operating system:   Linux (SuSE 9.3) /RedHat ES 4
Description:setting search path in select doesn't (always) work
Details: 

we have been running a Zope site with Postgresql 7.4.8 for a while. In this
installation almost the first statement is:

cvix=# SELECT set_config('search_path', '' || t2.schema || '', true)
cvix-# FROM Lindix.Gebruikers as t1, Lindix.Administratie as t2
cvix-# WHERE uid = 'zon0023'
cvix-# AND t1.administratie_id = t2.administratie_id;
set_config
--
 adeuxproductie
(1 row)

As you can see, I have a fixed schema called Lindix where the actual
destination schema is in a table. Depending on the user, the search_path is
set and it will be able to find the table.

Now we have installed a new server, with the same db version, the same
content (a restore from the original db) and the same coding.

After setting the search path the query 

Select * from vwexternetoegang 

produces the required results in the first installation, but in the new
installation, it cannot find the view. However, if I do an explicit 

Set search_path to testschema; 

it works as expected.

The only real difference between the 2 installations I see is that the
working installation has a RedHat Enterprise Linux ES Release 4 (Nahant
update1) version versus the new (not working) a SuSE 9.3 installation

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [BUGS] BUG #1858: setting search path in select doesn't (always) work

2005-09-02 Thread Tom Lane 
D.J. Kniep [EMAIL PROTECTED] writes:
 After setting the search path the query 
 Select * from vwexternetoegang 
 produces the required results in the first installation, but in the new
 installation, it cannot find the view. However, if I do an explicit 
 Set search_path to testschema; 
 it works as expected.

What does show search_path report in the failing and non-failing states?
Also try select current_schemas(true).

regards, tom lane

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [BUGS] BUG #1858: setting search path in select doesn't (always) work

2005-09-02 Thread Michael Fuhr 
On Fri, Sep 02, 2005 at 10:58:12AM -0400, Tom Lane wrote:
 D.J. Kniep [EMAIL PROTECTED] writes:
  After setting the search path the query 
  Select * from vwexternetoegang 
  produces the required results in the first installation, but in the new
  installation, it cannot find the view. However, if I do an explicit 
  Set search_path to testschema; 
  it works as expected.
 
 What does show search_path report in the failing and non-failing states?
 Also try select current_schemas(true).

This thread came up in pgsql-general yesterday; Dick says the real
problem was something else.

http://archives.postgresql.org/pgsql-general/2005-09/msg00074.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