ProgrammingError on IPAddress Create in UI & API #3480

whitej6 · 2023-03-23T23:03:54Z

Environment

Nautobot version (Docker tag too if applicable): >=1.5.0
Python version: 3.8
Database platform, version: postgres

Steps to Reproduce

create user without permission
add permission on status and add permission on ipaddress with a constraint using {"host__net_host_contained": "<some prefix>"
Attempt to add a new IPAddress within range of the constrained permission

Expected Behavior

IPAddress created

Observed Behavior

Programming error on create in UI & API

Environment:


Request Method: GET
Request URL: https://nautobot.whitej6.com/ipam/ip-addresses/80802b26-2fcf-4e4f-8f6e-c3640877eddb/

Django Version: 3.2.16
Python Version: 3.7.16
Installed Applications:
['django.contrib.auth',
 'django.contrib.contenttypes',
 'django.contrib.sessions',
 'django.contrib.messages',
 'django.contrib.staticfiles',
 'django.contrib.humanize',
 'cacheops',
 'corsheaders',
 'django_filters',
 'django_jinja',
 'django_tables2',
 'django_prometheus',
 'mptt',
 'social_django',
 'taggit',
 'timezone_field',
 'nautobot.core.apps.NautobotConstanceConfig',
 'nautobot.core',
 'django.contrib.admin',
 'django_celery_beat',
 'rest_framework',
 'db_file_storage',
 'nautobot.circuits',
 'nautobot.dcim',
 'nautobot.ipam',
 'nautobot.extras',
 'nautobot.tenancy',
 'nautobot.users',
 'nautobot.utilities',
 'nautobot.virtualization',
 'django_rq',
 'drf_spectacular',
 'drf_spectacular_sidecar',
 'graphene_django',
 'health_check',
 'health_check.storage',
 'django_extensions',
 'constance.backends.database',
 'django_ajax_tables',
 'debug_toolbar',
 'nautobot_url_endpoint_model.NautobotUrlEndpointModelConfig']
Installed Middleware:
['debug_toolbar.middleware.DebugToolbarMiddleware',
 'django_prometheus.middleware.PrometheusBeforeMiddleware',
 'corsheaders.middleware.CorsMiddleware',
 'django.contrib.sessions.middleware.SessionMiddleware',
 'django.middleware.common.CommonMiddleware',
 'django.middleware.csrf.CsrfViewMiddleware',
 'django.contrib.auth.middleware.AuthenticationMiddleware',
 'django.contrib.messages.middleware.MessageMiddleware',
 'django.middleware.clickjacking.XFrameOptionsMiddleware',
 'django.middleware.security.SecurityMiddleware',
 'nautobot.core.middleware.ExceptionHandlingMiddleware',
 'nautobot.core.middleware.RemoteUserMiddleware',
 'nautobot.core.middleware.ExternalAuthMiddleware',
 'nautobot.core.middleware.ObjectChangeMiddleware',
 'django_prometheus.middleware.PrometheusAfterMiddleware']


Template error:
In template /usr/local/lib/python3.7/site-packages/nautobot/core/templates/base.html, error at line 0
   column reference "host" is ambiguous
LINE 1: ...paddress"."nat_inside_id" = T3."id") WHERE (right(host::text...
                                                             ^

   1 : {% load static %}
   2 : {% load helpers %}
   3 : {% load plugins %}
   4 : 
   5 : <!DOCTYPE html>
   6 : <html lang="en"{% if request.COOKIES|get_item:"theme" == 'dark' %} data-theme="dark"{% endif %}>
   7 : <head>
   8 :     <title>{% block title %}Home{% endblock %} - {{ settings.BRANDING_TITLE }}</title>
   9 :     {% include 'inc/media.html' %}
   10 :     {% block extra_styles %}{% endblock %}


Traceback (most recent call last):
  File "/usr/local/lib/python3.7/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.7/site-packages/django_prometheus/db/common.py", line 71, in execute
    return super().execute(*args, **kwargs)

The above exception (column reference "host" is ambiguous
LINE 1: ...paddress"."nat_inside_id" = T3."id") WHERE (right(host::text...
                                                             ^
) was the direct cause of the following exception:
  File "/usr/local/lib/python3.7/site-packages/django/core/handlers/exception.py", line 47, in inner
    response = get_response(request)
  File "/usr/local/lib/python3.7/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.7/site-packages/django/views/generic/base.py", line 70, in view
    return self.dispatch(request, *args, **kwargs)
  File "/usr/local/lib/python3.7/site-packages/nautobot/utilities/views.py", line 116, in dispatch
    return super().dispatch(request, *args, **kwargs)
  File "/usr/local/lib/python3.7/site-packages/django/views/generic/base.py", line 98, in dispatch
    return handler(request, *args, **kwargs)
  File "/usr/local/lib/python3.7/site-packages/nautobot/core/views/generic.py", line 140, in get
    **self.get_extra_context(request, instance),
  File "/usr/local/lib/python3.7/site-packages/django/shortcuts.py", line 19, in render
    content = loader.render_to_string(template_name, context, request, using=using)
  File "/usr/local/lib/python3.7/site-packages/django/template/loader.py", line 62, in render_to_string
    return template.render(context, request)
  File "/usr/local/lib/python3.7/site-packages/django/template/backends/django.py", line 61, in render
    return self.template.render(context)
  File "/usr/local/lib/python3.7/site-packages/django/template/base.py", line 170, in render
    return self._render(context)
  File "/usr/local/lib/python3.7/site-packages/django/test/utils.py", line 100, in instrumented_test_render
    return self.nodelist.render(context)
  File "/usr/local/lib/python3.7/site-packages/django/template/base.py", line 938, in render
    bit = node.render_annotated(context)
  File "/usr/local/lib/python3.7/site-packages/django/template/base.py", line 905, in render_annotated
    return self.render(context)
  File "/usr/local/lib/python3.7/site-packages/django/template/loader_tags.py", line 150, in render
    return compiled_parent._render(context)
  File "/usr/local/lib/python3.7/site-packages/django/test/utils.py", line 100, in instrumented_test_render
    return self.nodelist.render(context)
  File "/usr/local/lib/python3.7/site-packages/django/template/base.py", line 938, in render
    bit = node.render_annotated(context)
  File "/usr/local/lib/python3.7/site-packages/django/template/base.py", line 905, in render_annotated
    return self.render(context)
  File "/usr/local/lib/python3.7/site-packages/django/template/loader_tags.py", line 150, in render
    return compiled_parent._render(context)
  File "/usr/local/lib/python3.7/site-packages/django/test/utils.py", line 100, in instrumented_test_render
    return self.nodelist.render(context)
  File "/usr/local/lib/python3.7/site-packages/django/template/base.py", line 938, in render
    bit = node.render_annotated(context)
  File "/usr/local/lib/python3.7/site-packages/django/template/base.py", line 905, in render_annotated
    return self.render(context)
  File "/usr/local/lib/python3.7/site-packages/django/template/loader_tags.py", line 150, in render
    return compiled_parent._render(context)
  File "/usr/local/lib/python3.7/site-packages/django/test/utils.py", line 100, in instrumented_test_render
    return self.nodelist.render(context)
  File "/usr/local/lib/python3.7/site-packages/django/template/base.py", line 938, in render
    bit = node.render_annotated(context)
  File "/usr/local/lib/python3.7/site-packages/django/template/base.py", line 905, in render_annotated
    return self.render(context)
  File "/usr/local/lib/python3.7/site-packages/django/template/loader_tags.py", line 62, in render
    result = block.nodelist.render(context)
  File "/usr/local/lib/python3.7/site-packages/django/template/base.py", line 938, in render
    bit = node.render_annotated(context)
  File "/usr/local/lib/python3.7/site-packages/django/template/base.py", line 905, in render_annotated
    return self.render(context)
  File "/usr/local/lib/python3.7/site-packages/django/template/loader_tags.py", line 62, in render
    result = block.nodelist.render(context)
  File "/usr/local/lib/python3.7/site-packages/django/template/base.py", line 938, in render
    bit = node.render_annotated(context)
  File "/usr/local/lib/python3.7/site-packages/django/template/base.py", line 905, in render_annotated
    return self.render(context)
  File "/usr/local/lib/python3.7/site-packages/django/template/defaulttags.py", line 314, in render
    if match:
  File "/usr/local/lib/python3.7/site-packages/django_tables2/rows.py", line 325, in __len__
    length = len(self.data)
  File "/usr/local/lib/python3.7/site-packages/django_tables2/data.py", line 156, in __len__
    self._length = len(self.data)
  File "/usr/local/lib/python3.7/site-packages/django/db/models/query.py", line 262, in __len__
    self._fetch_all()
  File "/usr/local/lib/python3.7/site-packages/cacheops/query.py", line 271, in _fetch_all
    return self._no_monkey._fetch_all(self)
  File "/usr/local/lib/python3.7/site-packages/django/db/models/query.py", line 1324, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/usr/local/lib/python3.7/site-packages/django/db/models/query.py", line 51, in __iter__
    results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)
  File "/usr/local/lib/python3.7/site-packages/django/db/models/sql/compiler.py", line 1175, in execute_sql
    cursor.execute(sql, params)
  File "/usr/local/lib/python3.7/site-packages/debug_toolbar/panels/sql/tracking.py", line 230, in execute
    return self._record(self.cursor.execute, sql, params)
  File "/usr/local/lib/python3.7/site-packages/debug_toolbar/panels/sql/tracking.py", line 154, in _record
    return method(sql, params)
  File "/usr/local/lib/python3.7/site-packages/django/db/backends/utils.py", line 98, in execute
    return super().execute(sql, params)
  File "/usr/local/lib/python3.7/site-packages/cacheops/transaction.py", line 97, in execute
    result = self._no_monkey.execute(self, sql, params)
  File "/usr/local/lib/python3.7/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.7/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/usr/local/lib/python3.7/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.7/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.7/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.7/site-packages/django_prometheus/db/common.py", line 71, in execute
    return super().execute(*args, **kwargs)

