Обсуждение: Revoke for a new role

Hi folks,

I'm new in this mailing list.

I need to create a role (Postgres user) that cannot drop or create table, but can create TEMP tables. This role must do I, U and D normally.

I have read some docs over the web and discovered that option to revoke "create table" doesn't exist.

Can someone give me a light?!

Thnks

Rafael Domiciano
Senffnet Ltda.
DBA Postgres

Rafael Domiciano написа:
> Hi folks,
>
> I'm new in this mailing list.
>
> I need to create a role (Postgres user) that cannot drop or create table,
> but can create TEMP tables. This role must do I, U and D normally.
>
> I have read some docs over the web and discovered that option to revoke
> "create table" doesn't exist.
>
> Can someone give me a light?!


For a role to be able to create tables (and other objects) it should
have "CREATE" privilege on the _schema_ in question. As for creating
temporary tables - "TEMP" ("TEMPORARY") privilege on the _database_ in
question.

Please check
http://www.postgresql.org/docs/current/static/sql-grant.html for details.

--
Milen A. Radev

"Milen A. Radev" <milen@radev.net> writes:
> Rafael Domiciano написа:
>> I need to create a role (Postgres user) that cannot drop or create table,
>> but can create TEMP tables. This role must do I, U and D normally.

> For a role to be able to create tables (and other objects) it should
> have "CREATE" privilege on the _schema_ in question.

More specifically, what you're going to need to do is revoke "public"
create access on the public schema, and then selectively grant it to
everyone you want to have it.  There's no notion of "everyone but X
gets this privilege".

            regards, tom lane

So, there is no manner to define that the user can't do create or drop objects, but can create temp tables?
I have to do it manually?

I have been reading about a patch that increment the CREATE ROLE clausule, called "PATCH NOCREATETABLE"...
This patch is what I need... someone had already hear some about?

>>> On Fri, Jun 13, 2008 at 11:06 AM, in message
<3a0028490806130906i553f9588g810b2d19d95a91a1@mail.gmail.com>, "Rafael
Domiciano" <rafael.domiciano@gmail.com> wrote:
> So, there is no manner to define that the user can't do create or
drop
> objects, but can create temp tables?

What we normally do is something like this.

create user dbowner password 'ownerpasswd';
create user db password 'normalpasswd';
create user dbviewer password 'viewerpasswd';
create database db with owner dbowner;
\c db
revoke create on database db from public;
revoke create on schema public from public;
grant create on schema public to dbowner;
set role dbowner;
<create objects>

Then we limit access to the given database to the appropriate users in
the pg_hba.conf file.  The owner grants the desired rights to each
user.

I hope this helps.

-Kevin