prompt: We are currently solving the following issue within our repository. Here is the issue text:
  --- BEGIN ISSUE ---
  PostgreSQL - permission denied for schema public
  <!--
  Welcome to the Frappe Framework issue tracker! Before creating an issue, please heed the following:
  
  1. This tracker should only be used to report bugs and request features / enhancements to Frappe
      - For questions and general support, use https://stackoverflow.com/questions/tagged/frappe
      - For documentation issues, refer to https://frappeframework.com/docs/user/en or the developer cheetsheet https://github.com/frappe/frappe/wiki/Developer-Cheatsheet
  2. Use the search function before creating a new issue. Duplicates will be closed and directed to
     the original discussion.
  3. When making a bug report, make sure you provide all required information. The easier it is for
     maintainers to reproduce, the faster it'll be fixed.
  4. If you think you know what the reason for the bug is, share it with us. Maybe put in a PR 😉
  -->
  
  ## Description of the issue
  When PostgreSQL version 15 or greater is used, the `bench new-site` command files with insufficient permission error from PostgreSQL. This is due to the change introduced, where `PostgreSQL 15 also revokes the CREATE permission from all users except a database owner from the public (or default) schema.`
  
  ## Context information (for bug reports)
  
  **Output of `bench version`**
  ```
  5.19.0
  ```
  
  ## Steps to reproduce the issue
  
  1. Configure Frappe to use PostgreSQL version which is 15 or greater (16.1 in my case)
  2. Attempt to perform `bench new-site` (Ex: ERPNext)
  
  ### Observed result
  Error from Postgres:
  ```
  ERROR:  permission denied for schema public at character 14
  ```
  
  Error from Frappe:
  ```
  psql:/home/frappe/frappe-bench/apps/frappe/frappe/database/postgres/framework_postgres.sql:72: ERROR:  permission denied for schema public
  ```
  
  ### Expected result
  Installation should proceed without any errors
  
  
  
  
  --- END ISSUE ---
  
  Below are some code segments, each from a relevant file. One or more of these files may contain bugs.
  --- BEGIN FILES ---
  Path: `frappe/database/postgres/setup_db.py`
  Content:
  ```
  1 import os
  2 
  3 import frappe
  4 from frappe import _
  5 
  6 
  7 def setup_database():
  8 	root_conn = get_root_connection(frappe.flags.root_login, frappe.flags.root_password)
  9 	root_conn.commit()
  10 	root_conn.sql("end")
  11 	root_conn.sql(f"DROP DATABASE IF EXISTS `{frappe.conf.db_name}`")
  12 	root_conn.sql(f"DROP USER IF EXISTS {frappe.conf.db_name}")
  13 	root_conn.sql(f"CREATE DATABASE `{frappe.conf.db_name}`")
  14 	root_conn.sql(f"CREATE user {frappe.conf.db_name} password '{frappe.conf.db_password}'")
  15 	root_conn.sql("GRANT ALL PRIVILEGES ON DATABASE `{0}` TO {0}".format(frappe.conf.db_name))
  16 	root_conn.close()
  17 
  18 
  19 def bootstrap_database(db_name, verbose, source_sql=None):
  20 	frappe.connect(db_name=db_name)
  21 	import_db_from_sql(source_sql, verbose)
  22 	frappe.connect(db_name=db_name)
  23 
  24 	if "tabDefaultValue" not in frappe.db.get_tables():
  25 		import sys
  26 
  27 		from click import secho
  28 
  29 		secho(
  30 			"Table 'tabDefaultValue' missing in the restored site. "
  31 			"This may be due to incorrect permissions or the result of a restore from a bad backup file. "
  32 			"Database not installed correctly.",
  33 			fg="red",
  34 		)
  35 		sys.exit(1)
  36 
  37 
  38 def import_db_from_sql(source_sql=None, verbose=False):
  39 	import shlex
  40 	from shutil import which
  41 
  42 	from frappe.database import get_command
  43 	from frappe.utils import execute_in_shell
  44 
  45 	# bootstrap db
  46 	if not source_sql:
  47 		source_sql = os.path.join(os.path.dirname(__file__), "framework_postgres.sql")
  48 
  49 	pv = which("pv")
  50 
  51 	command = []
  52 
  53 	if pv:
  54 		command.extend([pv, source_sql, "|"])
  55 		source = []
  56 		print("Restoring Database file...")
  57 	else:
  58 		source = ["-f", source_sql]
  59 
  60 	bin, args, bin_name = get_command(
  61 		host=frappe.conf.db_host,
  62 		port=frappe.conf.db_port,
  63 		user=frappe.conf.db_name,
  64 		password=frappe.conf.db_password,
  65 		db_name=frappe.conf.db_name,
  66 	)
  67 
  68 	if not bin:
  69 		frappe.throw(
  70 			_("{} not found in PATH! This is required to restore the database.").format(bin_name),
  71 			exc=frappe.ExecutableNotFound,
  72 		)
  73 	command.append(bin)
  74 	command.append(shlex.join(args))
  75 	command.extend(source)
  76 	execute_in_shell(" ".join(command), check_exit_code=True, verbose=verbose)
  77 	frappe.cache.delete_keys("")  # Delete all keys associated with this site.
  78 
  79 
  80 def get_root_connection(root_login=None, root_password=None):
  81 	if not frappe.local.flags.root_connection:
  82 		if not root_login:
  83 			root_login = frappe.conf.get("root_login") or None
  84 
  85 		if not root_login:
  86 			root_login = input("Enter postgres super user: ")
  87 
  88 		if not root_password:
  89 			root_password = frappe.conf.get("root_password") or None
  90 
  91 		if not root_password:
  92 			from getpass import getpass
  93 
  94 			root_password = getpass("Postgres super user password: ")
  95 
  96 		frappe.local.flags.root_connection = frappe.database.get_db(
  97 			host=frappe.conf.db_host,
  98 			port=frappe.conf.db_port,
  99 			user=root_login,
  100 			password=root_password,
  101 		)
  102 
  103 	return frappe.local.flags.root_connection
  104 
  105 
  106 def drop_user_and_database(db_name, root_login, root_password):
  107 	root_conn = get_root_connection(
  108 		frappe.flags.root_login or root_login, frappe.flags.root_password or root_password
  109 	)
  110 	root_conn.commit()
  111 	root_conn.sql(
  112 		"SELECT pg_terminate_backend (pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = %s",
  113 		(db_name,),
  114 	)
  115 	root_conn.sql("end")
  116 	root_conn.sql(f"DROP DATABASE IF EXISTS {db_name}")
  117 	root_conn.sql(f"DROP USER IF EXISTS {db_name}")
  118
  ```
  --- END FILES ---
  
  Please first localize the bug based on the issue statement, and then generate a patch according to the `git diff` format fenced by three backticks.
  
  Here is an example:
  
  ```diff
  diff --git a/examples/server_async.py b/examples/server_async.py
  --- a/examples/server_async.py
  +++ b/examples/server_async.py
  @@ -313,4 +313,4 @@
   
   
   if __name__ == "__main__":
  -    asyncio.run(run_async_server("."), debug=True)
  +    asyncio.run(run_async_server(), debug=True)
  diff --git a/examples/server_sync.py b/examples/server_sync.py
  --- a/examples/server_sync.py
  +++ b/examples/server_sync.py
  @@ -313,5 +313,5 @@
   
   
   if __name__ == "__main__":
  -    server = run_sync_server(".")
  +    server = run_sync_server()
       server.shutdown()
  
  ```
  
