PostgreSQL运维—drop owned

DROP OWNED — 移除一个数据库角色拥有的数据库对象

语法

DROP OWNED BY { name | CURRENT_USER | SESSION_USER } [, ...] [ CASCADE | RESTRICT ]

描述

DROP OWNED删除当前数据库中被指定角色之一拥有的所有对象。任何已被授予给给定角色在当前数据库中对象上或者在共享对象（数据库、表空间）上的特权也将会被收回。

postgres=# create user u1;
CREATE ROLE
postgres=# drop table t1;
DROP TABLE
postgres=# create table t1(id int);
CREATE TABLE
postgres=# grant select on t1 to u1;
GRANT
postgres=# \c - u1
You are now connected to database "postgres" as user "u1".
postgres=> select * from t1;
 id 
----
(0 rows)

postgres=> \c - postgres
You are now connected to database "postgres" as user "postgres".
postgres=# drop owned by u1 ;
DROP OWNED
postgres=# \c - u1
You are now connected to database "postgres" as user "u1".
postgres=> select * from t1;
ERROR:  permission denied for table t1
postgres=> 

参数

• name

  其对象将被删除并且其特权将被收回的角色的名称。

• CASCADE

  自动删除依赖于受影响对象的对象，然后删除所有依赖于那些对象的对象。

• RESTRICT

  如果有任何其他数据库对象依赖于一个受影响的对象， 则拒绝删除一个角色所拥有的对象。这是默认值。

注解

DROP OWNED常常被用来为移除一个 或者多个角色做准备。因为DROP OWNED 只影响当前数据库中的对象，通常需要在包含将被移除角色所拥有的对象的每一个数据库中都执行这个命令。

使用CASCADE选项可能导致这个命令递归去删除由其他用户所拥有的对象。

postgres=# create user u1;
CREATE ROLE
postgres=# alter user u1 createdb;
ALTER ROLE
postgres=# \c postgres u1
You are now connected to database "postgres" as user "u1".
postgres=> create database mydb;
ERROR:  database "mydb" already exists
postgres=> create database mydb1;
ERROR:  database "mydb1" already exists
postgres=> 
postgres=> create database mydb;
CREATE DATABASE
postgres=>  \c mydb u1
You are now connected to database "mydb" as user "u1".
mydb=> create schema myschema;
CREATE SCHEMA
mydb=> grant create on schema myschema to u2;
GRANT
mydb=> \c mydb u2
You are now connected to database "mydb" as user "u2".
mydb=> create table myschema.t1(id int);
CREATE TABLE
mydb=> \dn
   List of schemas
   Name   |  Owner   
----------+----------
 myschema | u1
 public   | postgres
(2 rows)

mydb=> \dt+ myschema.t1 
                                   List of relations
  Schema  | Name | Type  | Owner | Persistence | Access method |  Size   | Description 
----------+------+-------+-------+-------------+---------------+---------+-------------
 myschema | t1   | table | u2    | permanent   | heap          | 0 bytes | 
(1 row)

mydb=> \c mydb u1
You are now connected to database "mydb" as user "u1".
mydb=> drop owned by u1;
ERROR:  cannot drop schema myschema because other objects depend on it
DETAIL:  table myschema.t1 depends on schema myschema
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
mydb=> drop owned by u1 cascade;
NOTICE:  drop cascades to table myschema.t1
DROP OWNED
mydb=> 

REASSIGN OWNED命令是另一种选择，它可以把一个或多个角色所拥有的所有数据库对象重新授予给其他角色。不过，REASSIGN OWNED不处理其他对象的特权。

角色所拥有的数据库、表空间将不会被移除。

postgres=# create user u1;
CREATE ROLE
postgres=# create user u2;
CREATE ROLE
postgres=# create table t1 (id int);
CREATE TABLE
postgres=# grant select on t1 to u1;
GRANT
postgres=# drop user u1;
ERROR:  role "u1" cannot be dropped because some objects depend on it
DETAIL:  privileges for table t1
postgres=# reassign owned by u1 to u2;
REASSIGN OWNED
postgres=# drop owned by u1;
DROP OWNED
postgres=# drop user u1;
DROP ROLE
mydb1=# 

兼容性

DROP OWNED命令是一个 PostgreSQL扩展。