nautobot icon indicating copy to clipboard operation
nautobot copied to clipboard

Published 20 hours ago • verified publisher icon nautobot

Unable to stand up nautobot in a HA mode (one write instance & one read-only instance)

Open gneville-ot opened this issue 1 year ago • 5 comments

Environment

  • Nautobot version (Docker tag too if applicable): 1.3.6
  • Python version: 3.10
  • Database platform, version: postgres
  • Middleware(s):

Steps to Reproduce

  1. Standup nautobot using docker-compose but change the compose file so the database port is exposed
  2. Connect to the postgres database and create a read-only user and set transactions to read-only
CREATE USER nautobot_read WITH PASSWORD 'readonly';
GRANT CONNECT ON DATABASE nautobot TO nautobot_read;
GRANT USAGE ON SCHEMA public TO nautobot_read;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO nautobot_read;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO nautobot_read;
ALTER USER nautobot_read set default_transaction_read_only = on;
  1. Copy the docker-compose directory to another folder and change the compose file to use a different port for exposing the web interface and remove the db container and volume
  2. Change the local.env to update the DB settings so they use a DB host of the local IP on the machine and the database port that was exposed in step 1 and the DB account/password for the read user setup in Step 2.

Expected Behavior

I'm able to spin up a write instance and a separate read-only instance.

The idea being that nautobot can be hosted in two different sites, one site with write the other for read, the redis cluster is synced cross-site and the database has read-only replicas in the other site.

Observed Behavior

The read-only instance complains it doesn't have the permissions to perform INSERT/UPDATE statements in different tables.