golden_diff: diff --git a/frappe/database/postgres/setup_db.py b/frappe/database/postgres/setup_db.py
  --- a/frappe/database/postgres/setup_db.py
  +++ b/frappe/database/postgres/setup_db.py
  @@ -13,6 +13,9 @@
   	root_conn.sql(f"CREATE DATABASE `{frappe.conf.db_name}`")
   	root_conn.sql(f"CREATE user {frappe.conf.db_name} password '{frappe.conf.db_password}'")
   	root_conn.sql("GRANT ALL PRIVILEGES ON DATABASE `{0}` TO {0}".format(frappe.conf.db_name))
  +	psql_version = root_conn.sql(f"SELECT VERSION()", as_dict=True)
  +	if psql_version and psql_version[0].get("version", "PostgreSQL 14").split()[1] >= "15":
  +		root_conn.sql("ALTER DATABASE `{0}` OWNER TO {0}".format(frappe.conf.db_name))
   	root_conn.close()
  
verification_info: {"golden_diff": "diff --git a/frappe/database/postgres/setup_db.py b/frappe/database/postgres/setup_db.py\n--- a/frappe/database/postgres/setup_db.py\n+++ b/frappe/database/postgres/setup_db.py\n@@ -13,6 +13,9 @@\n \troot_conn.sql(f\"CREATE DATABASE `{frappe.conf.db_name}`\")\n \troot_conn.sql(f\"CREATE user {frappe.conf.db_name} password '{frappe.conf.db_password}'\")\n \troot_conn.sql(\"GRANT ALL PRIVILEGES ON DATABASE `{0}` TO {0}\".format(frappe.conf.db_name))\n+\tpsql_version = root_conn.sql(f\"SELECT VERSION()\", as_dict=True)\n+\tif psql_version and psql_version[0].get(\"version\", \"PostgreSQL 14\").split()[1] >= \"15\":\n+\t\troot_conn.sql(\"ALTER DATABASE `{0}` OWNER TO {0}\".format(frappe.conf.db_name))\n \troot_conn.close()\n", "issue": "PostgreSQL - permission denied for schema public\n<!--\r\nWelcome to the Frappe Framework issue tracker! Before creating an issue, please heed the following:\r\n\r\n1. This tracker should only be used to report bugs and request features / enhancements to Frappe\r\n    - For questions and general support, use https://stackoverflow.com/questions/tagged/frappe\r\n    - For documentation issues, refer to https://frappeframework.com/docs/user/en or the developer cheetsheet https://github.com/frappe/frappe/wiki/Developer-Cheatsheet\r\n2. Use the search function before creating a new issue. Duplicates will be closed and directed to\r\n   the original discussion.\r\n3. When making a bug report, make sure you provide all required information. The easier it is for\r\n   maintainers to reproduce, the faster it'll be fixed.\r\n4. If you think you know what the reason for the bug is, share it with us. Maybe put in a PR \ud83d\ude09\r\n-->\r\n\r\n## Description of the issue\r\nWhen PostgreSQL version 15 or greater is used, the `bench new-site` command files with insufficient permission error from PostgreSQL. This is due to the change introduced, where `PostgreSQL 15 also revokes the CREATE permission from all users except a database owner from the public (or default) schema.`\r\n\r\n## Context information (for bug reports)\r\n\r\n**Output of `bench version`**\r\n```\r\n5.19.0\r\n```\r\n\r\n## Steps to reproduce the issue\r\n\r\n1. Configure Frappe to use PostgreSQL version which is 15 or greater (16.1 in my case)\r\n2. Attempt to perform `bench new-site` (Ex: ERPNext)\r\n\r\n### Observed result\r\nError from Postgres:\r\n```\r\nERROR:  permission denied for schema public at character 14\r\n```\r\n\r\nError from Frappe:\r\n```\r\npsql:/home/frappe/frappe-bench/apps/frappe/frappe/database/postgres/framework_postgres.sql:72: ERROR:  permission denied for schema public\r\n```\r\n\r\n### Expected result\r\nInstallation should proceed without any errors\r\n\r\n\r\n\n", "before_files": [{"content": "import os\n\nimport frappe\nfrom frappe import _\n\n\ndef setup_database():\n\troot_conn = get_root_connection(frappe.flags.root_login, frappe.flags.root_password)\n\troot_conn.commit()\n\troot_conn.sql(\"end\")\n\troot_conn.sql(f\"DROP DATABASE IF EXISTS `{frappe.conf.db_name}`\")\n\troot_conn.sql(f\"DROP USER IF EXISTS {frappe.conf.db_name}\")\n\troot_conn.sql(f\"CREATE DATABASE `{frappe.conf.db_name}`\")\n\troot_conn.sql(f\"CREATE user {frappe.conf.db_name} password '{frappe.conf.db_password}'\")\n\troot_conn.sql(\"GRANT ALL PRIVILEGES ON DATABASE `{0}` TO {0}\".format(frappe.conf.db_name))\n\troot_conn.close()\n\n\ndef bootstrap_database(db_name, verbose, source_sql=None):\n\tfrappe.connect(db_name=db_name)\n\timport_db_from_sql(source_sql, verbose)\n\tfrappe.connect(db_name=db_name)\n\n\tif \"tabDefaultValue\" not in frappe.db.get_tables():\n\t\timport sys\n\n\t\tfrom click import secho\n\n\t\tsecho(\n\t\t\t\"Table 'tabDefaultValue' missing in the restored site. \"\n\t\t\t\"This may be due to incorrect permissions or the result of a restore from a bad backup file. \"\n\t\t\t\"Database not installed correctly.\",\n\t\t\tfg=\"red\",\n\t\t)\n\t\tsys.exit(1)\n\n\ndef import_db_from_sql(source_sql=None, verbose=False):\n\timport shlex\n\tfrom shutil import which\n\n\tfrom frappe.database import get_command\n\tfrom frappe.utils import execute_in_shell\n\n\t# bootstrap db\n\tif not source_sql:\n\t\tsource_sql = os.path.join(os.path.dirname(__file__), \"framework_postgres.sql\")\n\n\tpv = which(\"pv\")\n\n\tcommand = []\n\n\tif pv:\n\t\tcommand.extend([pv, source_sql, \"|\"])\n\t\tsource = []\n\t\tprint(\"Restoring Database file...\")\n\telse:\n\t\tsource = [\"-f\", source_sql]\n\n\tbin, args, bin_name = get_command(\n\t\thost=frappe.conf.db_host,\n\t\tport=frappe.conf.db_port,\n\t\tuser=frappe.conf.db_name,\n\t\tpassword=frappe.conf.db_password,\n\t\tdb_name=frappe.conf.db_name,\n\t)\n\n\tif not bin:\n\t\tfrappe.throw(\n\t\t\t_(\"{} not found in PATH! This is required to restore the database.\").format(bin_name),\n\t\t\texc=frappe.ExecutableNotFound,\n\t\t)\n\tcommand.append(bin)\n\tcommand.append(shlex.join(args))\n\tcommand.extend(source)\n\texecute_in_shell(\" \".join(command), check_exit_code=True, verbose=verbose)\n\tfrappe.cache.delete_keys(\"\")  # Delete all keys associated with this site.\n\n\ndef get_root_connection(root_login=None, root_password=None):\n\tif not frappe.local.flags.root_connection:\n\t\tif not root_login:\n\t\t\troot_login = frappe.conf.get(\"root_login\") or None\n\n\t\tif not root_login:\n\t\t\troot_login = input(\"Enter postgres super user: \")\n\n\t\tif not root_password:\n\t\t\troot_password = frappe.conf.get(\"root_password\") or None\n\n\t\tif not root_password:\n\t\t\tfrom getpass import getpass\n\n\t\t\troot_password = getpass(\"Postgres super user password: \")\n\n\t\tfrappe.local.flags.root_connection = frappe.database.get_db(\n\t\t\thost=frappe.conf.db_host,\n\t\t\tport=frappe.conf.db_port,\n\t\t\tuser=root_login,\n\t\t\tpassword=root_password,\n\t\t)\n\n\treturn frappe.local.flags.root_connection\n\n\ndef drop_user_and_database(db_name, root_login, root_password):\n\troot_conn = get_root_connection(\n\t\tfrappe.flags.root_login or root_login, frappe.flags.root_password or root_password\n\t)\n\troot_conn.commit()\n\troot_conn.sql(\n\t\t\"SELECT pg_terminate_backend (pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = %s\",\n\t\t(db_name,),\n\t)\n\troot_conn.sql(\"end\")\n\troot_conn.sql(f\"DROP DATABASE IF EXISTS {db_name}\")\n\troot_conn.sql(f\"DROP USER IF EXISTS {db_name}\")\n", "path": "frappe/database/postgres/setup_db.py"}], "after_files": [{"content": "import os\n\nimport frappe\nfrom frappe import _\n\n\ndef setup_database():\n\troot_conn = get_root_connection(frappe.flags.root_login, frappe.flags.root_password)\n\troot_conn.commit()\n\troot_conn.sql(\"end\")\n\troot_conn.sql(f\"DROP DATABASE IF EXISTS `{frappe.conf.db_name}`\")\n\troot_conn.sql(f\"DROP USER IF EXISTS {frappe.conf.db_name}\")\n\troot_conn.sql(f\"CREATE DATABASE `{frappe.conf.db_name}`\")\n\troot_conn.sql(f\"CREATE user {frappe.conf.db_name} password '{frappe.conf.db_password}'\")\n\troot_conn.sql(\"GRANT ALL PRIVILEGES ON DATABASE `{0}` TO {0}\".format(frappe.conf.db_name))\n\tpsql_version = root_conn.sql(f\"SELECT VERSION()\", as_dict=True)\n\tif psql_version and psql_version[0].get(\"version\", \"PostgreSQL 14\").split()[1] >= \"15\":\n\t\troot_conn.sql(\"ALTER DATABASE `{0}` OWNER TO {0}\".format(frappe.conf.db_name))\n\troot_conn.close()\n\n\ndef bootstrap_database(db_name, verbose, source_sql=None):\n\tfrappe.connect(db_name=db_name)\n\timport_db_from_sql(source_sql, verbose)\n\tfrappe.connect(db_name=db_name)\n\n\tif \"tabDefaultValue\" not in frappe.db.get_tables():\n\t\timport sys\n\n\t\tfrom click import secho\n\n\t\tsecho(\n\t\t\t\"Table 'tabDefaultValue' missing in the restored site. \"\n\t\t\t\"This may be due to incorrect permissions or the result of a restore from a bad backup file. \"\n\t\t\t\"Database not installed correctly.\",\n\t\t\tfg=\"red\",\n\t\t)\n\t\tsys.exit(1)\n\n\ndef import_db_from_sql(source_sql=None, verbose=False):\n\timport shlex\n\tfrom shutil import which\n\n\tfrom frappe.database import get_command\n\tfrom frappe.utils import execute_in_shell\n\n\t# bootstrap db\n\tif not source_sql:\n\t\tsource_sql = os.path.join(os.path.dirname(__file__), \"framework_postgres.sql\")\n\n\tpv = which(\"pv\")\n\n\tcommand = []\n\n\tif pv:\n\t\tcommand.extend([pv, source_sql, \"|\"])\n\t\tsource = []\n\t\tprint(\"Restoring Database file...\")\n\telse:\n\t\tsource = [\"-f\", source_sql]\n\n\tbin, args, bin_name = get_command(\n\t\thost=frappe.conf.db_host,\n\t\tport=frappe.conf.db_port,\n\t\tuser=frappe.conf.db_name,\n\t\tpassword=frappe.conf.db_password,\n\t\tdb_name=frappe.conf.db_name,\n\t)\n\n\tif not bin:\n\t\tfrappe.throw(\n\t\t\t_(\"{} not found in PATH! This is required to restore the database.\").format(bin_name),\n\t\t\texc=frappe.ExecutableNotFound,\n\t\t)\n\tcommand.append(bin)\n\tcommand.append(shlex.join(args))\n\tcommand.extend(source)\n\texecute_in_shell(\" \".join(command), check_exit_code=True, verbose=verbose)\n\tfrappe.cache.delete_keys(\"\")  # Delete all keys associated with this site.\n\n\ndef get_root_connection(root_login=None, root_password=None):\n\tif not frappe.local.flags.root_connection:\n\t\tif not root_login:\n\t\t\troot_login = frappe.conf.get(\"root_login\") or None\n\n\t\tif not root_login:\n\t\t\troot_login = input(\"Enter postgres super user: \")\n\n\t\tif not root_password:\n\t\t\troot_password = frappe.conf.get(\"root_password\") or None\n\n\t\tif not root_password:\n\t\t\tfrom getpass import getpass\n\n\t\t\troot_password = getpass(\"Postgres super user password: \")\n\n\t\tfrappe.local.flags.root_connection = frappe.database.get_db(\n\t\t\thost=frappe.conf.db_host,\n\t\t\tport=frappe.conf.db_port,\n\t\t\tuser=root_login,\n\t\t\tpassword=root_password,\n\t\t)\n\n\treturn frappe.local.flags.root_connection\n\n\ndef drop_user_and_database(db_name, root_login, root_password):\n\troot_conn = get_root_connection(\n\t\tfrappe.flags.root_login or root_login, frappe.flags.root_password or root_password\n\t)\n\troot_conn.commit()\n\troot_conn.sql(\n\t\t\"SELECT pg_terminate_backend (pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = %s\",\n\t\t(db_name,),\n\t)\n\troot_conn.sql(\"end\")\n\troot_conn.sql(f\"DROP DATABASE IF EXISTS {db_name}\")\n\troot_conn.sql(f\"DROP USER IF EXISTS {db_name}\")\n", "path": "frappe/database/postgres/setup_db.py"}]}