暂无图片
暂无图片
2
暂无图片
暂无图片
暂无图片

PostgreSQL运维—REASSIGN OWNED

原创 李先生 2022-02-13
1109

PostgreSQL运维—REASSIGN OWNED


REASSIGN OWNED — change the ownership of database objects owned by a database role

语法

REASSIGN OWNED BY { old_role | CURRENT_USER | SESSION_USER } [, ...]
               TO { new_role | CURRENT_USER | SESSION_USER }

描述

REASSIGN OWNED instructs the system to change the ownership of database objects owned by any of the old_roles to new_role.

postgres=# create user u1; CREATE ROLE postgres=# create user u2; CREATE ROLE postgres=# create table t1 (id int); CREATE TABLE postgres=# \c - u1 You are now connected to database "postgres" as user "u1". postgres=> select * from t1; ERROR: permission denied for table t1 postgres=# \c - u2 You are now connected to database "postgres" as user "u2". postgres=> select * from t1; ERROR: permission denied for table t1 postgres=> \c - postgres You are now connected to database "postgres" as user "postgres". 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=# reassign owned by u1 to u2; REASSIGN OWNED postgres=# \c - u2 You are now connected to database "postgres" as user "u2". postgres=> select * from t1; id ---- (0 rows) postgres=>

参数

  • old_role

    The name of a role. The ownership of all the objects within the current database, and of all shared objects (databases, tablespaces), owned by this role will be reassigned to new_role.

  • new_role

    The name of the role that will be made the new owner of the affected objects.

注解

REASSIGN OWNED is often used to prepare for the removal of one or more roles. Because REASSIGN OWNED does not affect objects within other databases, it is usually necessary to execute this command in each database that contains objects owned by a role that is to be removed.

REASSIGN OWNED requires membership on both the source role(s) and the target role.

The DROP OWNEDcommand is an alternative that simply drops all the database objects owned by one or more roles.

The REASSIGN OWNED command does not affect any privileges granted to the old_roles on objects that are not owned by them. Likewise, it does not affect default privileges created with ALTER DEFAULT PRIVILEGES. Use DROP OWNED to revoke such privileges.

mydb=> \c mydb u1 You are now connected to database "mydb" as user "u1". mydb=> create table t1(id int); CREATE TABLE mydb=> \c mydb postgres You are now connected to database "mydb" as user "postgres". mydb=# create table t2(id int); CREATE TABLE mydb=# grant select on t2 to u1; GRANT mydb=# \dt List of relations Schema | Name | Type | Owner --------+------+-------+---------- public | t1 | table | u1 public | t2 | table | postgres (2 rows) mydb=# \dp t2 Access privileges Schema | Name | Type | Access privileges | Column privileges | Policies --------+------+-------+---------------------------+-------------------+---------- public | t2 | table | postgres=arwdDxt/postgres+| | | | | u1=r/postgres | | (1 row) mydb=# reassign owned by u1 to u2; REASSIGN OWNED mydb=# \dt List of relations Schema | Name | Type | Owner --------+------+-------+---------- public | t1 | table | u2 public | t2 | table | postgres (2 rows) mydb=# \dp t2 Access privileges Schema | Name | Type | Access privileges | Column privileges | Policies --------+------+-------+---------------------------+-------------------+---------- public | t2 | table | postgres=arwdDxt/postgres+| | | | | u1=r/postgres | | (1 row) mydb=# drop user u1; ERROR: role "u1" cannot be dropped because some objects depend on it DETAIL: privileges for table t2 mydb=# drop owned by u1; DROP OWNED mydb=# \dp t2 Access privileges Schema | Name | Type | Access privileges | Column privileges | Policies --------+------+-------+---------------------------+-------------------+---------- public | t2 | table | postgres=arwdDxt/postgres | | (1 row) mydb=#

ALTER DEFAULT PRIVILEGES

mydb=# \c mydb u1 You are now connected to database "mydb" as user "u1". mydb=> create table t1(id int); CREATE TABLE mydb=> \c mydb postgres You are now connected to database "mydb" as user "postgres". mydb=# create table t2(id int); CREATE TABLE mydb=# grant select on t2 to u1; GRANT mydb=# alter default privileges in schema public grant select on tables TO u1; ALTER DEFAULT PRIVILEGES mydb=# create table t3(id int); CREATE TABLE mydb=# \dt List of relations Schema | Name | Type | Owner --------+------+-------+---------- public | t1 | table | u1 public | t2 | table | postgres public | t3 | table | postgres (3 rows) mydb=# \dp t2 Access privileges Schema | Name | Type | Access privileges | Column privileges | Policies --------+------+-------+---------------------------+-------------------+---------- public | t2 | table | postgres=arwdDxt/postgres+| | | | | u1=r/postgres | | (1 row) mydb=# \dp t3 Access privileges Schema | Name | Type | Access privileges | Column privileges | Policies --------+------+-------+---------------------------+-------------------+---------- public | t3 | table | postgres=arwdDxt/postgres+| | | | | u1=r/postgres | | (1 row) mydb=# reassign owned by u1 to u2;; REASSIGN OWNED mydb=# \dt List of relations Schema | Name | Type | Owner --------+------+-------+---------- public | t1 | table | u2 public | t2 | table | postgres public | t3 | table | postgres (3 rows) mydb=# \dp t2 Access privileges Schema | Name | Type | Access privileges | Column privileges | Policies --------+------+-------+---------------------------+-------------------+---------- public | t2 | table | postgres=arwdDxt/postgres+| | | | | u1=r/postgres | | (1 row) mydb=# \dp t3 Access privileges Schema | Name | Type | Access privileges | Column privileges | Policies --------+------+-------+---------------------------+-------------------+---------- public | t3 | table | postgres=arwdDxt/postgres+| | | | | u1=r/postgres | | (1 row) mydb=#

兼容性

The REASSIGN OWNED command is a PostgreSQL extension.

「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论