New in v20.2: OWNER TO is a subcommand of ALTER DATABASE, ALTER TABLE, ALTER SCHEMA, and ALTER TYPE, and is used to change the owner of an object in a cluster.

Note:

This page documents ALTER DATABASE ... OWNER TO and ALTER TABLE ... OWNER TO. For details on the ALTER SCHEMA ... OWNER TO and ALTER TYPE ... OWNER TO, see the ALTER SCHEMA and ALTER TYPE pages.

Required privileges

  • To change the owner of a database, the user must be an admin user, or the current owner of the database and a member of the new owner role. The user must also have the CREATEDB privilege.
  • To change the owner of a table, the user must be an admin user, or the current owner of the table and a member of the new owner role. The new owner role must also have the CREATE privilege on the schema to which the table belongs.

Syntax

Databases

ALTER DATABASE <name> OWNER TO <newowner>

Tables

ALTER TABLE <name> OWNER TO <newowner>

Parameters

Parameter Description
name The name of the table or database.
newowner The name of the new owner.

Examples

Setup

The following examples use MovR, a fictional vehicle-sharing application, to demonstrate CockroachDB SQL statements. For more information about the MovR example application and dataset, see MovR: A Global Vehicle-sharing App.

To follow along, run cockroach demo to start a temporary, in-memory cluster with the movr dataset preloaded:

copy
icon/buttons/copy
$ cockroach demo

Change a database's owner

Suppose that the current owner of the movr database is root and you want to change the owner to a new user named max.

copy
icon/buttons/copy
> ALTER DATABASE movr OWNER TO max;

To verify that the owner is now max, query the pg_catalog.pg_database and pg_catalog.pg_roles tables:

copy
icon/buttons/copy
> SELECT rolname FROM pg_catalog.pg_database d JOIN pg_catalog.pg_roles r ON d.datdba = r.oid WHERE datname = 'movr';
  rolname
-----------
  max
(1 row)
Note:

If the user running the command is not an admin user, they must own the database and be a member of the new owning role. They must also have the CREATEDB privilege.

Change a table's owner

Suppose that the current owner of the rides table is root and you want to change the owner to a new user named max.

copy
icon/buttons/copy
> ALTER TABLE promo_codes OWNER TO max;

To verify that the owner is now max, query the pg_catalog.pg_tables table:

copy
icon/buttons/copy
> SELECT tableowner FROM pg_catalog.pg_tables WHERE tablename = 'promo_codes';
  tableowner
--------------
  max
(1 row)
Note:

If the user running the command is not an admin user, they must own the table and be a member of the new owning role. Also, the new owner role must also have the CREATE privilege on the schema to which the table belongs.

See also



YesYes NoNo