psycopg2.errors.InsufficientPrivilege: permission denied for schema public

[kkaarreell]

Is your issue a feature request? If so, please raise it as an enhancement

Environment

• OS / version: Fedora Rawhide
• Processor architecture: x86_64
• TPM Manufacturer: swtpm
• Keylime version: current upstream 2c36871

Description

With pgsql database backend there is a traceback when verifier and registrar start.
This is most likely caused by a change introduced in PostgreSQL 15
https://www.cybertec-postgresql.com/en/error-permission-denied-schema-public/

keylime_verifier[42256]: Traceback (most recent call last):
keylime_verifier[42256]:   File "/usr/lib64/python3.11/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
keylime_verifier[42256]:     self.dialect.do_execute(
keylime_verifier[42256]:   File "/usr/lib64/python3.11/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
keylime_verifier[42256]:     cursor.execute(statement, parameters)
keylime_verifier[42256]: psycopg2.errors.InsufficientPrivilege: permission denied for schema public
keylime_verifier[42256]: LINE 2: CREATE TABLE alembic_version_cloud_verifier (
keylime_verifier[42256]:                      ^
keylime_verifier[42256]: The above exception was the direct cause of the following exception:
keylime_verifier[42256]: Traceback (most recent call last):
keylime_verifier[42256]:   File "/usr/local/bin/keylime_verifier", line 33, in <module>
keylime_verifier[42256]:     sys.exit(load_entry_point('keylime==6.5.2', 'console_scripts', 'keylime_verifier')())
keylime_verifier[42256]:              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
keylime_verifier[42256]:   File "/usr/local/lib/python3.11/site-packages/keylime-6.5.2-py3.11.egg/keylime/cmd/verifier.py", line 10, in main
keylime_verifier[42256]:     keylime.cmd.migrations_apply.apply("cloud_verifier")
keylime_verifier[42256]:   File "/usr/local/lib/python3.11/site-packages/keylime-6.5.2-py3.11.egg/keylime/cmd/migrations_apply.py", line 28, in apply
keylime_verifier[42256]:     alembic.config.main(argv=alembic_args)
keylime_verifier[42256]:   File "/usr/lib/python3.11/site-packages/alembic/config.py", line 590, in main
keylime_verifier[42256]:     CommandLine(prog=prog).main(argv=argv)
keylime_verifier[42256]:   File "/usr/lib/python3.11/site-packages/alembic/config.py", line 584, in main
keylime_verifier[42256]:     self.run_cmd(cfg, options)
keylime_verifier[42256]:   File "/usr/lib/python3.11/site-packages/alembic/config.py", line 561, in run_cmd
keylime_verifier[42256]:     fn(
keylime_verifier[42256]:   File "/usr/lib/python3.11/site-packages/alembic/command.py", line 322, in upgrade
keylime_verifier[42256]:     script.run_env()
keylime_verifier[42256]:   File "/usr/lib/python3.11/site-packages/alembic/script/base.py", line 569, in run_env
keylime_verifier[42256]:     util.load_python_file(self.dir, "env.py")
keylime_verifier[42256]:   File "/usr/lib/python3.11/site-packages/alembic/util/pyfiles.py", line 94, in load_python_file
keylime_verifier[42256]:     module = load_module_py(module_id, path)
keylime_verifier[42256]:              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
keylime_verifier[42256]:   File "/usr/lib/python3.11/site-packages/alembic/util/pyfiles.py", line 110, in load_module_py
keylime_verifier[42256]:     spec.loader.exec_module(module)  # type: ignore
keylime_verifier[42256]:     ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
keylime_verifier[42256]:   File "<frozen importlib._bootstrap_external>", line 940, in exec_module
keylime_verifier[42256]:   File "<frozen importlib._bootstrap>", line 241, in _call_with_frames_removed
keylime_verifier[42256]:   File "/usr/local/lib/python3.11/site-packages/keylime-6.5.2-py3.11.egg/keylime/migrations/env.py", line 147, in <module>
keylime_verifier[42256]:     run_migrations_online()
keylime_verifier[42256]:   File "/usr/local/lib/python3.11/site-packages/keylime-6.5.2-py3.11.egg/keylime/migrations/env.py", line 127, in run_migrations_online
keylime_verifier[42256]:     context.run_migrations(engine_name=name)
keylime_verifier[42256]:   File "<string>", line 8, in run_migrations
keylime_verifier[42256]:   File "/usr/lib/python3.11/site-packages/alembic/runtime/environment.py", line 853, in run_migrations
keylime_verifier[42256]:     self.get_context().run_migrations(**kw)
keylime_verifier[42256]:   File "/usr/lib/python3.11/site-packages/alembic/runtime/migration.py", line 606, in run_migrations
keylime_verifier[42256]:     self._ensure_version_table()
keylime_verifier[42256]:   File "/usr/lib/python3.11/site-packages/alembic/runtime/migration.py", line 542, in _ensure_version_table
keylime_verifier[42256]:     self._version.create(self.connection, checkfirst=True)
keylime_verifier[42256]:   File "/usr/lib64/python3.11/site-packages/sqlalchemy/sql/schema.py", line 962, in create
keylime_verifier[42256]:     bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
keylime_verifier[42256]:   File "/usr/lib64/python3.11/site-packages/sqlalchemy/engine/base.py", line 2211, in _run_ddl_visitor
keylime_verifier[42256]:     visitorcallable(self.dialect, self, **kwargs).traverse_single(element)
keylime_verifier[42256]:   File "/usr/lib64/python3.11/site-packages/sqlalchemy/sql/visitors.py", line 524, in traverse_single
keylime_verifier[42256]:     return meth(obj, **kw)
keylime_verifier[42256]:            ^^^^^^^^^^^^^^^
keylime_verifier[42256]:   File "/usr/lib64/python3.11/site-packages/sqlalchemy/sql/ddl.py", line 895, in visit_table
keylime_verifier[42256]:     self.connection.execute(
keylime_verifier[42256]:   File "/usr/lib64/python3.11/site-packages/sqlalchemy/engine/base.py", line 1380, in execute
keylime_verifier[42256]:     return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
keylime_verifier[42256]:            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
keylime_verifier[42256]:   File "/usr/lib64/python3.11/site-packages/sqlalchemy/sql/ddl.py", line 80, in _execute_on_connection
keylime_verifier[42256]:     return connection._execute_ddl(
keylime_verifier[42256]:            ^^^^^^^^^^^^^^^^^^^^^^^^
keylime_verifier[42256]:   File "/usr/lib64/python3.11/site-packages/sqlalchemy/engine/base.py", line 1472, in _execute_ddl
keylime_verifier[42256]:     ret = self._execute_context(
keylime_verifier[42256]:           ^^^^^^^^^^^^^^^^^^^^^^
keylime_verifier[42256]:   File "/usr/lib64/python3.11/site-packages/sqlalchemy/engine/base.py", line 1943, in _execute_context
keylime_verifier[42256]:     self._handle_dbapi_exception(
keylime_verifier[42256]:   File "/usr/lib64/python3.11/site-packages/sqlalchemy/engine/base.py", line 2124, in _handle_dbapi_exception
keylime_verifier[42256]:     util.raise_(
keylime_verifier[42256]:   File "/usr/lib64/python3.11/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
keylime_verifier[42256]:     raise exception
keylime_verifier[42256]:   File "/usr/lib64/python3.11/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
keylime_verifier[42256]:     self.dialect.do_execute(
keylime_verifier[42256]:   File "/usr/lib64/python3.11/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
keylime_verifier[42256]:     cursor.execute(statement, parameters)
keylime_verifier[42256]: sqlalchemy.exc.ProgrammingError: (psycopg2.errors.InsufficientPrivilege) permission denied for schema public
keylime_verifier[42256]: LINE 2: CREATE TABLE alembic_version_cloud_verifier (
keylime_verifier[42256]:                      ^
keylime_verifier[42256]: [SQL:
keylime_verifier[42256]: CREATE TABLE alembic_version_cloud_verifier (
keylime_verifier[42256]:         version_num VARCHAR(32) NOT NULL,
keylime_verifier[42256]:         CONSTRAINT alembic_version_cloud_verifier_pkc PRIMARY KEY (version_num)
keylime_verifier[42256]: )
keylime_verifier[42256]: ]
keylime_verifier[42256]: (Background on this error at: https://sqlalche.me/e/14/f405)
Dec 04 09:34:46 ip-172-31-16-104.us-east-2.compute.internal systemd[1]: keylime_verifier.service: Main process exited, code=exited, status=1/FAILURE
Dec 04 09:34:46 ip-172-31-16-104.us-east-2.compute.internal systemd[1]: keylime_verifier.service: Failed with result 'exit-code'.
Dec 04 09:34:46 ip-172-31-16-104.us-east-2.compute.internal systemd[1]: keylime_verifier.service: Consumed 1.959s CPU time.

Expected behavior vs. actual behavior

no traceback, verifier starts properly

Steps to reproduce problem

Scenario is automated in e2e test /functional/db-postgresql-sanity-on-localhost
it is sufficient to schedule it on Fedora Rawhide (basically any keylime PR tested today through Packit CI will be failing on Rawhide because of it).

[mpeters]

This seems like an issue with the user creation rather than with keylime. It seems that now in Postgres 15 you need to give the user permissions to create within the public schema, which was just globally allowed in <15. Or am I missing something?

[kkaarreell]

I believe you are right. The question is how to approach it on keylime side. Either update some docs we have, maybe drop a comment to keylime.conf. Or maybe handle the error a bit nicer. Or do nothing.

[mpeters]

In my experience working with databases, from an application standpoint you assume the person knows how to configure the users on their database. If there were special permissions that are needed you would mention them in the docs, but I don't know how special this is. Schema creation is pretty necessary for most applications, but I guess it couldn't hurt to mention in the docs that the db user needs permissions to create tables in the configured database/namespace.