| Server IP : 103.119.228.120 / Your IP : 3.147.86.143 Web Server : Apache System : Linux v8.techscape8.com 3.10.0-1160.119.1.el7.tuxcare.els2.x86_64 #1 SMP Mon Jul 15 12:09:18 UTC 2024 x86_64 User : nobody ( 99) PHP Version : 5.6.40 Disable Function : shell_exec,symlink,system,exec,proc_get_status,proc_nice,proc_terminate,define_syslog_variables,syslog,openlog,closelog,escapeshellcmd,passthru,ocinum cols,ini_alter,leak,listen,chgrp,apache_note,apache_setenv,debugger_on,debugger_off,ftp_exec,dl,dll,myshellexec,proc_open,socket_bind,proc_close,escapeshellarg,parse_ini_filepopen,fpassthru,exec,passthru,escapeshellarg,escapeshellcmd,proc_close,proc_open,ini_alter,popen,show_source,proc_nice,proc_terminate,proc_get_status,proc_close,pfsockopen,leak,apache_child_terminate,posix_kill,posix_mkfifo,posix_setpgid,posix_setsid,posix_setuid,dl,symlink,shell_exec,system,dl,passthru,escapeshellarg,escapeshellcmd,myshellexec,c99_buff_prepare,c99_sess_put,fpassthru,getdisfunc,fx29exec,fx29exec2,is_windows,disp_freespace,fx29sh_getupdate,fx29_buff_prepare,fx29_sess_put,fx29shexit,fx29fsearch,fx29ftpbrutecheck,fx29sh_tools,fx29sh_about,milw0rm,imagez,sh_name,myshellexec,checkproxyhost,dosyayicek,c99_buff_prepare,c99_sess_put,c99getsource,c99sh_getupdate,c99fsearch,c99shexit,view_perms,posix_getpwuid,posix_getgrgid,posix_kill,parse_perms,parsesort,view_perms_color,set_encoder_input,ls_setcheckboxall,ls_reverse_all,rsg_read,rsg_glob,selfURL,dispsecinfo,unix2DosTime,addFile,system,get_users,view_size,DirFiles,DirFilesWide,DirPrintHTMLHeaders,GetFilesTotal,GetTitles,GetTimeTotal,GetMatchesCount,GetFileMatchesCount,GetResultFiles,fs_copy_dir,fs_copy_obj,fs_move_dir,fs_move_obj,fs_rmdir,SearchText,getmicrotime MySQL : ON | cURL : ON | WGET : ON | Perl : ON | Python : ON | Sudo : ON | Pkexec : ON Directory : /usr/share/doc/postgresql-9.2.24/html/ |
Upload File : |
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <HTML ><HEAD ><TITLE >Dropping Roles</TITLE ><META NAME="GENERATOR" CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK REV="MADE" HREF="mailto:pgsql-docs@postgresql.org"><LINK REL="HOME" TITLE="PostgreSQL 9.2.24 Documentation" HREF="index.html"><LINK REL="UP" TITLE="Database Roles" HREF="user-manag.html"><LINK REL="PREVIOUS" TITLE="Role Membership" HREF="role-membership.html"><LINK REL="NEXT" TITLE="Function and Trigger Security" HREF="perm-functions.html"><LINK REL="STYLESHEET" TYPE="text/css" HREF="stylesheet.css"><META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=ISO-8859-1"><META NAME="creation" CONTENT="2017-11-06T22:43:11"></HEAD ><BODY CLASS="SECT1" ><DIV CLASS="NAVHEADER" ><TABLE SUMMARY="Header navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TH COLSPAN="5" ALIGN="center" VALIGN="bottom" ><A HREF="index.html" >PostgreSQL 9.2.24 Documentation</A ></TH ></TR ><TR ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A TITLE="Role Membership" HREF="role-membership.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="user-manag.html" ACCESSKEY="U" >Up</A ></TD ><TD WIDTH="60%" ALIGN="center" VALIGN="bottom" >Chapter 20. Database Roles</TD ><TD WIDTH="20%" ALIGN="right" VALIGN="top" ><A TITLE="Function and Trigger Security" HREF="perm-functions.html" ACCESSKEY="N" >Next</A ></TD ></TR ></TABLE ><HR ALIGN="LEFT" WIDTH="100%"></DIV ><DIV CLASS="SECT1" ><H1 CLASS="SECT1" ><A NAME="ROLE-REMOVAL" >20.4. Dropping Roles</A ></H1 ><P > Because roles can own database objects and can hold privileges to access other objects, dropping a role is often not just a matter of a quick <A HREF="sql-droprole.html" >DROP ROLE</A >. Any objects owned by the role must first be dropped or reassigned to other owners; and any permissions granted to the role must be revoked. </P ><P > Ownership of objects can be transferred one at a time using <TT CLASS="COMMAND" >ALTER</TT > commands, for example: </P><PRE CLASS="PROGRAMLISTING" >ALTER TABLE bobs_table OWNER TO alice;</PRE ><P> Alternatively, the <A HREF="sql-reassign-owned.html" >REASSIGN OWNED</A > command can be used to reassign ownership of all objects owned by the role-to-be-dropped to a single other role. Because <TT CLASS="COMMAND" >REASSIGN OWNED</TT > cannot access objects in other databases, it is necessary to run it in each database that contains objects owned by the role. (Note that the first such <TT CLASS="COMMAND" >REASSIGN OWNED</TT > will change the ownership of any shared-across-databases objects, that is databases or tablespaces, that are owned by the role-to-be-dropped.) </P ><P > Once any valuable objects have been transferred to new owners, any remaining objects owned by the role-to-be-dropped can be dropped with the <A HREF="sql-drop-owned.html" >DROP OWNED</A > command. Again, this command cannot access objects in other databases, so it is necessary to run it in each database that contains objects owned by the role. Also, <TT CLASS="COMMAND" >DROP OWNED</TT > will not drop entire databases or tablespaces, so it is necessary to do that manually if the role owns any databases or tablespaces that have not been transferred to new owners. </P ><P > <TT CLASS="COMMAND" >DROP OWNED</TT > also takes care of removing any privileges granted to the target role for objects that do not belong to it. Because <TT CLASS="COMMAND" >REASSIGN OWNED</TT > does not touch such objects, it's typically necessary to run both <TT CLASS="COMMAND" >REASSIGN OWNED</TT > and <TT CLASS="COMMAND" >DROP OWNED</TT > (in that order!) to fully remove the dependencies of a role to be dropped. </P ><P > In short then, the most general recipe for removing a role that has been used to own objects is: </P ><PRE CLASS="PROGRAMLISTING" >REASSIGN OWNED BY doomed_role TO successor_role; DROP OWNED BY doomed_role; -- repeat the above commands in each database of the cluster DROP ROLE doomed_role;</PRE ><P > When not all owned objects are to be transferred to the same successor owner, it's best to handle the exceptions manually and then perform the above steps to mop up. </P ><P > If <TT CLASS="COMMAND" >DROP ROLE</TT > is attempted while dependent objects still remain, it will issue messages identifying which objects need to be reassigned or dropped. </P ></DIV ><DIV CLASS="NAVFOOTER" ><HR ALIGN="LEFT" WIDTH="100%"><TABLE SUMMARY="Footer navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" ><A HREF="role-membership.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="index.html" ACCESSKEY="H" >Home</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" ><A HREF="perm-functions.html" ACCESSKEY="N" >Next</A ></TD ></TR ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" >Role Membership</TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="user-manag.html" ACCESSKEY="U" >Up</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" >Function and Trigger Security</TD ></TR ></TABLE ></DIV ></BODY ></HTML >