pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Carsten Klein <c.klein@datagis.com>
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: File-Access functions by default not executable by predefined role "pg_read_server_files"
Date: Tue, 30 May 2023 19:04:20 +0200
Message-ID: <32f6cce9-cbfb-9550-75d8-c53cd4894850@datagis.com> (raw)

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





Message-ID: <32f6cce9-cbfb-9550-75d8-c53cd4894850@datagis.com>
Permalink:  ../32f6cce9-cbfb-9550-75d8-c53cd4894850@datagis.com/
Also on:    postgresql.org/message-id/32f6cce9-cbfb-9550-75d8-c53cd4894850@datagis.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: c.klein@datagis.com
  Subject: Re: File-Access functions by default not executable by predefined role "pg_read_server_files"
  In-Reply-To: <32f6cce9-cbfb-9550-75d8-c53cd4894850@datagis.com>
  References: <32f6cce9-cbfb-9550-75d8-c53cd4894850@datagis.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox