Re: [GENERAL] How to drop user if objects depend on it

[Andrus]

The example script works for me.  What PG version are you running?  I have
a vague recollection that we've fixed bugs-of-omission in DROP OWNED in
the past.


I'm using

"PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real
(Debian 4.4.5-8) 4.4.5, 64-bit"

In   "PostgreSQL 9.4.4, compiled by Visual C++ build 1800, 32-bit" it works.

It looks like in 9.1  reassign owned should replaced with revoke commands.

Andrus. 




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to drop user if objects depend on it

[Tom Lane]

"Andrus"  writes:
>> The example script works for me.  What PG version are you running?  I have
>> a vague recollection that we've fixed bugs-of-omission in DROP OWNED in
>> the past.

> I'm using
> "PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real
> (Debian 4.4.5-8) 4.4.5, 64-bit"

Ah.  I believe this is fixed in 9.1.7 and later.  In prior versions DROP
OWNED neglects to revoke permissions on shared objects (databases,
tablespaces).

Considering that 9.1.x is up to 9.1.19 as of tomorrow, you really ought
to think about doing a minor-version upgrade.  We don't put out bug fix
releases just for idle amusement.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to drop user if objects depend on it

[Thom Brown]

On 7 October 2015 at 11:42, Andrus  wrote:
> Hi!
>
> Database idd owner is role idd_owner
> Database has 2 data schemas: public and firma1.
> User may have directly or indirectly assigned rights in this database and
> objects.
> User is not owner of any object. It has only rights assigned to objects.
>
> How to drop such  user ?
>
> I tried
>
> revoke all on all tables in schema public,firma1 from "vantaa" cascade;
> revoke all on all sequences in schema public,firma1 from "vantaa"
> cascade;
> revoke all on database idd from "vantaa" cascade;
> revoke all on all functions in schema public,firma1 from "vantaa"
> cascade;
> revoke all on schema public,firma1 from "vantaa" cascade;
> revoke idd_owner from "vantaa" cascade;
> ALTER DEFAULT PRIVILEGES IN SCHEMA public,firma1 revoke all ON TABLES
> from "vantaa";
> DROP ROLE if exists "vantaa"
>
> but got error
>
> role "vantaa" cannot be dropped because some objects depend on it
> DETAIL:  privileges for schema public
>
> in statement
>
> DROP ROLE if exists "vantaa"
>
> How to fix this so that user can dropped ?
>
> How to create sql or plpgsql method which takes user name as parameter and
> drops this user in all cases without dropping data ?
> Or maybe there is some command or simpler commands in postgres ?

The objects can't be owned by nothing, so you will need to reassign ownership:

REASSIGN OWNED BY old_role TO new_role;

e.g.

REASSIGN OWNED BY vantaa TO postgres;

Then you can drop the role.

Regards

Thom


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to drop user if objects depend on it

[Melvin Davidson]

Can you connect as user postgres? IE: psql -U postgres -d 

If so, then you should have the ability to execute the commands without any
problem.

On Wed, Oct 7, 2015 at 9:53 AM, Adrian Klaver 
wrote:

> On 10/07/2015 05:12 AM, Andrus wrote:
>
>> Hi!
>>
>> The objects can't be owned by nothing, so you will need to reassign
>>> ownership:
>>> REASSIGN OWNED BY old_role TO new_role;
>>> e.g.
>>> REASSIGN OWNED BY vantaa TO postgres;
>>> Then you can drop the role.
>>>
>>
>> User who deletes other users is not superuser. It is created using
>>
>> CREATE ROLE admin LOGIN
>>   NOSUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION CONNECTION LIMIT
>> 100;
>> GRANT idd_owner TO admin;
>>
>>
>> I tried
>>
>
> So to be clear admin is doing the below, correct?
>
>
>> REASSIGN OWNED BY vantaa TO postgres;
>>
>> and
>>
>> REASSIGN OWNED BY vantaa TO idd_owner;
>>
>> but got  error
>>
>> permission denied to reassign objects .
>>
>
> Is the above a blanket error or does it mention specific objects?
>
>
>> How to fix ?
>>
>
> What you are trying to do is reverse what you did to get the present
> setup. Do you have a record/script that shows what you did to create the
> role and assign it to the objects?
>
>
>> I can add some rights to user who invokes this command if this helps.
>>
>> Andrus.
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] How to drop user if objects depend on it

[Andrus]

Hi!

