PostgreSQL Error [42501]: ERROR: must be owner of relation table

Question

I am maintaining a database 'db' in which there are around 100 tables.I have one super user 'A' and 'A' is the owner of all tables. How can I give Alter permission to new user 'B' specific to single table without inheriting all permissions from 'A'.

I tried by providing Grant A to B;. This Grant option given all permissions from 'A' to 'B'. I want above scenario to restrict to one particular table. Is this possible?

Answer 1

The documentation recently acquired this explanation:

The right to modify or destroy an object is inherent in being the object's owner, and cannot be granted or revoked in itself. (However, like all privileges, that right can be inherited by members of the owning role; see Section 21.3.)

So the only people who can run ALTER TABLE are:

• Superusers

• the table owner

• members of the table owner role

So GRANT a TO b is the only way to give somebody the privilege.

You might be able to use a SECURITY DEFINER function that belongs to a, but be careful with that.