Exception Type: ProgrammingError at /ipam/ip-addresses/80802b26-2fcf-4e4f-8f6e-c3640877eddb/
Exception Value: column reference "host" is ambiguous
LINE 1: ...paddress"."nat_inside_id" = T3."id") WHERE (right(host::text...
                                                             ^

The text was updated successfully, but these errors were encountered:

whitej6 · 2023-03-23T23:11:12Z

UI will allow the object to be created but the redirect to the retrieve view will cause a the pictured stacktrace. The API will return a 500 error on a post and fail to create the IPAddress

…rmission

* Add table alias to _postgresql_varbin_to_integer * Add tests for #3480, failing currently as expected * Fixes for MySQL * Change fragment * Update nautobot/ipam/lookups.py Co-authored-by: Bryan Culver <31187+bryanculver@users.noreply.github.com> --------- Co-authored-by: Bryan Culver <me@bryanculver.com> Co-authored-by: Bryan Culver <31187+bryanculver@users.noreply.github.com>

whitej6 added a commit to whitej6/nautobot that referenced this issue Mar 23, 2023

Resolves nautobot#3480 - IPAddress ProgrammingError on constrained pe…

6535c3a

…rmission

whitej6 mentioned this issue Mar 23, 2023

Resolves #3480 - IPAddress ProgrammingError on constrained permission #3481

Closed

bryanculver added the type: bug Something isn't working as expected label Mar 24, 2023

glennmatthews added a commit that referenced this issue Mar 24, 2023

Add tests for #3480, failing currently as expected

672b83a

glennmatthews mentioned this issue Mar 24, 2023

IPAddress lookup fix #3487

Merged

3 tasks

glennmatthews assigned glennmatthews and bryanculver Mar 24, 2023

glennmatthews closed this as completed in #3487 Mar 27, 2023

github-actions bot locked as resolved and limited conversation to collaborators Jun 26, 2023

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

ProgrammingError on IPAddress Create in UI & API #3480

ProgrammingError on IPAddress Create in UI & API #3480

whitej6 commented Mar 23, 2023

whitej6 commented Mar 23, 2023

ProgrammingError on IPAddress Create in UI & API #3480

ProgrammingError on IPAddress Create in UI & API #3480

Comments

whitej6 commented Mar 23, 2023

Environment

Steps to Reproduce

Expected Behavior

Observed Behavior

whitej6 commented Mar 23, 2023