Postgres Pro
Downloads
Home   >   mailing lists

Thread: GRANT & DROP

GRANT & DROP

From
"Gavin M. Roy"
Date:
I recently suffered the pains of not having good security on a database
when a developer dropped a production transaction payment table.  Thus
now I am paying attention to what I should have before which is database
security.  I am looking at the grant command and see it covers alot of
important things, but the one I am most interested is DROP TABLE and I
don't see it, nor ALTER.  Does DELETE cover DROP as well?  Anyone have
experience here?

TIA,

Gavin

Re: GRANT & DROP

From
Tom Lane
Date:
"Gavin M. Roy" <gmr@readysetnet.com> writes:
> I am looking at the grant command and see it covers alot of
> important things, but the one I am most interested is DROP TABLE and I
> don't see it, nor ALTER.  Does DELETE cover DROP as well?

I believe DROP and ALTER rights are determined by ownership, nothing
else.  Ditto rights to do GRANT/REVOKE.  (The permissions capabilities
in Postgres badly need redesign, since they're nowhere close to what
the SQL spec calls for, but that's what we have at the moment.)

My two cents: any table that you want to be reasonably secure should be
owned by an "admin" sort of user that is not used for normal database
operations.  The admin user should grant appropriate permissions to the
userid(s) that are used for routine work.

It's probably good if the admin user is not the superuser, too.
The routine-work users had better not be superuser, else they will
bypass all the permissions checks anyway.

            regards, tom lane

Re: GRANT & DROP

From
"Anthony E . Greene"
Date:
On Mon, 08 Jan 2001 15:09:12 Gavin M. Roy wrote:
>I recently suffered the pains of not having good security on a database
>when a developer dropped a production transaction payment table.  Thus
>now I am paying attention to what I should have before which is database
>security.  I am looking at the grant command and see it covers alot of
>important things, but the one I am most interested is DROP TABLE and I
>don't see it, nor ALTER.  Does DELETE cover DROP as well?  Anyone have
>experience here?

On my Linux system, "man l drop" displays the man page for the DROP command,
with references to specific variants of the command.

I don't know if the latest versions are different but in 6.5.3 anyone can
drop a table, even if they don't have any permissions on the rows in that
table.

Tony
--
Anthony E. Greene <agreene@pobox.com> <http://www.pobox.com/~agreene/>
PGP Key: 0x6C94239D/7B3D BD7D 7D91 1B44 BA26  C484 A42A 60DD 6C94 239D
Chat:  AOL/Yahoo: TonyG05    ICQ: 91183266
Linux. The choice of a GNU Generation. <http://www.linux.org/>