Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit0fdc849

Browse files
committed
Add default roles for file/program access
This patch adds new default roles named 'pg_read_server_files','pg_write_server_files', 'pg_execute_server_program' whichallow an administrator to GRANT to a non-superuser role the ability toaccess server-side files or run programs through PostgreSQL (as the userthe database is running as). Having one of these roles allows anon-superuser to use server-side COPY to read, write, or with a program,and to use file_fdw (if installed by a superuser and GRANT'd USAGE onit) to read from files or run a program.The existing misc file functions are also changed to allow a user withthe 'pg_read_server_files' default role to read any files on thefilesystem, matching the privileges given to that role through COPY andfile_fdw from above.Reviewed-By: Michael PaquierDiscussion:https://postgr.es/m/20171231191939.GR2416%40tamriel.snowman.net
1 parente79350f commit0fdc849

File tree

9 files changed

+145
-47
lines changed

9 files changed

+145
-47
lines changed

‎contrib/file_fdw/file_fdw.c

Lines changed: 33 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -18,6 +18,7 @@
1818
#include"access/htup_details.h"
1919
#include"access/reloptions.h"
2020
#include"access/sysattr.h"
21+
#include"catalog/pg_authid.h"
2122
#include"catalog/pg_foreign_table.h"
2223
#include"commands/copy.h"
2324
#include"commands/defrem.h"
@@ -201,24 +202,6 @@ file_fdw_validator(PG_FUNCTION_ARGS)
201202
List*other_options=NIL;
202203
ListCell*cell;
203204

204-
/*
205-
* Only superusers are allowed to set options of a file_fdw foreign table.
206-
* This is because we don't want non-superusers to be able to control
207-
* which file gets read or which program gets executed.
208-
*
209-
* Putting this sort of permissions check in a validator is a bit of a
210-
* crock, but there doesn't seem to be any other place that can enforce
211-
* the check more cleanly.
212-
*
213-
* Note that the valid_options[] array disallows setting filename and
214-
* program at any options level other than foreign table --- otherwise
215-
* there'd still be a security hole.
216-
*/
217-
if (catalog==ForeignTableRelationId&& !superuser())
218-
ereport(ERROR,
219-
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
220-
errmsg("only superuser can change options of a file_fdw foreign table")));
221-
222205
/*
223206
* Check that only options supported by file_fdw, and allowed for the
224207
* current object type, are given.
@@ -264,6 +247,38 @@ file_fdw_validator(PG_FUNCTION_ARGS)
264247
ereport(ERROR,
265248
(errcode(ERRCODE_SYNTAX_ERROR),
266249
errmsg("conflicting or redundant options")));
250+
251+
/*
252+
* Check permissions for changing which file or program is used by
253+
* the file_fdw.
254+
*
255+
* Only members of the role 'pg_read_server_files' are allowed to
256+
* set the 'filename' option of a file_fdw foreign table, while
257+
* only members of the role 'pg_execute_server_program' are
258+
* allowed to set the 'program' option. This is because we don't
259+
* want regular users to be able to control which file gets read
260+
* or which program gets executed.
261+
*
262+
* Putting this sort of permissions check in a validator is a bit
263+
* of a crock, but there doesn't seem to be any other place that
264+
* can enforce the check more cleanly.
265+
*
266+
* Note that the valid_options[] array disallows setting filename
267+
* and program at any options level other than foreign table ---
268+
* otherwise there'd still be a security hole.
269+
*/
270+
if (strcmp(def->defname,"filename")==0&&
271+
!is_member_of_role(GetUserId(),DEFAULT_ROLE_READ_SERVER_FILES))
272+
ereport(ERROR,
273+
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
274+
errmsg("only superuser or a member of the pg_read_server_files role may specify the filename option of a file_fdw foreign table")));
275+
276+
if (strcmp(def->defname,"program")==0&&
277+
!is_member_of_role(GetUserId(),DEFAULT_ROLE_EXECUTE_SERVER_PROGRAM))
278+
ereport(ERROR,
279+
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
280+
errmsg("only superuser or a member of the pg_execute_server_program role may specify the program option of a file_fdw foreign table")));
281+
267282
filename=defGetString(def);
268283
}
269284