The objects can't be owned by nothing, so you will need to reassign 
ownership:

REASSIGN OWNED BY old_role TO new_role;
e.g.
REASSIGN OWNED BY vantaa TO postgres;
Then you can drop the role.


User who deletes other users is not superuser. It is created using

CREATE ROLE admin LOGIN
 NOSUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION CONNECTION LIMIT 
100;

GRANT idd_owner TO admin;


I tried

REASSIGN OWNED BY vantaa TO postgres;

and

REASSIGN OWNED BY vantaa TO idd_owner;

but got  error

permission denied to reassign objects .

How to fix ?

I can add some rights to user who invokes this command if this helps.

Andrus. 




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to drop user if objects depend on it

[Adrian Klaver]

On 10/07/2015 05:12 AM, Andrus wrote:

Hi!


The objects can't be owned by nothing, so you will need to reassign
ownership:
REASSIGN OWNED BY old_role TO new_role;
e.g.
REASSIGN OWNED BY vantaa TO postgres;
Then you can drop the role.


User who deletes other users is not superuser. It is created using

CREATE ROLE admin LOGIN
  NOSUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION CONNECTION LIMIT
100;
GRANT idd_owner TO admin;


I tried


So to be clear admin is doing the below, correct?



REASSIGN OWNED BY vantaa TO postgres;

and

REASSIGN OWNED BY vantaa TO idd_owner;

but got  error

permission denied to reassign objects .


Is the above a blanket error or does it mention specific objects?



How to fix ?


What you are trying to do is reverse what you did to get the present 
setup. Do you have a record/script that shows what you did to create the 
role and assign it to the objects?




I can add some rights to user who invokes this command if this helps.

Andrus.





--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to drop user if objects depend on it

[Andrus]

Hi!

No. You need to be a superuser to reassign objects unless you own the 
object.

1. first connect as user postgres
2. REASSIGN all the tables owned by the missing user first.
3. Then you can drop the missing user AFTER you have reassigned all the 
objects they own.


Script

reassign owned by farukkugay to postgres;
drop user farukkugay ;

is running by superuser but it still causes the error.



You must also be a superuser to drop roles.


Non-superuser creates roles, assigns rights and drop users using scripts 
which I provided.

Those scripts work OK on most cases.

For some users, vantaa and farukkugan  delete script causes error which I 
described. For farukkugan it occurs also if running under superuser.


So it looks like it should be possible for non-superusers also.

Andrus. 




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to drop user if objects depend on it

[Andrus]

Hi!


I tried
So to be clear admin is doing the below, correct?


Yes. I copied provided user definition which invokes delete command from 
pgadmin

code window for this user .


permission denied to reassign objects .
Is the above a blanket error or does it mention specific objects?


postgres log file contains two lines:

ERROR:  permission denied to reassign objects
STATEMENT:  reassign owned by vantaa to postgres

No objects are mentioned.
I can change log level if this helps.

What you are trying to do is reverse what you did to get the present setup. 
Do you have a record/script that shows what you did to create the role and 
assign it to the objects?


It was something like:

create script:

CREATE ROLE vantaa;
grant idd_owner to vantaa;

reset roles script:

revoke all on all tables in schema public,firma1 from vantaa cascade;
revoke all on all sequences in schema public,firma1 from vantaa cascade;
revoke all on database idd  from public,firma1 cascade;
revoke all on all functions in schema public,firma1 from vantaa cascade;
revoke all on schema public,firma1 from vantaa cascade;
revoke idd_owner  from vantaa cascade;
ALTER ROLE vantaa inherit NOCREATEROLE NOCREATEDB NOLOGIN;

grant all on all tables in schema public,firma1 to vantaa;
grant all on all sequences in schema public,firma1 to vantaa;
grant all on database idd  to vantaa;
grant all on schema public,firma1 to vantaa;
ALTER DEFAULT PRIVILEGES IN SCHEMA public,firma1  GRANT all ON TABLES TO 
vantaa;

-- Restrict some tables:
revoke all on  kasutaja,kaspriv,logifail from vantaa cascade;
grant select on kaspriv,kasutaja to vantaa;
grant update (eesnimi, nimi,email,amet,islocked,telefon,language,vabakuup) 
on kasutaja to vantaa;

grant insert on logifail to vantaa;

Andrus. 




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to drop user if objects depend on it

[Melvin Davidson]

No. You need to be a superuser to reassign objects unless you own the
object.
You must also be a superuser to drop roles.

