Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rjwet-00997I-Os for pgsql-bugs@arkaria.postgresql.org; Tue, 12 Mar 2024 07:34:48 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rjwes-007hTu-3A for pgsql-bugs@arkaria.postgresql.org; Tue, 12 Mar 2024 07:34:46 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rjwer-007hTm-NN for pgsql-bugs@lists.postgresql.org; Tue, 12 Mar 2024 07:34:46 +0000 Received: from mail-wr1-x433.google.com ([2a00:1450:4864:20::433]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rjweo-00456G-C9 for pgsql-bugs@lists.postgresql.org; Tue, 12 Mar 2024 07:34:44 +0000 Received: by mail-wr1-x433.google.com with SMTP id ffacd0b85a97d-33e899ce9e3so1957336f8f.1 for ; Tue, 12 Mar 2024 00:34:43 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cybertec-at.20230601.gappssmtp.com; s=20230601; t=1710228882; x=1710833682; darn=lists.postgresql.org; h=mime-version:user-agent:content-transfer-encoding:autocrypt :references:in-reply-to:date:to:from:subject:message-id:from:to:cc :subject:date:message-id:reply-to; bh=mgzZtSCwV20bGJq2fovx0wqgzXyHTF41Cn46lH3ElkU=; b=eI0W0ZSHtiSYpBGP0Yf341jWolqXWMqjX+gVo+ixNQ+8KYCVd3A6T6T5XJh8nVhnc3 V/Z4xP/lwcgkXfWDn3MFjfJUlJ1S2mwNq+dsfES65198Sf9H/ODKs4gOqw6YBwlbjm7+ BFRvNiMGqNOdiLWlYtcYZPjvTDJRDdlfkvlXxF5GyNkmdweetVbmK3A+IWp5plT5oiSQ JZV/zwYNDCFUvQ4RMT9MAqtSnBvPHV2pfW0jQld00t1MMeW46rnVHSHcbbiX7kpCdOLG 8qbEd+UvZVju4sYaAklAH2lrR+ZW8dG74EFTAYwx6U5CM3P/laXtWZhjzA2ehctC0xdF 8vRw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1710228882; x=1710833682; h=mime-version:user-agent:content-transfer-encoding:autocrypt :references:in-reply-to:date:to:from:subject:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=mgzZtSCwV20bGJq2fovx0wqgzXyHTF41Cn46lH3ElkU=; b=heko/IrGAZYcoBkDORqR600/vkK8GjNCo9WClZd+Hm4Z3Xl+v8LkFZeP7QAbQLbA5H +1DGMnvmX/nm8ohGlW93ZwvngvDYeGMthSokYZH7vdkaKtQ1krd/l/9P0MCQWUN9Caag B76gmKNPuKNvYWc62m3NNdQFFxQc1jk4GRqEu39clLwYB3Hvq2O2NTRggZsfE9YQO9gS 4uPmNFVYpZhju6Q4xh1RleDjWBlWDl0J8Q9fcmuuHyOdggkB9cceS3sJDfTjMOADsVs3 eok776Q8JX5ogQFPcLrlTMpwCw0naR4o2prCk0YHiIBkE5BvHee66GZIpjLs2Zqyd3+j kfVg== X-Forwarded-Encrypted: i=1; AJvYcCV+os9Qyzs+v6L3DLM3oPYz9cyfdZsyrMZRt/Q5fnbJiqDdPvZvk3cKZPAwxht/Wwt44ALMPZlDRzG2o/TQRySABbgJocwNIaHKMsyC1lnt X-Gm-Message-State: AOJu0YwyuGIxhd8AgCRMwK8LNIuUec3OsVZ5PXK2Jw7DH6UgkcmVYHth g8mPPPizFnaD93DjXsG7Pl5XZIFFb4n/Q04t6Z0xBOTvk5IEqYH4nJb2+/GqxNk= X-Google-Smtp-Source: AGHT+IEjOzlDeDE85E7H4Mdxk7fInakM4KxJQYy1hApQ0RR/nTPG2CO8Wurik9LuhYTlApsjrcL9JA== X-Received: by 2002:a5d:584a:0:b0:33e:7a71:1a31 with SMTP id i10-20020a5d584a000000b0033e7a711a31mr6849204wrf.6.1710228881640; Tue, 12 Mar 2024 00:34:41 -0700 (PDT) Received: from localhost.localdomain ([2001:871:5e:66c8:ce70:98fc:bce3:9897]) by smtp.gmail.com with ESMTPSA id c13-20020adfe74d000000b0033cf4e47496sm8339665wrn.51.2024.03.12.00.34.41 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Tue, 12 Mar 2024 00:34:41 -0700 (PDT) Message-ID: Subject: Re: BUG #18387: Erroneous permission checks and/or misleading error messages with refresh materialized view From: Laurenz Albe To: Heikki Linnakangas , maxim.boguk@gmail.com, pgsql-bugs@lists.postgresql.org Date: Tue, 12 Mar 2024 08:34:40 +0100 In-Reply-To: <36bb333d-13ec-4f8e-af33-48249ca2fe69@iki.fi> References: <18387-12041858d9efb6f6@postgresql.org> <36bb333d-13ec-4f8e-af33-48249ca2fe69@iki.fi> Autocrypt: addr=laurenz.albe@cybertec.at; prefer-encrypt=mutual; keydata=mQINBGGDwAQBEADgbWy5cKXQld3N2mF+DFyiNFbi2oBl2T+XgxpPF8wTRw2D/u4bBKXP0SYSE/lA86jIVNWWU0gf1KODIkVvgJm2w4vH2VBV1b7ddVViGl1Iu+9zaRnv9wulhnH42KefepXnoean6UT1EzLM0opF/Ik0j+40TxdRtobkBprkQUyHDXWlHc2ffPs3SipyFEP9AVLf7ejRC46CXWDnsqjOBSMEW8Z4HiK/8RrPZBsKLts8dJxKF4pygOdJb0CWk8k/X1jbcfdxo+zOLjOMvJcSJ2pFdJmQHU+JufB3rePziqQ2S9Ur6sccr9XnTC1GVBWN4Lf5VHq+vf+bFJjVwg+2hrySZnAVfcOrxoqFLErr7ug1zN2nM1kcpgA4VWn4gxlJtYNYYq+9WxX5dtvnNANlG3ZCrRKQzl8lxtzoF6Zo7LUhEqPaHDwn7Rvs+IdbOn41lF5UDTJGqmC4gS/bZydW2Fy3YWm4aSaN9fgFf8D+PVkrlKAZB7gBLz1TyHjbcRf85cYF+GKKrDld5SzMB/V60VX3oP/Eo8ikFpyWaqiz1f9X7MBot3/PjJkY+wDzp3nmb19QEcOBuQiSQ4xds2r0HewbuHTAR68u8jNNMGmpm2j4x+g09Jd/WQDjqlTBZ/jEltH41fYCCPWMfljXTOOXu2eLNGdfi7ETZogtwjM9oTtSPQARAQABtCdMYXVyZW56IEFsYmUgPGxhdXJlbnouYWxiZUBjeWJlcnRlYy5hdD6JAk4EEwEIADgWIQR0CqhbZGGABqoaSbdi8bhXA2EdmAUCYYPABAIbAwULCQgHAgYVCgkICwIEFgIDAQIeAQIXgAAKCRBi8bhXA2EdmM/6EADK232JCwmBzhlj8h7U9CjG6kx0JHP3uJGv+XfsHtHAlmY/RCwF1BHMEsRlk bT5UrLvJ2jb99bA9QARzhFaxzyn0F/BUKzuIjRGNs/n6d5dNUFA0kOt8sX+TacmC GEyjEBCrVCm4ranBiUyePn9NhHNWnaex7pJyqvMLLdwW9BEMJx0Fqo+DN8ukbXmYRsmhEtd3ue+x/luYmOmJnaGtzInaY5aOJYbW9XqoRIZkZvOCgbi1FfvNmoqWa+3oVxTOgw9RafjJDyW0lTHzKGjbGI5ofMU98l+/hKJFYJqWUF6VpFJY5YIcN/1lf4ZICMwDl+MPIVo/tpq8L10seJL28nLlvw3K+cI+TVW8IW/qL/LyVoDofI3USeOORuYmhpWRhik8JXX6xf3v6GrRilJIPWNFIJbxm1ZblQiQnOw3IOW7T+8nAmPin1HKqM3VrOrJQ2VtShsefNBibNAsr1oFaqcDBkn3yGG8i6CTW+FyO4PZ+/EwNxMVgktxbYdy5AT1/lpXr5tB+phhLIyVfiBvrWs5EThxYMQ/L8Y85c3GMsAy1l/x4h3jqySIYy3SCU9+jc5UVuNnXljbvkEzJ+NLWJ6C1rACFWrMszgPdh5tCrlRY9PpmYll4JbCgb8BtxEIUmR+xr50/ZElEK5iml7Q00KUekCcDt+36PsyGFTXBzNOrkCDQRhg8AEARAAzOZ2tLHlI4rrhG411h6cdCFjBZxuljaFCxFyHn3m6wbGLqwBUWC5k8UrRqjHMz88KcTSaNO7XGAmCqPdWd2SeflPZRnNTbjsVpw7mLdffsBm4JX7kki2Pvk5h0NtYeidXT1PSpc2ri4DutYXuT9uD8RAm1wUDCE5HQNUihT/WH6opt+hskHW21uHao0+y822tG0QQcGMqdQR5Vxdxj89wiEPdqW+HpU/oOZIhrf2E7prduAppxixjHy/o1rcnoznnJvc8D3+YgI9O0LrBMij89dM55pRGbLovTR1oGR3U74sX774+0xmSzeIKwZfiMUz7Atlvfk5SHOsRUFPN2Ux9kaXiiBibQpHFxt7b lDrT4wxdLJ/XCdbPPAyl+lZtOLsaHEEZvYNyTXwZc35dVf3R4/oz20HoG6s7ct8e1 AQygj43XAERzty9SkWgxs8+grp1PrGx6FHVSYRqBM8dS/ZR6yRVwOwJXPyaSSqfIF21DkE4j1y4n+ItSewPGoRp8K/yWCikt6qlkVkO2ASNIiX04fAbtzwVOaNn8ZMRNqyvLc1fED4sr49onE4cAIcBLjcC3KL+w9DUGRQCdziROj5H2Yl/sXGPdMciUHo/Uz2rggc+2th3bQiMhrHWSsBpUkDQp0yWewemstPpPgBL3h2fHKaX8B9oH5Qu/H1IgrOuX8AEQEAAYkCNgQYAQgAIBYhBHQKqFtkYYAGqhpJt2LxuFcDYR2YBQJhg8AEAhsMAAoJEGLxuFcDYR2YuPwQAMkpGtR80pQ1gVsONhdkqj0H2eU66efP/gO3CoyaoIcvrpKYj7C2HipVSmkt1gpByL0X4AMQ/vKuknUz3wd28Ba+G1dCfbVs/Xiusq+SmpUj5rTwmYqdSjWMuCo1R6oS5hdJMdUUJYGMT0QkVlm1KnW8jkmCTl9GzjDxOAsN9O6/6lPzaGFtk9XF+34Bry/N4HKiJkqpC4+UTd0AprPfzJ2jdT64e1F0+W88X8y1bTTgNrHwK4mDiLnlE4SKRuEm54lNhJz//ar86Or5BErzNpM6TL7lk44QS06hwsMrEdKIy8J/SYJPjfzR8tIUnKscclVpOgjKaBqC+0iFiVaRqAgfOlIEiezX6kMh5Q2FIUfqs46qWhhXjRrdKOEoStYAaikdLu5ZXr7vfb0ZaDh+ZwTQtbSMFolyOkecwI81MCdbMfT/1TqIGTOdAj5as9fAakk0jb2pXgUYQ8X1DVTR8ahSDVEaw9VTmWiSvTxvguVJ1Mb7gG4Gmh6aviDTJhfXtH4rPUNXhDLqrTH8JkJjyKROOMakIF68Hjse5vUfUxreBEOtb5r1Coa2Fe7ncJayaSE7ryrDbFqpZ 36UMAx4ulWMyqJajLNGY0DdG8qIsR5nxRhrnK/mrCidZ8F9/D3bWAl4rjtHlsztN59 +AnW5l0HsQcY9ntFL/zEBOaonjdJf Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable User-Agent: Evolution 3.50.4 (3.50.4-1.fc39) MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Tue, 2024-03-12 at 01:02 +0200, Heikki Linnakangas wrote: > test=3D# \c test > > You are now connected to database "test" as user "postgres". > > test=3D# REFRESH MATERIALIZED VIEW CONCURRENTLY test_mv; > > ERROR:=C2=A0 permission denied to create temporary tables in database "= test" > > --what??? N2 >=20 > That's interesting. REFRESH MATERIALIZED VIEW CONCURRENTLY uses=20 > temporary tables internally, which fails if the user doesn't have=20 > permissions to create temporary tables. >=20 > I guess we need to allow creating such internal temporary tables,=20 > despite the missing permission. That'll need some careful analysis to=20 > make sure we don't accidentally allow creating other temporary tables... Wouldn't it be sufficient to document that fact, perhaps add an error hint and require the MV owner to have TEMP on the database? That's not an outrageous requirement, and it couldn't open any security back doors. Yours, Laurenz Albe