‎contrib/file_fdw/output/file_fdw.source

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -422,7 +422,7 @@ ALTER FOREIGN TABLE agg_text OWNER TO regress_file_fdw_user;
422422
ALTER FOREIGN TABLE agg_text OPTIONS (SET format 'text');
423423
SET ROLE regress_file_fdw_user;
424424
ALTER FOREIGN TABLE agg_text OPTIONS (SET format 'text');
425-
ERROR: only superusercan change options of a file_fdw foreign table
425+
ERROR: only superuseror a member of the pg_read_server_files role may specify the filename option of a file_fdw foreign table
426426
SET ROLE regress_file_fdw_superuser;
427427
-- cleanup
428428
RESET ROLE;

‎doc/src/sgml/file-fdw.sgml

Lines changed: 5 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -186,9 +186,11 @@
186186
</para>
187187

188188
<para>
189-
Changing table-level options requires superuser privileges, for security
190-
reasons: only a superuser should be able to control which file is read
191-
or which program is run. In principle non-superusers could be allowed to
189+
Changing table-level options requires being a superuser or having the privileges
190+
of the default role <literal>pg_read_server_files</literal> (to use a filename) or
191+
the default role <literal>pg_execute_server_programs</literal> (to use a program),
192+
for security reasons: only certain users should be able to control which file is
193+
read or which program is run. In principle regular users could be allowed to
192194
change the other options, but that's not supported at present.
193195
</para>
194196

‎doc/src/sgml/func.sgml

Lines changed: 19 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -20119,10 +20119,21 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
2011920119
linkend="functions-admin-genfile-table"/> provide native access to
2012020120
files on the machine hosting the server. Only files within the
2012120121
database cluster directory and the <varname>log_directory</varname> can be
20122-
accessed. Use a relative path for files in the cluster directory,
20123-
and a path matching the <varname>log_directory</varname> configuration setting
20124-
for log files. Use of these functions is restricted to superusers
20125-
except where stated otherwise.
20122+
accessed unless the user is granted the role
20123+
<literal>pg_read_server_files</literal>. Use a relative path for files in
20124+
the cluster directory, and a path matching the <varname>log_directory</varname>
20125+
configuration setting for log files.
20126+
</para>
20127+
20128+
<para>
20129+
Note that granting users the EXECUTE privilege on the
20130+
<function>pg_read_file()</function>, or related, functions allows them the
20131+
ability to read any file on the server which the database can read and
20132+
that those reads bypass all in-database privilege checks. This means that,
20133+
among other things, a user with this access is able to read the contents of the
20134+
<literal>pg_authid</literal> table where authentication information is contained,
20135+
as well as read any file in the database. Therefore, granting access to these
20136+
functions should be carefully considered.
2012620137
</para>
2012720138

2012820139
<table id="functions-admin-genfile-table">
@@ -20140,7 +20151,7 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
2014020151
</entry>
2014120152
<entry><type>setof text</type></entry>
2014220153
<entry>
20143-
List the contents of a directory.
20154+
List the contents of a directory. Restricted to superusers by default, but other users can be granted EXECUTE to run the function.
2014420155
</entry>
2014520156
</row>
2014620157
<row>
@@ -20171,7 +20182,7 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
2017120182
</entry>
2017220183
<entry><type>text</type></entry>
2017320184
<entry>
20174-
Return the contents of a text file.
20185+
Return the contents of a text file. Restricted to superusers by default, but other users can be granted EXECUTE to run the function.
2017520186
</entry>
2017620187
</row>
2017720188
<row>
@@ -20180,7 +20191,7 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
2018020191
</entry>
2018120192
<entry><type>bytea</type></entry>
2018220193
<entry>
20183-
Return the contents of a file.
20194+
Return the contents of a file. Restricted to superusers by default, but other users can be granted EXECUTE to run the function.
2018420195
</entry>
2018520196
</row>
2018620197
<row>
@@ -20189,7 +20200,7 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
2018920200
</entry>
2019020201
<entry><type>record</type></entry>
2019120202
<entry>
20192-
Return information about a file.
20203+
Return information about a file. Restricted to superusers by default, but other users can be granted EXECUTE to run the function.
2019320204
</entry>
2019420205
</row>
2019520206
</tbody>

‎doc/src/sgml/ref/copy.sgml

