Change owner of tables and sequences in the db

This is copied from stacoshiny#2 (closed)

I'm programming the app so that we can change schema.... for instance switch from logrami to iav from the interface see above ... BUT ... This means relogging everytime with the user of the schema or loggin as a superuser. For instance, when connecting to create a bilan migration, the table bjo is filled in an there is a sequence; the superuser can write, but we need to be in a schema with the right role to run alter sequence. We will get

ERROR: must be owner of relation t_bilanmigrationjournalier_bjo_bjo_identifiant_seq

And the app will crash...

So if logged in as logrami, I can't run a bilan in iav, or to do so, I have to log out and then log in again.... not very practical. One solution is to use a role group called group_stacomi that would be owner of all sequences in all schema (or simply owner of everything).

From the manual

The members of a group role can use the privileges of the role in two ways. First, every member of a group can explicitly do SET ROLE to temporarily “become” the group role. In this state, the database session has access to the privileges of the group role rather than the original login role, and any database objects created are considered owned by the group role not the login role. Second, member roles that have the INHERIT attribute automatically have use of the privileges of roles of which they are members, including any privileges inherited by those roles.

Something like this example set for logrami and iav

create role group_stacomi_sequence NOINHERIT NOLOGIN; -- no inherit ensures that the privileges cannot be passed to another group role created later
create role group_stacomi INHERIT NOLOGIN; -- group_stacomi will be able to inherit from privileges set in  group_stacomi_sequence
GRANT group_stacomi_sequence TO group_stacomi;
GRANT group_stacomi TO iav;
GRANT group_stacomi TO logrami;
REASSIGN OWNED BY 'iav' TO 'group_stacomi';
REASSIGN OWNED BY 'logrami' TO 'group_stacomi';

-- below owned by just ensures that the sequence is dropped if the column is dropped also.
-- also use ref.updatesql to apply changes to a schema.
select ref.updatesql('{"iav","logrami"}',
'
ALTER SEQUENCE t_bilanmigrationjournalier_bjo_bjo_identifiant_seq OWNED BY t_bilanmigrationjournalier_bjo.bjo_identifiant_seq OWNER TO 'group_stacomi_sequence';
ALTER SEQUENCE t_bilanmigrationmensuel_bme_bme_identifiant_seq OWNED BY t_bilanmigrationmensuel_bme.bme_identifiant_seq OWNER TO 'group_stacomi_sequence';
ALTER SEQUENCE t_lot_lot_lot_identifiant_seq OWNED BY t_lot_lot.lot_identifiant_seq OWNER TO 'group_stacomi_sequence';
ALTER SEQUENCE t_operation_ope_ope_identifiant_seq OWNED BY t_operation_ope.ope_identifiant_seq OWNER TO 'group_stacomi_sequence';
ALTER SEQUENCE t_ouvrage_ouv_ouv_identifiant_seq OWNED BY t_ouvrage_ouv.ope_identifiant_seq OWNER TO 'group_stacomi_sequence';
ALTER SEQUENCE tg_dispositif_dis_dis_identifiant_seq OWNED BY tg_dispositif_dis.dis_identifiant_seq OWNER TO 'group_stacomi_sequence';
ALTER SEQUENCE tj_stationmesure_stm_stm_identifiant_seq OWNED BY tj_stationmesure_stm.stm_identifiant_seq OWNER TO 'group_stacomi_sequence';
ALTER SEQUENCE ts_maintenance_main_main_identifiant_seq  OWNED BY ts_maintenance_main.main_identifiant_seq OWNER TO 'group_stacomi_sequence';
ALTER SEQUENCE ts_masque_mas_mas_id_seq  OWNED BY ts_masque_mas.mas_id_seq OWNER TO 'group_stacomi_sequence';
ALTER SEQUENCE ts_masquecaracteristiquelot_mac_mac_id_seq  OWNED BY ts_masquecaracteristiquelot_mac.mac_mac_id OWNER TO 'group_stacomi_sequence';
ALTER SEQUENCE ts_masqueordreaffichage_maa_maa_id_seq  OWNED BY ts_masqueordreaffichage_maa.maa_id_seq OWNER TO 'group_stacomi_sequence';
')

We will have to check that roles do have the inherit privilege when created in the different db : the code of the role must have inherit in the list, originally in the db it was not so....

CREATE ROLE iav;
ALTER ROLE iav WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'xxxx';