Re: What user privileges do I need to CREATE FUNCTION's?

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