X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 31E375294F for ; Wed, 13 Jul 2005 09:16:29 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 13212-01 for ; Wed, 13 Jul 2005 12:16:25 +0000 (GMT) Received: from as1.cineca.com (as1.cineca.com [130.186.84.251]) by svr1.postgresql.org (Postfix) with ESMTP id 2E6DD528D9 for ; Wed, 13 Jul 2005 09:16:23 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by as1.cineca.com (Postfix) with ESMTP id 3902B65911 for ; Wed, 13 Jul 2005 12:16:26 +0000 (UTC) Received: from cineca.mm.cineca.it (cineca.mm.cineca.it [130.186.10.200]) by as1.cineca.com (Postfix) with ESMTP id 0DD6865919 for ; Wed, 13 Jul 2005 12:16:24 +0000 (UTC) Received: from [130.186.7.197] (dhcp7-197.cineca.it [130.186.7.197]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) as user f.dallaca@cineca.it by cineca.mm.cineca.it (Postfix) with ESMTP id C4832527659 for ; Wed, 13 Jul 2005 14:16:23 +0200 (MEST) Message-ID: <42D50617.6010204@cineca.it> From: Francesco Dalla Ca' Organization: CINECA User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.8) Gecko/20050513 Fedora/1.7.8-1.3.1 X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: procedural languages and public schema Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Date: Wed, 13 Jul 2005 14:16:23 +0200 (MEST) X-Virus-Scanned: Cineca AppOs 0.85 at as1.cineca.com X-Virus-Scanned: by amavisd-new at hub.org X-Archive-Number: 200507/81 X-Sequence-Number: 18137 What are the implications between the use of a pl language and the public schema? When i use createlang on a database without the public schema (dropped for safety reason), createlang fail with the sequent error ... postgres@pc-dba:~> createlang -d prova2 plpgsql --echo SELECT oid FROM pg_language WHERE lanname = 'plpgsql'; SELECT oid FROM pg_proc WHERE proname = 'plpgsql_call_handler' AND prorettype = 'pg_catalog.language_handler'::regtype AND pronargs = 0; SELECT oid FROM pg_proc WHERE proname = 'plpgsql_validator' AND proargtypes[0] = 'pg_catalog.oid'::regtype AND pronargs = 1; CREATE FUNCTION "plpgsql_call_handler" () RETURNS language_handler AS '$libdir/plpgsql' LANGUAGE C; CREATE FUNCTION "plpgsql_validator" (oid) RETURNS void AS '$libdir/plpgsql' LANGUAGE C; CREATE TRUSTED LANGUAGE "plpgsql" HANDLER "plpgsql_call_handler" VALIDATOR "plpgsql_validator"; createlang: language installation failed: ERROR: no schema has been selected to create in postgres@pc-dba:~> ... If i recreate the public schema (as default) the createlang perform without error. The client 'createlang' attempt to create 2 functions on the public schema: plxxx_call_handler plxxx_validator these function have got respectively null acl list, from pg_proc: prova2=# select pg_proc.oid, proname, nspname, proacl from pg_proc, pg_namespace where pg_namespace.nspname='public' and pg_namespace.oid=pronamespace; oid | proname | nspname | proacl --------+----------------------+---------+-------- 571455 | plpgsql_call_handler | public | 571456 | plpgsql_validator | public | (2 rows) prova2=# Can i create languages functions on a different schema? This schema must be accessible for all db user? With which privileges (only usage)? What's the mean of the proacl column for these functions relatively to TRUSTED|UNTRUSTED create language clause? =========================================== CINECA Via Magnanelli 6/3 40033 Casalecchio di Reno (Bologna) Settore Gestione Sistemi Francesco Dalla Ca' Email f.dallaca@cineca.it ===========================================