1

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?

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
yukuan
  • 521
  • 5
  • 18

1 Answers1

4

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Thanks @Laurenz Albe, Actually I simplify the use case in my question, the real case is: I want to use partition table by inheritance. so I create and automatic trigger for insert operation, when insert data row, it will auto create child table by created time, and it failed due to above reason, that's why I create this question. After I do some investigation, I've resolved it use SECURITY DEFINER. – yukuan Mar 14 '19 at 09:50
  • Use a trigger function owned by the table owner and define it as `SECURITY DEFINER`. Don't forget `SET search_path = pg_catalog` in the function declaration. – Laurenz Albe Mar 14 '19 at 09:53
  • Actually I'm confused about this search_path, I see the doc mentioned this, and I'm not quite sure what it used to, currently I set it like this: `SET search_path = public, pg_temp;` is there any problem here? – yukuan Mar 14 '19 at 09:54
  • Sorry for being too terse, I was referring to [this](https://www.postgresql.org/docs/current/sql-createfunction.html#SQL-CREATEFUNCTION-SECURITY) important warning about `SECURITY DEFINER` functions in the documentation. – Laurenz Albe Mar 14 '19 at 10:00
  • I also read this part, search_path is set to `admin, pg_temp` in the example, and `pg_catalog` in your comment, if my table A and table B is belong to public schema, should i set search_path to `public, pg_temp`? I'm just not quite understand how it works.. – yukuan Mar 14 '19 at 10:06
  • It should never contain `public` unless you revoke the `CREATE` privilege on that schema. The safest way is to use `pg_catalog` like I said and qualify all access to objects in the function with the schema. – Laurenz Albe Mar 14 '19 at 10:10
  • Which means the tables can only be accessed like: `pg_catalog.A` `pg_catalog.B` ? – yukuan Mar 14 '19 at 10:16
  • Yes, precisely. – Laurenz Albe Mar 14 '19 at 10:35