Thread: Prevent users from creating tables
Hello,
I’m trying to set up PostgreSQL for proof of concept according to our standards.
I need to create a user for database01 that has the ability to create any objects they want.
They can create tables, views, indexes, etc.
Then I need a second user that has the privileges to only SELECT, INSERT, UPDATE, DELETE from objects in a certain database.
How do I grant these privs?
How do I prevent a user from dropping objects?
Thank you!
Liberty Mutual
Infrastructure Software Engineering
Database Management
desk: (603) 245 4092
cell: (603) 219 5539
"Campano, Troy" <Troy.Campano@LibertyMutual.com> wrote: > > Hello, > I'm trying to set up PostgreSQL for proof of concept according to our > standards. > I need to create a user for database01 that has the ability to create > any objects they want. > They can create tables, views, indexes, etc. > > Then I need a second user that has the privileges to only SELECT, > INSERT, UPDATE, DELETE from objects in a certain database. > > How do I grant these privs? > How do I prevent a user from dropping objects? http://www.postgresql.org/docs/7.4/static/sql-grant.html Jim
I tried this out, however none of this seems to prevent a user from creating tables. It can prevent users from viewing or modifying data on existing tables, but I can't find a solution where I can prevent users from creating tables. Any ideas? Thank you! -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Jim Seymour Sent: Tuesday, June 08, 2004 5:06 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Prevent users from creating tables "Campano, Troy" <Troy.Campano@LibertyMutual.com> wrote: > > Hello, > I'm trying to set up PostgreSQL for proof of concept according to our > standards. > I need to create a user for database01 that has the ability to create > any objects they want. > They can create tables, views, indexes, etc. > > Then I need a second user that has the privileges to only SELECT, > INSERT, UPDATE, DELETE from objects in a certain database. > > How do I grant these privs? > How do I prevent a user from dropping objects? http://www.postgresql.org/docs/7.4/static/sql-grant.html Jim ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly
My DDL for a schema usually looks something like CREATE SCHEMA passport AUTHORIZATION postgres; REVOKE ALL ON SCHEMA passport FROM public; GRANT ALL ON SCHEMA passport TO postgres; GRANT USAGE ON SCHEMA passport TO GROUP acct; Perhaps you have missed the REVOKE ALL...? --Berend Tober > I tried this out, however none of this seems to prevent a user from > creating tables. It can prevent users from viewing or modifying data on > existing tables, but I can't find a solution where I can prevent users > from creating tables. > > Any ideas? > > Thank you! > > > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Jim Seymour > Sent: Tuesday, June 08, 2004 5:06 PM > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Prevent users from creating tables > > > "Campano, Troy" <Troy.Campano@LibertyMutual.com> wrote: >> >> Hello, >> I'm trying to set up PostgreSQL for proof of concept according to our >> standards. >> I need to create a user for database01 that has the ability to create >> any objects they want. >> They can create tables, views, indexes, etc. >> >> Then I need a second user that has the privileges to only SELECT, >> INSERT, UPDATE, DELETE from objects in a certain database. >> >> How do I grant these privs? >> How do I prevent a user from dropping objects? > > http://www.postgresql.org/docs/7.4/static/sql-grant.html
Yes, that's what I needed. I needed to revoke privs from the public schema to all users and then revoke the privs for the users to create their own schemas. So by default, all users have privs to create tables in schema 'public'? Why would postgresql developers decide to do that instead of denying all access unless it is granted? For ease of use? Just curious. Thank you for your help, it's appreciated! ~ T r o y ~ -----Original Message----- From: btober@computer.org [mailto:btober@computer.org] Sent: Wednesday, June 09, 2004 9:07 AM To: Campano, Troy Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Prevent users from creating tables My DDL for a schema usually looks something like CREATE SCHEMA passport AUTHORIZATION postgres; REVOKE ALL ON SCHEMA passport FROM public; GRANT ALL ON SCHEMA passport TO postgres; GRANT USAGE ON SCHEMA passport TO GROUP acct; Perhaps you have missed the REVOKE ALL...? --Berend Tober > I tried this out, however none of this seems to prevent a user from > creating tables. It can prevent users from viewing or modifying data on > existing tables, but I can't find a solution where I can prevent users > from creating tables. > > Any ideas? > > Thank you! > > > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Jim Seymour > Sent: Tuesday, June 08, 2004 5:06 PM > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Prevent users from creating tables > > > "Campano, Troy" <Troy.Campano@LibertyMutual.com> wrote: >> >> Hello, >> I'm trying to set up PostgreSQL for proof of concept according to our >> standards. >> I need to create a user for database01 that has the ability to create >> any objects they want. >> They can create tables, views, indexes, etc. >> >> Then I need a second user that has the privileges to only SELECT, >> INSERT, UPDATE, DELETE from objects in a certain database. >> >> How do I grant these privs? >> How do I prevent a user from dropping objects? > > http://www.postgresql.org/docs/7.4/static/sql-grant.html
Campano, Troy wrote:
> I tried this out, however none of this seems to prevent a user from
> creating tables. It can prevent users from viewing or modifying data on
> existing tables, but I can't find a solution where I can prevent users
> from creating tables.
You have to do it per schema basis. This is on a CVS tip on windows so you might
have to check for any version differences.
Grant help says that 'create on database' is for creating schemas and 'create on
schema' is create objects in schema.
What you could do is,
* revoke all user rights
* grant access to one schema
* revoke create for that schema.
I hope that is good enough for you..
-----------------
C:\Documents and Settings\shridhar>psql test1
Welcome to psql 7.5devel, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
Warning: Console codepage (437) differs from windows codepage (1252)
8-bit characters will not work correctly. See PostgreSQL
documentation "Installation on Windows" for details.
test1=# revoke create on schema testschema from testuser;
ERROR: schema "testschema" does not exist
test1=# create schema testschema;
CREATE SCHEMA
test1=# revoke create on schema testschema from testuser;
REVOKE
test1=# \q
C:\Documents and Settings\shridhar>psql -U testuser test1
Welcome to psql 7.5devel, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
Warning: Console codepage (437) differs from windows codepage (1252)
8-bit characters will not work correctly. See PostgreSQL
documentation "Installation on Windows" for details.
test1=> create table testschema.t1(name varchar(30));
ERROR: permission denied for schema testschema
test1=>
-----------------
HTH
Shridhar
My DDL for a schema usually looks something like CREATE SCHEMA passport AUTHORIZATION postgres; REVOKE ALL ON SCHEMA passport FROM public; GRANT ALL ON SCHEMA passport TO postgres; GRANT USAGE ON SCHEMA passport TO GROUP acct; Perhaps you have missed the REVOKE ALL? --Berend Tober > I tried this out, however none of this seems to prevent a user from > creating tables. It can prevent users from viewing or modifying data on > existing tables, but I can't find a solution where I can prevent users > from creating tables. > > Any ideas? > > Thank you! > > > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Jim Seymour > Sent: Tuesday, June 08, 2004 5:06 PM > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Prevent users from creating tables > > > "Campano, Troy" <Troy.Campano@LibertyMutual.com> wrote: >> >> Hello, >> I'm trying to set up PostgreSQL for proof of concept according to our >> standards. >> I need to create a user for database01 that has the ability to create >> any objects they want. >> They can create tables, views, indexes, etc. >> >> Then I need a second user that has the privileges to only SELECT, >> INSERT, UPDATE, DELETE from objects in a certain database. >> >> How do I grant these privs? >> How do I prevent a user from dropping objects? > > http://www.postgresql.org/docs/7.4/static/sql-grant.html > > Jim
"Campano, Troy" <Troy.Campano@LibertyMutual.com> writes:
> So by default, all users have privs to create tables in schema 'public'?
> Why would postgresql developers decide to do that instead of denying all
> access unless it is granted? For ease of use? Just curious.
That and backwards compatibility. There are some other exceptions to
the "no access by default" rule, too --- see the GRANT reference page.
regards, tom lane