Error role cannot be dropped because some objects depend on it detail privileges for database

Issue: Cannot drop PostgreSQL role. Error: cannot be dropped because some objects depend on it myd...

Cover image for How To Drop A Postgres Role/User With privileges

🚀 Vu Dao 🚀

Issue: Cannot drop PostgreSQL role. Error: cannot be dropped because some objects depend on it 

mydatabase=# drop user jack;
ERROR:  role "jack" cannot be dropped because some objects depend on it
DETAIL:  owner of view jacktest_view
owner of table jacktest
privileges for default privileges on new relations belonging to role postgres in schema public

postgres=# drop user jack;
ERROR:  role "jack" cannot be dropped because some objects depend on it
DETAIL:  3 objects in database mydatabase

Enter fullscreen mode

Exit fullscreen mode

Alt Text

1. Before going to solution, list all privileges of a role (grantee) 

mydatabase=# SELECT grantor, grantee, table_schema, table_name, privilege_type FROM information_schema.table_privileges WHERE grantee = 'jack';
 grantor | grantee | table_schema |  table_name   | privilege_type 
---------+---------+--------------+---------------+----------------
 jack    | jack    | public       | jacktest      | TRUNCATE
 jack    | jack    | public       | jacktest      | REFERENCES
 jack    | jack    | public       | jacktest      | TRIGGER
 jack    | jack    | public       | jacktest_view | TRUNCATE
 jack    | jack    | public       | jacktest_view | REFERENCES
 jack    | jack    | public       | jacktest_view | TRIGGER
(6 rows)

mydatabase=# ddp+                                                                                                                                                                                                                                                           
            Default access privileges                                                                                                                                                                                                                                                     
  Owner   | Schema | Type  |  Access privileges                                                                                                                                                                                                                                           
----------+--------+-------+---------------------                                                                                                                                                                                                                                         
 postgres | public | table | readonly=r/postgres+                                                                                                                                                                                                                                         
          |        |       | jack=arwd/postgres                                                                                                                                                                                                                                           
(1 row)

Enter fullscreen mode

Exit fullscreen mode

2. Quick way to drop the user

  • On default database 
postgres=# REASSIGN OWNED BY jack TO postgres;
postgres=# DROP OWNED BY jack;

Enter fullscreen mode

Exit fullscreen mode

  • Repeat above step on the database which is showed about DETAIL: 3 objects in database mydatabase 
mydatabase=# REASSIGN OWNED BY jack TO postgres;
mydatabase=# DROP OWNED BY jack;
mydatabase=# DROP USER jack;

Enter fullscreen mode

Exit fullscreen mode

3. Another way: REVOKE all privileges in the privilege_type list 

postgres=# REVOKE TRUNCATE, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA public FROM jack;

Enter fullscreen mode

Exit fullscreen mode

Hey 😍

Want to help the DEV Community feel more like a community?

Head over to the Welcome Thread and greet some new community members!

It only takes a minute of your time, and goes a long way!

Источник

Why can’t I drop a user or role in my RDS for PostgreSQL DB instance?

Last updated: 2022-07-07

When I try to drop a user or role in my Amazon Relational Database Service (Amazon RDS) for PostgreSQL instance, I get the error «role cannot be dropped because some objects depend on it».

Short description

When a user or role in RDS for PostgreSQL creates an object, such as a table or schema, the user or role is the owner of the object created. If you try to drop a user or role that owns one or more objects in any database or has privileges on these objects, then you receive an error indicating that there are objects that depend on the user or role along with granted permissions, if there are any.

To drop a user or role that has dependent objects, you must do the following:

  1. Reassign the ownership of these objects to another user.
  2. Revoke any permissions that were granted to the user or role.

Note: If these objects are no longer needed, consider dropping these objects and then deleting the role. You can drop all objects that are owned by a role in a database using the DROP OWNED command. You can also revoke any privileges granted to the role on objects in that database or shared objects. After the DROP OWNED command runs successfully, you can drop the role.

Resolution

