[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-admin
Subject: Re: Completely replacing an old user
From: M Sarwar <sarwarmd02 () outlook ! com>
Date: 2023-09-17 20:04:05
Message-ID: DM4PR19MB5978ED70DB3F6E2CB047A933D3F4A () DM4PR19MB5978 ! namprd19 ! prod ! outlook ! com
[Download RAW message or body]
Hi Pepe,
You are right. It got dropped after revoking the privileges.
Thank you so much,
Sarwar
________________________________
From: Pepe TD Vo <pepevo@yahoo.com>
Sent: Saturday, September 16, 2023 6:46 PM
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; Ko=
en De Groote <kdg.dev@gmail.com>
Subject: Re: Completely replacing an old user
Sound like this is actually an alias for DROP ROLE.
You have to explicitly drop any privileges associated with that user, also =
to move its ownership to other roles (or drop the object).
reassign owned by <olduser> to <nnewuser> ;
and then
drop owned by <olduser>;
The latter will remove any privileges granted to the user.
https://www.postgresql.org/docs/current/role-removal.html
Bach-Nga
No one in this world is pure and perfect. If you avoid people for their mi=
stakes you will be alone. So judge less, love, and forgive more.[Emoji][Emo=
ji][Emoji]
To call him a dog hardly seems to do him justice though in as much as he ha=
d four legs, a tail, and barked, I admit he was, to all outward appearances=
. But to those who knew him well, he was a perfect gentleman (Hermione Ging=
old)
**Live simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success
On Saturday, September 16, 2023 at 02:28:13 PM EDT, Koen De Groote <kdg.dev=
@gmail.com> wrote:
All,
I am trying to set up a new user for a client application, and thus want to=
remove the old user afterward.
The steps are:
1/ create the new user
2/ give the new user all priviliges on database, table, sequence, function =
and procedures, just like the old user
3/ deply client application with the new user and credentials
3/ Swap ownership of the databases
4/ Remove privileges from the old user
5/ Drop the old user
But I'm getting stuck on one last error message:
ERROR: role "X" cannot be dropped because some objects depend on it
DETAIL: privileges for default privileges on new functions belonging to ro=
le postgres in schema public
The "new functions" bit is confusing. I've swapped over all the current fun=
ctions, but cannot seem to find the appropriate table for privileges on new=
functions.
What am I missing here?
Regards,
Koen De Groote
[Attachment #3 (text/html)]
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<style type="text/css" style="display:none;"> P {margin-top:0;margin-bottom:0;} \
</style> </head>
<body dir="ltr">
<div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: \
rgb(0, 0, 0);" class="elementToProof"> Hi Pepe,</div>
<div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: \
rgb(0, 0, 0);" class="elementToProof"> You are right. It got dropped after revoking \
the privileges.</div> <div style="font-family: Calibri, Helvetica, sans-serif; \
font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof"> Thank you so \
much,</div> <div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; \
color: rgb(0, 0, 0);" class="elementToProof"> Sarwar</div>
<div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: \
rgb(0, 0, 0);" class="elementToProof"> <br>
</div>
<div id="appendonsend"></div>
<hr style="display:inline-block;width:98%" tabindex="-1">
<div id="divRplyFwdMsg" dir="ltr"><font face="Calibri, sans-serif" \
style="font-size:11pt" color="#000000"><b>From:</b> Pepe TD Vo \
<pepevo@yahoo.com><br> <b>Sent:</b> Saturday, September 16, 2023 6:46 PM<br>
<b>To:</b> pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; \
Koen De Groote <kdg.dev@gmail.com><br> <b>Subject:</b> Re: Completely replacing \
an old user</font> <div> </div>
</div>
<div>
<div class="x_ydp34f3abdayahoo-style-wrap" style="font-family:times new roman,new \
york,times,serif; font-size:16px"> <div>
<div dir="ltr" data-setdir="false">
<div>
<p style="margin:0px 0px 1.1em; padding:0px; border:0px; font-stretch:inherit; \
line-height:inherit; font-size:15px; vertical-align:baseline; clear:both; \
color:rgb(35,38,41)"> Sound like this is actually an alias for <code \
style="margin:0px; border:0px; font-style:inherit; font-weight:inherit; \
font-stretch:inherit; line-height:inherit; vertical-align:baseline">DROP \
ROLE</code>.</p> <div style="margin:0px 0px 1.1em; padding:0px; border:0px; \
font-stretch:inherit; line-height:inherit; font-size:15px; vertical-align:baseline; \
clear:both; color:rgb(35,38,41)"> You have to explicitly drop any privileges \
associated with that user, also to move its ownership to other roles (or drop the \
object).</div> <div style="margin:0px 0px 1.1em; padding:0px; border:0px; \
font-stretch:inherit; line-height:inherit; font-size:15px; vertical-align:baseline; \
clear:both; color:rgb(35,38,41)"> reassign owned by <olduser> to \
<nnewuser> ;</div> <div style="margin:0px 0px 1.1em; padding:0px; border:0px; \
font-stretch:inherit; line-height:inherit; font-size:15px; vertical-align:baseline; \
clear:both; color:rgb(35,38,41)"> and then </div>
<div style="margin:0px 0px 1.1em; padding:0px; border:0px; font-stretch:inherit; \
line-height:inherit; font-size:15px; vertical-align:baseline; clear:both; \
color:rgb(35,38,41)"> drop owned by <olduser>;</div>
<div dir="ltr" data-setdir="false" style="margin:0px 0px 1.1em; padding:0px; \
border:0px; font-stretch:inherit; line-height:inherit; font-size:15px; \
vertical-align:baseline; clear:both; color:rgb(35,38,41)"> The latter will remove any \
privileges granted to the user.</div> <div dir="ltr" data-setdir="false" \
style="margin:0px 0px 1.1em; padding:0px; border:0px; font-stretch:inherit; \
line-height:inherit; font-size:15px; vertical-align:baseline; clear:both; \
color:rgb(35,38,41)"> <a \
href="https://www.postgresql.org/docs/current/role-removal.html" \
originalsrc="https://www.postgresql.org/docs/current/role-removal.html" \
shash="cDUyg9Vac6AiMzwhsH21MAQR46OaUGmuUUCIo8eVWhhV24d9m7Nka706ggFHSKi7qZpsgt9BThillX5 \
uO0jfxHyyfkqUOq0iGo5VBJd+QbvBG/Is4tYlte6DF5EU9i2lS1426/dl8VeMH8IrCZQKBqfURhv2drn+YccQtSmIROc=" \
rel="nofollow" target="_blank" style="font-family:times new roman,new \
york,times,serif; font-size:16px">https://www.postgresql.org/docs/current/role-removal.html</a></div>
</div>
<br>
</div>
<div><br>
</div>
<div class="x_ydp34f3abdasignature">
<div style="font-family:new times,serif; font-size:16px">
<div id="x_ydpf8dd9bc5yui_3_13_0_rc_1_1_1381789421038_7868" \
class="x_ydpf8dd9bc5yiv3811552299ms__id13889"> <font color="#0000ff"><b><span \
class="x_ydpf8dd9bc5yiv3811552299sg"><font color="#8000ff" \
style="background-color:inherit"></font></span></b></font> <div \
id="x_ydpf8dd9bc5yui_3_13_0_rc_1_1_1381789421038_7874" \
class="x_ydpf8dd9bc5yiv3811552299ms__id13893"> <font \
id="x_ydpf8dd9bc5yui_3_13_0_rc_1_1_1381789421038_7873" color="#0000ff"><font \
color="#8000ff"></font><span id="x_ydpf8dd9bc5yui_3_13_0_rc_1_1_1381789421038_7871" \
class="x_ydpf8dd9bc5yiv3811552299sg"><font \
id="x_ydpf8dd9bc5yui_3_13_0_rc_1_1_1381789421038_7870" color="#8000ff" \
style="background-color:inherit"><b \
id="x_ydpf8dd9bc5yui_3_13_0_rc_1_16_1381789421038_18">Bach-Nga<br clear="none"> <br \
clear="none"> </b>No one in this world is pure and perfect. If you avoid people \
for their mistakes you will be alone. So judge less, love, and forgive more.<img \
title="Emoji" alt="Emoji" height="16" width="16" \
class="x_ydp34f3abdayahoo-emoji-wrapper" data-inlineimagemanipulating="true" \
style="padding:0px 2px; vertical-align:middle" \
src="https://s.yimg.com/nq/yemoji_assets/latest/yemoji_assets/1f64f.png"><img \
title="Emoji" alt="Emoji" height="16" width="16" \
class="x_ydp34f3abdayahoo-emoji-wrapper" data-inlineimagemanipulating="true" \
style="padding:0px 2px; vertical-align:middle" \
src="https://s.yimg.com/nq/yemoji_assets/latest/yemoji_assets/1f64f.png"><img \
title="Emoji" alt="Emoji" height="16" width="16" \
class="x_ydp34f3abdayahoo-emoji-wrapper" data-inlineimagemanipulating="true" \
style="padding:0px 2px; vertical-align:middle" \
src="https://s.yimg.com/nq/yemoji_assets/latest/yemoji_assets/1f64f.png"></font></span></font></div>
<div id="x_ydpf8dd9bc5yui_3_13_0_rc_1_1_1381789421038_7874" \
class="x_ydpf8dd9bc5yiv3811552299ms__id13893"> <font color="#0000ff"><span \
class="x_ydpf8dd9bc5yiv3811552299sg"><font color="#8000ff" \
style="background-color:inherit">To call him a dog hardly seems to do him justice \
though in as much as he had four legs, a tail, and barked, I admit he was, to all \
outward appearances. But to those who knew him well, he was a perfect gentleman \
(Hermione Gingold)</font></span></font></div> <br clear="none">
<span id="x_ydpf8dd9bc5yui_3_13_0_rc_1_1_1381789421038_7867" \
style="color:rgb(128,0,255)">**Live simply **Love generously **Care deeply **Speak \
kindly.</span></div> <div id="x_ydpf8dd9bc5yui_3_13_0_rc_1_1_1381789421038_7866" \
class="x_ydpf8dd9bc5yiv3811552299ms__id13894"> <span \
id="x_ydpf8dd9bc5yui_3_13_0_rc_1_1_1381789421038_7865" \
class="x_ydpf8dd9bc5yiv3811552299sg"><font \
id="x_ydpf8dd9bc5yui_3_13_0_rc_1_1_1381789421038_7864" color="#8000ff" \
style="background-color:inherit">*** Genuinely rich *** Faithful talent *** Sharing \
success</font></span></div> <div \
id="x_ydpf8dd9bc5yui_3_13_0_rc_1_1_1381789421038_7866" \
class="x_ydpf8dd9bc5yiv3811552299ms__id13894"> <span \
class="x_ydpf8dd9bc5yiv3811552299sg"><font color="#8000ff" \
style="background-color:inherit"><br> </font></span></div>
<div id="x_ydpf8dd9bc5yui_3_13_0_rc_1_1_1381789421038_7866" dir="ltr" \
class="x_ydpf8dd9bc5yiv3811552299ms__id13894"> <span \
class="x_ydpf8dd9bc5yiv3811552299sg"><font color="#8000ff" \
style="background-color:inherit"><span></span><br> </font></span></div>
</div>
</div>
</div>
<div><br>
</div>
<div><br>
</div>
</div>
<div id="x_yahoo_quoted_5869077138" class="x_yahoo_quoted">
<div style="font-family:'Helvetica Neue',Helvetica,Arial,sans-serif; font-size:13px; \
color:#26282a"> <div>On Saturday, September 16, 2023 at 02:28:13 PM EDT, Koen De \
Groote <kdg.dev@gmail.com> wrote: </div>
<div><br>
</div>
<div><br>
</div>
<div>
<div id="x_yiv9144939987">
<div dir="ltr">
<div>All,</div>
<div><br>
</div>
<div>I am trying to set up a new user for a client application, and thus want to \
remove the old user afterward.</div> <div><br>
</div>
<div>The steps are:</div>
<div><br>
</div>
<div>1/ create the new user</div>
<div>2/ give the new user all priviliges on database, table, sequence, function and \
procedures, just like the old user</div> <div>3/ deply client application with the \
new user and credentials<br> </div>
<div>3/ Swap ownership of the databases</div>
<div>4/ Remove privileges from the old user</div>
<div>5/ Drop the old user</div>
<div><br>
</div>
<div>But I'm getting stuck on one last error message:</div>
<div><br>
</div>
<div><br>
</div>
<div>ERROR: role "X" cannot be dropped because some objects depend on \
it<br>
DETAIL: privileges for default privileges on new functions belonging to role \
postgres in schema public</div> <div><br>
</div>
<div></div>
<div>The "new functions" bit is confusing. I've swapped over all the \
current functions, but cannot seem to find the appropriate table for privileges on \
new functions.</div> <div><br>
</div>
<div><br>
</div>
<div>What am I missing here?</div>
<div><br>
</div>
<div>Regards,</div>
<div>Koen De Groote<br>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</body>
</html>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic