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 1q42lr-0004Vr-Mq for pgsql-general@arkaria.postgresql.org; Tue, 30 May 2023 17:04:31 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1q42lq-0004av-IW for pgsql-general@arkaria.postgresql.org; Tue, 30 May 2023 17:04:30 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1q42lq-0004am-7d for pgsql-general@lists.postgresql.org; Tue, 30 May 2023 17:04:30 +0000 Received: from smtprelay01.ispgateway.de ([80.67.18.13]) by makus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1q42ln-002jpM-7n for pgsql-general@lists.postgresql.org; Tue, 30 May 2023 17:04:28 +0000 Received: from [109.90.124.214] (helo=mail.office.datagis.com) by smtprelay01.ispgateway.de with esmtpsa (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1q42lj-00086H-Nb for pgsql-general@lists.postgresql.org; Tue, 30 May 2023 19:04:23 +0200 Received: from localhost (intranator.datagis.local [127.0.0.1]) by localhost (Postfix) with ESMTP id 79E4E279 for ; Tue, 30 May 2023 19:04:23 +0200 (CEST) X-Virus-Scanned: by Intra2net Mail Security (AVE=8.3.66.92,VDF=8.20.1.114) X-Spam-Status: X-Spam-Level: 0 Received: from [192.168.12.10] (dslb-084-057-082-186.084.057.pools.vodafone-ip.de [84.57.82.186]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange ECDHE (P-384) server-signature RSA-PSS (2048 bits) server-digest SHA256) (No client certificate requested) (Authenticated sender: smtp-auth-user) by mail.office.datagis.com (Postfix) with ESMTPSA id 15F9210A for ; Tue, 30 May 2023 19:04:22 +0200 (CEST) Message-ID: <32f6cce9-cbfb-9550-75d8-c53cd4894850@datagis.com> Date: Tue, 30 May 2023 19:04:20 +0200 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:102.0) Gecko/20100101 Thunderbird/102.11.2 To: "pgsql-general@lists.postgresql.org" Content-Language: en-US From: Carsten Klein Subject: File-Access functions by default not executable by predefined role "pg_read_server_files" Organization: DataGis Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-Df-Sender: NTkwMDE1 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Hi there, in PG 14, you've removed explicit 'superuser()' checks in file-access functions (like pg_ls_dir, pg_read_file, etc.) and moved to an ACL based approach to restrict access to these functions. In turn, you've also removed EXECUTE permission from role "public", leaving these functions accessible by superusers only. See also: https://github.com/postgres/postgres/commit/e79350fef2917522571add750e3e21af293b50fe The docs state (up to PG 16), that, in order to access files *outside* the cluster directory and the "log_directory", a user must be a superuser OR must be granted the role "pg_read_server_files". https://www.postgresql.org/docs/16/functions-admin.html#FUNCTIONS-ADMIN-GENFILE > Only files within the database cluster directory and the log_directory can be accessed, unless the user is a superuser or is granted the role pg_read_server_files. Actually, it seems that it does not matter what file I'm trying to access as a non-superuser. There's no distinction between cluster or log directory and any other directory. Only the function's ACL seems to govern who can execute the function. After explicitly granting EXECUTE permission for such a function to a non-superuser, it can access any file the server backend has read-access to. Maybe the documentation is partially outdated (mixing old superuser() and new ACL-based behavior)? In any case, it could/should be more clear in this respect. Also, as you can see in the above commit, while removing EXECUTE perms from role "public", you grant EXECUTE perms for some of them to role "pg_monitor". You still do this in PG 16 for even more functions (now in new file system_functions.sql -> https://github.com/postgres/postgres/blob/master/src/backend/catalog/system_functions.sql). Why don't you grant EXECUTE perms to predefined role "pg_read_server_files"? That would make this role not as useless as it currently seems to be (for my mind, at least). Is there something that I'm overlooking? Regards, Carsten