In the following example, three different database roles are used:

  • test_user: This is the user or role that must be dropped.
  • admin_user: This is the role that’s used to drop the required user or role. This user is the highest privileged user in RDS with the rds_superuser role attached to it.
  • another_user: This is the user or role that’s assigned ownership of objects owned by test_user.

Run the following command to see the role with which you logged in:

pg_example=> SELECT current_user;

The output looks similar to the following:

current_user
--------------
 admin_user
(1 row)

When you try to drop a user or role with dependent objects, you get an error similar to the following:

pg_example=> DROP ROLE test_user;
ERROR:  role "test_user" cannot be dropped because some objects depend on it
DETAIL:  privileges for database pg_example
owner of table test_table
owner of schema test_schema
owner of sequence test_schema.test_seq
privileges for table test_t2

In this example, the role being dropped is test_user. Note that the role that’s currently logged in is admin_user, which is the master user of the database.

From the error message, you get the following information:

  • The role test_user has privileges granted on the database pg_example and table test_t2.
  • The role test_user owns the table test_table, schema test_schema, and a sequence object test_seq in test_schema.

Note: if you drop a user or role when you’re connected to a different database, then you get an output similar to the following:

pg_another_db=> DROP ROLE test_user;
ERROR:  role "test_user" cannot be dropped because some objects depend on it
DETAIL:  privileges for database pg_example
4 objects in database pg_example

To see objects that are owned by a user or role, be sure to connect to the database where the owned objects are located.

To drop the user or role, you must reassign the ownership of the owned objects to another user or role and revoke associated permissions. You can use the PostgreSQL REASSIGN OWNED command to reassign the ownership of these objects to another user. When running this command, you might get an error similar to the following:

pg_example=> select current_user;
 current_user
--------------
 test_user
pg_example=> REASSIGN OWNED BY test_user TO another_user;
ERROR:  permission denied to reassign objects

To resolve this issue, you must grant the user or role to the user that’s reassigning ownership. You can’t use test_user to do so because test_user isn’t the owner of another_user. Therefore, you might see an error similar to the following:

pg_example=> select current_user;
 current_user
--------------
 test_user
pg_example=> grant another_user to test_user;
ERROR:  must have admin option on role "another_user"

You can do either of the following to grant the user or role to the user that’s reassigning ownership:

  • Sign in to your master user and run the GRANT command:
pg_example=> select current_user;
 current_user
--------------
 admin_user
pg_example=> GRANT another_user TO test_user;
GRANT ROLE

  • Sign in to the user that will reassign ownership and run the GRANT command:
pg_example=> select current_user;
 current_user
--------------
 another_user
pg_example=> GRANT another_user TO test_user;
GRANT ROLE

After choosing one of the preceding options, reassign the ownership of objects owned by test_user to another_user after logging in to test_user:

pg_example=> select current_user;
 current_user
--------------
 test_user
pg_example=> reassign owned by test_user to another_user;
REASSIGN OWNED

If you sign in to your master user and attempt to drop test_user that still has existing privileges, then you might error similar to the following:

pg_example=> select current_user;
 current_user
--------------
 admin_user
pg_example=> DROP ROLE test_user;
ERROR:  role "test_user" cannot be dropped because some objects depend on it
DETAIL:  privileges for database pg_example
privileges for table test_t2

In this case, you get an error even though the REASSIGN command is successful. This is because, the privileges of test_user must be revoked. Run the REVOKE command to revoke all usage permissions from any object on which test_user has privileges. In this example, revoke the permissions on the database pg_example and table test_t2 for test_user.

pg_example=> REVOKE ALL ON TABLE test_t2 FROM test_user;
REVOKE
pg_example=> REVOKE ALL ON DATABASE pg_example FROM test_user;
REVOKE

Then, drop the user test_user:

pg_example=> DROP ROLE test_user;
DROP ROLE

After revoking the privileges, you can successfully drop the role.

Did this article help?

Do you need billing or technical support?

AWS support for Internet Explorer ends on 07/31/2022. Supported browsers are Chrome, Firefox, Edge, and Safari.
Learn more »

Источник

Skip to content

We are going to see the solution for “PostgreSQL ERROR: role cannot be dropped because some objects depend on it”

ERROR:  role "james" cannot be dropped because some objects depend on it
DETAIL:  owner of database emp
6 objects in database r2schools
1 object in database postgres
3 objects in database newdb
3 objects in database emp

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

Solution:

A role cannot be dropped or removed if it is still referenced in any database or database objects.

Before dropping the role, we have reassign their ownership to other user. Revoke any privileges the role has been granted on other objects.

Steps to solve this issue:

1. Assign role permissions to another role(user) as shown below:

c r2schools
reassign owned by james to mike;
This step has to be performed in all databases where user owns any objects.

2. In each database revoke any permissions after revoking ownerships.

revoke all on table_name from user_name;

All revoke operations are performed as below:

postgres=# drop user james;
ERROR:  role "james" cannot be dropped because some objects depend on it
DETAIL:  1 object in database lonprod2
3 objects in database newdb
3 objects in database emp
postgres=# drop user james;
ERROR:  role "james" cannot be dropped because some objects depend on it
DETAIL:  1 object in database lonprod2
1 object in database newdb
1 object in database emp
postgres=# c lonprod2
You are now connected to database "lonprod2" as user "postgres".
lonprod2=# revoke all on test2 from james;
REVOKE
lonprod2=# drop user james;
ERROR:  role "james" cannot be dropped because some objects depend on it
DETAIL:  privileges for table xyz
1 object in database newdb
lonprod2=# revoke all on xyz from james;
REVOKE
lonprod2=# c newdb
You are now connected to database "newdb" as user "postgres".
newdb=# drop user james;
ERROR:  role "james" cannot be dropped because some objects depend on it
DETAIL:  privileges for table rr
newdb=# revoke all on rr from james;
REVOKE

3. Now Perform drop user query:

drop user james;

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

Источник

Try to drop a role/user in PostgreSQL, and get below errors:

postgres=>drop role testuser_read;
ERROR: role "testuser_read" cannot be dropped because some objects depend on it
DETAIL: privileges for table user1.tab1
privileges for table table user1.tab1
privileges for sequence user1.seq1
...
..
.

SOLUTION

1)Revoke objects privileges from this role/user which is to be dropped:

postgres=> select grantee,table_schema,table_name,privilege_type
            from information_schema.role_table_grants
           where grantee='testuser_read' 
           order by table_name,privilege_type;
grantee    | table_schema | table_name | privilege_type
-----------+--------------+------------+----------------
testuser_read |    user1     | tab1       | SELECT
testuser_read |    user1     | tab2       | SELECT
...
..
.

postgres=> select 'revoke '||privilege_type ||' on '||
           table_schema||'.'||table_name||' from '||grantee||' ;'
from information_schema.role_table_grants
where grantee='testuser_read' ;

-----------------------------------------------------------------------------
 revoke SELECT on user1.tab1 from testuser_read ;
 revoke SELECT on user1.tab2 from testuser_read ;
...
..
.

OR just ...

postgres=>REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA user1 
          FROM testuser_read; 

postgres=>REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA user1
          FROM testuser_read; 

postgres=>alter default privileges in schema testuser revoke select  
          on tables from testuser_read;

postgres=>alter default privileges in schema testuser revoke select ,  
          usage on sequences from testuser_read;

2) Revoke depended roles.

du to find dependent roles

postgres=>du
postgres=>revoke other_role from testuser_read;

3) Drop the role.

postgres=> drop role testuser_read;
ERROR:  role "testuser_read" cannot be dropped because some objects depend on it
DETAIL:  privileges for default privileges on new sequences belonging to role testuser in schema testuser
privileges for default privileges on new relations belonging to role tetsuser in schema testuser

postgres=> ALTER DEFAULT PRIVILEGES FOR ROLE testuser IN SCHEMA testuser REVOKE ALL ON TABLES FROM testuser_read;
ALTER DEFAULT PRIVILEGES
postgres=> ALTER DEFAULT PRIVILEGES FOR ROLE testuser IN SCHEMA testuser REVOKE ALL ON SEQUENCES FROM testuser_read;
ALTER DEFAULT PRIVILEGES
postgres=> drop role testuser_read;
DROP ROLE
postgres=>