Lines changed: 6 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -444,8 +444,12 @@ COPY <replaceable class="parameter">count</replaceable>
444444
by the server, not by the client application, must be executable by the
445445
<productname>PostgreSQL</productname> user.
446446
<command>COPY</command> naming a file or command is only allowed to
447-
database superusers, since it allows reading or writing any file that the
448-
server has privileges to access.
447+
database superusers or users who are granted one of the default roles
448+
<literal>pg_read_server_files</literal>,
449+
<literal>pg_write_server_files</literal>,
450+
or <literal>pg_execute_server_program</literal>, since it allows reading
451+
or writing any file or running a program that the server has privileges to
452+
access.
449453
</para>
450454

451455
<para>

‎doc/src/sgml/user-manag.sgml

Lines changed: 27 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -534,6 +534,21 @@ DROP ROLE doomed_role;
534534
<entry>pg_signal_backend</entry>
535535
<entry>Send signals to other backends (eg: cancel query, terminate).</entry>
536536
</row>
537+
<row>
538+
<entry>pg_read_server_files</entry>
539+
<entry>Allow reading files from any location the database can access on the server with COPY and
540+
other file-access functions.</entry>
541+
</row>
542+
<row>
543+
<entry>pg_write_server_files</entry>
544+
<entry>Allow writing to files in any location the database can access on the server with COPY and
545+
other file-access functions.</entry>
546+
</row>
547+
<row>
548+
<entry>pg_execute_server_program</entry>
549+
<entry>Allow executing programs on the database server as the user the database runs as with
550+
COPY and other functions which allow executing a server-side program.</entry>
551+
</row>
537552
<row>
538553
<entry>pg_monitor</entry>
539554
<entry>Read/execute various monitoring views and functions.
@@ -545,6 +560,16 @@ DROP ROLE doomed_role;
545560
</tgroup>
546561
</table>
547562

563+
<para>
564+
The <literal>pg_read_server_files</literal>, <literal>pg_write_server_files</literal> and
565+
<literal>pg_execute_server_program</literal> roles are intended to allow administrators to have
566+
trusted, but non-superuser, roles which are able to access files and run programs on the
567+
database server as the user the database runs as. As these roles are able to access any file on
568+
the server filesystem, they bypass all database-level permission checks when accessing files
569+
directly and they could be used to gain superuser-level access, therefore care should be taken
570+
when granting these roles to users.
571+
</para>
572+
548573
<para>
549574
The <literal>pg_monitor</literal>, <literal>pg_read_all_settings</literal>,
550575
<literal>pg_read_all_stats</literal> and <literal>pg_stat_scan_tables</literal>
@@ -556,7 +581,8 @@ DROP ROLE doomed_role;
556581

557582
<para>
558583
Care should be taken when granting these roles to ensure they are only used where
559-
needed to perform the desired monitoring.
584+
needed and with the understanding that these roles grant access to privileged
585+
information.
560586
</para>
561587

562588
<para>

‎src/backend/commands/copy.c

Lines changed: 32 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -23,6 +23,8 @@
2323
#include"access/sysattr.h"
2424
#include"access/xact.h"
2525
#include"access/xlog.h"
26+
#include"catalog/dependency.h"
27+
#include"catalog/pg_authid.h"
2628
#include"catalog/pg_type.h"
2729
#include"commands/copy.h"
2830
#include"commands/defrem.h"
@@ -769,8 +771,8 @@ CopyLoadRawBuf(CopyState cstate)
769771
* input/output stream. The latter could be either stdin/stdout or a
770772
* socket, depending on whether we're running under Postmaster control.
771773
*
772-
* Do not allow a Postgres user withoutsuperuser privilege to read from
773-
* or write to a file.
774+
* Do not allow a Postgres user withoutthe 'pg_access_server_files' role to
775+
*read fromor write to a file.
774776
*
775777
* Do not allow the copy if user doesn't have proper permission to access
776778
* the table or the specifically requested columns.
@@ -787,21 +789,37 @@ DoCopy(ParseState *pstate, const CopyStmt *stmt,
787789
Oidrelid;
788790
RawStmt*query=NULL;
789791

790-
/* Disallow COPY to/from file or program except to superusers. */
791-
if (!pipe&& !superuser())
792+
/*
793+
* Disallow COPY to/from file or program except to users with the
794+
* appropriate role.
795+
*/
796+
if (!pipe)
792797
{
793798
if (stmt->is_program)
794-
ereport(ERROR,
795-
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
796-
errmsg("must be superuser to COPY to or from an external program"),
797-
errhint("Anyone can COPY to stdout or from stdin. "
798-
"psql's \\copy command also works for anyone.")));
799+
{
800+
if (!is_member_of_role(GetUserId(),DEFAULT_ROLE_EXECUTE_SERVER_PROGRAM))
801+
ereport(ERROR,
802+
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
803+
errmsg("must be superuser or a member of the pg_execute_server_program role to COPY to or from an external program"),
804+
errhint("Anyone can COPY to stdout or from stdin. "
805+
"psql's \\copy command also works for anyone.")));
806+
}
799807
else
800-
ereport(ERROR,
801-
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
802-
errmsg("must be superuser to COPY to or from a file"),
803-
errhint("Anyone can COPY to stdout or from stdin. "
804-
"psql's \\copy command also works for anyone.")));
808+
{
809+
if (is_from&& !is_member_of_role(GetUserId(),DEFAULT_ROLE_READ_SERVER_FILES))
810+
ereport(ERROR,
811+
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
812+
errmsg("must be superuser or a member of the pg_read_server_files role to COPY from a file"),
813+
errhint("Anyone can COPY to stdout or from stdin. "
814+
"psql's \\copy command also works for anyone.")));
815+
816+
if (!is_from&& !is_member_of_role(GetUserId(),DEFAULT_ROLE_WRITE_SERVER_FILES))
817+
ereport(ERROR,
818+
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
819+
errmsg("must be superuser or a member of the pg_write_server_files role to COPY to a file"),
820+
errhint("Anyone can COPY to stdout or from stdin. "
821+
"psql's \\copy command also works for anyone.")));
822+
}
805823
}
806824

