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

Commit64d4da5

Browse files
committed
For foreign keys, check REFERENCES privilege only on the referenced table.
We were requiring that the user have REFERENCES permission on both thereferenced and referencing tables --- but this doesn't seem to have anysupport in the SQL standard, which says only that you need REFERENCESpermission on the referenced table. And ALTER TABLE ADD FOREIGN KEY hasalready checked that you own the referencing table, so the check couldonly fail if a table owner has revoked his own REFERENCES permission.Moreover, the symmetric interpretation of this permission is unintuitiveand confusing, as per complaint from Paul Jungwirth. So let's drop thereferencing-side check.In passing, do a bit of wordsmithing on the GRANT reference page so thatall the privilege types are described in similar fashion.Discussion:https://postgr.es/m/8940.1490906755@sss.pgh.pa.us
1 parent8f18a88 commit64d4da5

File tree

3 files changed

+20
-15
lines changed

3 files changed

+20
-15
lines changed

‎doc/src/sgml/ref/create_table.sgml

Lines changed: 5 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -750,9 +750,11 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
750750
class="parameter">refcolumn</replaceable> list is omitted, the
751751
primary key of the <replaceable class="parameter">reftable</replaceable>
752752
is used. The referenced columns must be the columns of a non-deferrable
753-
unique or primary key constraint in the referenced table. Note that
754-
foreign key constraints cannot be defined between temporary tables and
755-
permanent tables.
753+
unique or primary key constraint in the referenced table. The user
754+
must have <literal>REFERENCES</> permission on the referenced table
755+
(either the whole table, or the specific referenced columns).
756+
Note that foreign key constraints cannot be defined between temporary
757+
tables and permanent tables.
756758
</para>
757759

758760
<para>

‎doc/src/sgml/ref/grant.sgml

Lines changed: 9 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -257,10 +257,9 @@ GRANT <replaceable class="PARAMETER">role_name</replaceable> [, ...] TO <replace
257257
<term>REFERENCES</term>
258258
<listitem>
259259
<para>
260-
To create a foreign key constraint, it is
261-
necessary to have this privilege on both the referencing and
262-
referenced columns. The privilege may be granted for all columns
263-
of a table, or just specific columns.
260+
Allows creation of a foreign key constraint referencing the specified
261+
table, or specified column(s) of the table. (See the
262+
<xref linkend="sql-createtable"> statement.)
264263
</para>
265264
</listitem>
266265
</varlistentry>
@@ -351,7 +350,7 @@ GRANT <replaceable class="PARAMETER">role_name</replaceable> [, ...] TO <replace
351350
<function>currval</function> and <function>nextval</function> functions.
352351
</para>
353352
<para>
354-
For types and domains, this privilegeallow the use of the type or
353+
For types and domains, this privilegeallows the use of the type or
355354
domain in the creation of tables, functions, and other schema objects.
356355
(Note that it does not control general <quote>usage</quote> of the type,
357356
such as values of the type appearing in queries. It only prevents
@@ -360,13 +359,13 @@ GRANT <replaceable class="PARAMETER">role_name</replaceable> [, ...] TO <replace
360359
which could prevent the owner from changing the type later.)
361360
</para>
362361
<para>
363-
For foreign-data wrappers, this privilegeenables the grantee
364-
to createnew servers usingthat foreign-data wrapper.
362+
For foreign-data wrappers, this privilegeallows creation of
363+
new servers usingthe foreign-data wrapper.
365364
</para>
366365
<para>
367-
For servers, this privilegeenables the grantee to create foreign
368-
tables usingthe server, and also to create, alter, or drop their own
369-
user's user mappings associated with that server.
366+
For servers, this privilegeallows creation of foreign tables using
367+
the server. Grantees may also create, alter, or drop their own
368+
user mappings associated with that server.
370369
</para>
371370
</listitem>
372371
</varlistentry>

‎src/backend/commands/tablecmds.c

Lines changed: 6 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -6817,7 +6817,6 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
68176817
* Now we can check permissions.
68186818
*/
68196819
checkFkeyPermissions(pkrel,pkattnum,numpks);
6820-
checkFkeyPermissions(rel,fkattnum,numfks);
68216820

68226821
/*
68236822
* Look up the equality operators to use in the constraint.
@@ -7745,7 +7744,12 @@ findFkeyCast(Oid targetTypeId, Oid sourceTypeId, Oid *funcid)
77457744
returnret;
77467745
}
77477746

7748-
/* Permissions checks for ADD FOREIGN KEY */
7747+
/*
7748+
* Permissions checks on the referenced table for ADD FOREIGN KEY
7749+
*
7750+
* Note: we have already checked that the user owns the referencing table,
7751+
* else we'd have failed much earlier; no additional checks are needed for it.
7752+
*/
77497753
staticvoid
77507754
checkFkeyPermissions(Relationrel,int16*attnums,intnatts)
77517755
{

0 commit comments

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