Источник

Summary: in this tutorial, you will learn how to use the PostgreSQL DROP ROLE statement to remove a role.

Introduction to PostgreSQL DROP ROLE statement

To remove a specified role, you use the DROP ROLE statement:

DROP ROLE [IF EXISTS] target_role;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

  • Specify the name of the role that you want to remove after the DROP ROLE keywords.
  • Use the IF EXISTS option if you want PostgreSQL to issue a notice instead of an error when you remove a role that does not exist.

To remove a superuser role, you need to be a superuser. To drop non-superuser roles, you need to have the CREATEROLE privilege.

When you remove a role referenced in any database, PostgreSQL will raise an error. In this case, you have to take two steps:

  • First, either remove the database objects owned by the role using the DROP OWNED statement or reassign the ownership of the database objects to another role REASSIGN OWNED.
  • Second, revoke any permissions granted to the role.

The REASSIGN OWNED statement reassigns the ownership of all dependent objects of a target role to another role. Because the REASSIGN OWNED statement can only access objects in the current database, you need to execute this statement in each database that contains objects owned by the target role.

After transferring the ownerships of objects to another role, you need to drop any remaining objects owned by the target role by executing the DROP OWNED statement in each database that contains objects owned by the target role.

In other words, you should execute the following statements in sequence to drop a role:

-- execute these statements in the database that contains
-- the object owned by the target role
REASSIGN OWNED BY target_role TO another_role;
DROP OWNED BY target_role;

-- drop the role
DROP ROLE target_role;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Let’s see the following example.

PostgreSQL DROP ROLE example

In this example:

  • First, we will create a new role called alice and use this role to create a table named customers.
  • Then, we will show you step by step how to remove the role alice from the PostgreSQL database server.

We’ll use the psql tool. However, you can use any client tool of your choice.

Step 1. Setting a new role and database

First, login to PostgreSQL using the postgres role:

psql -U postgres
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, create a new role called alice:

postgres=# create role alice with login password 'Abcd1234';
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Third, grant createdb privilege to alice:

postgres=# alter role alice createdb;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Fourth, create a new database called sales:

postgres=# create database sales;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Exit the current session:

postgres=# q
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Step 2. Using the new role to create database objects

First, login to the PostgreSQL database server using the alice role:

psql -U alice -W sales
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, create a new table in the sales database:

create table customers(
    customer_id int generated always as identity,
    customer_name varchar(150) not null,
    primary key(customer_id)
);
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Third, show the table list in the sales database:

sales=> dt
        List of relations
Schema |   Name    | Type  | Owner
--------+-----------+-------+-------
public | customers | table | alice
(1 row)

Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Finally, quit the current session:

postgres=# q

Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Step 3. Removing the role alice

First, login to the PostgreSQL database server using the postgres role:

psql -U postgres

Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, attempt to drop the role alice:

postgres=# drop role alice;

Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

PostgreSQL issued the following error:

Error:

ERROR:  role "alice" cannot be dropped because some objects depend on it
DETAIL:  2 objects in database sales

Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The role alice cannot be dropped because it has dependent objects.

Third, switch to the sales database:

postgres=# c sales

Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Fourth, reassign owned objects of alice to postgres:

sales=# reassign owned by alice to postgres;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Fifth, drop owned objects by alice:

sales=# drop owned by alice;





  



                       
  




Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Sixth, drop the role alice:

sales=# drop role alice;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Seventh, list the current roles:

sales=#du
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

You will see that the role alice has been removed.

Finally, quit the current session:

sales=#q
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary

  • Use the PostgreSQL DROP ROLE statement to remove a specified role.
  • If a role has dependent objects, use the REASSIGN OWNED and DROP OWNED statements in sequence to remove dependent objects of the role before executing the DROP ROLE statement.

Was this tutorial helpful ?

Источник

Понравилась статья? Поделить с друзьями:
  • Error rockstar games social club was not detected при установке патча
  • Error roblox wiki
  • Error rlink32 too many resources to handle
  • Error rlink32 out of memory
  • Error rlink32 error writing file