pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
What user privileges do I need to CREATE FUNCTION's?
5+ messages / 3 participants
[nested] [flat]

* What user privileges do I need to CREATE FUNCTION's?
@ 2007-07-17 19:33 km4hr <km4hr@netscape.net>
  2007-07-17 21:09 ` Re: What user privileges do I need to CREATE FUNCTION's? Milen A. Radev <milen@radev.net>
  2007-07-17 21:20 ` Re: What user privileges do I need to CREATE FUNCTION's? Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 5+ messages in thread

From: km4hr @ 2007-07-17 19:33 UTC (permalink / raw)
  To: pgsql-admin


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#a1165631...
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: What user privileges do I need to CREATE FUNCTION's?
  2007-07-17 19:33 What user privileges do I need to CREATE FUNCTION's? km4hr <km4hr@netscape.net>
@ 2007-07-17 21:09 ` Milen A. Radev <milen@radev.net>
  2007-07-18 14:42   ` Re: What user privileges do I need to CREATE FUNCTION's? km4hr <km4hr@netscape.net>
  1 sibling, 1 reply; 5+ messages in thread

From: Milen A. Radev @ 2007-07-17 21:09 UTC (permalink / raw)
  To: pgsql-admin

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




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: What user privileges do I need to CREATE FUNCTION's?
  2007-07-17 19:33 What user privileges do I need to CREATE FUNCTION's? km4hr <km4hr@netscape.net>
  2007-07-17 21:09 ` Re: What user privileges do I need to CREATE FUNCTION's? Milen A. Radev <milen@radev.net>
@ 2007-07-18 14:42   ` km4hr <km4hr@netscape.net>
  2007-07-18 15:09     ` Re: What user privileges do I need to CREATE FUNCTION's? km4hr <km4hr@netscape.net>
  0 siblings, 1 reply; 5+ messages in thread

From: km4hr @ 2007-07-18 14:42 UTC (permalink / raw)
  To: pgsql-admin


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#a1167011...
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: What user privileges do I need to CREATE FUNCTION's?
  2007-07-17 19:33 What user privileges do I need to CREATE FUNCTION's? km4hr <km4hr@netscape.net>
  2007-07-17 21:09 ` Re: What user privileges do I need to CREATE FUNCTION's? Milen A. Radev <milen@radev.net>
  2007-07-18 14:42   ` Re: What user privileges do I need to CREATE FUNCTION's? km4hr <km4hr@netscape.net>
@ 2007-07-18 15:09     ` km4hr <km4hr@netscape.net>
  0 siblings, 0 replies; 5+ messages in thread

From: km4hr @ 2007-07-18 15:09 UTC (permalink / raw)
  To: pgsql-admin


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#a1167042...
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: What user privileges do I need to CREATE FUNCTION's?
  2007-07-17 19:33 What user privileges do I need to CREATE FUNCTION's? km4hr <km4hr@netscape.net>
@ 2007-07-17 21:20 ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 5+ messages in thread

From: Tom Lane @ 2007-07-17 21:20 UTC (permalink / raw)
  To: km4hr <km4hr@netscape.net>; +Cc: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2007-07-18 15:09 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-07-17 19:33 What user privileges do I need to CREATE FUNCTION's? km4hr <km4hr@netscape.net>
2007-07-17 21:09 ` Milen A. Radev <milen@radev.net>
2007-07-18 14:42   ` km4hr <km4hr@netscape.net>
2007-07-18 15:09     ` km4hr <km4hr@netscape.net>
2007-07-17 21:20 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox