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
