Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pLQf1-0001Qs-IN for pgsql-committers@arkaria.postgresql.org; Fri, 27 Jan 2023 15:29:03 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pLQf0-0007qx-GH for pgsql-committers@arkaria.postgresql.org; Fri, 27 Jan 2023 15:29:02 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pLQf0-0007qo-4P for pgsql-committers@lists.postgresql.org; Fri, 27 Jan 2023 15:29:02 +0000 Received: from mail-il1-x136.google.com ([2607:f8b0:4864:20::136]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pLQew-0000Kp-81 for pgsql-committers@lists.postgresql.org; Fri, 27 Jan 2023 15:29:01 +0000 Received: by mail-il1-x136.google.com with SMTP id m8so2408754ili.7 for ; Fri, 27 Jan 2023 07:28:57 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=telsasoft-com.20210112.gappssmtp.com; s=20210112; h=user-agent:in-reply-to:content-disposition:mime-version:references :message-id:subject:cc:to:from:date:from:to:cc:subject:date :message-id:reply-to; bh=b61vXyvJrNQobLE+W9bkvtVkeYwwUfvW1tjPmqd7nhs=; b=4Wlch5jE+gK5dG4jGZ8iQJfRjo7xuL5qTdQpSih8HA8Ke9/aLvYpjMeDGBZVZouBX6 8h9txkj5lKI58AB1Ea0CJsRhFcmVlVyls8WY/Zw43JYi/Kg45eR9cK4uUt1RCZJHdbDj nKNWiU54ligD45v5VrvJW7N/eN4RsHyqa7lCBnqdU5DcuC9ccaTftusYNNwvWw2dgA+c JCT1sHoSZObGUvxoOeS48LmaTrLBhgV2MxNwew4r2GsStrz8QlynDnnzuurQT94wKeD2 LFZ6YXhpw8IBl6e5spltmntCWPxcp626PCccsn5gemsPXOx+Xo5AKOha/epmNWPE6M0n B79g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=user-agent:in-reply-to:content-disposition:mime-version:references :message-id:subject:cc:to:from:date:x-gm-message-state:from:to:cc :subject:date:message-id:reply-to; bh=b61vXyvJrNQobLE+W9bkvtVkeYwwUfvW1tjPmqd7nhs=; b=N02GsMDBA3rB2HSnv9ZHRJsq67ssm5TxuUBNoB7OudrJ73g9bZ68JU4fat662v/S7o atFqzrhLBSaSxIBgTbfIEZOTkmOQC279Id7kZMIYacPhdbXM/PE0HrMXdyRSPqe4ihD9 GftAwlMvOC3wSfbQpD+08bECKNlbPcj6TFwnn88X32f9MrJEJn5j1XbwinAL0aJx5/DY jKFpH5tgBG3l4cXs1ARSjHMnPZ6NiWW+DuQ2sH8CYECRv51TbHd27cYQqUD1hyrt6mHn PLy877FHqtZZUTmGSbVRlBwQceZIl8yAX9QAxc2hd/+oGKFMP+OaTkBYJ0xnc6vG7Igh SbFA== X-Gm-Message-State: AO0yUKVqYXLDlL2Um9zYSKXENlUj5MoTU+A5xG/UeXa9ACPd56WAOq4a 582bsiCL9+VATqJxuFUEdXjdFg== X-Google-Smtp-Source: AK7set+YyESoP4rWnqT3yhqBOFD5v7zp/V5BUgoYP4WONJxeHwk0q8o1tG6Dax2YAHy1xgZzKKFOSQ== X-Received: by 2002:a05:6e02:1aa4:b0:310:bcb1:db9c with SMTP id l4-20020a056e021aa400b00310bcb1db9cmr3639087ilv.9.1674833336556; Fri, 27 Jan 2023 07:28:56 -0800 (PST) Received: from pryzbyj.telsasoft (charmander.telsasoft.com. [50.244.222.1]) by smtp.gmail.com with ESMTPSA id t17-20020a026411000000b003a7d4e3597asm1598480jac.140.2023.01.27.07.28.55 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Fri, 27 Jan 2023 07:28:55 -0800 (PST) Received: by pryzbyj.telsasoft (Postfix, from userid 1000) id 867CB800676; Fri, 27 Jan 2023 09:28:54 -0600 (CST) Date: Fri, 27 Jan 2023 09:28:54 -0600 From: Justin Pryzby To: Andrew Dunstan Cc: Tom Lane , Michael Paquier , Robert Haas , Alvaro Herrera , pgsql-committers@lists.postgresql.org Subject: Re: pgsql: Adjust interaction of CREATEROLE with role properties. Message-ID: <20230127152854.GY22427@telsasoft.com> References: <20230126114659.x3yuypot7p6zj73c@alvherre.pgsql> <25633601-9749-a03c-e1cc-8ff2cf7c599a@dunslane.net> <20230126183106.GS22427@telsasoft.com> <3248936.1674785303@sss.pgh.pa.us> <3255990.1674789499@sss.pgh.pa.us> <6c20449e-ac9c-7876-0d90-70a1ddea1076@dunslane.net> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <6c20449e-ac9c-7876-0d90-70a1ddea1076@dunslane.net> User-Agent: Mutt/1.9.4 (2018-02-28) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Fri, Jan 27, 2023 at 08:27:40AM -0500, Andrew Dunstan wrote: > On 2023-01-26 Th 22:18, Tom Lane wrote: > > Michael Paquier writes: > >> On Thu, Jan 26, 2023 at 09:08:23PM -0500, Tom Lane wrote: > >>> Is it worth checking for leftover regress_xxx tablespaces as well as > >>> roles? > >> Guess so. If this is to be applied to everything that fails under the > >> naming restrictions, there could be a point in doing the same for > >> databases, subscriptions and replication origins. Now the argument > >> has less weight for these object types, surely, compared to roles and > >> tbspaces. > > By the time we've spun up a new backend, we might as well do more > > than one query, so it seems like we might as well check everything > > that's globally visible. > > > > However ... this'd only cover mistakes of this kind in the core > > regression tests. Is there a way to cover contrib's installcheck > > (without adding an additional psql run to each module's tests)? > > > > Maybe we need to enforce this at some other level than the tests > > themselves, perhaps in pg_regress? > > Yeah, that seems like a better way to go. Then it would even apply to > external project tests. Maybe this could be done by creating a pg_dump ? The dump could be grepped (like pg_restore -l |perl -pe '/ROLE|.../') for offending objects. And the dump could be re-used for other things, like not re-running regression tests in pg_upgrade and 027_stream_regress. I don't know whether the pg_dump would be run directly by pg_regress or something else. -- Justin