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擴展。