Skip to content

Commit fbe19ee

Browse files
sfrostsfrost
committed
ALTER TABLESPACE ... MOVE ... OWNED BY
Add the ability to specify the objects to move by who those objects are owned by (as relowner) and change ALL to mean ALL objects. This makes the command always operate against a well-defined set of objects and not have the objects-to-be-moved based on the role of the user running the command. Per discussion with Simon and Tom.
1 parent 3ee74df commit fbe19ee

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 migrate all of the objects in the current database which
38-
are owned by the user out of a given tablespace.
37+
a tablespace or to migrate objects in the current database between
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
HeapTuple tuple;
995996
Oid orig_tablespaceoid;
996997
Oid new_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_type check_password_hook = NULL;
5050

51-
static List *roleNamesToIds(List *memberNames);
5251
static void AddRoleMems(const char *rolename, Oid roleid,
5352
List *memberNames, List *memberIds,
5453
Oid grantorId, bool admin_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-
static List *
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
const AlterTableSpaceMoveStmt *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
| ALTER TABLESPACE name MOVE TABLES TO name opt_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
| ALTER TABLESPACE name MOVE INDEXES TO name opt_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
| ALTER TABLESPACE name MOVE MATERIALIZED VIEWS TO name opt_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+
| ALTER TABLESPACE name MOVE ALL OWNED BY role_list TO name opt_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+
| ALTER TABLESPACE name MOVE TABLES OWNED BY role_list TO name opt_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+
| ALTER TABLESPACE name MOVE INDEXES OWNED BY role_list TO name opt_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+
| ALTER TABLESPACE name MOVE MATERIALIZED VIEWS OWNED BY role_list TO name opt_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
| ALTER TABLESPACE name SET reloptions

‎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
extern Oid RenameRole(const char *oldname, const char *newname);
3131
extern void DropOwnedObjects(DropOwnedStmt *stmt);
3232
extern void ReassignOwnedObjects(ReassignOwnedStmt *stmt);
33+
extern List *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
NodeTag type;
16931693
char *orig_tablespacename;
1694+
ObjectType objtype; /* set to -1 if move_all is true */
1695+
bool move_all; /* move all, or just objtype objects? */
1696+
List *roles; /* List of roles to move objects of */
16941697
char *new_tablespacename;
1695-
ObjectType objtype;
16961698
bool nowait;
1697-
bool move_all;
16981699
} AlterTableSpaceMoveStmt;
16991700

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

0 commit comments

Comments
 (0)