So.
1. first connect as user postgres
2. REASSIGN all the tables owned by the missing user first.
3. Then you can drop the missing user AFTER you have reassigned all the
objects they own.

On Wed, Oct 7, 2015 at 10:48 AM, Andrus  wrote:

> Hi!
>
> Can you connect as user postgres? IE: psql -U postgres -d 
>>
>
> Applicaton has admin users which should be able to delete other users.
> Those users dont have superuser rights.
>
> I can connect as user postgres for testing only.
> I'm looking for a way to delete users without superuser right.
>
> If so, then you should have the ability to execute the commands without
>> any problem.
>>
>
> I tried in database ktp :
>
> reassign owned by farukkugay to postgres;
> drop user farukkugay ;
>
> This causes error
>
> ERROR:  role "farukkugay" cannot be dropped because some objects depend on
> it
> DETAIL:  privileges for schema public
>
> So even superuser cannot delete.
>
> Andrus.
>



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] How to drop user if objects depend on it

[Tom Lane]

"Andrus"  writes:
> ALTER DEFAULT PRIVILEGES IN SCHEMA public,firma1  GRANT all ON TABLES TO 
> vantaa;

I am not sure that REASSIGN OWNED will get rid of default-privilege
specifiers --- you might have to reverse this step separately.

In general, REASSIGN OWNED has to be done by a role that has privileges
of (is a member of) both the source and target roles.  Superusers are
considered members of all roles, so that's how come it works for them.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to drop user if objects depend on it

[Andrus]

Hi!


Can you connect as user postgres? IE: psql -U postgres -d 


Applicaton has admin users which should be able to delete other users.
Those users dont have superuser rights.

I can connect as user postgres for testing only.
I'm looking for a way to delete users without superuser right.

If so, then you should have the ability to execute the commands without any 
problem.


I tried in database ktp :

reassign owned by farukkugay to postgres;
drop user farukkugay ;

This causes error

ERROR:  role "farukkugay" cannot be dropped because some objects depend on 
it

DETAIL:  privileges for schema public

So even superuser cannot delete.

Andrus. 




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to drop user if objects depend on it

[Melvin Davidson]

Andrus,

>is running by superuser but it still causes the error.

That does not sound right. Please verify you are running as user postgres
with:

SELECT current_user;

Then make sure postgres is still a superuser with:

SELECT rolname as user,
   CASE WHEN rolcanlogin THEN 'user'
ELSE 'group'
   END,
   CASE WHEN rolsuper THEN 'SUPERUSER'
ELSE 'normal'
END AS super
  FROM pg_authid
WHERE rolname = 'postgres';

If you still get errors, then please show the exact error to us.


On Wed, Oct 7, 2015 at 11:11 AM, Andrus  wrote:

> Hi!
>
> No. You need to be a superuser to reassign objects unless you own the
>> object.
>> 1. first connect as user postgres
>> 2. REASSIGN all the tables owned by the missing user first.
>> 3. Then you can drop the missing user AFTER you have reassigned all the
>> objects they own.
>>
>
> Script
>
> reassign owned by farukkugay to postgres;
> drop user farukkugay ;
>
> is running by superuser but it still causes the error.
>
>
> You must also be a superuser to drop roles.
>>
>
> Non-superuser creates roles, assigns rights and drop users using scripts
> which I provided.
> Those scripts work OK on most cases.
>
> For some users, vantaa and farukkugan  delete script causes error which I
> described. For farukkugan it occurs also if running under superuser.
>
> So it looks like it should be possible for non-superusers also.
>
> Andrus.
>



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] How to drop user if objects depend on it

[Tom Lane]

"Andrus"  writes:
>> A little further review shows that DROP OWNED is the way to get rid of
>> leftover privileges.  So in general you need to do REASSIGN OWNED to move
>> the ownership of objects, then DROP OWNED to get rid of privileges granted
>> on non-owned objects, before you can drop a role.

> I tried this in database mydb using script below but still got error
> ERROR:  role "vantaa" cannot be dropped because some objects depend on it
> DETAIL:  privileges for database mydb

The example script works for me.  What PG version are you running?  I have
a vague recollection that we've fixed bugs-of-omission in DROP OWNED in
the past.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to drop user if objects depend on it

[Andrus]

A little further review shows that DROP OWNED is the way to get rid of
leftover privileges.  So in general you need to do REASSIGN OWNED to move
the ownership of objects, then DROP OWNED to get rid of privileges granted
on non-owned objects, before you can drop a role.


