pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedCompletely replacing an old user
6+ messages / 5 participants
[nested] [flat]
* Completely replacing an old user
@ 2023-09-16 18:27 Koen De Groote <kdg.dev@gmail.com>
0 siblings, 4 replies; 6+ messages in thread
From: Koen De Groote @ 2023-09-16 18:27 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
role postgres in schema public
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.
What am I missing here?
Regards,
Koen De Groote
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Completely replacing an old user
@ 2023-09-16 20:37 Erik Wienhold <ewie@ewie.name>
parent: Koen De Groote <kdg.dev@gmail.com>
3 siblings, 0 replies; 6+ messages in thread
From: Erik Wienhold @ 2023-09-16 20:37 UTC (permalink / raw)
To: Koen De Groote <kdg.dev@gmail.com>; pgsql-admin@lists.postgresql.org
On 16/09/2023 20:27 CEST Koen De Groote <kdg.dev@gmail.com> wrote:
> I am trying to set up a new user for a client application, and thus want to
> remove the old user afterward.
>
> 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
> role postgres in schema public
You must also revoke its default privileges before you can drop a role.
That's mentioned in the notes of ALTER DEFAULT PRIVILEGES [1].
> 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.
psql command \ddp will show you the default privileges.
[1] https://www.postgresql.org/docs/16/sql-alterdefaultprivileges.html#SQL-ALTERDEFAULTPRIVILEGES-NOTES
--
Erik
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Completely replacing an old user
@ 2023-09-16 22:45 David G. Johnston <david.g.johnston@gmail.com>
parent: Koen De Groote <kdg.dev@gmail.com>
3 siblings, 0 replies; 6+ messages in thread
From: David G. Johnston @ 2023-09-16 22:45 UTC (permalink / raw)
To: Koen De Groote <kdg.dev@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Saturday, September 16, 2023, Koen De Groote <kdg.dev@gmail.com> wrote:
>
>
>
> ERROR: role "X" cannot be dropped because some objects depend on it
> DETAIL: privileges for default privileges on new functions belonging to
> role postgres in schema public
>
> 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.
>
>
> What am I missing here?
>
Alter default privileges.
And…
https://www.postgresql.org/docs/current/catalog-pg-default-acl.html
David J.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Completely replacing an old user
@ 2023-09-16 22:46 Pepe TD Vo <pepevo@yahoo.com>
parent: Koen De Groote <kdg.dev@gmail.com>
3 siblings, 1 reply; 6+ messages in thread
From: Pepe TD Vo @ 2023-09-16 22:46 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; Koen De Groote <kdg.dev@gmail.com>
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 mistakes you will be alone. So judge less, love, and forgive more.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)
**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 user2/ give the new user all priviliges on database, table, sequence, function and procedures, just like the old user3/ deply client application with the new user and credentials
3/ Swap ownership of the databases4/ Remove privileges from the old user5/ 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 role postgres in schema public
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.
What am I missing here?
Regards,Koen De Groote
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Completely replacing an old user
@ 2023-09-17 12:04 Koen De Groote <kdg.dev@gmail.com>
parent: Koen De Groote <kdg.dev@gmail.com>
3 siblings, 0 replies; 6+ messages in thread
From: Koen De Groote @ 2023-09-17 12:04 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Right, from reading the responses, it seems my previous efforts ignored the
privileges on schema, and I focused only on database and everything
dependent on the database object.
With altering default privileges on the schema, my issue is solved.
Thanks to all who contributed.
Regards,
Koen De Groote
On Sat, Sep 16, 2023 at 8:27 PM 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
> role postgres in schema public
>
> 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.
>
>
> What am I missing here?
>
> Regards,
> Koen De Groote
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Completely replacing an old user
@ 2023-09-17 20:04 M Sarwar <sarwarmd02@outlook.com>
parent: Pepe TD Vo <pepevo@yahoo.com>
0 siblings, 0 replies; 6+ messages in thread
From: M Sarwar @ 2023-09-17 20:04 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; Koen De Groote <kdg.dev@gmail.com>; Pepe TD Vo <pepevo@yahoo.com>
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>; Koen 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 mistakes you will be alone. So judge less, love, and forgive more.[Emoji][Emoji][Emoji]
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)
**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 role postgres in schema public
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.
What am I missing here?
Regards,
Koen De Groote
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2023-09-17 20:04 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-09-16 18:27 Completely replacing an old user Koen De Groote <kdg.dev@gmail.com>
2023-09-16 20:37 ` Erik Wienhold <ewie@ewie.name>
2023-09-16 22:45 ` David G. Johnston <david.g.johnston@gmail.com>
2023-09-16 22:46 ` Pepe TD Vo <pepevo@yahoo.com>
2023-09-17 20:04 ` M Sarwar <sarwarmd02@outlook.com>
2023-09-17 12:04 ` Koen De Groote <kdg.dev@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox