[MM3-users] Re: psycopg2 error?

On Fri, Oct 27, 2023 at 6:23 PM Mark Sapiro <mark@msapiro.net> wrote:

On 10/27/23 01:09, Odhiambo Washington wrote:

...

So my database is PostgreSQL-15:

Try the following in your venv.

pip uninstall psycopg2-binary
pip cache remove psycopg2-binary
pip install psycopg2-binary

root@debian12:/home/wash# su - mailman
(venv) mailman@debian12:~$ pip uninstall psycopg2-binary
Found existing installation: psycopg2-binary 2.8.6
Uninstalling psycopg2-binary-2.8.6:
  Would remove:
    /opt/mailman/venv/lib/python3.11/site-packages/psycopg2/*

/opt/mailman/venv/lib/python3.11/site-packages/psycopg2_binary-2.8.6.dist-info/*
Proceed (Y/n)? y
  Successfully uninstalled psycopg2-binary-2.8.6
(venv) mailman@debian12:~$ pip cache remove psycopg2-binary
WARNING: No matching packages for pattern "psycopg2-binary"
Files removed: 0
(venv) mailman@debian12:~$ pip install psycopg2-binary
Collecting psycopg2-binary
  Using cached
psycopg2_binary-2.9.9-cp311-cp311-manylinux_2_17_x86_64.manylinux2014_x86_64.whl
(3.0 MB)
Installing collected packages: psycopg2-binary
Successfully installed psycopg2-binary-2.9.9
(venv) mailman@debian12:~$ mailman info
Traceback (most recent call last):
  File
"/opt/mailman/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py",
line 1969, in _exec_single_context
    self.dialect.do_execute(
  File
"/opt/mailman/venv/lib/python3.11/site-packages/sqlalchemy/engine/default.py",
line 922, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.InsufficientPrivilege: permission denied for schema public
LINE 2: CREATE TABLE ban (
                     ^


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/opt/mailman/venv/bin/mailman", line 33, in <module>
    sys.exit(load_entry_point('mailman==3.3.9', 'console_scripts',
'mailman')())

 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/mailman/venv/lib/python3.11/site-packages/click/core.py", line
1157, in __call__
    return self.main(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/mailman/venv/lib/python3.11/site-packages/click/core.py", line
1077, in main
    with self.make_context(prog_name, args, **extra) as ctx:
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/mailman/venv/lib/python3.11/site-packages/click/core.py", line
943, in make_context
    self.parse_args(ctx, args)
  File "/opt/mailman/venv/lib/python3.11/site-packages/click/core.py", line
1644, in parse_args
    rest = super().parse_args(ctx, args)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/mailman/venv/lib/python3.11/site-packages/click/core.py", line
1408, in parse_args
    value, args = param.handle_parse_result(ctx, opts, args)
                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/mailman/venv/lib/python3.11/site-packages/click/core.py", line
2400, in handle_parse_result
    value = self.process_value(ctx, value)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/mailman/venv/lib/python3.11/site-packages/click/core.py", line
2362, in process_value
    value = self.callback(ctx, self, value)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File
"/opt/mailman/venv/lib/python3.11/site-packages/mailman/bin/mailman.py",
line 95, in initialize_config
    initialize(value)
  File
"/opt/mailman/venv/lib/python3.11/site-packages/mailman/core/initialize.py",
line 229, in initialize
    initialize_2(propagate_logs=propagate_logs)
  File
"/opt/mailman/venv/lib/python3.11/site-packages/mailman/core/initialize.py",
line 187, in initialize_2
    config.db = getUtility(IDatabaseFactory, utility_name).create()
                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File
"/opt/mailman/venv/lib/python3.11/site-packages/mailman/database/factory.py",
line 58, in create
    SchemaManager(database).setup_database()
  File
"/opt/mailman/venv/lib/python3.11/site-packages/mailman/database/factory.py",
line 97, in setup_database
    Model.metadata.create_all(self._database.engine)
  File
"/opt/mailman/venv/lib/python3.11/site-packages/sqlalchemy/sql/schema.py",
line 5828, in create_all
    bind._run_ddl_visitor(
  File
"/opt/mailman/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py",
line 3243, in _run_ddl_visitor
    conn._run_ddl_visitor(visitorcallable, element, **kwargs)
  File
"/opt/mailman/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py",
line 2447, in _run_ddl_visitor
    visitorcallable(self.dialect, self, **kwargs).traverse_single(element)
  File
"/opt/mailman/venv/lib/python3.11/site-packages/sqlalchemy/sql/visitors.py",
line 671, in traverse_single
    return meth(obj, **kw)
           ^^^^^^^^^^^^^^^
  File
"/opt/mailman/venv/lib/python3.11/site-packages/sqlalchemy/sql/ddl.py",
line 919, in visit_metadata
    self.traverse_single(
  File
"/opt/mailman/venv/lib/python3.11/site-packages/sqlalchemy/sql/visitors.py",
line 671, in traverse_single
    return meth(obj, **kw)
           ^^^^^^^^^^^^^^^
  File
"/opt/mailman/venv/lib/python3.11/site-packages/sqlalchemy/sql/ddl.py",
line 957, in visit_table
    )._invoke_with(self.connection)
      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File
"/opt/mailman/venv/lib/python3.11/site-packages/sqlalchemy/sql/ddl.py",
line 315, in _invoke_with
    return bind.execute(self)
           ^^^^^^^^^^^^^^^^^^
  File
"/opt/mailman/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py",
line 1416, in execute
    return meth(
           ^^^^^
  File
"/opt/mailman/venv/lib/python3.11/site-packages/sqlalchemy/sql/ddl.py",
line 181, in _execute_on_connection
    return connection._execute_ddl(
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File
"/opt/mailman/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py",
line 1528, in _execute_ddl
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File
"/opt/mailman/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py",
line 1848, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File
"/opt/mailman/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py",
line 1988, in _exec_single_context
    self._handle_dbapi_exception(
  File
"/opt/mailman/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py",
line 2343, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File
"/opt/mailman/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py",
line 1969, in _exec_single_context
    self.dialect.do_execute(
  File
"/opt/mailman/venv/lib/python3.11/site-packages/sqlalchemy/engine/default.py",
line 922, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.InsufficientPrivilege)
permission denied for schema public
LINE 2: CREATE TABLE ban (
                     ^

[SQL:
CREATE TABLE ban (
        id SERIAL NOT NULL,
        email VARCHAR,
        list_id VARCHAR,
        PRIMARY KEY (id)
)

]
(Background on this error at: https://sqlalche.me/e/20/f405)

I then followed the directions for GRANTing access the public schema:

postgres=# \c mailman postgres
You are now connected to database "mailman" as user "postgres".
mailman=# GRANT ALL ON SCHEMA public TO mailman;
GRANT
mailman=# \c mailmanweb postgres
You are now connected to database "mailmanweb" as user "postgres".
mailmanweb=# GRANT ALL ON SCHEMA public TO mailman;
GRANT
mailmanweb=#

...and tested IF mailman user could create a table in the mailman db:

(venv) mailman@debian12:~$ psql -h localhost -U mailman -d mailman -W
Password:
psql (15.3 (Debian 15.3-0+deb12u1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384,
compression: off)
Type "help" for help.

mailman=> create table xyz(a varchar(100));
CREATE TABLE
mailman=> DROP TABLE xyz;
DROP TABLE
mailman=> \q

But then again mailman info groks!

(venv) mailman@debian12:~$ mailman info
Traceback (most recent call last):
  File "/opt/mailman/venv/bin/mailman", line 33, in <module>
    sys.exit(load_entry_point('mailman==3.3.9', 'console_scripts',
'mailman')())

 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/mailman/venv/lib/python3.11/site-packages/click/core.py", line
1157, in __call__
    return self.main(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/mailman/venv/lib/python3.11/site-packages/click/core.py", line
1077, in main
    with self.make_context(prog_name, args, **extra) as ctx:
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/mailman/venv/lib/python3.11/site-packages/click/core.py", line
943, in make_context
    self.parse_args(ctx, args)
  File "/opt/mailman/venv/lib/python3.11/site-packages/click/core.py", line
1644, in parse_args
    rest = super().parse_args(ctx, args)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/mailman/venv/lib/python3.11/site-packages/click/core.py", line
1408, in parse_args
    value, args = param.handle_parse_result(ctx, opts, args)
                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/mailman/venv/lib/python3.11/site-packages/click/core.py", line
2400, in handle_parse_result
    value = self.process_value(ctx, value)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/mailman/venv/lib/python3.11/site-packages/click/core.py", line
2362, in process_value
    value = self.callback(ctx, self, value)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File
"/opt/mailman/venv/lib/python3.11/site-packages/mailman/bin/mailman.py",
line 95, in initialize_config
    initialize(value)
  File
"/opt/mailman/venv/lib/python3.11/site-packages/mailman/core/initialize.py",
line 229, in initialize
    initialize_2(propagate_logs=propagate_logs)
  File
"/opt/mailman/venv/lib/python3.11/site-packages/mailman/core/initialize.py",
line 198, in initialize_2
    initialize_pipelines()
  File
"/opt/mailman/venv/lib/python3.11/site-packages/mailman/core/pipelines.py",
line 73, in initialize
    add_components('handlers', IHandler, config.handlers)
  File
"/opt/mailman/venv/lib/python3.11/site-packages/mailman/utilities/modules.py",
line 218, in add_components
    for component_class in find_pluggable_components(subpackage, interface):
  File
"/opt/mailman/venv/lib/python3.11/site-packages/mailman/utilities/modules.py",
line 177, in find_pluggable_components
    yield from find_components('mailman.' + subpackage, interface)
  File
"/opt/mailman/venv/lib/python3.11/site-packages/mailman/utilities/modules.py",
line 150, in find_components
    module = import_module(module_name)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/lib/python3.11/importlib/__init__.py", line 126, in
import_module
    return _bootstrap._gcd_import(name[level:], package, level)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "<frozen importlib._bootstrap>", line 1206, in _gcd_import
  File "<frozen importlib._bootstrap>", line 1178, in _find_and_load
  File "<frozen importlib._bootstrap>", line 1149, in
_find_and_load_unlocked
  File "<frozen importlib._bootstrap>", line 690, in _load_unlocked
  File "<frozen importlib._bootstrap_external>", line 940, in exec_module
  File "<frozen importlib._bootstrap>", line 241, in
_call_with_frames_removed
  File
"/opt/mailman/venv/lib/python3.11/site-packages/mailman/handlers/arc_sign.py",
line 22, in <module>
    from authheaders import sign_message
  File
"/opt/mailman/venv/lib/python3.11/site-packages/authheaders/__init__.py",
line 25, in <module>
    from authheaders.dmarc_lookup import dns_query, receiver_record,
receiver_record_walk, get_org_domain
  File
"/opt/mailman/venv/lib/python3.11/site-packages/authheaders/dmarc_lookup.py",
line 22, in <module>
    from pkg_resources import resource_filename  # Part of setuptools
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File
"/opt/mailman/venv/lib/python3.11/site-packages/pkg_resources/__init__.py",
line 3248, in <module>
    @_call_aside
     ^^^^^^^^^^^
  File
"/opt/mailman/venv/lib/python3.11/site-packages/pkg_resources/__init__.py",
line 3223, in _call_aside
    f(*args, **kwargs)
  File
"/opt/mailman/venv/lib/python3.11/site-packages/pkg_resources/__init__.py",
line 3261, in _initialize_master_working_set
    working_set = WorkingSet._build_master()
                  ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File
"/opt/mailman/venv/lib/python3.11/site-packages/pkg_resources/__init__.py",
line 617, in _build_master
    ws.require(__requires__)
  File
"/opt/mailman/venv/lib/python3.11/site-packages/pkg_resources/__init__.py",
line 956, in require
    needed = self.resolve(parse_requirements(requirements))
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File
"/opt/mailman/venv/lib/python3.11/site-packages/pkg_resources/__init__.py",
line 815, in resolve
    dist = self._resolve_dist(
           ^^^^^^^^^^^^^^^^^^^
  File
"/opt/mailman/venv/lib/python3.11/site-packages/pkg_resources/__init__.py",
line 856, in _resolve_dist
    raise DistributionNotFound(req, requirers)
pkg_resources.DistributionNotFound: The 'flufl.lock>=5.1' distribution was
not found and is required by mailman

Looking at what I have:

(venv) mailman@debian12:~$ pip freeze | grep flufl.lock flufl.lock==8.0.2 (venv) mailman@debian12:~$

Now I am lost as to what the issue could be.

-- Best regards, Odhiambo WASHINGTON, Nairobi,KE +254 7 3200 0004/+254 7 2274 3223 "Oh, the cruft.", egrep -v '^$|^.*#' ¯\_(ツ)_/¯ :-) [How to ask smart questions: http://www.catb.org/~esr/faqs/smart-questions.html]