Postgres Pro
Downloads
Home   >   mailing lists

Thread: Revoking table & function creation privileges

Revoking table & function creation privileges

From
Moshe Jacobson
Date:
I posted this in Devshed forums (here) but no one seems to be responding. Hopefully someone here can help.

How do I keep a user from being able to create tables in the public schema?

I have tried REVOKE CREATE FROM username ON SCHEMA PUBLIC but they can still create tables.
The user has no special attributes and is not a member of another role.

mjacobson@zeus=>[local]:ises=> \dn+ public
                          List of schemas
  Name  |  Owner   |  Access privileges   |      Description
--------+----------+----------------------+------------------------
 public | postgres | postgres=UC/postgres+| standard public schema
        |          | =UC/postgres        +|
        |          | webuser=U/postgres   |
(1 row)

mjacobson@zeus=>[local]:ises=> select current_user;
 current_user
--------------
 mjacobson
(1 row)

mjacobson@zeus=>[local]:ises=> \du+ mjacobson
                  List of roles
 Role name | Attributes | Member of | Description
-----------+------------+-----------+-------------
 mjacobson |            | {}        |

mjacobson@zeus=>[local]:ises=> create table tb_foo(i int);
CREATE TABLE
mjacobson@zeus=>[local]:ises=> drop table tb_foo;
DROP TABLE

--
Moshe Jacobson
Nead Werx, Inc. | Senior Systems Engineer
2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
moshe@neadwerx.com | www.neadwerx.com

Re: Revoking table & function creation privileges

From
Mike Blackwell
Date:
The second line in the list of privilges shows the default grant of UC to "public" for that schema.  Until you revoke that, the individual grants are superfluous. 

On Thu, Aug 9, 2012 at 12:53 PM, Moshe Jacobson <moshe@neadwerx.com> wrote:
I posted this in Devshed forums (here) but no one seems to be responding. Hopefully someone here can help.

How do I keep a user from being able to create tables in the public schema?
 
mjacobson@zeus=>[local]:ises=> \dn+ public
                          List of schemas
  Name  |  Owner   |  Access privileges   |      Description
--------+----------+----------------------+------------------------
 public | postgres | postgres=UC/postgres+| standard public schema
        |          | =UC/postgres        +|
        |          | webuser=U/postgres   |
(1 row)

__________________________________________________________________________________
Mike Blackwell | Technical Analyst, Distribution Services/Rollout Management | RR Donnelley
1750 Wallace Ave | St Charles, IL 60174-3401 
Office: 630.313.7818 
Mike.Blackwell@rrd.com
http://www.rrdonnelley.com