Bug 1589625 - no unique constraint to the name column on the roles table
Summary: no unique constraint to the name column on the roles table
Keywords:
Status: CLOSED ERRATA
Alias: None
Product: Red Hat Satellite 6
Classification: Red Hat
Component: Users & Roles
Version: 6.3.1
Hardware: Unspecified
OS: Unspecified
unspecified
medium vote
Target Milestone: Released
Assignee: Dominik Hlavac Duran
QA Contact: jcallaha
URL:
Whiteboard:
Depends On:
Blocks: 1713769
TreeView+ depends on / blocked
 
Reported: 2018-06-11 02:08 UTC by matt jia
Modified: 2019-10-07 17:17 UTC (History)
6 users (show)

Fixed In Version:
Doc Type: If docs needed, set a value
Doc Text:
Clone Of:
Environment:
Last Closed: 2019-05-14 12:37:23 UTC


Attachments (Terms of Use)


Links
System ID Priority Status Summary Last Updated
Red Hat Product Errata RHSA-2019:1222 None None None 2019-05-14 12:37:30 UTC
Foreman Issue Tracker 24002 None None None 2018-06-19 11:38:08 UTC

Description matt jia 2018-06-11 02:08:26 UTC
Description of problem:

The role name is unique. However, this doesn't reflect in the underline db.

Version-Release number of selected component (if applicable):

6.3.1

How reproducible:

Easy


Steps to Reproduce:
1. su - postgres
2. psql foreman
3. insert into roles(name, origin) values('Access Insights Admin', 'redhat_access');

Actual results:

Duplicate records for 'Access Insights Admin'

Expected results:

No duplicate record.

Comment 1 matt jia 2018-06-11 22:51:30 UTC
Adding an index should work.

create UNIQUE INDEX "index_roles_on_name" on roles using btree (name);\

Comment 4 Jan Hutař 2018-06-19 11:24:56 UTC
On Snap7:

# sudo -u foreman psql foreman
psql (9.2.23)
Type "help" for help.

foreman=> \d roles
                                     Table "public.roles"
     Column     |          Type          |                     Modifiers                      
----------------+------------------------+----------------------------------------------------
 id             | integer                | not null default nextval('roles_id_seq'::regclass)
 name           | character varying(255) | 
 builtin        | integer                | default 0
 description    | text                   | 
 origin         | character varying      | 
 cloned_from_id | integer                | 
Indexes:
    "roles_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "filters" CONSTRAINT "filters_roles_id_fk" FOREIGN KEY (role_id) REFERENCES roles(id)
    TABLE "user_roles" CONSTRAINT "user_roles_role_id_fk" FOREIGN KEY (role_id) REFERENCES roles(id)

foreman=> insert into roles(name, origin) values('ABC', 'abc');
INSERT 0 1
foreman=> insert into roles(name, origin) values('ABC', 'abc');
INSERT 0 1

Comment 5 pm-sat@redhat.com 2018-07-12 14:21:25 UTC
Upstream bug assigned to dhlavacd@redhat.com

Comment 6 pm-sat@redhat.com 2018-07-15 14:21:17 UTC
Moving this bug to POST for triage into Satellite 6 since the upstream issue http://projects.theforeman.org/issues/24002 has been resolved.

Comment 9 jcallaha 2019-01-16 22:01:08 UTC
The original steps still fail, however i do see the unique index mentioned in the above comment. Is this the result you were going for?

-bash-4.2# su - postgres
-bash-4.2$ psql foreman
psql (9.2.24)
Type "help" for help.

foreman=# insert into roles(name, origin) values('Access Insights Admin', 'redhat_access');
ERROR:  duplicate key value violates unique constraint "index_roles_on_name"
DETAIL:  Key (name)=(Access Insights Admin) already exists.
foreman=# 
foreman=# \d roles;
                                     Table "public.roles"
     Column     |          Type          |                     Modifiers                      
----------------+------------------------+----------------------------------------------------
 id             | integer                | not null default nextval('roles_id_seq'::regclass)
 name           | character varying(255) | 
 builtin        | integer                | default 0
 description    | text                   | 
 origin         | character varying      | 
 cloned_from_id | integer                | 
Indexes:
    "roles_pkey" PRIMARY KEY, btree (id)
    "index_roles_on_name" UNIQUE, btree (name)
Referenced by:
    TABLE "filters" CONSTRAINT "filters_roles_id_fk" FOREIGN KEY (role_id) REFERENCES roles(id)
    TABLE "user_roles" CONSTRAINT "user_roles_role_id_fk" FOREIGN KEY (role_id) REFERENCES roles(id)

Comment 10 Dominik Hlavac Duran 2019-01-24 11:23:05 UTC
Hi,

yes this is expected result because if you want to enter role with same name it don't allow you to do that.

After adding index on name is not possible to create duplicate entry with same name in db.

Comment 11 jcallaha 2019-01-24 14:16:16 UTC
Verified in Satellite 6.5.0 Snap 12, based on results in #9 and clarification in #10.

Comment 13 errata-xmlrpc 2019-05-14 12:37:23 UTC
Since the problem described in this bug report should be
resolved in a recent advisory, it has been closed with a
resolution of ERRATA.

For information on the advisory, and where to find the updated
files, follow the link below.

If the solution does not work for you, open a new bug report.

https://access.redhat.com/errata/RHSA-2019:1222


Note You need to log in before you can comment on or make changes to this bug.