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

Commitfbe19ee

Browse files
committed
ALTER TABLESPACE ... MOVE ... OWNED BY
Add the ability to specify the objects to move by who those objects areowned by (as relowner) and change ALL to mean ALL objects. Thismakes the command always operate against a well-defined set of objectsand not have the objects-to-be-moved based on the role of the userrunning the command.Per discussion with Simon and Tom.
1 parent3ee74df commitfbe19ee

File tree

8 files changed

+115
-27
lines changed

8 files changed

+115
-27
lines changed

‎doc/src/sgml/ref/alter_tablespace.sgml

Lines changed: 25 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -25,7 +25,7 @@ ALTER TABLESPACE <replaceable>name</replaceable> RENAME TO <replaceable>new_name
2525
ALTER TABLESPACE <replaceable>name</replaceable> OWNER TO <replaceable>new_owner</replaceable>
2626
ALTER TABLESPACE <replaceable>name</replaceable> SET ( <replaceable class="PARAMETER">tablespace_option</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] )
2727
ALTER TABLESPACE <replaceable>name</replaceable> RESET ( <replaceable class="PARAMETER">tablespace_option</replaceable> [, ... ] )
28-
ALTER TABLESPACE <replaceable>name</replaceable> MOVE { ALL | TABLES | INDEXES | MATERIALIZED VIEWS } TO <replaceable>new_tablespace</replaceable> [ NOWAIT ]
28+
ALTER TABLESPACE <replaceable>name</replaceable> MOVE { ALL | TABLES | INDEXES | MATERIALIZED VIEWS }[ OWNED BY <replaceable class="PARAMETER">role_name</replaceable> [, ...] ]TO <replaceable>new_tablespace</replaceable> [ NOWAIT ]
2929
</synopsis>
3030
</refsynopsisdiv>
3131

