[HIVE-28195] Add option to use custom schema for Hive metastore tables in PostgreSQL

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 4.0.0
• Component/s: Database/Schema
• Assignee: Unassigned
• Reporter: Smruti Biswal
• Created: 10/Apr/24 19:30
• Updated: 10/Apr/24 19:30

Description

As per the design, hive metastore tables get created under public schema, this restricts users to reuse one database but different schemas for different applications. 

We have SET search_path = public, pg_catalog set in https://github.com/apache/hive/blob/master/standalone-metastore/metastore-server/src/main/sql/postgres/hive-schema-4.0.0.postgres.sql#L12C1-L12C38

Can we change this to the default value in Postgres i.e. SET search_path = "$user", public ? So, the tables get created under the schema based on the Hive DB user.