REVOKE DROP rights

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

REVOKE DROP rights

Miles Elam-4
Is there any way to prevent a user from dropping a table when that user has create rights? I'd like to allow that user to be able to create and delete their own tables but not specific shared tables.

Is the only way to put the shared tables into a different schema?

Thanks in advance
Reply | Threaded
Open this post in threaded view
|

Re: REVOKE DROP rights

Tom Lane-2
Miles Elam <[hidden email]> writes:
> Is there any way to prevent a user from dropping a table when that user has
> create rights? I'd like to allow that user to be able to create and delete
> their own tables but not specific shared tables.

I think maybe you didn't read the manual closely.  Creation privileges
cover the right to create an object (in a given database or
schema), but only the creator/owner has the right to drop a particular
object once it exists.

We do grant the owner of a schema or database the right to drop objects
within it, since they could surely achieve that result by dropping the
whole schema or database.  But merely having create privilege doesn't
extend to that.

So basically you want a shared schema that is owned by some trusted
role, and your less-trusted roles have create (and usage!) on that
schema.

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: REVOKE DROP rights

Miles Elam-4
Makes sense. Thanks!

On Wed, Sep 11, 2019 at 1:43 PM Tom Lane <[hidden email]> wrote:
Miles Elam <[hidden email]> writes:
> Is there any way to prevent a user from dropping a table when that user has
> create rights? I'd like to allow that user to be able to create and delete
> their own tables but not specific shared tables.

I think maybe you didn't read the manual closely.  Creation privileges
cover the right to create an object (in a given database or
schema), but only the creator/owner has the right to drop a particular
object once it exists.

We do grant the owner of a schema or database the right to drop objects
within it, since they could surely achieve that result by dropping the
whole schema or database.  But merely having create privilege doesn't
extend to that.

So basically you want a shared schema that is owned by some trusted
role, and your less-trusted roles have create (and usage!) on that
schema.

                        regards, tom lane