@@ -34,8 +34,8 @@ ALTER TABLESPACE <replaceable>name</replaceable> MOVE { ALL | TABLES | INDEXES |
3434

3535
<para>
3636
<command>ALTER TABLESPACE</command> can be used to change the definition of
37-
a tablespace or to migrateall of theobjects in the current databasewhich
38-
are owned by the user out of a given tablespace.
37+
a tablespace or to migrate objects in the current databasebetween
38+
tablespaces.
3939
</para>
4040

4141
<para>
@@ -44,13 +44,19 @@ ALTER TABLESPACE <replaceable>name</replaceable> MOVE { ALL | TABLES | INDEXES |
4444
owning role.
4545
(Note that superusers have these privileges automatically.)
4646

47-
Users may use ALTER TABLESPACE ... MOVE to move either ALL of their objects,
48-
or just TABLES, INDEXES, or MATERIALIZED VIEWS, but they must have CREATE
49-
rights on the new tablespace and only objects, directly or indirectly, owned
50-
by the user will be moved. Note that the superuser is considered an owner
51-
of all objects and therefore an ALTER TABLESPACE ... MOVE ALL issued by the
52-
superuser will move all objects in the current database which are in the
53-
tablespace.
47+
Users may use ALTER TABLESPACE ... MOVE to move objects between tablespaces.
48+
ALL will move all tables, indexes and materialized views while specifying
49+
TABLES will move only tables (but not their indexes), INDEXES will only move
50+
indexes (including those underneath materialized views, but not tables) and
51+
MATERIALIZED VIEWS will only move the table relation of the materialized
52+
view (but no indexes associated with it). Users may also specify a list of
53+
roles whose objects are to be moved using OWNED BY.
54+
55+
Users must have CREATE rights on the new tablespace and be considered an
56+
owner (either directly or indirectly) on all objects to be moved. Note that
57+
the superuser is considered an owner of all objects and therefore an
58+
ALTER TABLESPACE ... MOVE ALL issued by the superuser will move all objects
59+
in the current database which are in the tablespace.
5460

5561
All objects to be moved will be locked immediately by the command. The
5662
NOWAIT option, if specified, will cause the command to fail if it is unable
@@ -115,6 +121,15 @@ ALTER TABLESPACE <replaceable>name</replaceable> MOVE { ALL | TABLES | INDEXES |
115121
</listitem>
116122
</varlistentry>
117123

124+
<varlistentry>
125+
<term><replaceable class="parameter">role_name</replaceable></term>
126+
<listitem>
127+
<para>
128+
Role(s) whose objects are to be moved.
129+
</para>
130+
</listitem>
131+
</varlistentry>
132+
118133
<varlistentry>
119134
<term><replaceable class="parameter">new_tablespace</replaceable></term>
120135
<listitem>

‎src/backend/commands/tablespace.c

Lines changed: 21 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -67,6 +67,7 @@
6767
#include"commands/seclabel.h"
6868
#include"commands/tablecmds.h"
6969
#include"commands/tablespace.h"
70+
#include"commands/user.h"
7071
#include"common/relpath.h"
7172
#include"miscadmin.h"
7273
#include"postmaster/bgwriter.h"
@@ -994,6 +995,7 @@ AlterTableSpaceMove(AlterTableSpaceMoveStmt *stmt)
994995
HeapTupletuple;
995996
Oidorig_tablespaceoid;
996997
Oidnew_tablespaceoid;
998+
List*role_oids=roleNamesToIds(stmt->roles);
997999

9981000
/* Ensure we were not asked to move something we can't */
9991001
if (!stmt->move_all&&stmt->objtype!=OBJECT_TABLE&&
@@ -1075,14 +1077,10 @@ AlterTableSpaceMove(AlterTableSpaceMoveStmt *stmt)
10751077
relForm->relnamespace==PG_TOAST_NAMESPACE)
10761078
continue;
10771079

1078-
/*
1079-
* Only move objects that we are considered an owner of and only
1080-
* objects which can actually have a tablespace.
1081-
*/
1082-
if (!pg_class_ownercheck(relOid,GetUserId())||
1083-
(relForm->relkind!=RELKIND_RELATION&&
1084-
relForm->relkind!=RELKIND_INDEX&&
1085-
relForm->relkind!=RELKIND_MATVIEW))
1080+
/* Only consider objects which live in tablespaces */
1081+
if (relForm->relkind!=RELKIND_RELATION&&
1082+
relForm->relkind!=RELKIND_INDEX&&
1083+
relForm->relkind!=RELKIND_MATVIEW)
10861084
continue;
10871085

10881086
/* Check if we were asked to only move a certain type of object */
@@ -1095,6 +1093,21 @@ AlterTableSpaceMove(AlterTableSpaceMoveStmt *stmt)
10951093
relForm->relkind!=RELKIND_MATVIEW)))
10961094
continue;
10971095

