Thread: Bug, Feature, or what else?
Hi, i have created a normal user (no superuser) akretschmer01 and another norma= l user ak02. All fine. The first user is the owner of the db. As user akretschmer01 i granted: -bash-4.1$ psql -U akretschmer01 db115150 psql (9.1.8) Type "help" for help. db115150=3D> grant all on schema public to ak02; GRANT db115150=3D> commit; There are no tables or other objects createt as user ak02. Now i switched to user postgres (superuser): -bash-4.1$ psql -U postgres db115150 psql (9.1.8) Type "help" for help. db115150=3D# drop user ak02 db115150-# ; FEHLER: kann Rolle =C2=BBak02=C2=AB nicht l=C3=B6schen, weil andere Objekt= e davon abh=C3=A4ngen DETAIL: Privilegien f=C3=BCr Schema public (role ak02 cannot be dropped because some objects depend on it, Detail: privileges for schema public) I can revoke all on database from user: db115150=3D# revoke all on database db115150 from ak02; REVOKE db115150=3D# drop user ak02; FEHLER: kann Rolle =C2=BBak02=C2=AB nicht l=C3=B6schen, weil andere Objekt= e davon abh=C3=A4ngen DETAIL: Privilegien f=C3=BCr Schema public but still i can't drop the user. I can do that with 9.1.8 and 9.1.1 too, same problem. How can i drop a user as SUPERUSER (!) with all privileges? Regards, Andreas
2013/2/8 Andreas Kretschmer <andreas@a-kretschmer.de>
--
Victor Y. Yegorov
How can i drop a user as SUPERUSER (!) with all privileges?
According to the docs: http://www.postgresql.org/docs/current/interactive/sql-droprole.html
> A role cannot be removed if it is still referenced in any database of the cluster;
> an error will be raised if so. Before dropping the role, you must drop all the objects
> it owns (or reassign their ownership) and revoke any privileges the role has been
Victor Y. Yegorov
QW5kcmVhcyBLcmV0c2NobWVyIHdyb3RlOg0KPiBpIGhhdmUgY3JlYXRlZCBhIG5vcm1hbCB1c2Vy IChubyBzdXBlcnVzZXIpIGFrcmV0c2NobWVyMDEgYW5kIGFub3RoZXIgbm9ybWFsDQo+IHVzZXIg YWswMi4gQWxsIGZpbmUuDQo+IFRoZSBmaXJzdCB1c2VyIGlzIHRoZSBvd25lciBvZiB0aGUgZGIu DQo+IA0KPiANCj4gQXMgdXNlciBha3JldHNjaG1lcjAxIGkgZ3JhbnRlZDoNCg0KPiBkYjExNTE1 MD0+IGdyYW50IGFsbCBvbiBzY2hlbWEgcHVibGljIHRvIGFrMDI7DQoNCj4gVGhlcmUgYXJlIG5v IHRhYmxlcyBvciBvdGhlciBvYmplY3RzIGNyZWF0ZXQgYXMgdXNlciBhazAyLg0KPiANCj4gDQo+ IE5vdyBpIHN3aXRjaGVkIHRvIHVzZXIgcG9zdGdyZXMgKHN1cGVydXNlcik6DQoNCj4gZGIxMTUx NTA9IyBkcm9wIHVzZXIgYWswMg0KPiBkYjExNTE1MC0jIDsNCj4gRkVITEVSOiAga2FubiBSb2xs ZSDCu2FrMDLCqyBuaWNodCBsw7ZzY2hlbiwgd2VpbCBhbmRlcmUgT2JqZWt0ZSBkYXZvbiBhYmjD pG5nZW4NCj4gREVUQUlMOiAgUHJpdmlsZWdpZW4gZsO8ciBTY2hlbWEgcHVibGljDQo+IA0KPiAo cm9sZSBhazAyIGNhbm5vdCBiZSBkcm9wcGVkIGJlY2F1c2Ugc29tZSBvYmplY3RzIGRlcGVuZCBv biBpdCwgRGV0YWlsOg0KPiBwcml2aWxlZ2VzIGZvciBzY2hlbWEgcHVibGljKQ0KPiANCj4gSSBj YW4gcmV2b2tlIGFsbCBvbiBkYXRhYmFzZSBmcm9tIHVzZXI6DQo+IA0KPiBkYjExNTE1MD0jIHJl dm9rZSBhbGwgb24gZGF0YWJhc2UgZGIxMTUxNTAgZnJvbSBhazAyOw0KPiBSRVZPS0UNCj4gZGIx MTUxNTA9IyBkcm9wIHVzZXIgYWswMjsNCj4gRkVITEVSOiAga2FubiBSb2xsZSDCu2FrMDLCqyBu aWNodCBsw7ZzY2hlbiwgd2VpbCBhbmRlcmUgT2JqZWt0ZSBkYXZvbiBhYmjDpG5nZW4NCj4gREVU QUlMOiAgUHJpdmlsZWdpZW4gZsO8ciBTY2hlbWEgcHVibGljDQo+IA0KPiANCj4gYnV0IHN0aWxs IGkgY2FuJ3QgZHJvcCB0aGUgdXNlci4NCj4gDQo+IA0KPiBJIGNhbiBkbyB0aGF0IHdpdGggOS4x LjggYW5kIDkuMS4xIHRvbywgc2FtZSBwcm9ibGVtLg0KPiANCj4gDQo+IEhvdyBjYW4gaSBkcm9w IGEgdXNlciBhcyBTVVBFUlVTRVIgKCEpIHdpdGggYWxsIHByaXZpbGVnZXM/DQoNCllvdSBoYXZl IHRvIHJldm9rZSB0aGUgcGVybWlzc2lvbnMgdGhhdCB5b3UgZ3JhbnRlZC4NCg0KVGhlcmUgYXJl IHR3byBvcHRpb25zOg0KDQpkYjExNTE1MD0jIERST1AgT1dORUQgQlkgYWswMjsNCkRST1AgT1dO RUQNCg0Kb3INCg0KZGIxMTUxNTA9IyBSRVZPS0UgQUxMIE9OIFNDSEVNQSBwdWJsaWMgRlJPTSBh azAyOw0KUkVWT0tFDQoNClRoZW4geW91IGNhbiBkcm9wIHRoZSB1c2VyLg0KDQpZb3VycywNCkxh dXJlbnogQWxiZQ0K
On 02/08/2013 06:25 AM, Andreas Kretschmer wrote: > Hi, > > i have created a normal user (no superuser) akretschmer01 and another normal > user ak02. All fine. > The first user is the owner of the db. > > > As user akretschmer01 i granted: > > -bash-4.1$ psql -U akretschmer01 db115150 > psql (9.1.8) > Type "help" for help. > > db115150=> grant all on schema public to ak02; > GRANT > db115150=> commit; > > There are no tables or other objects createt as user ak02. > > > Now i switched to user postgres (superuser): > > > -bash-4.1$ psql -U postgres db115150 > psql (9.1.8) > Type "help" for help. > > db115150=# drop user ak02 > db115150-# ; > FEHLER: kann Rolle »ak02« nicht löschen, weil andere Objekte davon abhängen > DETAIL: Privilegien für Schema public > > (role ak02 cannot be dropped because some objects depend on it, Detail: > privileges for schema public) > > I can revoke all on database from user: > > db115150=# revoke all on database db115150 from ak02; > REVOKE > db115150=# drop user ak02; > FEHLER: kann Rolle »ak02« nicht löschen, weil andere Objekte davon abhängen > DETAIL: Privilegien für Schema public > > > but still i can't drop the user. > > > I can do that with 9.1.8 and 9.1.1 too, same problem. > > > How can i drop a user as SUPERUSER (!) with all privileges? I believe you will need to REVOKE ALL ON SCHEMA public FROM ak02. REVOKE ALL ON DATABASE does not mean revoke all privileges for all objects in the database. It only applies to DATABASE privileges-CONNECT, CREATE, TEMP > > > > Regards, Andreas > > -- Adrian Klaver adrian.klaver@gmail.com
> > How can i drop a user as SUPERUSER (!) with all privileges? > > You have to revoke the permissions that you granted. > > There are two options: > > db115150=3D# DROP OWNED BY ak02; > DROP OWNED > > or > > db115150=3D# REVOKE ALL ON SCHEMA public FROM ak02; > REVOKE > > Then you can drop the user. > > Yours, > Laurenz Albe nice idea, but unfortunately no: db115150=3D# drop owned by ak02; DROP OWNED db115150=3D# drop user ak02; FEHLER: kann Rolle =C2=BBak02=C2=AB nicht l=C3=B6schen, weil andere Objekt= e davon abh=C3=A4ngen DETAIL: Privilegien f=C3=BCr Schema public Andreas
> > How can i drop a user as SUPERUSER (!) with all privileges? > > I believe you will need to REVOKE ALL ON SCHEMA public FROM ak02. doesn't help: -bash-4.1$ psql -U postgres db115150 psql (9.1.8) Type "help" for help. db115150=3D# begin; BEGIN db115150=3D# revoke all on schema public from ak02; REVOKE db115150=3D# drop user ak02; FEHLER: kann Rolle =C2=BBak02=C2=AB nicht l=C3=B6schen, weil andere Objekt= e davon abh=C3=A4ngen DETAIL: Privilegien f=C3=BCr Schema public db115150=3D#
On 02/08/2013 06:58 AM, Andreas Kretschmer wrote: > >>> How can i drop a user as SUPERUSER (!) with all privileges? >> >> I believe you will need to REVOKE ALL ON SCHEMA public FROM ak02. > > > doesn't help: > > -bash-4.1$ psql -U postgres db115150 > psql (9.1.8) > Type "help" for help. > > db115150=# begin; > BEGIN > db115150=# revoke all on schema public from ak02; > REVOKE > db115150=# drop user ak02; > FEHLER: kann Rolle »ak02« nicht löschen, weil andere Objekte davon abhängen > DETAIL: Privilegien für Schema public > db115150=# > > So what does \dn+ public show? -- Adrian Klaver adrian.klaver@gmail.com
Adrian Klaver <adrian.klaver@gmail.com> hat am 8. Februar 2013 um 16:19
geschrieben:
> On 02/08/2013 06:58 AM, Andreas Kretschmer wrote:
> >
> >>> How can i drop a user as SUPERUSER (!) with all privileges?
> >>
> >> I believe you will need to REVOKE ALL ON SCHEMA public FROM ak02.
> >
> >
> > doesn't help:
> >
> > -bash-4.1$ psql -U postgres db115150
> > psql (9.1.8)
> > Type "help" for help.
> >
> > db115150=3D# begin;
> > BEGIN
> > db115150=3D# revoke all on schema public from ak02;
> > REVOKE
> > db115150=3D# drop user ak02;
> > FEHLER: kann Rolle =C2=BBak02=C2=AB nicht l=C3=B6schen, weil andere Ob=
jekte davon abh=C3=A4ngen
> > DETAIL: Privilegien f=C3=BCr Schema public
> > db115150=3D#
> >
> >
>
> So what does \dn+ public show?
db115150=3D# \dn+ public
List of schemas
Name | Owner | Access privileges | Description
--------+----------+-----------------------------+------------------------
public | postgres | postgres=3DUC/postgres +| standard public schema
| | akretschmer01=3DU*C*/postgres+|
| | ak02=3DUC/akretschmer01 |
(1 row)
On 02/08/2013 07:23 AM, Andreas Kretschmer wrote: > > > Adrian Klaver <adrian.klaver@gmail.com> hat am 8. Februar 2013 um 16:19 > geschrieben: >> On 02/08/2013 06:58 AM, Andreas Kretschmer wrote: >>> >>>>> How can i drop a user as SUPERUSER (!) with all privileges? >>>> >>>> I believe you will need to REVOKE ALL ON SCHEMA public FROM ak02. >>> >>> >>> doesn't help: >>> >>> -bash-4.1$ psql -U postgres db115150 >>> psql (9.1.8) >>> Type "help" for help. >>> >>> db115150=# begin; >>> BEGIN >>> db115150=# revoke all on schema public from ak02; >>> REVOKE >>> db115150=# drop user ak02; >>> FEHLER: kann Rolle »ak02« nicht löschen, weil andere Objekte davon abhängen >>> DETAIL: Privilegien für Schema public >>> db115150=# >>> >>> >> >> So what does \dn+ public show? > > db115150=# \dn+ public > List of schemas > Name | Owner | Access privileges | Description > --------+----------+-----------------------------+------------------------ > public | postgres | postgres=UC/postgres +| standard public schema > | | akretschmer01=U*C*/postgres+| > | | ak02=UC/akretschmer01 | > (1 row) > > If I am following the Notes section correctly in: http://www.postgresql.org/docs/9.1/interactive/sql-revoke.html "If a superuser chooses to issue a GRANT or REVOKE command, the command is performed as though it were issued by the owner of the affected object. Since all privileges ultimately come from the object owner (possibly indirectly via chains of grant options), it is possible for a superuser to revoke all privileges, but this might require use of CASCADE as stated above." Try: revoke all on schema public from ak02 cascade; -- Adrian Klaver adrian.klaver@gmail.com
> > If I am following the Notes section correctly in: > > http://www.postgresql.org/docs/9.1/interactive/sql-revoke.html > > "If a superuser chooses to issue a GRANT or REVOKE command, the command > is performed as though it were issued by the owner of the affected > object. Since all privileges ultimately come from the object owner > (possibly indirectly via chains of grant options), it is possible for a > superuser to revoke all privileges, but this might require use of > CASCADE as stated above." > > > Try: > > revoke all on schema public from ak02 cascade; Yeah, i read that before, but doesn't help: db115150=3D# revoke all on schema public from ak02 cascade; REVOKE db115150=3D# drop user ak02; FEHLER: kann Rolle =C2=BBak02=C2=AB nicht l=C3=B6schen, weil andere Objekt= e davon abh=C3=A4ngen DETAIL: Privilegien f=C3=BCr Schema public Strange, isn't it? Andreas
On 02/08/2013 07:45 AM, Andreas Kretschmer wrote: > >> >> If I am following the Notes section correctly in: >> >> http://www.postgresql.org/docs/9.1/interactive/sql-revoke.html >> >> "If a superuser chooses to issue a GRANT or REVOKE command, the command >> is performed as though it were issued by the owner of the affected >> object. Since all privileges ultimately come from the object owner >> (possibly indirectly via chains of grant options), it is possible for a >> superuser to revoke all privileges, but this might require use of >> CASCADE as stated above." >> >> >> Try: >> >> revoke all on schema public from ak02 cascade; > > Yeah, i read that before, but doesn't help: > > > > db115150=# revoke all on schema public from ak02 cascade; > REVOKE > db115150=# drop user ak02; > FEHLER: kann Rolle »ak02« nicht löschen, weil andere Objekte davon abhängen > DETAIL: Privilegien für Schema public > > > Strange, isn't it? Well I got left is: REVOKE PUBLIC FROM ak02; > > > Andreas > > -- Adrian Klaver adrian.klaver@gmail.com
Adrian Klaver <adrian.klaver@gmail.com> hat am 8. Februar 2013 um 16:48 geschrieben: > On 02/08/2013 07:45 AM, Andreas Kretschmer wrote: > > > >> > >> If I am following the Notes section correctly in: > >> > >> http://www.postgresql.org/docs/9.1/interactive/sql-revoke.html > >> > >> "If a superuser chooses to issue a GRANT or REVOKE command, the comman= d > >> is performed as though it were issued by the owner of the affected > >> object. Since all privileges ultimately come from the object owner > >> (possibly indirectly via chains of grant options), it is possible for = a > >> superuser to revoke all privileges, but this might require use of > >> CASCADE as stated above." > >> > >> > >> Try: > >> > >> revoke all on schema public from ak02 cascade; > > > > Yeah, i read that before, but doesn't help: > > > > > > > > db115150=3D# revoke all on schema public from ak02 cascade; > > REVOKE > > db115150=3D# drop user ak02; > > FEHLER: kann Rolle =C2=BBak02=C2=AB nicht l=C3=B6schen, weil andere Ob= jekte davon abh=C3=A4ngen > > DETAIL: Privilegien f=C3=BCr Schema public > > > > > > Strange, isn't it? > > Well I got left is: > > REVOKE PUBLIC FROM ak02; > i think you mean: db115150=3D# revoke all on schema public from ak02; REVOKE db115150=3D# drop user ak02; FEHLER: kann Rolle =C2=BBak02=C2=AB nicht l=C3=B6schen, weil andere Objekt= e davon abh=C3=A4ngen DETAIL: Privilegien f=C3=BCr Schema public
QW5kcmVhcyBLcmV0c2NobWVyIHdyb3RlOg0KPj4+IGRiMTE1MTUwPSMgcmV2b2tlIGFsbCBvbiBz Y2hlbWEgcHVibGljIGZyb20gYWswMjsNCj4+PiBSRVZPS0UNCj4+PiBkYjExNTE1MD0jIGRyb3Ag dXNlciBhazAyOw0KPj4+IEZFSExFUjogIGthbm4gUm9sbGUgwrthazAywqsgbmljaHQgbMO2c2No ZW4sIHdlaWwgYW5kZXJlIE9iamVrdGUgZGF2b24gYWJow6RuZ2VuDQo+Pj4gREVUQUlMOiAgUHJp dmlsZWdpZW4gZsO8ciBTY2hlbWEgcHVibGljDQo+Pg0KPj4gU28gd2hhdCBkb2VzIFxkbisgcHVi bGljIHNob3c/DQo+IA0KPiBkYjExNTE1MD0jIFxkbisgcHVibGljDQo+ICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgTGlzdCBvZiBzY2hlbWFzDQo+ICAgTmFtZSAgfCAgT3duZXIgICB8ICAg ICAgQWNjZXNzIHByaXZpbGVnZXMgICAgICB8ICAgICAgRGVzY3JpcHRpb24NCj4gLS0tLS0tLS0r LS0tLS0tLS0tLSstLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLSstLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0NCj4gIHB1YmxpYyB8IHBvc3RncmVzIHwgcG9zdGdyZXM9VUMvcG9zdGdyZXMgICAg ICAgK3wgc3RhbmRhcmQgcHVibGljIHNjaGVtYQ0KPiAgICAgICAgIHwgICAgICAgICAgfCBha3Jl dHNjaG1lcjAxPVUqQyovcG9zdGdyZXMrfA0KPiAgICAgICAgIHwgICAgICAgICAgfCBhazAyPVVD L2FrcmV0c2NobWVyMDEgICAgICAgfA0KPiAoMSByb3cpDQoNClNlZW1zIGxpa2UgeW91IGNhbid0 IHJldm9rZSBwcml2aWxlZ2VzIHlvdSBkaWRuJ3QgZ3JhbnQsDQpldmVuIGFzIHN1cGVydXNlci4N Cg0KVHJ5Og0KDQpTRVQgU0VTU0lPTiBBVVRIT1JJWkFUSU9OIGFrcmV0c2NobWVyMDE7DQpSRVZP S0UgQUxMIE9OIFNDSEVNQSBQVUJMSUMgRlJPTSBhazAyOw0KUkVTRVQgU0VTU0lPTiBBVVRIT1JJ WkFUSU9OOw0KRFJPUCBVU0VSIGFrMDI7DQoNCllvdXJzLA0KTGF1cmVueiBBbGJlDQo=
Andreas Kretschmer <andreas@a-kretschmer.de> writes:
> Adrian Klaver <adrian.klaver@gmail.com> hat am 8. Februar 2013 um 16:19
>> So what does \dn+ public show?
> db115150=# \dn+ public
> List of schemas
> Name | Owner | Access privileges | Description
> --------+----------+-----------------------------+------------------------
> public | postgres | postgres=UC/postgres +| standard public schema
> | | akretschmer01=U*C*/postgres+|
> | | ak02=UC/akretschmer01 |
> (1 row)
Ah: this shows that you didn't tell us the whole truth to start with.
What you've actually got here is that postgres granted ALL WITH GRANT
OPTION to akretschmer01, and then akretschmer01 used the grant option
to grant rights to ak02. (I was wondering how it was that a non
superuser would be able to grant anything about schema public...)
Only akretschmer01 can directly drop the grant to ak02. What postgres
could do is revoke the grant option to akretschmer01, and the cascaded
effect of that would remove the privileges for ak02.
Of course, postgres has other options besides that, of which "DROP OWNED
BY ak02" is probably the most appropriate here. Or if you really want
to get rid of just that grant, SET ROLE TO akretschmer01 and revoke.
regards, tom lane
On 02/08/2013 08:14 AM, Tom Lane wrote: > Andreas Kretschmer <andreas@a-kretschmer.de> writes: >> Adrian Klaver <adrian.klaver@gmail.com> hat am 8. Februar 2013 um 16:19 >>> So what does \dn+ public show? > >> db115150=# \dn+ public >> List of schemas >> Name | Owner | Access privileges | Description >> --------+----------+-----------------------------+------------------------ >> public | postgres | postgres=UC/postgres +| standard public schema >> | | akretschmer01=U*C*/postgres+| >> | | ak02=UC/akretschmer01 | >> (1 row) > > Ah: this shows that you didn't tell us the whole truth to start with. > What you've actually got here is that postgres granted ALL WITH GRANT > OPTION to akretschmer01, and then akretschmer01 used the grant option > to grant rights to ak02. (I was wondering how it was that a non > superuser would be able to grant anything about schema public...) > > Only akretschmer01 can directly drop the grant to ak02. What postgres > could do is revoke the grant option to akretschmer01, and the cascaded > effect of that would remove the privileges for ak02. > > Of course, postgres has other options besides that, of which "DROP OWNED > BY ak02" is probably the most appropriate here. Or if you really want > to get rid of just that grant, SET ROLE TO akretschmer01 and revoke. The DROP OWNED was tried further up the thread and did not seem to work: " nice idea, but unfortunately no: db115150=# drop owned by ak02; DROP OWNED db115150=# drop user ak02; FEHLER: kann Rolle »ak02« nicht löschen, weil andere Objekte davon abhängen DETAIL: Privilegien für Schema public " > > regards, tom lane > > -- Adrian Klaver adrian.klaver@gmail.com
On 02/08/2013 07:57 AM, Andreas Kretschmer wrote: >> Well I got left is: >> >> REVOKE PUBLIC FROM ak02; >> > > i think you mean: > > db115150=# revoke all on schema public from ak02; > REVOKE > db115150=# drop user ak02; > FEHLER: kann Rolle »ak02« nicht löschen, weil andere Objekte davon abhängen > DETAIL: Privilegien für Schema public > No I was trying to revoke membership in the PUBLIC role for ak02, on the chance that ak02 was picking up privileges from it. I do not know if that is even possible, but at this point I am grasping at straws. -- Adrian Klaver adrian.klaver@gmail.com
Tom Lane <tgl@sss.pgh.pa.us> wrote: > Andreas Kretschmer <andreas@a-kretschmer.de> writes: > > Adrian Klaver <adrian.klaver@gmail.com> hat am 8. Februar 2013 um 16:19 > >> So what does \dn+ public show? > > > db115150=# \dn+ public > > List of schemas > > Name | Owner | Access privileges | Description > > --------+----------+-----------------------------+------------------------ > > public | postgres | postgres=UC/postgres +| standard public schema > > | | akretschmer01=U*C*/postgres+| > > | | ak02=UC/akretschmer01 | > > (1 row) > > Ah: this shows that you didn't tell us the whole truth to start with. > What you've actually got here is that postgres granted ALL WITH GRANT > OPTION to akretschmer01, and then akretschmer01 used the grant option > to grant rights to ak02. (I was wondering how it was that a non > superuser would be able to grant anything about schema public...) > > Only akretschmer01 can directly drop the grant to ak02. What postgres > could do is revoke the grant option to akretschmer01, and the cascaded > effect of that would remove the privileges for ak02. > > Of course, postgres has other options besides that, of which "DROP OWNED > BY ak02" is probably the most appropriate here. Or if you really want > to get rid of just that grant, SET ROLE TO akretschmer01 and revoke. > > regards, tom lane Thanks. A colleague of me (the author of the wiki-artikel) says: Thanks for your reply. Sorry to have been unclear, but yes the grants in question were created by an intermediate admin. (cf. http://wiki.postgresql.org/wiki/Shared_Database_Hosting, with DBMAINUSER=akretschmer01 and DBEXTRAUSER=ak02). Just as a side note, the user that granted the "ALL WITH GRANT" to akretschmer01 wasn't actually postgres but an additional supervisor role with a different name, yet still it says postgres in the \dn+ output. Anyway, I get that a non-super-user role may only revoke permissions that it originally granted. But I am a bit confused about the following paragraph from the docs: " If a superuser chooses to issue a GRANT or REVOKE command, the command is performed as though it were issued by the owner of the affected object. Since all privileges ultimately come from the object owner (possibly indirectly via chains of grant options), it is possible for a superuser to revoke all privileges, but this might require use of CASCADE as stated above. " http://www.postgresql.org/docs/9.2/static/sql-grant.html And yes we really only want to get rid of the grants of the DBEXTRAUSER, the rational in a shared hosting scenario being that DBMAINUSER might have granted a plethora of rights to DBEXTRAUSER via the psql, but still should be able to remove a DBEXTRAUSER at any time with just a click of a button (in the hosting panel) *without* affecting the existence of any objects. Basically we are trying to emulate a command like DROP ROLE <X> ALSO REMOVING ALL GRANTS TO ROLE <X> TO ANY OBJECT IN ANY DATABASE. (after doing a REASSIGN OWNED BY <X> TO <DBMAINUSER>) Regards, Thomas Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
Andreas Kretschmer <andreas@a-kretschmer.de> wrote: > How can i drop a user as SUPERUSER (!) with all privileges? Okay, i think, it's time to say 'thank you' to all on this list! It's great, only a few hours and our observation confirmed. I (we) think, this is a bug. We will make a workaround for us in the next days, and we will keep you informed about our solution. Again, many thanks from /me and my colleague, not only to Tom! Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°