Provide option for change of owner of tables in Database which already exists

[nv1-0]

Currently when we apply Postgres CR it creates a DB if it doesn't exists and if it exists it changes its owner to the role created by the Postgres CRs. But the owner of the tables inside it remain unaltered due to which if we try to access it using credentials created by Postgres CRs then we get this error

<database>=> select * from <table>;
ERROR:  permission denied for table <table>

It would be great if we get an option to migrate the owners of all the tables in the database to the role created by Postgres CRs

[nv1-0]

I have created a pull request for this topic :- #58.
Its missing tests right now. It can be reviewed

[hitman99]

I think we already covered this in discussion on #58 - you can specify a masterRole that already owns the database and schemas and postgres-operator will not create a new owner role:

postgres-operator/pkg/controller/postgres/postgres_controller.go

Lines 146 to 149 in 243a5ec

	owner := instance.Spec.MasterRole
	if owner == "" {
	owner = fmt.Sprintf("%s-group", instance.Spec.Database)
	}

It will then try to create this role and will just use the role provided in spec if it already exists:

postgres-operator/pkg/postgres/role.go

Lines 23 to 28 in 243a5ec

	// Error code 42710 is duplicate_object (role already exists)
	_, err := c.db.Exec(fmt.Sprintf(CREATE_GROUP_ROLE, role))
	if err != nil && err.(*pq.Error).Code != "42710" {
	return err
	}
	return nil

[sambonbonne]

I know this issue is more than half and a year old but it seems I have the same problem, even using masterRole with the existing role: new users access is denied on existing tables (I tried with OWNER privilege and it doesn't work better).