Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id A02BC9FB7E0 for ; Tue, 17 Jul 2007 18:09:56 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 43340-02 for ; Tue, 17 Jul 2007 18:09:43 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from ciao.gmane.org (main.gmane.org [80.91.229.2]) by postgresql.org (Postfix) with ESMTP id 014739FB6FE for ; Tue, 17 Jul 2007 18:09:51 -0300 (ADT) Received: from list by ciao.gmane.org with local (Exim 4.43) id 1IAuIn-0008HZ-To for pgsql-admin@postgresql.org; Tue, 17 Jul 2007 23:09:49 +0200 Received: from 77.70.61.39 ([77.70.61.39]) by main.gmane.org with esmtp (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Tue, 17 Jul 2007 23:09:49 +0200 Received: from milen by 77.70.61.39 with local (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Tue, 17 Jul 2007 23:09:49 +0200 X-Injected-Via-Gmane: http://gmane.org/ To: pgsql-admin@postgresql.org From: "Milen A. Radev" Subject: Re: What user privileges do I need to CREATE FUNCTION's? Date: Wed, 18 Jul 2007 00:09:40 +0300 Lines: 56 Message-ID: References: <11656315.post@talk.nabble.com> Mime-Version: 1.0 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit X-Complaints-To: usenet@sea.gmane.org X-Gmane-NNTP-Posting-Host: 77.70.61.39 User-Agent: Thunderbird 2.0.0.4 (Windows/20070604) In-Reply-To: <11656315.post@talk.nabble.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=2.068 tagged_above=0 required=5 tests=BAYES_50=0.001, RCVD_NUMERIC_HELO=2.067 X-Spam-Level: ** X-Archive-Number: 200707/191 X-Sequence-Number: 26172 km4hr написа: > I'm installing an application that provides a ".sql" script to create tables > and other objects in a postgresql database. When I run the script as user > "postgres" the script executes without errors. But then all the tables and > other objects are owned by "postgres". A user that I created to access the > tables, "newuser", doesn't have privileges needed to use them. > > If I login as "newuser" and execute the install script, I get errors > indicating "newuser" doesn't have privileges to CREATE FUNCTION's . > > How should I execute the install script so that all the objects are owned by > "newuser" and the FUNCTIONS are created? You need to create all DB objects with one preferably administrative role and then GRANT only some privileges to other, everyday roles. Something like (assuming those are run as a administrator): CREATE TABLE example_table ( id integer PRIMARY KEY, ... ); REVOKE ALL PRIVILEGES ON TABLE example_table FROM PUBLIC; GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE example_table TO newuser; REVOKE ALL PRIVILEGES ON SEQUENCE example_table_id_seq FROM PUBLIC; GRANT USAGE ON SEQUENCE example_table_id_seq TO newuser; CREATE FUNCTION example_func(...) RETURNS ... AS $$ ... $$ LANGUAGE SQL; REVOKE ALL PRIVILEGES ON FUNCTION example_func FROM PUBLIC; GRANT EXECUTE ON FUNCTION example_func TO newuser; Or you could change the owner of the newly created DB object: ALTER TABLE example_table OWNER TO newuser; ALTER FUNCTION example_func(...) OWNER TO newuser; This way the new owner has all the privileges on that object. Of course I prefer the first method of dealing with the needed privileges. > What privileges does "newuser" need to create functions? I can't find that > described in the postgres manual? I suppose the role should be the owner of the schema or a superuser. -- Milen A. Radev