CODEWITHSUNDEEP
postgresqldatabase-permissions
yukuan
yukuan

Reputation: 541

Is there a way to inherits table which is not owned by current user

I create two users in my PostgreSQL db. let's say migration and dev.

I create a table A with user migration. Then I create table B which inherits table A as user dev, but that fails.

ERROR:  must be owner of relation A

If I don't want to alter the table owner, is there another way to make it work?

Upvotes: 1

Views: 722

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246033

One way is to use a role of which both users are members:

CREATE ROLE tab_owner NOLOGIN NOINHERIT;

GRANT tab_owner TO dev, migration;

Now when migration creates a table, it first runs:

SET ROLE tab_owner;

Then the table is owned by the role.

User dev does the same thing before creating an inheritance child of the table.

Upvotes: 4

Related Questions