Search code examples
sqlpostgresqlddl

Unexpected behavior from changing owner in postgres

I have a schema of tables myschema with different owners in postgres - owner 'foo' and owner 'bar'

After granting all privileges on the database to owner 'bar', and then re-logging in as user 'bar', I try to change the ownership of all bar-owned tables to 'foo' with the following command:

SELECT format(
  'ALTER TABLE %I.%I.%I OWNER TO %I;',
  table_catalog,
  table_schema,
  table_name,
  'foo'  
)
FROM information_schema.tables
WHERE table_schema = 'myschema'

This then returns:

 ALTER TABLE my_db.my_schema.my_tableA OWNER TO foo;
 ALTER TABLE my_db.my_schema.my_tableB OWNER TO foo;
 ALTER TABLE my_db.my_schema.my_tableC OWNER TO foo;

successfully

Then, when I run select * from pg_tables;, I see that none of the tableowners have changed. How is this possible and what could be happening?

Solution

  • To change ownership of tables from bar to foo, a user must be a member of both roles. So if bar should be able to do that, it must be a superuser or a member of foo:

    GRANT foo TO bar;

    I guess your statements caused an error that you somehow failed to see and didn't do anything.