0

I read that only owner can drop table and no other user may be granted to do so.

I created a table with owner postgres and then drop it as another user. What privileges should I revoke to disallow it?

The user has all the privileges granted by default in db properties but I don't see any "create table" or "drop table" privilege.

  • Please show the output from \dt+ droppable_table and \du non_postgres_user, issued from psql. – dezso Feb 8 '16 at 12:58
4

Superusers are always exempt from lowly permission checks. The documentation:

superuser status

A database superuser bypasses all permission checks, except the right to log in. This is a dangerous privilege and should not be used carelessly; ...

To see whether your current user is a superuser:

SHOW IS_SUPERUSER;

To list all superuser roles:

SELECT rolname FROM pg_roles WHERE rolsuper;

And the schema owner can do it, too. The documentation for DROP TABLE:

Only the table owner, the schema owner, and superuser can drop a table.

To list schema-owners:

SELECT n.nspname AS schema_name, pg_get_userbyid(n.nspowner) AS owner
FROM   pg_namespace n
WHERE  n.nspname NOT LIKE 'pg_%'
AND    n.nspname <> 'information_schema'
ORDER  BY 1;

About the same as \dn in psql.

And who are you?

SELECT current_user, session_user;
  • Is it possible to disallow schema owner to drop tables? – Vlad Feb 9 '16 at 14:43
  • 1
    @Vlad: I don't think so. Either make the superuser (postgres) own the schema or use the same owner for schema and table to avoid the split. – Erwin Brandstetter Feb 9 '16 at 15:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.