Thread: What user privileges do I need to CREATE FUNCTION's?
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? What privileges does "newuser" need to create functions? I can't find that described in the postgres manual? thanks -- View this message in context: http://www.nabble.com/What-user-privileges-do-I-need-to-CREATE-FUNCTION%27s--tf4099063.html#a11656315 Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
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
km4hr <km4hr@netscape.net> writes:
> What privileges does "newuser" need to create functions? I can't find that
> described in the postgres manual?
The GRANT reference page has most of the details you want, I think.
regards, tom lane
Thanks Milen. Assigning superuser role worked. Speaking of schema's, I logged in as user "postgres" and created a schema called "schema1" as follows: CREATE SCHEMA schema1 AUTHORIZATION newuser; I then typed "\dn" and saw "schema1" in the list. I then logged in as "newuser" and typed "\dn". "schema1" was not in the list! Why not? I then created a table. Next I entered: "select * from schema1.newtable". An error message indicated that "schema1" doesn't exist. How do I make "newuser" aware of his new schema? thanks Milen A. Radev-2 wrote: > > 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 > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > > -- View this message in context: http://www.nabble.com/What-user-privileges-do-I-need-to-CREATE-FUNCTION%27s--tf4099063.html#a11670119 Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
Please disregard my last post about creating a schema. I found the answer. You have to be connected to a database to create a schema in that database. km4hr wrote: > > Thanks Milen. Assigning superuser role worked. > > Speaking of schema's, I logged in as user "postgres" and created a schema > called "schema1" as follows: > CREATE SCHEMA schema1 AUTHORIZATION newuser; > > I then typed "\dn" and saw "schema1" in the list. > > I then logged in as "newuser" and typed "\dn". "schema1" was not in the > list! Why not? > I then created a table. Next I entered: "select * from schema1.newtable". > An error message > indicated that "schema1" doesn't exist. How do I make "newuser" aware of > his new schema? > > thanks > > > > Milen A. Radev-2 wrote: >> >> 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 >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 5: don't forget to increase your free space map settings >> >> > > -- View this message in context: http://www.nabble.com/What-user-privileges-do-I-need-to-CREATE-FUNCTION%27s--tf4099063.html#a11670425 Sent from the PostgreSQL - admin mailing list archive at Nabble.com.