pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_read_server_files doesn't let me use pg_ls_dir() or pg_read_file?
5+ messages / 2 participants
[nested] [flat]
* pg_read_server_files doesn't let me use pg_ls_dir() or pg_read_file?
@ 2023-03-14 19:33 hubert depesz lubaczewski <depesz@depesz.com>
2023-03-15 02:10 ` Re: pg_read_server_files doesn't let me use pg_ls_dir() or pg_read_file? Kyotaro Horiguchi <horikyota.ntt@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: hubert depesz lubaczewski @ 2023-03-14 19:33 UTC (permalink / raw)
To: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Hi,
Tested it now on built today Pg 16devel, straight from repo.
In docs (https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-GENFILE), I found:
> The functions shown in Table 9.99 provide native access to files on
> the machine hosting the server. 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. Use
> a relative path for files in the cluster directory, and a path
> matching the log_directory configuration setting for log files.
which I understand that if I'll grant pg_read_server_files to some user,
then this user should be able to use the generic file access functions.
So I tried:
#v+
$ select current_user;
current_user
──────────────
test
(1 row)
$ \du+ test
List of roles
Role name │ Attributes │ Member of │ Description
───────────┼────────────┼───────────────────────────────────────────────────────────────┼─────────────
test │ │ {pg_read_all_settings,pg_read_all_stats,pg_read_server_files} │
$ select * from pg_ls_dir('.');
ERROR: permission denied for function pg_ls_dir
$ select * from pg_ls_dir('log');
ERROR: permission denied for function pg_ls_dir
$ select * from pg_ls_dir('/etc');
ERROR: permission denied for function pg_ls_dir
$ select * from pg_read_file('PG_VERSION');
ERROR: permission denied for function pg_read_file
$ select * from pg_read_file('/etc/passwd');
ERROR: permission denied for function pg_read_file
#v-
After I additionally granted EXECUTE on the functions, I was able to run them, even when I revoked pg_read_server_files.
Am I missing something in there, or is this a bug?
Best regards,
depesz
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pg_read_server_files doesn't let me use pg_ls_dir() or pg_read_file?
2023-03-14 19:33 pg_read_server_files doesn't let me use pg_ls_dir() or pg_read_file? hubert depesz lubaczewski <depesz@depesz.com>
@ 2023-03-15 02:10 ` Kyotaro Horiguchi <horikyota.ntt@gmail.com>
2023-03-15 10:46 ` Re: pg_read_server_files doesn't let me use pg_ls_dir() or pg_read_file? hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 1 reply; 5+ messages in thread
From: Kyotaro Horiguchi @ 2023-03-15 02:10 UTC (permalink / raw)
To: depesz@depesz.com; +Cc: pgsql-bugs@lists.postgresql.org
At Tue, 14 Mar 2023 20:33:10 +0100, hubert depesz lubaczewski <depesz@depesz.com> wrote in
> Hi,
> Tested it now on built today Pg 16devel, straight from repo.
>
> In docs (https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-GENFILE), I found:
> > The functions shown in Table 9.99 provide native access to files on
> > the machine hosting the server. 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. Use
> > a relative path for files in the cluster directory, and a path
> > matching the log_directory configuration setting for log files.
>
> which I understand that if I'll grant pg_read_server_files to some user,
> then this user should be able to use the generic file access functions.
> $ select * from pg_ls_dir('.');
> ERROR: permission denied for function pg_ls_dir
"GRANT EXECUTE ON FUNCTION pg_ls_dir(text) TO test" might work for you.
The doc section you are referring to also describes that on a
per-function basis like this.
> This function is restricted to superusers by default, but other
> users can be granted EXECUTE to run the function.
regards.
--
Kyotaro Horiguchi
NTT Open Source Software Center
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pg_read_server_files doesn't let me use pg_ls_dir() or pg_read_file?
2023-03-14 19:33 pg_read_server_files doesn't let me use pg_ls_dir() or pg_read_file? hubert depesz lubaczewski <depesz@depesz.com>
2023-03-15 02:10 ` Re: pg_read_server_files doesn't let me use pg_ls_dir() or pg_read_file? Kyotaro Horiguchi <horikyota.ntt@gmail.com>
@ 2023-03-15 10:46 ` hubert depesz lubaczewski <depesz@depesz.com>
2023-03-16 01:11 ` Re: pg_read_server_files doesn't let me use pg_ls_dir() or pg_read_file? Kyotaro Horiguchi <horikyota.ntt@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: hubert depesz lubaczewski @ 2023-03-15 10:46 UTC (permalink / raw)
To: Kyotaro Horiguchi <horikyota.ntt@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
On Wed, Mar 15, 2023 at 11:10:11AM +0900, Kyotaro Horiguchi wrote:
> At Tue, 14 Mar 2023 20:33:10 +0100, hubert depesz lubaczewski <depesz@depesz.com> wrote in
> > Hi,
> > Tested it now on built today Pg 16devel, straight from repo.
> >
> > In docs (https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-GENFILE), I found:
> > > The functions shown in Table 9.99 provide native access to files on
> > > the machine hosting the server. 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. Use
> > > a relative path for files in the cluster directory, and a path
> > > matching the log_directory configuration setting for log files.
> >
> > which I understand that if I'll grant pg_read_server_files to some user,
> > then this user should be able to use the generic file access functions.
>
> > $ select * from pg_ls_dir('.');
> > ERROR: permission denied for function pg_ls_dir
>
> "GRANT EXECUTE ON FUNCTION pg_ls_dir(text) TO test" might work for you.
>
> The doc section you are referring to also describes that on a
> per-function basis like this.
OK, I get it, but then, what is the point of mentioning
pg_read_server_files in the doc? I can just as well grant execute on the
functions to someone. And, I tested, they don't need to be in
pg_read_server_files.
Best regards,
depesz
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pg_read_server_files doesn't let me use pg_ls_dir() or pg_read_file?
2023-03-14 19:33 pg_read_server_files doesn't let me use pg_ls_dir() or pg_read_file? hubert depesz lubaczewski <depesz@depesz.com>
2023-03-15 02:10 ` Re: pg_read_server_files doesn't let me use pg_ls_dir() or pg_read_file? Kyotaro Horiguchi <horikyota.ntt@gmail.com>
2023-03-15 10:46 ` Re: pg_read_server_files doesn't let me use pg_ls_dir() or pg_read_file? hubert depesz lubaczewski <depesz@depesz.com>
@ 2023-03-16 01:11 ` Kyotaro Horiguchi <horikyota.ntt@gmail.com>
2023-03-16 11:56 ` Re: pg_read_server_files doesn't let me use pg_ls_dir() or pg_read_file? hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 1 reply; 5+ messages in thread
From: Kyotaro Horiguchi @ 2023-03-16 01:11 UTC (permalink / raw)
To: depesz@depesz.com; +Cc: pgsql-bugs@lists.postgresql.org
At Wed, 15 Mar 2023 11:46:17 +0100, hubert depesz lubaczewski <depesz@depesz.com> wrote in
> On Wed, Mar 15, 2023 at 11:10:11AM +0900, Kyotaro Horiguchi wrote:
> > At Tue, 14 Mar 2023 20:33:10 +0100, hubert depesz lubaczewski <depesz@depesz.com> wrote
>
> OK, I get it, but then, what is the point of mentioning
> pg_read_server_files in the doc? I can just as well grant execute on the
> functions to someone. And, I tested, they don't need to be in
> pg_read_server_files.
I believe you can confirm that the non-superuser test cannot access
/etc without the pg_read_server_files privilege.
> postgres=> select * from pg_ls_dir('/etc');
> ERROR: absolute path not allowed
> postgres=> select * from pg_ls_dir('../../');
> ERROR: path must be in or below the current directory
("current" directory...)
Although the order of the descriptions may seem reversed, the doc
clearly states that pg_read_server_files is responsible for
controlling access outside the database cluster directory and the
log_directory for non-superusers, while GRANT EXECUTE regulates
executability for non-superusers.
regards.
--
Kyotaro Horiguchi
NTT Open Source Software Center
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pg_read_server_files doesn't let me use pg_ls_dir() or pg_read_file?
2023-03-14 19:33 pg_read_server_files doesn't let me use pg_ls_dir() or pg_read_file? hubert depesz lubaczewski <depesz@depesz.com>
2023-03-15 02:10 ` Re: pg_read_server_files doesn't let me use pg_ls_dir() or pg_read_file? Kyotaro Horiguchi <horikyota.ntt@gmail.com>
2023-03-15 10:46 ` Re: pg_read_server_files doesn't let me use pg_ls_dir() or pg_read_file? hubert depesz lubaczewski <depesz@depesz.com>
2023-03-16 01:11 ` Re: pg_read_server_files doesn't let me use pg_ls_dir() or pg_read_file? Kyotaro Horiguchi <horikyota.ntt@gmail.com>
@ 2023-03-16 11:56 ` hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 0 replies; 5+ messages in thread
From: hubert depesz lubaczewski @ 2023-03-16 11:56 UTC (permalink / raw)
To: Kyotaro Horiguchi <horikyota.ntt@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
On Thu, Mar 16, 2023 at 10:11:04AM +0900, Kyotaro Horiguchi wrote:
> At Wed, 15 Mar 2023 11:46:17 +0100, hubert depesz lubaczewski <depesz@depesz.com> wrote in
> > On Wed, Mar 15, 2023 at 11:10:11AM +0900, Kyotaro Horiguchi wrote:
> > > At Tue, 14 Mar 2023 20:33:10 +0100, hubert depesz lubaczewski <depesz@depesz.com> wrote
> >
> > OK, I get it, but then, what is the point of mentioning
> > pg_read_server_files in the doc? I can just as well grant execute on the
> > functions to someone. And, I tested, they don't need to be in
> > pg_read_server_files.
>
> I believe you can confirm that the non-superuser test cannot access
> /etc without the pg_read_server_files privilege.
>
> > postgres=> select * from pg_ls_dir('/etc');
> > ERROR: absolute path not allowed
> > postgres=> select * from pg_ls_dir('../../');
> > ERROR: path must be in or below the current directory
> ("current" directory...)
>
> Although the order of the descriptions may seem reversed, the doc
> clearly states that pg_read_server_files is responsible for
> controlling access outside the database cluster directory and the
> log_directory for non-superusers, while GRANT EXECUTE regulates
> executability for non-superusers.
Ok, tested. You're right. So it was my misunderstanding. Could be that
the docs could use some clarification, but otoh, it could be just as
well that I suck at understanding :)
Thanks a lot for the info.
Best regards,
depesz
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2023-03-16 11:56 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-03-14 19:33 pg_read_server_files doesn't let me use pg_ls_dir() or pg_read_file? hubert depesz lubaczewski <depesz@depesz.com>
2023-03-15 02:10 ` Kyotaro Horiguchi <horikyota.ntt@gmail.com>
2023-03-15 10:46 ` hubert depesz lubaczewski <depesz@depesz.com>
2023-03-16 01:11 ` Kyotaro Horiguchi <horikyota.ntt@gmail.com>
2023-03-16 11:56 ` hubert depesz lubaczewski <depesz@depesz.com>
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