nauto_read-celery_beat-1      | Traceback (most recent call last):
nauto_read-celery_beat-1      |   File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 84, in _execute
nauto_read-celery_beat-1      |     return self.cursor.execute(sql, params)
nauto_read-celery_beat-1      | psycopg2.errors.ReadOnlySqlTransaction: cannot execute UPDATE in a read-only transaction
nauto_read-celery_beat-1      | 
nauto_read-celery_beat-1      | 
nauto_read-celery_beat-1      | The above exception was the direct cause of the following exception:
nauto_read-celery_beat-1      | 
nauto_read-celery_beat-1      | Traceback (most recent call last):
nauto_read-celery_beat-1      |   File "/usr/local/lib/python3.10/site-packages/django_celery_beat/schedulers.py", line 320, in update_from_dict
nauto_read-celery_beat-1      |     entry = self.Entry.from_entry(name,
nauto_read-celery_beat-1      |   File "/usr/local/lib/python3.10/site-packages/django_celery_beat/schedulers.py", line 180, in from_entry
nauto_read-celery_beat-1      |     name=name, defaults=cls._unpack_fields(**entry),
nauto_read-celery_beat-1      |   File "/usr/local/lib/python3.10/site-packages/django_celery_beat/schedulers.py", line 187, in _unpack_fields
nauto_read-celery_beat-1      |     model_schedule, model_field = cls.to_model_schedule(schedule)
nauto_read-celery_beat-1      |   File "/usr/local/lib/python3.10/site-packages/django_celery_beat/schedulers.py", line 172, in to_model_schedule
nauto_read-celery_beat-1      |     model_schedule.save()
nauto_read-celery_beat-1      |   File "/usr/local/lib/python3.10/site-packages/django/db/models/base.py", line 739, in save
nauto_read-celery_beat-1      |     self.save_base(using=using, force_insert=force_insert,
nauto_read-celery_beat-1      |   File "/usr/local/lib/python3.10/site-packages/django/db/models/base.py", line 776, in save_base
nauto_read-celery_beat-1      |     updated = self._save_table(
nauto_read-celery_beat-1      |   File "/usr/local/lib/python3.10/site-packages/django/db/models/base.py", line 858, in _save_table
nauto_read-celery_beat-1      |     updated = self._do_update(base_qs, using, pk_val, values, update_fields,
nauto_read-celery_beat-1      |   File "/usr/local/lib/python3.10/site-packages/django/db/models/base.py", line 912, in _do_update
nauto_read-celery_beat-1      |     return filtered._update(values) > 0
nauto_read-celery_beat-1      |   File "/usr/local/lib/python3.10/site-packages/django/db/models/query.py", line 802, in _update
nauto_read-celery_beat-1      |     return query.get_compiler(self.db).execute_sql(CURSOR)
nauto_read-celery_beat-1      |   File "/usr/local/lib/python3.10/site-packages/django/db/models/sql/compiler.py", line 1559, in execute_sql
nauto_read-celery_beat-1      |     cursor = super().execute_sql(result_type)
nauto_read-celery_beat-1      |   File "/usr/local/lib/python3.10/site-packages/django/db/models/sql/compiler.py", line 1175, in execute_sql
nauto_read-celery_beat-1      |     cursor.execute(sql, params)
nauto_read-celery_beat-1      |   File "/usr/local/lib/python3.10/site-packages/cacheops/transaction.py", line 97, in execute
nauto_read-celery_beat-1      |     result = self._no_monkey.execute(self, sql, params)
nauto_read-celery_beat-1      |   File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 66, in execute
nauto_read-celery_beat-1      |     return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
nauto_read-celery_beat-1      |   File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
nauto_read-celery_beat-1      |     return executor(sql, params, many, context)
nauto_read-celery_beat-1      |   File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 79, in _execute
nauto_read-celery_beat-1      |     with self.db.wrap_database_errors:
nauto_read-celery_beat-1      |   File "/usr/local/lib/python3.10/site-packages/django/db/utils.py", line 90, in __exit__
nauto_read-celery_beat-1      |     raise dj_exc_value.with_traceback(traceback) from exc_value
nauto_read-celery_beat-1      |   File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 84, in _execute
nauto_read-celery_beat-1      |     return self.cursor.execute(sql, params)
nauto_read-celery_beat-1      | django.db.utils.InternalError: cannot execute UPDATE in a read-only transaction
nauto_read-celery_beat-1      | 

nauto_read-nautobot-1         | Traceback (most recent call last):
nauto_read-nautobot-1         |   File "/usr/local/lib/python3.10/site-packages/django/db/models/query.py", line 581, in get_or_create
nauto_read-nautobot-1         |     return self.get(**kwargs), False
nauto_read-nautobot-1         |   File "/usr/local/lib/python3.10/site-packages/cacheops/query.py", line 351, in get
nauto_read-nautobot-1         |     return qs._no_monkey.get(qs, *args, **kwargs)
nauto_read-nautobot-1         |   File "/usr/local/lib/python3.10/site-packages/django/db/models/query.py", line 435, in get
nauto_read-nautobot-1         |     raise self.model.DoesNotExist(
nauto_read-nautobot-1         | django.contrib.contenttypes.models.ContentType.DoesNotExist: ContentType matching query does not exist.
nauto_read-nautobot-1         | 
nauto_read-nautobot-1         | During handling of the above exception, another exception occurred:
nauto_read-nautobot-1         | 
nauto_read-nautobot-1         | Traceback (most recent call last):
nauto_read-nautobot-1         |   File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 84, in _execute
nauto_read-nautobot-1         |     return self.cursor.execute(sql, params)
nauto_read-nautobot-1         | psycopg2.errors.ReadOnlySqlTransaction: cannot execute INSERT in a read-only transaction
nauto_read-nautobot-1         | 
nauto_read-nautobot-1         | 
nauto_read-nautobot-1         | The above exception was the direct cause of the following exception:
nauto_read-nautobot-1         | 
nauto_read-nautobot-1         | Traceback (most recent call last):
nauto_read-nautobot-1         |   File "/usr/local/bin/nautobot-server", line 8, in <module>
nauto_read-nautobot-1         |     sys.exit(main())
nauto_read-nautobot-1         |   File "/usr/local/lib/python3.10/site-packages/nautobot/core/cli.py", line 54, in main
nauto_read-nautobot-1         |     run_app(
nauto_read-nautobot-1         |   File "/usr/local/lib/python3.10/site-packages/nautobot/core/runner/runner.py", line 266, in run_app
nauto_read-nautobot-1         |     management.execute_from_command_line([runner_name, command] + command_args)
nauto_read-nautobot-1         |   File "/usr/local/lib/python3.10/site-packages/django/core/management/__init__.py", line 419, in execute_from_command_line
nauto_read-nautobot-1         |     utility.execute()
nauto_read-nautobot-1         |   File "/usr/local/lib/python3.10/site-packages/django/core/management/__init__.py", line 413, in execute
nauto_read-nautobot-1         |     self.fetch_command(subcommand).run_from_argv(self.argv)
nauto_read-nautobot-1         |   File "/usr/local/lib/python3.10/site-packages/django/core/management/base.py", line 354, in run_from_argv
nauto_read-nautobot-1         |     self.execute(*args, **cmd_options)
nauto_read-nautobot-1         |   File "/usr/local/lib/python3.10/site-packages/django/core/management/base.py", line 398, in execute
nauto_read-nautobot-1         |     output = self.handle(*args, **options)
nauto_read-nautobot-1         |   File "/usr/local/lib/python3.10/site-packages/nautobot/core/management/commands/post_upgrade.py", line 78, in handle
nauto_read-nautobot-1         |     call_command(
nauto_read-nautobot-1         |   File "/usr/local/lib/python3.10/site-packages/django/core/management/__init__.py", line 181, in call_command
nauto_read-nautobot-1         |     return command.execute(*args, **defaults)
nauto_read-nautobot-1         |   File "/usr/local/lib/python3.10/site-packages/django/core/management/base.py", line 398, in execute
nauto_read-nautobot-1         |     output = self.handle(*args, **options)
nauto_read-nautobot-1         |   File "/usr/local/lib/python3.10/site-packages/django/core/management/base.py", line 89, in wrapped
nauto_read-nautobot-1         |     res = handle_func(*args, **kwargs)
nauto_read-nautobot-1         |   File "/usr/local/lib/python3.10/site-packages/django/core/management/commands/migrate.py", line 268, in handle
nauto_read-nautobot-1         |     emit_post_migrate_signal(
nauto_read-nautobot-1         |   File "/usr/local/lib/python3.10/site-packages/django/core/management/sql.py", line 42, in emit_post_migrate_signal
nauto_read-nautobot-1         |     models.signals.post_migrate.send(
nauto_read-nautobot-1         |   File "/usr/local/lib/python3.10/site-packages/django/dispatch/dispatcher.py", line 180, in send
nauto_read-nautobot-1         |     return [
nauto_read-nautobot-1         |   File "/usr/local/lib/python3.10/site-packages/django/dispatch/dispatcher.py", line 181, in <listcomp>
nauto_read-nautobot-1         |     (receiver, receiver(signal=self, sender=sender, **named))
nauto_read-nautobot-1         |   File "/usr/local/lib/python3.10/site-packages/constance/apps.py", line 28, in create_perm
nauto_read-nautobot-1         |     content_type, created = ContentType.objects.using(using).get_or_create(
nauto_read-nautobot-1         |   File "/usr/local/lib/python3.10/site-packages/django/db/models/query.py", line 588, in get_or_create
nauto_read-nautobot-1         |     return self.create(**params), True
nauto_read-nautobot-1         |   File "/usr/local/lib/python3.10/site-packages/django/db/models/query.py", line 453, in create
nauto_read-nautobot-1         |     obj.save(force_insert=True, using=self.db)
nauto_read-nautobot-1         |   File "/usr/local/lib/python3.10/site-packages/django/db/models/base.py", line 739, in save
nauto_read-nautobot-1         |     self.save_base(using=using, force_insert=force_insert,
nauto_read-nautobot-1         |   File "/usr/local/lib/python3.10/site-packages/django/db/models/base.py", line 776, in save_base
nauto_read-nautobot-1         |     updated = self._save_table(
nauto_read-nautobot-1         |   File "/usr/local/lib/python3.10/site-packages/django/db/models/base.py", line 881, in _save_table
nauto_read-nautobot-1         |     results = self._do_insert(cls._base_manager, using, fields, returning_fields, raw)
nauto_read-nautobot-1         |   File "/usr/local/lib/python3.10/site-packages/django/db/models/base.py", line 919, in _do_insert
nauto_read-nautobot-1         |     return manager._insert(
nauto_read-nautobot-1         |   File "/usr/local/lib/python3.10/site-packages/django/db/models/manager.py", line 85, in manager_method
nauto_read-nautobot-1         |     return getattr(self.get_queryset(), name)(*args, **kwargs)
nauto_read-nautobot-1         |   File "/usr/local/lib/python3.10/site-packages/django/db/models/query.py", line 1270, in _insert
nauto_read-nautobot-1         |     return query.get_compiler(using=using).execute_sql(returning_fields)
nauto_read-nautobot-1         |   File "/usr/local/lib/python3.10/site-packages/django/db/models/sql/compiler.py", line 1416, in execute_sql
nauto_read-nautobot-1         |     cursor.execute(sql, params)
nauto_read-nautobot-1         |   File "/usr/local/lib/python3.10/site-packages/cacheops/transaction.py", line 97, in execute
nauto_read-nautobot-1         |     result = self._no_monkey.execute(self, sql, params)
nauto_read-nautobot-1         |   File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 66, in execute
nauto_read-nautobot-1         |     return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
nauto_read-nautobot-1         |   File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
nauto_read-nautobot-1         |     return executor(sql, params, many, context)
nauto_read-nautobot-1         |   File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 79, in _execute
nauto_read-nautobot-1         |     with self.db.wrap_database_errors:
nauto_read-nautobot-1         |   File "/usr/local/lib/python3.10/site-packages/django/db/utils.py", line 90, in __exit__
nauto_read-nautobot-1         |     raise dj_exc_value.with_traceback(traceback) from exc_value
nauto_read-nautobot-1         |   File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 84, in _execute
nauto_read-nautobot-1         |     return self.cursor.execute(sql, params)
nauto_read-nautobot-1         | django.db.utils.InternalError: cannot execute INSERT in a read-only transaction

gneville-ot avatar Sep 23 '22 13:09 gneville-ot

We also have plans to bring up a read-only instance (with access to a database replica). I was hoping this was possible, but this issue lowers my expectations 😄

Is there any supported way to achieve something like this? A secondary Nautobot installation using the same backend database in a read-only fashion?

joaopsys avatar Sep 27 '22 12:09 joaopsys

You should be able to put your read-only instance into maintenance mode (see: https://nautobot.readthedocs.io/en/stable/configuration/optional-settings/?h=maint#maintenance_mode) which will stop the inserts and updates from that application instance to the connected database.

bryanculver avatar Sep 27 '22 19:09 bryanculver

Thanks for the suggestion @bryanculver , that's got a little further but I still see two problems.

  • celery beat still complains about it trying to do an UPDATE statement. I guess celery beat can be dropped for the read-only instance
  • the 'nautobot' container now starts but when you try to hit it externally it errors saying it can't find any files in /static/

This is what I did:

Added these to the local.env file:

NAUTOBOT_MAINTENANCE_MODE=True
NAUTOBOT_DOCKER_SKIP_INIT=True

Altered a local copy of nautobot_config.py to add this line:

SESSION_ENGINE = "django.contrib.sessions.backends.cache"

Altered the docker-compose.yml to mount override the nautobot_config.py files in all containers except redis:

    volumes:
      - ${PWD}/nautobot_config.py:/opt/nautobot/nautobot_config.py

I tried exec'ing in to the container and can see my new config file is mounted and I see other files and directories under /opt/nautobot/ but the /static directory is empty. This isn't a problem with the "write" instance.

gneville-ot avatar Sep 28 '22 10:09 gneville-ot

In debugging a little with @gneville-ot on Slack, I have a root cause at least for the issue with static files when NAUTOBOT_DOCKER_SKIP_INIT=True is set. This results in nautobot-server post_upgrade not being called AT ALL, which I would reckon is too heavy-handed for this use-case:

https://github.com/nautobot/nautobot/blob/487b26e88bdb93ea3a5b07bc98b0575724479ba6/docker/docker-entrypoint.sh#L12-L26

We should consider making each of this post_upgrade steps discretely toggled in some way.

jathanism avatar Oct 07 '22 19:10 jathanism

@jathanism, I've raised https://github.com/nautobot/nautobot/pull/2600.

I can now stand up the read-only instance, running in k8s. I can browse to the GUI and API access via Token works.

I do however still have an outstanding issue, I'm unable to login via the GUI using local or SSO accounts.

I was hoping to use a redis cluster that is local to where the read-only instance is running, the active/write instance of nautobot is in another site. We have cross-site replication for our redis clusters, but in one site it is set to a read mode. When the nautobot read-only instance points to its local read redis cluster, in read mode, I get the following error:

Setting NAUTOBOT_CACHEOPS_ENABLED to False makes no difference.

unavailable: Unable to connect to Redis Sentinel: MasterNotFoundError
Traceback (most recent call last):
  File "/usr/local/lib/python3.10/site-packages/nautobot/extras/health_checks.py", line 51, in check_status
    master.ping()
  File "/usr/local/lib/python3.10/site-packages/redis/commands/core.py", line 1053, in ping
    return self.execute_command("PING", **kwargs)
  File "/usr/local/lib/python3.10/site-packages/redis/client.py", line 1215, in execute_command
    conn = self.connection or pool.get_connection(command_name, **options)
  File "/usr/local/lib/python3.10/site-packages/redis/connection.py", line 1386, in get_connection
    connection.connect()
  File "/usr/local/lib/python3.10/site-packages/redis/sentinel.py", line 54, in connect
    return self.retry.call_with_retry(
  File "/usr/local/lib/python3.10/site-packages/redis/retry.py", line 50, in call_with_retry
    raise error
  File "/usr/local/lib/python3.10/site-packages/redis/retry.py", line 45, in call_with_retry
    return do()
  File "/usr/local/lib/python3.10/site-packages/redis/sentinel.py", line 44, in _connect_retry
    self.connect_to(self.connection_pool.get_master_address())
  File "/usr/local/lib/python3.10/site-packages/redis/sentinel.py", line 117, in get_master_address
    master_address = self.sentinel_manager.discover_master(self.service_name)
  File "/usr/local/lib/python3.10/site-packages/redis/sentinel.py", line 251, in discover_master
    raise MasterNotFoundError(f"No master found for {service_name!r}")
redis.sentinel.MasterNotFoundError: No master found for 'nautobot'

I wonder if the master.ping() call in health_checks.py should be changed so if NAUTOBOT_MAINTENANCE_MODE=True or another envvar is set, then it doesn't execute this check?

In order to work around this I've changed the read-only instance to point back to the other site's redis cluster, but when I do this I get this issue when attempting to login using a local account:

  File "/usr/local/lib/python3.10/site-packages/redis/connection.py", line 959, in __init__
    super().__init__(**kwargs)
TypeError: Connection.__init__() got an unexpected keyword argument 'connection_pool'

When attempting to login using OKTA, our SSO provider, then I see this issue:

2022-10-10 11:19:19.113 ERROR   django.request :
  Internal Server Error: /login/okta-openidconnect/
Traceback (most recent call last):
  File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.ReadOnlySqlTransaction: cannot execute INSERT in a read-only transaction

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

Traceback (most recent call last):
  File "/usr/local/lib/python3.10/site-packages/django/core/handlers/exception.py", line 47, in inner
    response = get_response(request)
  File "/usr/local/lib/python3.10/site-packages/django/core/handlers/base.py", line 181, in _get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)
  File "/usr/local/lib/python3.10/site-packages/django/views/decorators/cache.py", line 44, in _wrapped_view_func
    response = view_func(request, *args, **kwargs)
  File "/usr/local/lib/python3.10/site-packages/social_django/utils.py", line 46, in wrapper
    return func(request, backend, *args, **kwargs)
  File "/usr/local/lib/python3.10/site-packages/social_django/views.py", line 23, in auth
    return do_auth(request.backend, redirect_name=REDIRECT_FIELD_NAME)
  File "/usr/local/lib/python3.10/site-packages/social_core/actions.py", line 29, in do_auth
    return backend.start()
  File "/usr/local/lib/python3.10/site-packages/social_core/backends/base.py", line 35, in start
    return self.strategy.redirect(self.auth_url())
  File "/usr/local/lib/python3.10/site-packages/social_core/backends/oauth.py", line 324, in auth_url
    params = self.auth_params(state)
  File "/usr/local/lib/python3.10/site-packages/social_core/backends/open_id_connect.py", line 98, in auth_params
    params['nonce'] = self.get_and_store_nonce(
  File "/usr/local/lib/python3.10/site-packages/social_core/backends/open_id_connect.py", line 108, in get_and_store_nonce
    self.strategy.storage.association.store(url, association)
  File "/usr/local/lib/python3.10/site-packages/social_django/storage.py", line 181, in store
    assoc.save()
  File "/usr/local/lib/python3.10/site-packages/django/db/models/base.py", line 739, in save
    self.save_base(using=using, force_insert=force_insert,
  File "/usr/local/lib/python3.10/site-packages/django/db/models/base.py", line 776, in save_base
    updated = self._save_table(
  File "/usr/local/lib/python3.10/site-packages/django/db/models/base.py", line 881, in _save_table
    results = self._do_insert(cls._base_manager, using, fields, returning_fields, raw)
  File "/usr/local/lib/python3.10/site-packages/django/db/models/base.py", line 919, in _do_insert
    return manager._insert(
  File "/usr/local/lib/python3.10/site-packages/django/db/models/manager.py", line 85, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/usr/local/lib/python3.10/site-packages/django/db/models/query.py", line 1270, in _insert
    return query.get_compiler(using=using).execute_sql(returning_fields)
  File "/usr/local/lib/python3.10/site-packages/django/db/models/sql/compiler.py", line 1416, in execute_sql
    cursor.execute(sql, params)
  File "/usr/local/lib/python3.10/site-packages/cacheops/transaction.py", line 97, in execute
    result = self._no_monkey.execute(self, sql, params)
  File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 66, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 79, in _execute
    with self.db.wrap_database_errors:
  File "/usr/local/lib/python3.10/site-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.InternalError: cannot execute INSERT in a read-only transaction

================== Adding as a note: In order to stand up a read-only instance I had to set the following envvars, with the changes raised in the PR included.

  • Set envvar SESSION_ENGINE to django.contrib.sessions.backends.cache
  • Set envvar NAUTOBOT_MAINTENANCE_MODE to True
  • Set envvar NAUTOBOT_DOCKER_SKIP_INIT to True
  • Set envvar NAUTOBOT_CACHEOPS_ENABLED to False

gneville-ot avatar Oct 10 '22 11:10 gneville-ot

I just want to thank @gneville-ot and @jathanism for all the troubleshooting done here.

I managed to successfully bring up a read-only instance thanks to all the advice in this thread. Granted I'm not using authentication, so I'm not facing the same issues as @gneville-ot, but the instance itself is up and running.

joaopsys avatar Oct 18 '22 15:10 joaopsys

When it comes to that authentiction error @gneville-ot and @jathanism, I believe Nautobot still tries to update the last login time under MAINTENANCE_MODE in some cases, for example when it comes to remote user authentication.

The MAINTENANCE_MODE check exists in users/views.py - https://github.com/nautobot/nautobot/blob/21c85f25e6458ad97e62484bc89e5effa1328a80/nautobot/users/views.py#L67

However remote user authentication doesn't seem to pass that check, it goes straight to core/middleware.py and is passed to Django, right? https://github.com/nautobot/nautobot/blob/21c85f25e6458ad97e62484bc89e5effa1328a80/nautobot/core/middleware.py#L23

I have fixed this by adding the following to core/middleware.py under process_request under class RemoteUserMiddleware(RemoteUserMiddleware_):

+ from django.contrib.auth.signals import user_logged_in
+ from django.contrib.auth.models import update_last_login
...
...
class RemoteUserMiddleware(RemoteUserMiddleware_):
...
    def process_request(self, request):
    ...
+        if settings.MAINTENANCE_MODE:
 +           user_logged_in.disconnect(update_last_login, dispatch_uid="update_last_login")

I have no clue if this is the right approach though, but it did fix it for me.

joaopsys avatar Oct 19 '22 12:10 joaopsys

Thanks @joaopsys for the in-depth debugging. Seems like this can result in some extra documentation and a small fix here.

jathanism avatar Oct 19 '22 22:10 jathanism

PR https://github.com/nautobot/nautobot/pull/2656 raised

joaopsys avatar Oct 20 '22 08:10 joaopsys

Thanks for the extra work @joaopsys and glad to see you were able to stand up a read-only instance. However with regards to the auth problem it appears your fix only applies if you are using HTTP headers for auth.

I'm using SSO (OKTA) for auth, I did try adding your code to class ExternalAuthMiddleware(MiddlewareMixin): but I still get an cannot execute INSERT in a read-only transaction.

@jathanism, any ideas how to over come this issue?

2022-10-10 11:19:19.113 ERROR   django.request :
  Internal Server Error: /login/okta-openidconnect/
Traceback (most recent call last):
  File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.ReadOnlySqlTransaction: cannot execute INSERT in a read-only transaction

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

Traceback (most recent call last):
  File "/usr/local/lib/python3.10/site-packages/django/core/handlers/exception.py", line 47, in inner
    response = get_response(request)
  File "/usr/local/lib/python3.10/site-packages/django/core/handlers/base.py", line 181, in _get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)
  File "/usr/local/lib/python3.10/site-packages/django/views/decorators/cache.py", line 44, in _wrapped_view_func
    response = view_func(request, *args, **kwargs)
  File "/usr/local/lib/python3.10/site-packages/social_django/utils.py", line 46, in wrapper
    return func(request, backend, *args, **kwargs)
  File "/usr/local/lib/python3.10/site-packages/social_django/views.py", line 23, in auth
    return do_auth(request.backend, redirect_name=REDIRECT_FIELD_NAME)
  File "/usr/local/lib/python3.10/site-packages/social_core/actions.py", line 29, in do_auth
    return backend.start()
  File "/usr/local/lib/python3.10/site-packages/social_core/backends/base.py", line 35, in start
    return self.strategy.redirect(self.auth_url())
  File "/usr/local/lib/python3.10/site-packages/social_core/backends/oauth.py", line 324, in auth_url
    params = self.auth_params(state)
  File "/usr/local/lib/python3.10/site-packages/social_core/backends/open_id_connect.py", line 98, in auth_params
    params['nonce'] = self.get_and_store_nonce(
  File "/usr/local/lib/python3.10/site-packages/social_core/backends/open_id_connect.py", line 108, in get_and_store_nonce
    self.strategy.storage.association.store(url, association)
  File "/usr/local/lib/python3.10/site-packages/social_django/storage.py", line 181, in store
    assoc.save()
  File "/usr/local/lib/python3.10/site-packages/django/db/models/base.py", line 739, in save
    self.save_base(using=using, force_insert=force_insert,
  File "/usr/local/lib/python3.10/site-packages/django/db/models/base.py", line 776, in save_base
    updated = self._save_table(
  File "/usr/local/lib/python3.10/site-packages/django/db/models/base.py", line 881, in _save_table
    results = self._do_insert(cls._base_manager, using, fields, returning_fields, raw)
  File "/usr/local/lib/python3.10/site-packages/django/db/models/base.py", line 919, in _do_insert
    return manager._insert(
  File "/usr/local/lib/python3.10/site-packages/django/db/models/manager.py", line 85, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/usr/local/lib/python3.10/site-packages/django/db/models/query.py", line 1270, in _insert
    return query.get_compiler(using=using).execute_sql(returning_fields)
  File "/usr/local/lib/python3.10/site-packages/django/db/models/sql/compiler.py", line 1416, in execute_sql
    cursor.execute(sql, params)
  File "/usr/local/lib/python3.10/site-packages/cacheops/transaction.py", line 97, in execute
    result = self._no_monkey.execute(self, sql, params)
  File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 66, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 79, in _execute
    with self.db.wrap_database_errors:
  File "/usr/local/lib/python3.10/site-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.InternalError: cannot execute INSERT in a read-only transaction

gneville-ot avatar Oct 20 '22 10:10 gneville-ot

@gneville-ot Can you enable DEBUG mode, so can we confirm that this is still related to the update of the last login time? You should see the exact SQL query it tries to do in debug mode

I'm assuming all users already exist in the read-only database right?

joaopsys avatar Oct 20 '22 11:10 joaopsys

@joaopsys, with DEBUG enabled, it looks to be due to django wanting to perform an INSERT in to the social_auth_association table, therefore ignoring the NAUTOBOT_SESSION_ENGINE=django.contrib.sessions.backends.cache setting.


nauto_read-nautobot-1  | Traceback (most recent call last):
nauto_read-nautobot-1  |   File "/usr/local/lib/python3.10/site-packages/django/template/base.py", line 848, in _resolve_lookup
nauto_read-nautobot-1  |     raise VariableDoesNotExist("Failed lookup for key "
nauto_read-nautobot-1  | django.template.base.VariableDoesNotExist: Failed lookup for key [q] in <QueryDict: {}>
nauto_read-nautobot-1  | 16:40:37.369 DEBUG   django.db.backends :
nauto_read-nautobot-1  |   (0.003) SELECT "social_auth_association"."id", "social_auth_association"."server_url", "social_auth_association"."handle", "social_auth_association"."secret", "social_auth_association"."issued", "social_auth_association"."lifetime", "social_auth_association"."assoc_type" FROM "social_auth_association" WHERE ("social_auth_association"."handle" = '<key>' AND "social_auth_association"."server_url" = 'https://<domain>.okta.com/oauth2/v1/authorize') LIMIT 21; args=('<key>', 'https://<domain>/oauth2/v1/authorize')
nauto_read-nautobot-1  | 16:40:37.372 DEBUG   django.db.backends :
nauto_read-nautobot-1  |   (0.002) INSERT INTO "social_auth_association" ("server_url", "handle", "secret", "issued", "lifetime", "assoc_type") VALUES ('https://<domain>/oauth2/v1/authorize', '<key>', '', 0, 0, '<key>') RETURNING "social_auth_association"."id"; args=('https://<domain>.okta.com/oauth2/v1/authorize', '<key>', '', 0, 0, '<key>')
nauto_read-nautobot-1  | 16:40:37.396 DEBUG   django.db.backends :
nauto_read-nautobot-1  |   (0.002) SELECT "social_auth_association"."id", "social_auth_association"."server_url", "social_auth_association"."handle", "social_auth_association"."secret", "social_auth_association"."issued", "social_auth_association"."lifetime", "social_auth_association"."assoc_type" FROM "social_auth_association" LIMIT 21; args=()
nauto_read-nautobot-1  | 16:40:37.451 ERROR   django.request :
nauto_read-nautobot-1  |   Internal Server Error: /login/okta-openidconnect/
nauto_read-nautobot-1  | Traceback (most recent call last):
nauto_read-nautobot-1  |   File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 84, in _execute
nauto_read-nautobot-1  |     return self.cursor.execute(sql, params)
nauto_read-nautobot-1  | psycopg2.errors.ReadOnlySqlTransaction: cannot execute INSERT in a read-only transaction

gneville-ot avatar Oct 20 '22 16:10 gneville-ot

@bryanculver , this is still an issue and pending #2600

grahamneville avatar Nov 28 '22 19:11 grahamneville

@grahamneville Can you confirm again? @joaopsys made tweaks that further respected read-only earlier in the app startup. I see there is some valuable adds to #2600 for env flags but that can be a future follow-on since they can also be set in the settings.py.

bryanculver avatar Nov 28 '22 20:11 bryanculver

@bryanculver - I've just upgraded to 1.5.5 and tested this out. I'm still not able to login using SSO (OKTA) with a read-only instance.

I do see this in the logs to show it's running in maintenance mode:

2022-12-14 13:01:42.524 WARNING nautobot.core.apps :
  Maintenance mode enabled: disabling update of most recent login time

This is the error:

spawned uWSGI master process (pid: 1)
spawned uWSGI worker 1 (pid: 85, cores: 1)
2022-12-14 13:01:56.370 INFO    nautobot.core.wsgi :
  Closing existing DB and cache connections on worker 1 after uWSGI forked ...
spawned uWSGI worker 2 (pid: 86, cores: 1)
2022-12-14 13:01:56.373 INFO    nautobot.core.wsgi :
  Closing existing DB and cache connections on worker 2 after uWSGI forked ...
spawned uWSGI worker 3 (pid: 87, cores: 1)
2022-12-14 13:01:56.375 INFO    nautobot.core.wsgi :
  Closing existing DB and cache connections on worker 3 after uWSGI forked ...
spawned uWSGI http 1 (pid: 88)
2022-12-14 13:03:06.354 ERROR   django.request :
  Internal Server Error: /login/okta-openidconnect/
Traceback (most recent call last):
  File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.ReadOnlySqlTransaction: cannot execute INSERT in a read-only transaction


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

Traceback (most recent call last):
  File "/usr/local/lib/python3.10/site-packages/django/core/handlers/exception.py", line 47, in inner
    response = get_response(request)
  File "/usr/local/lib/python3.10/site-packages/django/core/handlers/base.py", line 181, in _get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)
  File "/usr/local/lib/python3.10/site-packages/django/views/decorators/cache.py", line 44, in _wrapped_view_func
    response = view_func(request, *args, **kwargs)
  File "/usr/local/lib/python3.10/site-packages/social_django/utils.py", line 46, in wrapper
    return func(request, backend, *args, **kwargs)
  File "/usr/local/lib/python3.10/site-packages/social_django/views.py", line 23, in auth
    return do_auth(request.backend, redirect_name=REDIRECT_FIELD_NAME)
  File "/usr/local/lib/python3.10/site-packages/social_core/actions.py", line 29, in do_auth
    return backend.start()
  File "/usr/local/lib/python3.10/site-packages/social_core/backends/base.py", line 35, in start
    return self.strategy.redirect(self.auth_url())
  File "/usr/local/lib/python3.10/site-packages/social_core/backends/oauth.py", line 328, in auth_url
    params = self.auth_params(state)
  File "/usr/local/lib/python3.10/site-packages/social_core/backends/open_id_connect.py", line 118, in auth_params
    params['nonce'] = self.get_and_store_nonce(
  File "/usr/local/lib/python3.10/site-packages/social_core/backends/open_id_connect.py", line 128, in get_and_store_nonce
    self.strategy.storage.association.store(url, association)
  File "/usr/local/lib/python3.10/site-packages/social_django/storage.py", line 181, in store
    assoc.save()
  File "/usr/local/lib/python3.10/site-packages/django/db/models/base.py", line 739, in save
    self.save_base(using=using, force_insert=force_insert,
  File "/usr/local/lib/python3.10/site-packages/django/db/models/base.py", line 776, in save_base
    updated = self._save_table(
  File "/usr/local/lib/python3.10/site-packages/django/db/models/base.py", line 881, in _save_table
    results = self._do_insert(cls._base_manager, using, fields, returning_fields, raw)
  File "/usr/local/lib/python3.10/site-packages/django/db/models/base.py", line 919, in _do_insert
    return manager._insert(
  File "/usr/local/lib/python3.10/site-packages/django/db/models/manager.py", line 85, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/usr/local/lib/python3.10/site-packages/django/db/models/query.py", line 1270, in _insert
    return query.get_compiler(using=using).execute_sql(returning_fields)
  File "/usr/local/lib/python3.10/site-packages/django/db/models/sql/compiler.py", line 1416, in execute_sql
    cursor.execute(sql, params)
  File "/usr/local/lib/python3.10/site-packages/cacheops/transaction.py", line 97, in execute
    result = self._no_monkey.execute(self, sql, params)
  File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 66, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 79, in _execute
    with self.db.wrap_database_errors:
  File "/usr/local/lib/python3.10/site-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.InternalError: cannot execute INSERT in a read-only transaction

gneville-ot avatar Dec 14 '22 13:12 gneville-ot

At a glance, looks like an issue with social_core/social_django get_and_store_nonce(), probably https://python-social-auth.readthedocs.io/en/latest/storage.html. It looks like it might be necessary to find or write an alternative to DjangoAssociationMixin that understands Nautobot's MAINTENANCE_MODE setting and doesn't try to write to the DB in that case.

glennmatthews avatar Dec 14 '22 16:12 glennmatthews