Re: [GENERAL] Deny creation of tables for a user
Albe Laurenz wrote: Pascal Cohen wrote: I am playing with security in Postgres And I would like to have a database that can be managed by a given user that could do almost anything but I would also have a user that can just handle what is created. I mean she could insert, update delete rows but not create tables. I did not find a way to revoke such thing. Is it possible ? The concept of the privilege system is that each database object determines what you can do with it (with an access control list). The owner of a database object can do everything with it. So I'd do it like this: Owning user (owns schema "myschema"): CREATE TABLE myschema.mytable (...); GRANT USAGE ON SCHEMA myschema TO bibi; GRANT INSERT, UPDATE, DELETE ON myschema.mytable TO bibi; Now user "bibi" can du exactly what you want. Yours, Laurenz Albe Thanks all for your help. Your examples + re-reading the documentation made things clear to my mind. Thanks again! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Deny creation of tables for a user
"Roberts, Jon" <[EMAIL PROTECTED]> writes: > You probably want to also "REVOKE ALL ON SCHEMA public FROM public;" so > users can't create objects in that schema. More like REVOKE CREATE ..., unless your intent is also to deny access to existing stuff in the public schema. You'd also want to make sure the user doesn't have CREATE privilege on the database, lest he create his own schema and then make tables within that. (This is off by default, though.) Lastly, if you don't want him creating even temp tables, you'd need to revoke TEMP privilege on the database from public. Having revoked all these privileges from public, you'd need to grant 'em back to whichever individual users should have them. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Deny creation of tables for a user
Pascal Cohen wrote: > I am playing with security in Postgres > And I would like to have a database that can be managed by a given user > that could do almost anything but I would also have a user that can just > handle what is created. > I mean she could insert, update delete rows but not create tables. > > I did not find a way to revoke such thing. Is it possible ? The concept of the privilege system is that each database object determines what you can do with it (with an access control list). The owner of a database object can do everything with it. So I'd do it like this: Owning user (owns schema "myschema"): CREATE TABLE myschema.mytable (...); GRANT USAGE ON SCHEMA myschema TO bibi; GRANT INSERT, UPDATE, DELETE ON myschema.mytable TO bibi; Now user "bibi" can du exactly what you want. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Deny creation of tables for a user
> Terry Lee Tucker wrote: > > On Wednesday 23 April 2008 06:46, Pascal Cohen wrote: > > > >> Hello > >> I am playing with security in Postgres > >> And I would like to have a database that can be managed by a given user > >> that could do almost anything but I would also have a user that can > just > >> handle what is created. > >> I mean she could insert, update delete rows but not create tables. > >> > >> I did not find a way to revoke such thing. Is it possible ? > >> > >> Thanks! > >> > > > > Have you looked at GRANT? > > http://www.postgresql.org/docs/8.3/interactive/sql-grant.html > > > > > Yes I did. > In fact I looked at GRANT and REVOKE commands but I would like to define > that a role r cannot create a new table and I did not find the way to do > so. > I can prevent him from inserting or updating in an existing table but > not to create a new table > It is handled at the schema level. If a user doesn't have create on any schemas, then the user can't create any tables. "For schemas, allows new objects to be created within the schema. To rename an existing object, you must own the object and have this privilege for the containing schema." You probably want to also "REVOKE ALL ON SCHEMA public FROM public;" so users can't create objects in that schema. Jon -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Deny creation of tables for a user
Terry Lee Tucker wrote: On Wednesday 23 April 2008 06:46, Pascal Cohen wrote: Hello I am playing with security in Postgres And I would like to have a database that can be managed by a given user that could do almost anything but I would also have a user that can just handle what is created. I mean she could insert, update delete rows but not create tables. I did not find a way to revoke such thing. Is it possible ? Thanks! Have you looked at GRANT? http://www.postgresql.org/docs/8.3/interactive/sql-grant.html Yes I did. In fact I looked at GRANT and REVOKE commands but I would like to define that a role r cannot create a new table and I did not find the way to do so. I can prevent him from inserting or updating in an existing table but not to create a new table -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Deny creation of tables for a user
On Wednesday 23 April 2008 06:46, Pascal Cohen wrote: > Hello > I am playing with security in Postgres > And I would like to have a database that can be managed by a given user > that could do almost anything but I would also have a user that can just > handle what is created. > I mean she could insert, update delete rows but not create tables. > > I did not find a way to revoke such thing. Is it possible ? > > Thanks! Have you looked at GRANT? http://www.postgresql.org/docs/8.3/interactive/sql-grant.html -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 [EMAIL PROTECTED] www.turbocorp.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Deny creation of tables for a user
Hello I am playing with security in Postgres And I would like to have a database that can be managed by a given user that could do almost anything but I would also have a user that can just handle what is created. I mean she could insert, update delete rows but not create tables. I did not find a way to revoke such thing. Is it possible ? Thanks! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
