pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedRevoke for a new role
5+ messages / 4 participants
[nested] [flat]
* Revoke for a new role
@ 2008-06-13 13:35 Rafael Domiciano <rafael.domiciano@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Rafael Domiciano @ 2008-06-13 13:35 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Revoke for a new role
@ 2008-06-13 14:05 Milen A. Radev <milen@radev.net>
parent: Rafael Domiciano <rafael.domiciano@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Milen A. Radev @ 2008-06-13 14:05 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Revoke for a new role
@ 2008-06-13 15:11 Tom Lane <tgl@sss.pgh.pa.us>
parent: Milen A. Radev <milen@radev.net>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2008-06-13 15:11 UTC (permalink / raw)
To: Milen A. Radev <milen@radev.net>; +Cc: Rafael Domiciano <rafael.domiciano@gmail.com>; pgsql-admin
"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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Revoke for a new role
@ 2008-06-13 16:06 Rafael Domiciano <rafael.domiciano@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Rafael Domiciano @ 2008-06-13 16:06 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Milen A. Radev <milen@radev.net>; pgsql-admin
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?
2008/6/13 Tom Lane <tgl@sss.pgh.pa.us>:
> "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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Revoke for a new role
@ 2008-06-19 17:56 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Rafael Domiciano <rafael.domiciano@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Kevin Grittner @ 2008-06-19 17:56 UTC (permalink / raw)
To: Rafael Domiciano <rafael.domiciano@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin; Milen A. Radev <milen@radev.net>
>>> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2008-06-19 17:56 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-06-13 13:35 Revoke for a new role Rafael Domiciano <rafael.domiciano@gmail.com>
2008-06-13 14:05 ` Milen A. Radev <milen@radev.net>
2008-06-13 15:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-06-13 16:06 ` Rafael Domiciano <rafael.domiciano@gmail.com>
2008-06-19 17:56 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox