PostgreSQL Change Table Ownership

Saya ada kasus sepert ini: saya hendak menghapus user strapi pada database PostgreSQL, namun gagal karena user tersebut masih memiliki akses ke suatu tabel/schema pada dabase tertentu. Nah, solusi untuk hal tersebut adalah memindahkan ownsership atau kepemilikan tabel pada user lain, dalam hal ini saya akan menggunakan default user postgres.

Pertama kita list dulu table mana yang dipegang oleh user strapi:

SELECT * FROM pg_tables WHERE tableowner = 'strapi';

begini kira-kira hasilnya:

 schemaname |                tablename                 | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity 
------------+------------------------------------------+------------+------------+------------+----------+-------------+-------------
 public     | strapi_migrations                        | strapi     |            | t          | f        | f           | f
 public     | strapi_database_schema                   | strapi     |            | t          | f        | f           | f
..........................

Setelah kita tau daftar tabel-nya, pindahkan kepemilikan tabel tersebut ke user postgres sekaligus.

do $$
DECLARE ROW record;
BEGIN
  FOR ROW IN SELECT tablename AS name FROM pg_tables WHERE tableowner = 'strapi' loop
    raise notice 'row: %', ROW;
    EXECUTE format('alter table "%s" owner to "postgres"', ROW.name);
  END loop;
END; $$;

Verifikasi dengan perintah berikut untuk memastikan kepemilikan tabel telah beralih ke user postgres:

SELECT * FROM pg_tables WHERE tableowner = 'postgres';

kalau hasilnya berubah, perhatikan pada kolom tableowner:

 schemaname |                tablename                 | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity 
------------+------------------------------------------+------------+------------+------------+----------+-------------+-------------
 public     | strapi_migrations                        | postgres   |            | t          | f        | f           | f
 public     | strapi_database_schema                   | postgres   |            | t          | f        | f           | f
..........................

Artinya berhasil, lanjut delete usernya:

DROP ROLE strapi;

Oh, tidak ternyata masih gagal hapus user strapi?

ERROR:  role "strapi" cannot be dropped because some objects depend on it
DETAIL:  owner of sequence strapi_migrations_id_seq
..........................

Apa? tidak bisa hapus karena ada dependensi akses ke sequence? Coba deh kita verifikasi:

SELECT * FROM pg_sequences WHERE sequenceowner = 'strapi';

Oh, benar:

 schemaname |                  sequencename                   | sequenceowner | data_type | start_value | min_value | max_value  | increment_by | cycle | cache_size | last_value 
------------+-------------------------------------------------+---------------+-----------+-------------+-----------+------------+--------------+-------+------------+------------
 public     | strapi_migrations_id_seq                        | strapi        | integer   |           1 |         1 | 2147483647 |            1 | f     |          1 |           
 public     | strapi_database_schema_id_seq                   | strapi        | integer   |           1 |         1 | 2147483647 |            1 | f     |          1 |    
..........................

Sekarang, mari kita hapus juga kepemilikan atau ownership sequence tersebut:

do $$
DECLARE ROW record;
BEGIN
  FOR ROW IN SELECT sequencename AS name FROM pg_sequences WHERE sequenceowner = 'strapi' loop
    raise notice 'row: %', ROW;
    EXECUTE format('alter sequence "%s" owner to "postgres"', ROW.name);
  END loop;
END; $$;

verifikasi sekali lagi:

SELECT * FROM pg_sequences WHERE sequenceowner = 'postgres';

hasilnya:

 schemaname |                  sequencename                   | sequenceowner | data_type | start_value | min_value | max_value  | increment_by | cycle | cache_size | last_value 
------------+-------------------------------------------------+---------------+-----------+-------------+-----------+------------+--------------+-------+------------+------------
 public     | strapi_migrations_id_seq                        | postgres      | integer   |           1 |         1 | 2147483647 |            1 | f     |          1 |           
 public     | strapi_database_schema_id_seq                   | postgres      | integer   |           1 |         1 | 2147483647 |            1 | f     |          1 |    
..........................

Sampai di sini, seharusnya user strapi sudah bisa dihapus. Selamat mencoba….