pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to prevent users from creating tables
5+ messages / 3 participants
[nested] [flat]
* How to prevent users from creating tables
@ 2009-02-04 00:52 raf <raf@raf.org>
0 siblings, 1 reply; 5+ messages in thread
From: raf @ 2009-02-04 00:52 UTC (permalink / raw)
To: pgsql-admin
hi,
in my database, normal users can't select, insert, update or
delete anything directly. they can only call functions which
are all security defining functions which give access to the
data. that's the way i like it.
however, i discovered that normal users can create new
tables in the database. is there any way to prevent this?
i couldn't find any grant/revoke syntax in the doco that
looked relevant.
cheers,
raf
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to prevent users from creating tables
@ 2009-02-04 02:05 Milen A. Radev <milen@radev.net>
parent: raf <raf@raf.org>
0 siblings, 1 reply; 5+ messages in thread
From: Milen A. Radev @ 2009-02-04 02:05 UTC (permalink / raw)
To: pgsql-admin
raf написа:
> hi,
>
> in my database, normal users can't select, insert, update or
> delete anything directly. they can only call functions which
> are all security defining functions which give access to the
> data. that's the way i like it.
>
> however, i discovered that normal users can create new
> tables in the database. is there any way to prevent this?
> i couldn't find any grant/revoke syntax in the doco that
> looked relevant.
[...]
You need to revoke the "CREATE" privilege for the schema(s) in question
(http://www.postgresql.org/docs/current/static/sql-grant.html#SQL-GRANT-DESCRIPTION-OBJECTS).
--
Milen A. Radev
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to prevent users from creating tables
@ 2009-02-04 14:37 raf <raf@raf.org>
parent: Milen A. Radev <milen@radev.net>
0 siblings, 1 reply; 5+ messages in thread
From: raf @ 2009-02-04 14:37 UTC (permalink / raw)
To: pgsql-admin
Milen A. Radev wrote:
> raf ????????????:
> > hi,
> >
> > in my database, normal users can't select, insert, update or
> > delete anything directly. they can only call functions which
> > are all security defining functions which give access to the
> > data. that's the way i like it.
> >
> > however, i discovered that normal users can create new
> > tables in the database. is there any way to prevent this?
> > i couldn't find any grant/revoke syntax in the doco that
> > looked relevant.
> [...]
>
> You need to revoke the "CREATE" privilege for the schema(s) in question
> (http://www.postgresql.org/docs/current/static/sql-grant.html#SQL-GRANT-DESCRIPTION-OBJECTS).
>
> --
> Milen A. Radev
i had revoked all privileges on the database.
i didn't realise this privilege applied to schemas.
so, i'll revoke this privilege from the public schema.
hmm, if i try:
revoke all privileges on schema public from public;
revoke all privileges on schema public from staff; -- staff is a role
then i get two warnings:
WARNING: no privileges could be revoked for "public"
WARNING: no privileges could be revoked for "public"
and a member of the staff role can still create tables.
if i replace "all privileges" with just "create", i
get the same warnings and still no effect.
what did i do wrong?
PostgreSQL 8.3.5 on i386-apple-darwin8.11.1,
compiled by GCC i686-apple-darwin8-gcc-4.0.1
(GCC) 4.0.1 (Apple Computer, Inc. build 5370)
many thanks,
raf
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to prevent users from creating tables
@ 2009-02-04 14:48 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: raf <raf@raf.org>
0 siblings, 1 reply; 5+ messages in thread
From: Kevin Grittner @ 2009-02-04 14:48 UTC (permalink / raw)
To: pgsql-admin; raf <raf@raf.org>
>>> raf <raf@raf.org> wrote:
> revoke all privileges on schema public from public;
> revoke all privileges on schema public from staff; -- staff is a
role
>
> then i get two warnings:
>
> WARNING: no privileges could be revoked for "public"
> WARNING: no privileges could be revoked for "public"
>
> and a member of the staff role can still create tables.
> if i replace "all privileges" with just "create", i
> get the same warnings and still no effect.
Are you logged in as a database superuser?
(Database owner is not good enough for this operation.)
-Kevin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to prevent users from creating tables
@ 2009-02-05 02:00 raf <raf@raf.org>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 5+ messages in thread
From: raf @ 2009-02-05 02:00 UTC (permalink / raw)
To: pgsql-admin
Kevin Grittner wrote:
> >>> raf <raf@raf.org> wrote:
> > revoke all privileges on schema public from public;
> > revoke all privileges on schema public from staff; -- staff is a role
> >
> > then i get two warnings:
> >
> > WARNING: no privileges could be revoked for "public"
> > WARNING: no privileges could be revoked for "public"
> >
> > and a member of the staff role can still create tables.
> > if i replace "all privileges" with just "create", i
> > get the same warnings and still no effect.
>
> Are you logged in as a database superuser?
>
> (Database owner is not good enough for this operation.)
>
> -Kevin
i wasn't logged in as the superuser.
thanks. it's good now.
cheers,
raf
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2009-02-05 02:00 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-02-04 00:52 How to prevent users from creating tables raf <raf@raf.org>
2009-02-04 02:05 ` Milen A. Radev <milen@radev.net>
2009-02-04 14:37 ` raf <raf@raf.org>
2009-02-04 14:48 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2009-02-05 02:00 ` raf <raf@raf.org>
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