X-Git-Url: https://git.arvados.org/arvados.git/blobdiff_plain/b629d9e88ad0c6e62018a8fb4768013975eccc70..62d6b9d286c8c6bcb295587464650eccb525b79c:/services/api/db/structure.sql diff --git a/services/api/db/structure.sql b/services/api/db/structure.sql index 9bb059c2a9..d874bce940 100644 --- a/services/api/db/structure.sql +++ b/services/api/db/structure.sql @@ -11,31 +11,245 @@ SET xmloption = content; SET client_min_messages = warning; -- --- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: - +-- Name: pg_trgm; Type: EXTENSION; Schema: -; Owner: - -- -CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; +CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA public; -- --- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: - +-- Name: EXTENSION pg_trgm; Type: COMMENT; Schema: -; Owner: - -- --- COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'; +-- COMMENT ON EXTENSION pg_trgm IS 'text similarity measurement and index searching based on trigrams'; -- --- Name: pg_trgm; Type: EXTENSION; Schema: -; Owner: - --- - -CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA public; +-- Name: compute_permission_subgraph(character varying, character varying, integer, character varying); Type: FUNCTION; Schema: public; Owner: - +-- + +CREATE FUNCTION public.compute_permission_subgraph(perm_origin_uuid character varying, starting_uuid character varying, starting_perm integer, perm_edge_id character varying) RETURNS TABLE(user_uuid character varying, target_uuid character varying, val integer, traverse_owned boolean) + LANGUAGE sql STABLE + AS $$ + +/* The purpose of this function is to compute the permissions for a + subgraph of the database, starting from a given edge. The newly + computed permissions are used to add and remove rows from the main + permissions table. + + perm_origin_uuid: The object that 'gets' the permission. + + starting_uuid: The starting object the permission applies to. + + starting_perm: The permission that perm_origin_uuid 'has' on + starting_uuid One of 1, 2, 3 for can_read, + can_write, can_manage respectively, or 0 to revoke + permissions. + + perm_edge_id: Identifies the permission edge that is being updated. + Changes of ownership, this is starting_uuid. + For links, this is the uuid of the link object. + This is used to override the edge value in the database + with starting_perm. This is necessary when revoking + permissions because the update happens before edge is + actually removed. +*/ +with + /* Starting from starting_uuid, determine the set of objects that + could be affected by this permission change. + + Note: We don't traverse users unless it is an "identity" + permission (permission origin is self). + */ + perm_from_start(perm_origin_uuid, target_uuid, val, traverse_owned) as ( + +WITH RECURSIVE + traverse_graph(origin_uuid, target_uuid, val, traverse_owned, starting_set) as ( + + values (perm_origin_uuid, starting_uuid, starting_perm, + should_traverse_owned(starting_uuid, starting_perm), + (perm_origin_uuid = starting_uuid or starting_uuid not like '_____-tpzed-_______________')) + + union + (select traverse_graph.origin_uuid, + edges.head_uuid, + least( +case (edges.edge_id = perm_edge_id) + when true then starting_perm + else edges.val + end +, + traverse_graph.val), + should_traverse_owned(edges.head_uuid, edges.val), + false + from permission_graph_edges as edges, traverse_graph + where traverse_graph.target_uuid = edges.tail_uuid + and (edges.tail_uuid like '_____-j7d0g-_______________' or + traverse_graph.starting_set))) + select traverse_graph.origin_uuid, target_uuid, max(val) as val, bool_or(traverse_owned) as traverse_owned from traverse_graph + group by (traverse_graph.origin_uuid, target_uuid) +), + + /* Find other inbound edges that grant permissions to 'targets' in + perm_from_start, and compute permissions that originate from + those. + + This is necessary for two reasons: + + 1) Other users may have access to a subset of the objects + through other permission links than the one we started from. + If we don't recompute them, their permission will get dropped. + + 2) There may be more than one path through which a user gets + permission to an object. For example, a user owns a project + and also shares it can_read with a group the user belongs + to. adding the can_read link must not overwrite the existing + can_manage permission granted by ownership. + */ + additional_perms(perm_origin_uuid, target_uuid, val, traverse_owned) as ( + +WITH RECURSIVE + traverse_graph(origin_uuid, target_uuid, val, traverse_owned, starting_set) as ( + + select edges.tail_uuid as origin_uuid, edges.head_uuid as target_uuid, edges.val, + should_traverse_owned(edges.head_uuid, edges.val), + edges.head_uuid like '_____-j7d0g-_______________' + from permission_graph_edges as edges + where edges.edge_id != perm_edge_id and + edges.tail_uuid not in (select target_uuid from perm_from_start where target_uuid like '_____-j7d0g-_______________') and + edges.head_uuid in (select target_uuid from perm_from_start) + + union + (select traverse_graph.origin_uuid, + edges.head_uuid, + least( +case (edges.edge_id = perm_edge_id) + when true then starting_perm + else edges.val + end +, + traverse_graph.val), + should_traverse_owned(edges.head_uuid, edges.val), + false + from permission_graph_edges as edges, traverse_graph + where traverse_graph.target_uuid = edges.tail_uuid + and (edges.tail_uuid like '_____-j7d0g-_______________' or + traverse_graph.starting_set))) + select traverse_graph.origin_uuid, target_uuid, max(val) as val, bool_or(traverse_owned) as traverse_owned from traverse_graph + group by (traverse_graph.origin_uuid, target_uuid) +), + + /* Combine the permissions computed in the first two phases. */ + all_perms(perm_origin_uuid, target_uuid, val, traverse_owned) as ( + select * from perm_from_start + union all + select * from additional_perms + ) + + /* The actual query that produces rows to be added or removed + from the materialized_permissions table. This is the clever + bit. + + Key insights: + + * For every group, the materialized_permissions lists all users + that can access to that group. + + * The all_perms subquery has computed permissions on on a set of + objects for all inbound "origins", which are users or groups. + + * Permissions through groups are transitive. + + We can infer: + + 1) The materialized_permissions table declares that user X has permission N on group Y + 2) The all_perms result has determined group Y has permission M on object Z + 3) Therefore, user X has permission min(N, M) on object Z + + This allows us to efficiently determine the set of users that + have permissions on the subset of objects, without having to + follow the chain of permission back up to find those users. + + In addition, because users always have permission on themselves, this + query also makes sure those permission rows are always + returned. + */ + select v.user_uuid, v.target_uuid, max(v.perm_level), bool_or(v.traverse_owned) from + (select m.user_uuid, + u.target_uuid, + least(u.val, m.perm_level) as perm_level, + u.traverse_owned + from all_perms as u, materialized_permissions as m + where u.perm_origin_uuid = m.target_uuid AND m.traverse_owned + AND (m.user_uuid = m.target_uuid or m.target_uuid not like '_____-tpzed-_______________') + union all + select target_uuid as user_uuid, target_uuid, 3, true + from all_perms + where all_perms.target_uuid like '_____-tpzed-_______________') as v + group by v.user_uuid, v.target_uuid +$$; + + +-- +-- Name: project_subtree_with_is_frozen(character varying, boolean); Type: FUNCTION; Schema: public; Owner: - +-- + +CREATE FUNCTION public.project_subtree_with_is_frozen(starting_uuid character varying, starting_is_frozen boolean) RETURNS TABLE(uuid character varying, is_frozen boolean) + LANGUAGE sql STABLE + AS $$ +WITH RECURSIVE + project_subtree(uuid, is_frozen) as ( + values (starting_uuid, starting_is_frozen) + union + select groups.uuid, project_subtree.is_frozen or groups.frozen_by_uuid is not null + from groups join project_subtree on (groups.owner_uuid = project_subtree.uuid) + ) + select uuid, is_frozen from project_subtree; +$$; + + +-- +-- Name: project_subtree_with_trash_at(character varying, timestamp without time zone); Type: FUNCTION; Schema: public; Owner: - +-- + +CREATE FUNCTION public.project_subtree_with_trash_at(starting_uuid character varying, starting_trash_at timestamp without time zone) RETURNS TABLE(target_uuid character varying, trash_at timestamp without time zone) + LANGUAGE sql STABLE + AS $$ +/* Starting from a project, recursively traverse all the projects + underneath it and return a set of project uuids and trash_at times + (may be null). The initial trash_at can be a timestamp or null. + The trash_at time propagates downward to groups it owns, i.e. when a + group is trashed, everything underneath it in the ownership + hierarchy is also considered trashed. However, this is fact is + recorded in the trashed_groups table, not by updating trash_at field + in the groups table. +*/ +WITH RECURSIVE + project_subtree(uuid, trash_at) as ( + values (starting_uuid, starting_trash_at) + union + select groups.uuid, LEAST(project_subtree.trash_at, groups.trash_at) + from groups join project_subtree on (groups.owner_uuid = project_subtree.uuid) + ) + select uuid, trash_at from project_subtree; +$$; -- --- Name: EXTENSION pg_trgm; Type: COMMENT; Schema: -; Owner: - +-- Name: should_traverse_owned(character varying, integer); Type: FUNCTION; Schema: public; Owner: - -- --- COMMENT ON EXTENSION pg_trgm IS 'text similarity measurement and index searching based on trigrams'; +CREATE FUNCTION public.should_traverse_owned(starting_uuid character varying, starting_perm integer) RETURNS boolean + LANGUAGE sql IMMUTABLE + AS $$ +/* Helper function. Determines if permission on an object implies + transitive permission to things the object owns. This is always + true for groups, but only true for users when the permission level + is can_manage. +*/ +select starting_uuid like '_____-j7d0g-_______________' or + (starting_uuid like '_____-tpzed-_______________' and starting_perm >= 3); +$$; SET default_tablespace = ''; @@ -227,73 +441,6 @@ CREATE SEQUENCE public.collections_id_seq ALTER SEQUENCE public.collections_id_seq OWNED BY public.collections.id; --- --- Name: commit_ancestors; Type: TABLE; Schema: public; Owner: - --- - -CREATE TABLE public.commit_ancestors ( - id integer NOT NULL, - repository_name character varying(255), - descendant character varying(255) NOT NULL, - ancestor character varying(255) NOT NULL, - "is" boolean DEFAULT false NOT NULL, - created_at timestamp without time zone NOT NULL, - updated_at timestamp without time zone NOT NULL -); - - --- --- Name: commit_ancestors_id_seq; Type: SEQUENCE; Schema: public; Owner: - --- - -CREATE SEQUENCE public.commit_ancestors_id_seq - START WITH 1 - INCREMENT BY 1 - NO MINVALUE - NO MAXVALUE - CACHE 1; - - --- --- Name: commit_ancestors_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - --- - -ALTER SEQUENCE public.commit_ancestors_id_seq OWNED BY public.commit_ancestors.id; - - --- --- Name: commits; Type: TABLE; Schema: public; Owner: - --- - -CREATE TABLE public.commits ( - id integer NOT NULL, - repository_name character varying(255), - sha1 character varying(255), - message character varying(255), - created_at timestamp without time zone NOT NULL, - updated_at timestamp without time zone NOT NULL -); - - --- --- Name: commits_id_seq; Type: SEQUENCE; Schema: public; Owner: - --- - -CREATE SEQUENCE public.commits_id_seq - START WITH 1 - INCREMENT BY 1 - NO MINVALUE - NO MAXVALUE - CACHE 1; - - --- --- Name: commits_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - --- - -ALTER SEQUENCE public.commits_id_seq OWNED BY public.commits.id; - - -- -- Name: container_requests; Type: TABLE; Schema: public; Owner: - -- @@ -332,7 +479,8 @@ CREATE TABLE public.container_requests ( output_name character varying(255) DEFAULT NULL::character varying, output_ttl integer DEFAULT 0 NOT NULL, secret_mounts jsonb DEFAULT '{}'::jsonb, - runtime_token text + runtime_token text, + output_storage_classes jsonb DEFAULT '["default"]'::jsonb ); @@ -392,7 +540,10 @@ CREATE TABLE public.containers ( runtime_user_uuid text, runtime_auth_scopes jsonb, runtime_token text, - lock_count integer DEFAULT 0 NOT NULL + lock_count integer DEFAULT 0 NOT NULL, + gateway_address character varying, + interactive_session_started boolean DEFAULT false NOT NULL, + output_storage_classes jsonb DEFAULT '["default"]'::jsonb ); @@ -415,6 +566,15 @@ CREATE SEQUENCE public.containers_id_seq ALTER SEQUENCE public.containers_id_seq OWNED BY public.containers.id; +-- +-- Name: frozen_groups; Type: TABLE; Schema: public; Owner: - +-- + +CREATE TABLE public.frozen_groups ( + uuid character varying +); + + -- -- Name: groups; Type: TABLE; Schema: public; Owner: - -- @@ -434,7 +594,8 @@ CREATE TABLE public.groups ( trash_at timestamp without time zone, is_trashed boolean DEFAULT false NOT NULL, delete_at timestamp without time zone, - properties jsonb DEFAULT '{}'::jsonb + properties jsonb DEFAULT '{}'::jsonb, + frozen_by_uuid character varying ); @@ -786,93 +947,17 @@ ALTER SEQUENCE public.logs_id_seq OWNED BY public.logs.id; -- --- Name: users; Type: TABLE; Schema: public; Owner: - +-- Name: materialized_permissions; Type: TABLE; Schema: public; Owner: - -- -CREATE TABLE public.users ( - id integer NOT NULL, - uuid character varying(255), - owner_uuid character varying(255) NOT NULL, - created_at timestamp without time zone NOT NULL, - modified_by_client_uuid character varying(255), - modified_by_user_uuid character varying(255), - modified_at timestamp without time zone, - email character varying(255), - first_name character varying(255), - last_name character varying(255), - identity_url character varying(255), - is_admin boolean, - prefs text, - updated_at timestamp without time zone NOT NULL, - default_owner_uuid character varying(255), - is_active boolean DEFAULT false, - username character varying(255), - redirect_to_user_uuid character varying +CREATE TABLE public.materialized_permissions ( + user_uuid character varying, + target_uuid character varying, + perm_level integer, + traverse_owned boolean ); --- --- Name: materialized_permission_view; Type: MATERIALIZED VIEW; Schema: public; Owner: - --- - -CREATE MATERIALIZED VIEW public.materialized_permission_view AS - WITH RECURSIVE perm_value(name, val) AS ( - VALUES ('can_read'::text,(1)::smallint), ('can_login'::text,1), ('can_write'::text,2), ('can_manage'::text,3) - ), perm_edges(tail_uuid, head_uuid, val, follow, trashed) AS ( - SELECT links.tail_uuid, - links.head_uuid, - pv.val, - ((pv.val = 3) OR (groups.uuid IS NOT NULL)) AS follow, - (0)::smallint AS trashed, - (0)::smallint AS followtrash - FROM ((public.links - LEFT JOIN perm_value pv ON ((pv.name = (links.name)::text))) - LEFT JOIN public.groups ON (((pv.val < 3) AND ((groups.uuid)::text = (links.head_uuid)::text)))) - WHERE ((links.link_class)::text = 'permission'::text) - UNION ALL - SELECT groups.owner_uuid, - groups.uuid, - 3, - true AS bool, - CASE - WHEN ((groups.trash_at IS NOT NULL) AND (groups.trash_at < clock_timestamp())) THEN 1 - ELSE 0 - END AS "case", - 1 - FROM public.groups - ), perm(val, follow, user_uuid, target_uuid, trashed) AS ( - SELECT (3)::smallint AS val, - true AS follow, - (users.uuid)::character varying(32) AS user_uuid, - (users.uuid)::character varying(32) AS target_uuid, - (0)::smallint AS trashed - FROM public.users - UNION - SELECT (LEAST((perm_1.val)::integer, edges.val))::smallint AS val, - edges.follow, - perm_1.user_uuid, - (edges.head_uuid)::character varying(32) AS target_uuid, - ((GREATEST((perm_1.trashed)::integer, edges.trashed) * edges.followtrash))::smallint AS trashed - FROM (perm perm_1 - JOIN perm_edges edges ON ((perm_1.follow AND ((edges.tail_uuid)::text = (perm_1.target_uuid)::text)))) - ) - SELECT perm.user_uuid, - perm.target_uuid, - max(perm.val) AS perm_level, - CASE perm.follow - WHEN true THEN perm.target_uuid - ELSE NULL::character varying - END AS target_owner_uuid, - max(perm.trashed) AS trashed - FROM perm - GROUP BY perm.user_uuid, perm.target_uuid, - CASE perm.follow - WHEN true THEN perm.target_uuid - ELSE NULL::character varying - END - WITH NO DATA; - - -- -- Name: nodes; Type: TABLE; Schema: public; Owner: - -- @@ -918,31 +1003,66 @@ ALTER SEQUENCE public.nodes_id_seq OWNED BY public.nodes.id; -- --- Name: permission_refresh_lock; Type: TABLE; Schema: public; Owner: - +-- Name: users; Type: TABLE; Schema: public; Owner: - -- -CREATE TABLE public.permission_refresh_lock ( - id integer NOT NULL +CREATE TABLE public.users ( + id integer NOT NULL, + uuid character varying(255), + owner_uuid character varying(255) NOT NULL, + created_at timestamp without time zone NOT NULL, + modified_by_client_uuid character varying(255), + modified_by_user_uuid character varying(255), + modified_at timestamp without time zone, + email character varying(255), + first_name character varying(255), + last_name character varying(255), + identity_url character varying(255), + is_admin boolean, + prefs text, + updated_at timestamp without time zone NOT NULL, + default_owner_uuid character varying(255), + is_active boolean DEFAULT false, + username character varying(255), + redirect_to_user_uuid character varying ); -- --- Name: permission_refresh_lock_id_seq; Type: SEQUENCE; Schema: public; Owner: - --- - -CREATE SEQUENCE public.permission_refresh_lock_id_seq - START WITH 1 - INCREMENT BY 1 - NO MINVALUE - NO MAXVALUE - CACHE 1; - - --- --- Name: permission_refresh_lock_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - --- - -ALTER SEQUENCE public.permission_refresh_lock_id_seq OWNED BY public.permission_refresh_lock.id; +-- Name: permission_graph_edges; Type: VIEW; Schema: public; Owner: - +-- + +CREATE VIEW public.permission_graph_edges AS + SELECT groups.owner_uuid AS tail_uuid, + groups.uuid AS head_uuid, + 3 AS val, + groups.uuid AS edge_id + FROM public.groups +UNION ALL + SELECT users.owner_uuid AS tail_uuid, + users.uuid AS head_uuid, + 3 AS val, + users.uuid AS edge_id + FROM public.users +UNION ALL + SELECT users.uuid AS tail_uuid, + users.uuid AS head_uuid, + 3 AS val, + ''::character varying AS edge_id + FROM public.users +UNION ALL + SELECT links.tail_uuid, + links.head_uuid, + CASE + WHEN ((links.name)::text = 'can_read'::text) THEN 1 + WHEN ((links.name)::text = 'can_login'::text) THEN 1 + WHEN ((links.name)::text = 'can_write'::text) THEN 2 + WHEN ((links.name)::text = 'can_manage'::text) THEN 3 + ELSE 0 + END AS val, + links.uuid AS edge_id + FROM public.links + WHERE ((links.link_class)::text = 'permission'::text); -- @@ -1146,6 +1266,16 @@ CREATE SEQUENCE public.traits_id_seq ALTER SEQUENCE public.traits_id_seq OWNED BY public.traits.id; +-- +-- Name: trashed_groups; Type: TABLE; Schema: public; Owner: - +-- + +CREATE TABLE public.trashed_groups ( + group_uuid character varying, + trash_at timestamp without time zone +); + + -- -- Name: users_id_seq; Type: SEQUENCE; Schema: public; Owner: - -- @@ -1267,20 +1397,6 @@ ALTER TABLE ONLY public.authorized_keys ALTER COLUMN id SET DEFAULT nextval('pub ALTER TABLE ONLY public.collections ALTER COLUMN id SET DEFAULT nextval('public.collections_id_seq'::regclass); --- --- Name: commit_ancestors id; Type: DEFAULT; Schema: public; Owner: - --- - -ALTER TABLE ONLY public.commit_ancestors ALTER COLUMN id SET DEFAULT nextval('public.commit_ancestors_id_seq'::regclass); - - --- --- Name: commits id; Type: DEFAULT; Schema: public; Owner: - --- - -ALTER TABLE ONLY public.commits ALTER COLUMN id SET DEFAULT nextval('public.commits_id_seq'::regclass); - - -- -- Name: container_requests id; Type: DEFAULT; Schema: public; Owner: - -- @@ -1358,13 +1474,6 @@ ALTER TABLE ONLY public.logs ALTER COLUMN id SET DEFAULT nextval('public.logs_id ALTER TABLE ONLY public.nodes ALTER COLUMN id SET DEFAULT nextval('public.nodes_id_seq'::regclass); --- --- Name: permission_refresh_lock id; Type: DEFAULT; Schema: public; Owner: - --- - -ALTER TABLE ONLY public.permission_refresh_lock ALTER COLUMN id SET DEFAULT nextval('public.permission_refresh_lock_id_seq'::regclass); - - -- -- Name: pipeline_instances id; Type: DEFAULT; Schema: public; Owner: - -- @@ -1461,22 +1570,6 @@ ALTER TABLE ONLY public.collections ADD CONSTRAINT collections_pkey PRIMARY KEY (id); --- --- Name: commit_ancestors commit_ancestors_pkey; Type: CONSTRAINT; Schema: public; Owner: - --- - -ALTER TABLE ONLY public.commit_ancestors - ADD CONSTRAINT commit_ancestors_pkey PRIMARY KEY (id); - - --- --- Name: commits commits_pkey; Type: CONSTRAINT; Schema: public; Owner: - --- - -ALTER TABLE ONLY public.commits - ADD CONSTRAINT commits_pkey PRIMARY KEY (id); - - -- -- Name: container_requests container_requests_pkey; Type: CONSTRAINT; Schema: public; Owner: - -- @@ -1565,14 +1658,6 @@ ALTER TABLE ONLY public.nodes ADD CONSTRAINT nodes_pkey PRIMARY KEY (id); --- --- Name: permission_refresh_lock permission_refresh_lock_pkey; Type: CONSTRAINT; Schema: public; Owner: - --- - -ALTER TABLE ONLY public.permission_refresh_lock - ADD CONSTRAINT permission_refresh_lock_pkey PRIMARY KEY (id); - - -- -- Name: pipeline_instances pipeline_instances_pkey; Type: CONSTRAINT; Schema: public; Owner: - -- @@ -1665,13 +1750,6 @@ CREATE INDEX authorized_keys_search_index ON public.authorized_keys USING btree CREATE INDEX collection_index_on_properties ON public.collections USING gin (properties); --- --- Name: collections_full_text_search_idx; Type: INDEX; Schema: public; Owner: - --- - -CREATE INDEX collections_full_text_search_idx ON public.collections USING gin (to_tsvector('english'::regconfig, substr((((((((((((((((((COALESCE(owner_uuid, ''::character varying))::text || ' '::text) || (COALESCE(modified_by_client_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(modified_by_user_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(portable_data_hash, ''::character varying))::text) || ' '::text) || (COALESCE(uuid, ''::character varying))::text) || ' '::text) || (COALESCE(name, ''::character varying))::text) || ' '::text) || (COALESCE(description, ''::character varying))::text) || ' '::text) || COALESCE((properties)::text, ''::text)) || ' '::text) || COALESCE(file_names, ''::text)), 0, 1000000))); - - -- -- Name: collections_search_index; Type: INDEX; Schema: public; Owner: - -- @@ -1686,13 +1764,6 @@ CREATE INDEX collections_search_index ON public.collections USING btree (owner_u CREATE INDEX collections_trgm_text_search_idx ON public.collections USING gin (((((((((((((((((((COALESCE(owner_uuid, ''::character varying))::text || ' '::text) || (COALESCE(modified_by_client_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(modified_by_user_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(portable_data_hash, ''::character varying))::text) || ' '::text) || (COALESCE(uuid, ''::character varying))::text) || ' '::text) || (COALESCE(name, ''::character varying))::text) || ' '::text) || (COALESCE(description, ''::character varying))::text) || ' '::text) || COALESCE((properties)::text, ''::text)) || ' '::text) || COALESCE(file_names, ''::text))) public.gin_trgm_ops); --- --- Name: container_requests_full_text_search_idx; Type: INDEX; Schema: public; Owner: - --- - -CREATE INDEX container_requests_full_text_search_idx ON public.container_requests USING gin (to_tsvector('english'::regconfig, substr((((((((((((((((((((((((((((((((((((((((((COALESCE(uuid, ''::character varying))::text || ' '::text) || (COALESCE(owner_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(modified_by_client_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(modified_by_user_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(name, ''::character varying))::text) || ' '::text) || COALESCE(description, ''::text)) || ' '::text) || COALESCE((properties)::text, ''::text)) || ' '::text) || (COALESCE(state, ''::character varying))::text) || ' '::text) || (COALESCE(requesting_container_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(container_uuid, ''::character varying))::text) || ' '::text) || COALESCE(runtime_constraints, ''::text)) || ' '::text) || (COALESCE(container_image, ''::character varying))::text) || ' '::text) || COALESCE(environment, ''::text)) || ' '::text) || (COALESCE(cwd, ''::character varying))::text) || ' '::text) || COALESCE(command, ''::text)) || ' '::text) || (COALESCE(output_path, ''::character varying))::text) || ' '::text) || COALESCE(filters, ''::text)) || ' '::text) || COALESCE(scheduling_parameters, ''::text)) || ' '::text) || (COALESCE(output_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(log_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(output_name, ''::character varying))::text), 0, 1000000))); - - -- -- Name: container_requests_index_on_properties; Type: INDEX; Schema: public; Owner: - -- @@ -1728,18 +1799,11 @@ CREATE INDEX containers_search_index ON public.containers USING btree (uuid, own CREATE INDEX group_index_on_properties ON public.groups USING gin (properties); --- --- Name: groups_full_text_search_idx; Type: INDEX; Schema: public; Owner: - --- - -CREATE INDEX groups_full_text_search_idx ON public.groups USING gin (to_tsvector('english'::regconfig, substr((((((((((((((((COALESCE(uuid, ''::character varying))::text || ' '::text) || (COALESCE(owner_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(modified_by_client_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(modified_by_user_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(name, ''::character varying))::text) || ' '::text) || (COALESCE(description, ''::character varying))::text) || ' '::text) || (COALESCE(group_class, ''::character varying))::text) || ' '::text) || COALESCE((properties)::text, ''::text)), 0, 1000000))); - - -- -- Name: groups_search_index; Type: INDEX; Schema: public; Owner: - -- -CREATE INDEX groups_search_index ON public.groups USING btree (uuid, owner_uuid, modified_by_client_uuid, modified_by_user_uuid, name, group_class); +CREATE INDEX groups_search_index ON public.groups USING btree (uuid, owner_uuid, modified_by_client_uuid, modified_by_user_uuid, name, group_class, frozen_by_uuid); -- @@ -1841,10 +1905,10 @@ CREATE UNIQUE INDEX index_authorized_keys_on_uuid ON public.authorized_keys USIN -- --- Name: index_collections_on_created_at; Type: INDEX; Schema: public; Owner: - +-- Name: index_collections_on_created_at_and_uuid; Type: INDEX; Schema: public; Owner: - -- -CREATE INDEX index_collections_on_created_at ON public.collections USING btree (created_at); +CREATE INDEX index_collections_on_created_at_and_uuid ON public.collections USING btree (created_at, uuid); -- @@ -1917,20 +1981,6 @@ CREATE INDEX index_collections_on_trash_at ON public.collections USING btree (tr CREATE UNIQUE INDEX index_collections_on_uuid ON public.collections USING btree (uuid); --- --- Name: index_commit_ancestors_on_descendant_and_ancestor; Type: INDEX; Schema: public; Owner: - --- - -CREATE UNIQUE INDEX index_commit_ancestors_on_descendant_and_ancestor ON public.commit_ancestors USING btree (descendant, ancestor); - - --- --- Name: index_commits_on_repository_name_and_sha1; Type: INDEX; Schema: public; Owner: - --- - -CREATE UNIQUE INDEX index_commits_on_repository_name_and_sha1 ON public.commits USING btree (repository_name, sha1); - - -- -- Name: index_container_requests_on_container_uuid; Type: INDEX; Schema: public; Owner: - -- @@ -2037,10 +2087,17 @@ CREATE UNIQUE INDEX index_containers_on_uuid ON public.containers USING btree (u -- --- Name: index_groups_on_created_at; Type: INDEX; Schema: public; Owner: - +-- Name: index_frozen_groups_on_uuid; Type: INDEX; Schema: public; Owner: - -- -CREATE INDEX index_groups_on_created_at ON public.groups USING btree (created_at); +CREATE UNIQUE INDEX index_frozen_groups_on_uuid ON public.frozen_groups USING btree (uuid); + + +-- +-- Name: index_groups_on_created_at_and_uuid; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_groups_on_created_at_and_uuid ON public.groups USING btree (created_at, uuid); -- @@ -2303,10 +2360,10 @@ CREATE UNIQUE INDEX index_keep_services_on_uuid ON public.keep_services USING bt -- --- Name: index_links_on_created_at; Type: INDEX; Schema: public; Owner: - +-- Name: index_links_on_created_at_and_uuid; Type: INDEX; Schema: public; Owner: - -- -CREATE INDEX index_links_on_created_at ON public.links USING btree (created_at); +CREATE INDEX index_links_on_created_at_and_uuid ON public.links USING btree (created_at, uuid); -- @@ -2366,10 +2423,10 @@ CREATE UNIQUE INDEX index_links_on_uuid ON public.links USING btree (uuid); -- --- Name: index_logs_on_created_at; Type: INDEX; Schema: public; Owner: - +-- Name: index_logs_on_created_at_and_uuid; Type: INDEX; Schema: public; Owner: - -- -CREATE INDEX index_logs_on_created_at ON public.logs USING btree (created_at); +CREATE INDEX index_logs_on_created_at_and_uuid ON public.logs USING btree (created_at, uuid); -- @@ -2625,10 +2682,24 @@ CREATE UNIQUE INDEX index_traits_on_uuid ON public.traits USING btree (uuid); -- --- Name: index_users_on_created_at; Type: INDEX; Schema: public; Owner: - +-- Name: index_trashed_groups_on_group_uuid; Type: INDEX; Schema: public; Owner: - +-- + +CREATE UNIQUE INDEX index_trashed_groups_on_group_uuid ON public.trashed_groups USING btree (group_uuid); + + +-- +-- Name: index_users_on_created_at_and_uuid; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_users_on_created_at_and_uuid ON public.users USING btree (created_at, uuid); + + +-- +-- Name: index_users_on_identity_url; Type: INDEX; Schema: public; Owner: - -- -CREATE INDEX index_users_on_created_at ON public.users USING btree (created_at); +CREATE UNIQUE INDEX index_users_on_identity_url ON public.users USING btree (identity_url); -- @@ -2722,13 +2793,6 @@ CREATE UNIQUE INDEX index_workflows_on_uuid ON public.workflows USING btree (uui CREATE INDEX job_tasks_search_index ON public.job_tasks USING btree (uuid, owner_uuid, modified_by_client_uuid, modified_by_user_uuid, job_uuid, created_by_job_task_uuid); --- --- Name: jobs_full_text_search_idx; Type: INDEX; Schema: public; Owner: - --- - -CREATE INDEX jobs_full_text_search_idx ON public.jobs USING gin (to_tsvector('english'::regconfig, substr((((((((((((((((((((((((((((((((((((((((((((COALESCE(uuid, ''::character varying))::text || ' '::text) || (COALESCE(owner_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(modified_by_client_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(modified_by_user_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(submit_id, ''::character varying))::text) || ' '::text) || (COALESCE(script, ''::character varying))::text) || ' '::text) || (COALESCE(script_version, ''::character varying))::text) || ' '::text) || COALESCE(script_parameters, ''::text)) || ' '::text) || (COALESCE(cancelled_by_client_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(cancelled_by_user_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(output, ''::character varying))::text) || ' '::text) || (COALESCE(is_locked_by_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(log, ''::character varying))::text) || ' '::text) || COALESCE(tasks_summary, ''::text)) || ' '::text) || COALESCE(runtime_constraints, ''::text)) || ' '::text) || (COALESCE(repository, ''::character varying))::text) || ' '::text) || (COALESCE(supplied_script_version, ''::character varying))::text) || ' '::text) || (COALESCE(docker_image_locator, ''::character varying))::text) || ' '::text) || (COALESCE(description, ''::character varying))::text) || ' '::text) || (COALESCE(state, ''::character varying))::text) || ' '::text) || (COALESCE(arvados_sdk_version, ''::character varying))::text) || ' '::text) || COALESCE(components, ''::text)), 0, 1000000))); - - -- -- Name: jobs_search_index; Type: INDEX; Schema: public; Owner: - -- @@ -2807,24 +2871,17 @@ CREATE INDEX nodes_search_index ON public.nodes USING btree (uuid, owner_uuid, m -- --- Name: permission_target_trashed; Type: INDEX; Schema: public; Owner: - +-- Name: permission_target; Type: INDEX; Schema: public; Owner: - -- -CREATE INDEX permission_target_trashed ON public.materialized_permission_view USING btree (trashed, target_uuid); +CREATE INDEX permission_target ON public.materialized_permissions USING btree (target_uuid); -- --- Name: permission_target_user_trashed_level; Type: INDEX; Schema: public; Owner: - +-- Name: permission_user_target; Type: INDEX; Schema: public; Owner: - -- -CREATE INDEX permission_target_user_trashed_level ON public.materialized_permission_view USING btree (user_uuid, trashed, perm_level); - - --- --- Name: pipeline_instances_full_text_search_idx; Type: INDEX; Schema: public; Owner: - --- - -CREATE INDEX pipeline_instances_full_text_search_idx ON public.pipeline_instances USING gin (to_tsvector('english'::regconfig, substr((((((((((((((((((((((COALESCE(uuid, ''::character varying))::text || ' '::text) || (COALESCE(owner_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(modified_by_client_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(modified_by_user_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(pipeline_template_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(name, ''::character varying))::text) || ' '::text) || COALESCE(components, ''::text)) || ' '::text) || COALESCE(properties, ''::text)) || ' '::text) || (COALESCE(state, ''::character varying))::text) || ' '::text) || COALESCE(components_summary, ''::text)) || ' '::text) || (COALESCE(description, ''::character varying))::text), 0, 1000000))); +CREATE UNIQUE INDEX permission_user_target ON public.materialized_permissions USING btree (user_uuid, target_uuid); -- @@ -2848,13 +2905,6 @@ CREATE INDEX pipeline_instances_trgm_text_search_idx ON public.pipeline_instance CREATE UNIQUE INDEX pipeline_template_owner_uuid_name_unique ON public.pipeline_templates USING btree (owner_uuid, name); --- --- Name: pipeline_templates_full_text_search_idx; Type: INDEX; Schema: public; Owner: - --- - -CREATE INDEX pipeline_templates_full_text_search_idx ON public.pipeline_templates USING gin (to_tsvector('english'::regconfig, substr((((((((((((((COALESCE(uuid, ''::character varying))::text || ' '::text) || (COALESCE(owner_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(modified_by_client_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(modified_by_user_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(name, ''::character varying))::text) || ' '::text) || COALESCE(components, ''::text)) || ' '::text) || (COALESCE(description, ''::character varying))::text), 0, 1000000))); - - -- -- Name: pipeline_templates_search_index; Type: INDEX; Schema: public; Owner: - -- @@ -2911,13 +2961,6 @@ CREATE INDEX users_search_index ON public.users USING btree (uuid, owner_uuid, m CREATE INDEX virtual_machines_search_index ON public.virtual_machines USING btree (uuid, owner_uuid, modified_by_client_uuid, modified_by_user_uuid, hostname); --- --- Name: workflows_full_text_search_idx; Type: INDEX; Schema: public; Owner: - --- - -CREATE INDEX workflows_full_text_search_idx ON public.workflows USING gin (to_tsvector('english'::regconfig, substr((((((((((((COALESCE(uuid, ''::character varying))::text || ' '::text) || (COALESCE(owner_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(modified_by_client_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(modified_by_user_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(name, ''::character varying))::text) || ' '::text) || COALESCE(description, ''::text)), 0, 1000000))); - - -- -- Name: workflows_search_idx; Type: INDEX; Schema: public; Owner: - -- @@ -3125,6 +3168,24 @@ INSERT INTO "schema_migrations" (version) VALUES ('20190214214814'), ('20190322174136'), ('20190422144631'), -('20190523180148'); +('20190523180148'), +('20190808145904'), +('20190809135453'), +('20190905151603'), +('20200501150153'), +('20200602141328'), +('20200914203202'), +('20201103170213'), +('20201105190435'), +('20201202174753'), +('20210108033940'), +('20210126183521'), +('20210621204455'), +('20210816191509'), +('20211027154300'), +('20220224203102'), +('20220301155729'), +('20220303204419'), +('20220401153101');