Re: Prevent users from creating tables

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