I tried this in database mydb using script below but still got error

ERROR:  role "vantaa" cannot be dropped because some objects depend on it
DETAIL:  privileges for database mydb

How to drop role?

Andrus.

set local role admin; -- admin is not superuser but is member of 
mydb_owner

CREATE ROLE vantaa;
grant mydb_owner to vantaa;

revoke all on all tables in schema public,firma1 from vantaa cascade;
revoke all on all sequences in schema public,firma1 from vantaa cascade;
revoke all on database mydb  from vantaa cascade;
revoke all on all functions in schema public,firma1 from vantaa cascade;
revoke all on schema public,firma1 from vantaa cascade;
revoke mydb_owner  from vantaa cascade;
ALTER ROLE vantaa inherit NOCREATEROLE NOCREATEDB NOLOGIN;

grant all on all tables in schema public,firma1 to vantaa;
grant all on all sequences in schema public,firma1 to vantaa;
grant all on database mydb  to vantaa;
grant all on schema public,firma1 to vantaa;
ALTER DEFAULT PRIVILEGES IN SCHEMA public,firma1  GRANT all ON TABLES TO
vantaa;
revoke all on  kasutaja,kaspriv,logifail from vantaa cascade;
grant select on kaspriv,kasutaja to vantaa;
grant update (eesnimi, nimi,email,amet,islocked,telefon,language,vabakuup)
on kasutaja to vantaa;
grant insert on logifail to vantaa;

GRANT vantaa TO admin;
reassign owned by vantaa to mydb_owner;
drop owned by vantaa;
drop user vantaa; 




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to drop user if objects depend on it

[Tom Lane]

I wrote:
> "Andrus"  writes:
>> ALTER DEFAULT PRIVILEGES IN SCHEMA public,firma1  GRANT all ON TABLES TO 
>> vantaa;

> I am not sure that REASSIGN OWNED will get rid of default-privilege
> specifiers --- you might have to reverse this step separately.

A little further review shows that DROP OWNED is the way to get rid of
leftover privileges.  So in general you need to do REASSIGN OWNED to move
the ownership of objects, then DROP OWNED to get rid of privileges granted
on non-owned objects, before you can drop a role.

This is documented, but only in passing in the REASSIGN OWNED man page.
I think it needs to be explained more prominently.  Will see about making
that happen.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to drop user if objects depend on it

[Andrus]

ALTER DEFAULT PRIVILEGES IN SCHEMA public,firma1  GRANT all ON TABLES TO
vantaa;

I am not sure that REASSIGN OWNED will get rid of default-privilege
specifiers --- you might have to reverse this step separately.
In general, REASSIGN OWNED has to be done by a role that has privileges
of (is a member of) both the source and target roles.  Superusers are
considered members of all roles, so that's how come it works for them.


I tried as superuser:

reassign owned by farukkugay to postgres;
ALTER DEFAULT PRIVILEGES IN SCHEMA public,firma2 revoke all ON TABLES from 
farukkugay;

drop user farukkugay ;

but got error

ERROR: role "farukkugay" cannot be dropped because some objects depend on it
SQL state: 2BP01
Detail: privileges for schema public

How to to delete user ?

Andrus. 




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to drop user if objects depend on it

[Adrian Klaver]

On 10/07/2015 09:50 AM, Andrus wrote:

ALTER DEFAULT PRIVILEGES IN SCHEMA public,firma1  GRANT all ON TABLES TO
vantaa;

I am not sure that REASSIGN OWNED will get rid of default-privilege
specifiers --- you might have to reverse this step separately.
In general, REASSIGN OWNED has to be done by a role that has privileges
of (is a member of) both the source and target roles.  Superusers are
considered members of all roles, so that's how come it works for them.


I tried as superuser:

reassign owned by farukkugay to postgres;
ALTER DEFAULT PRIVILEGES IN SCHEMA public,firma2 revoke all ON TABLES
from farukkugay;
drop user farukkugay ;

but got error

ERROR: role "farukkugay" cannot be dropped because some objects depend
on it
SQL state: 2BP01
Detail: privileges for schema public



Above you revoked DEFAULT PRIVILEGES which applies to objects created in 
future. You still probably have PRIVILEGES assigned to farukkugay on the 
public schema. So in psql do:


\dn+ public



How to to delete user ?

Andrus.



--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general