PostgreSQL – How to Grant DROP TABLE/FUNCTION Privileges to a Role

permissionspostgresql

I want to grant drop privileges on all tables and functions (not only those owned by the user) in certain schema of a specific database to a specific role. However, GRANT ALL PRIVILEGES is not enough and I didn't find how to do without making the role a superuser – superuser has rights over other databases on the same server, which is not what I want. I wouldn't mind superuser privileges limited to a specific database, but I'm not sure how to do it.

My code:

CREATE USER _administrator PASSWORD 'pwd12345';
CREATE ROLE administrator NOLOGIN ADMIN _administrator;

GRANT ALL PRIVILEGES ON DATABASE "myDB" TO administrator;
GRANT ALL PRIVILEGES ON SCHEMA public TO administrator;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO administrator;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO administrator;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO administrator;

administrator is the group of myDB database administrators, _administrator is the most powerful role my client app will be able to log in as.

What I did miss or do wrong?

Best Answer

Only the owner (and superusers) can drop objects. Per documentation:

The right to drop an object, or to alter its definition in any way, is not treated as a grantable privilege; it is inherent in the owner, and cannot be granted or revoked. (However, a similar effect can be obtained by granting or revoking membership in the role that owns the object; see below.) The owner implicitly has all grant options for the object, too.

So, make administrator own such objects that users should be able to drop.

ALTER FUNCTION foo() OWNER TO administrator;
ALTER TABLE foo      OWNER TO administrator;

And you remembered to actually grant group membership, right?

GRANT administrator TO _administrator;
Related Question