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.
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
adminuser, or the current owner of the database and a member of the new owner role. The user must also have theCREATEDBprivilege. - To change the owner of a table, the user must be an
adminuser, or the current owner of the table and a member of the new owner role. The new owner role must also have theCREATEprivilege 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:
$ 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.
> 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:
> 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)
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.
> ALTER TABLE promo_codes OWNER TO max;
To verify that the owner is now max, query the pg_catalog.pg_tables table:
> SELECT tableowner FROM pg_catalog.pg_tables WHERE tablename = 'promo_codes';
tableowner
--------------
max
(1 row)
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
Was this helpful?