1096+
/* Check if we are only moving objects owned by certain roles */
1097+
if (role_oids!=NIL&& !list_member_oid(role_oids,relForm->relowner))
1098+
continue;
1099+
1100+
/*
1101+
* Handle permissions-checking here since we are locking the tables
1102+
* and also to avoid doing a bunch of work only to fail part-way.
1103+
* Note that permissions will also be checked by AlterTableInternal().
1104+
*
1105+
* Caller must be considered an owner on the table to move it.
1106+
*/
1107+
if (!pg_class_ownercheck(relOid,GetUserId()))
1108+
aclcheck_error(ACLCHECK_NOT_OWNER,ACL_KIND_CLASS,
1109+
NameStr(relForm->relname));
1110+
10981111
if (stmt->nowait&&
10991112
!ConditionalLockRelationOid(relOid,AccessExclusiveLock))
11001113
ereport(ERROR,

‎src/backend/commands/user.c

Lines changed: 1 addition & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -48,7 +48,6 @@ extern bool Password_encryption;
4848
/* Hook to check passwords in CreateRole() and AlterRole() */
4949
check_password_hook_typecheck_password_hook=NULL;
5050

51-
staticList*roleNamesToIds(List*memberNames);
5251
staticvoidAddRoleMems(constchar*rolename,Oidroleid,
5352
List*memberNames,List*memberIds,
5453
OidgrantorId,booladmin_opt);
@@ -1302,7 +1301,7 @@ ReassignOwnedObjects(ReassignOwnedStmt *stmt)
13021301
* Given a list of role names (as String nodes), generate a list of role OIDs
13031302
* in the same order.
13041303
*/
1305-
staticList*
1304+
List*
13061305
roleNamesToIds(List*memberNames)
13071306
{
13081307
List*result=NIL;

‎src/backend/nodes/copyfuncs.c

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3404,6 +3404,9 @@ _copyAlterTableSpaceMoveStmt(const AlterTableSpaceMoveStmt *from)
34043404
AlterTableSpaceMoveStmt*newnode=makeNode(AlterTableSpaceMoveStmt);
34053405

34063406
COPY_STRING_FIELD(orig_tablespacename);
3407+
COPY_SCALAR_FIELD(objtype);
3408+
COPY_SCALAR_FIELD(move_all);
3409+
COPY_NODE_FIELD(roles);
34073410
COPY_STRING_FIELD(new_tablespacename);
34083411
COPY_SCALAR_FIELD(nowait);
34093412

‎src/backend/nodes/equalfuncs.c

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1640,6 +1640,9 @@ _equalAlterTableSpaceMoveStmt(const AlterTableSpaceMoveStmt *a,
16401640
constAlterTableSpaceMoveStmt*b)
16411641
{
16421642
COMPARE_STRING_FIELD(orig_tablespacename);
1643+
COMPARE_SCALAR_FIELD(objtype);
1644+
COMPARE_SCALAR_FIELD(move_all);
1645+
COMPARE_NODE_FIELD(roles);
16431646
COMPARE_STRING_FIELD(new_tablespacename);
16441647
COMPARE_SCALAR_FIELD(nowait);
16451648

‎src/backend/parser/gram.y

Lines changed: 58 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -7325,42 +7325,95 @@ RenameStmt: ALTER AGGREGATE func_name aggr_args RENAME TO name
73257325
AlterTableSpaceMoveStmt *n =
73267326
makeNode(AlterTableSpaceMoveStmt);
73277327
n->orig_tablespacename =$3;
7328+
n->objtype = -1;
7329+
n->move_all =true;
7330+
n->roles = NIL;
73287331
n->new_tablespacename =$7;
73297332
n->nowait =$8;
7330-
n->move_all =true;
73317333
$$ = (Node *)n;
73327334
}
73337335
|ALTERTABLESPACEnameMOVETABLESTOnameopt_nowait
73347336
{
73357337
AlterTableSpaceMoveStmt *n =
73367338
makeNode(AlterTableSpaceMoveStmt);
73377339
n->orig_tablespacename =$3;
7338-
n->new_tablespacename =$7;
7339-
n->nowait =$8;
73407340
n->objtype = OBJECT_TABLE;
73417341
n->move_all =false;
7342+
n->roles = NIL;
7343+
n->new_tablespacename =$7;
7344+
n->nowait =$8;
73427345
$$ = (Node *)n;
73437346
}
73447347
|ALTERTABLESPACEnameMOVEINDEXESTOnameopt_nowait
73457348
{
73467349
AlterTableSpaceMoveStmt *n =
73477350
makeNode(AlterTableSpaceMoveStmt);
73487351
n->orig_tablespacename =$3;
7349-
n->new_tablespacename =$7;
7350-
n->nowait =$8;
73517352
n->objtype = OBJECT_INDEX;
73527353
n->move_all =false;
7354+
n->roles = NIL;
7355+
n->new_tablespacename =$7;
7356+
n->nowait =$8;
73537357
$$ = (Node *)n;
73547358
}
73557359
|ALTERTABLESPACEnameMOVEMATERIALIZEDVIEWSTOnameopt_nowait
73567360
{
73577361
AlterTableSpaceMoveStmt *n =
73587362
makeNode(AlterTableSpaceMoveStmt);
73597363
n->orig_tablespacename =$3;
7364+
n->objtype = OBJECT_MATVIEW;
7365+
n->move_all =false;
7366+
n->roles = NIL;
73607367
n->new_tablespacename =$8;
73617368
n->nowait =$9;
7369+
$$ = (Node *)n;
7370+
}
7371+
|ALTERTABLESPACEnameMOVEALLOWNEDBYrole_listTOnameopt_nowait
7372+
{
7373+
AlterTableSpaceMoveStmt *n =
7374+
makeNode(AlterTableSpaceMoveStmt);
7375+
n->orig_tablespacename =$3;
7376+
n->objtype = -1;
7377+
n->move_all =true;
7378+
n->roles =$8;
7379+
n->new_tablespacename =$10;
7380+
n->nowait =$11;
7381+
$$ = (Node *)n;
7382+
}
7383+
|ALTERTABLESPACEnameMOVETABLESOWNEDBYrole_listTOnameopt_nowait
7384+
{
7385+
AlterTableSpaceMoveStmt *n =
7386+
makeNode(AlterTableSpaceMoveStmt);
7387+
n->orig_tablespacename =$3;
7388+
n->objtype = OBJECT_TABLE;
7389+
n->move_all =false;
7390+
n->roles =$8;
7391+
n->new_tablespacename =$10;
7392+
n->nowait =$11;
7393+
$$ = (Node *)n;
7394+
}
7395+
|ALTERTABLESPACEnameMOVEINDEXESOWNEDBYrole_listTOnameopt_nowait
7396+
{
7397+
AlterTableSpaceMoveStmt *n =
7398+
makeNode(AlterTableSpaceMoveStmt);
7399+
n->orig_tablespacename =$3;
7400+
n->objtype = OBJECT_INDEX;
7401+
n->move_all =false;
7402+
n->roles =$8;
7403+
n->new_tablespacename =$10;
7404+
n->nowait =$11;
7405+
$$ = (Node *)n;
7406+
}
7407+
|ALTERTABLESPACEnameMOVEMATERIALIZEDVIEWSOWNEDBYrole_listTOnameopt_nowait
7408+
{
7409+
AlterTableSpaceMoveStmt *n =
7410+
makeNode(AlterTableSpaceMoveStmt);
7411+
n->orig_tablespacename =$3;
73627412
n->objtype = OBJECT_MATVIEW;
73637413
n->move_all =false;
7414+
n->roles =$9;
7415+
n->new_tablespacename =$11;
7416+
n->nowait =$12;
73647417
$$ = (Node *)n;
73657418
}
73667419
|ALTERTABLESPACEnameSETreloptions

‎src/include/commands/user.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -30,5 +30,6 @@ extern void GrantRole(GrantRoleStmt *stmt);
3030
externOidRenameRole(constchar*oldname,constchar*newname);
3131
externvoidDropOwnedObjects(DropOwnedStmt*stmt);
3232
externvoidReassignOwnedObjects(ReassignOwnedStmt*stmt);
33+
externList*roleNamesToIds(List*memberNames);
3334

3435
#endif/* USER_H */

‎src/include/nodes/parsenodes.h

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1691,10 +1691,11 @@ typedef struct AlterTableSpaceMoveStmt
16911691
{
16921692
NodeTagtype;
16931693
char*orig_tablespacename;
1694+
ObjectTypeobjtype;/* set to -1 if move_all is true */
1695+
boolmove_all;/* move all, or just objtype objects? */
1696+
List*roles;/* List of roles to move objects of */
16941697
char*new_tablespacename;
1695-
ObjectTypeobjtype;
16961698
boolnowait;
1697-
boolmove_all;
16981699
}AlterTableSpaceMoveStmt;
16991700

17001701
/* ----------------------

0 commit comments

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