807825
if (stmt->relation)

‎src/backend/utils/adt/genfile.c

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -22,6 +22,7 @@
2222

2323
#include"access/htup_details.h"
2424
#include"access/xlog_internal.h"
25+
#include"catalog/pg_authid.h"
2526
#include"catalog/pg_type.h"
2627
#include"funcapi.h"
2728
#include"mb/pg_wchar.h"
@@ -45,6 +46,12 @@ typedef struct
4546
*
4647
* Filename may be absolute or relative to the DataDir, but we only allow
4748
* absolute paths that match DataDir or Log_directory.
49+
*
50+
* This does a privilege check against the 'pg_read_server_files' role, so
51+
* this function is really only appropriate for callers who are only checking
52+
* 'read' access. Do not use this function if you are looking for a check
53+
* for 'write' or 'program' access without updating it to access the type
54+
* of check as an argument and checking the appropriate role membership.
4855
*/
4956
staticchar*
5057
convert_and_check_filename(text*arg)
@@ -54,6 +61,15 @@ convert_and_check_filename(text *arg)
5461
filename=text_to_cstring(arg);
5562
canonicalize_path(filename);/* filename can change length here */
5663

64+
/*
65+
* Members of the 'pg_read_server_files' role are allowed to access any
66+
* files on the server as the PG user, so no need to do any further checks
67+
* here.
68+
*/
69+
if (is_member_of_role(GetUserId(),DEFAULT_ROLE_READ_SERVER_FILES))
70+
returnfilename;
71+
72+
/* User isn't a member of the default role, so check if it's allowable */
5773
if (is_absolute_path(filename))
5874
{
5975
/* Disallow '/a/b/data/..' */

‎src/include/catalog/pg_authid.h

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -108,6 +108,12 @@ DATA(insert OID = 3375 ( "pg_read_all_stats" f t f f f f f -1 _null_ _null_));
108108
#defineDEFAULT_ROLE_READ_ALL_STATS 3375
109109
DATA(insertOID=3377 ("pg_stat_scan_tables"ftfffff-1_null__null_));
110110
#defineDEFAULT_ROLE_STAT_SCAN_TABLES3377
111+
DATA(insertOID=4569 ("pg_read_server_files"ftfffff-1_null__null_));
112+
#defineDEFAULT_ROLE_READ_SERVER_FILES4569
113+
DATA(insertOID=4570 ("pg_write_server_files"ftfffff-1_null__null_));
114+
#defineDEFAULT_ROLE_WRITE_SERVER_FILES4570
115+
DATA(insertOID=4571 ("pg_execute_server_program"ftfffff-1_null__null_));
116+
#defineDEFAULT_ROLE_EXECUTE_SERVER_PROGRAM4571
111117
DATA(insertOID=4200 ("pg_signal_backend"ftfffff-1_null__null_));
112118
#defineDEFAULT_ROLE_SIGNAL_BACKENDID4200
113119

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp