All Products
Search

This Product

    Hologres:Drop a user

    Document Center

    Hologres:Drop a user

    Last Updated:Jan 06, 2026

    This article describes how to delete a Hologres user and troubleshoot related errors.

    Background

    When managing users in Hologres, you may need to delete them for some purposes. However, direct deletion of users that own objects like databases, schemas, tables, or views is not permitted and will result in an error. Common errors encountered include:

    • An error occurs if the user owns objects:

      ERROR:  role "<uid>" cannot be dropped because some objects depend on it
DETAIL:  owner of table xxx
owner of schema yyy

    • An error occurs if the user has object dependencies:

      ERROR:  role "<uid>" cannot be dropped because some objects depend on it
DETAIL:  1 object in database xxx

    • An error occurs if the user has been granted privileges on objects:

      ERROR: role "<uid>" cannot be dropped because some objects depend on it
Detail: privileges for table xxx
privileges for table yyy

    These error messages indicate the user has object dependencies or privileges that prevent you from deleting it directly.

    Determine the reason for user deletion

    Before deleting a user, it's crucial to understand the underlying reason for the deletion. Common scenarios include:

    • Incorrect authorization: The user's current permissions are wrong, and the goal is to reconfigure them correctly.

    • User no longer needed: The user has left the company, the account is inactive, or there are other business reasons necessitating its removal.

    Recommended solutions:

    • Scenario 1: Incorrect authorization

      If the reason for deletion is incorrect authorization, deleting the user account is not necessary. Instead, you can modify the user's permissions directly.

      • Revoke and grant permissions using Standard PostgreSQL authorization model.

      • Revoke and grant permissions using simple permission model and SLPM.

      • To promote a regular user to a superuser (mistakenly created as regular):

        -- For a RAM user, replace uid with p4_uid.
alter user "<uid>" superuser;

        uid: The Alibaba Cloud or RAM account ID. See Account IDs.

      • To demote a superuser to a regular user (mistakenly created as superuser):

        Note

        After you change a superuser to a regular user, they will have no permissions. You must then grant the necessary permissions.

        -- For a RAM user, replace uid with p4_uid.
alter user "<uid>" nosuperuser;

        uid: The Alibaba Cloud or RAM account ID. For more information, see Account IDs.

    • Scenario 2: User no longer needed

      If the user is being deleted for reasons such as the user no longer being with the company, and you wish to retain the objects owned by that user, you can transfer ownership of these objects to another user before deleting the user. For more information, see Delete users but retain the objects they own.

    Delete users but retain the objects they own

    To delete a user but retain the objects, transfer ownership of these objects to another user before deletion. Procedure:

    1. Transfer object ownership.

      REASSIGN OWNED BY "<uid>" TO "<Another_uid>" ;

      uid: The Alibaba Cloud or RAM account ID. For more information, see Account IDs.

    2. Delete the user.

      DROP USER "<uid>";

    Check object dependencies

    1. View the objects dependent on a specific user.

      select 'select * from ' || s.classid::regclass || ' where oid = ' || s.objid || '; (Execute in the '
 || d.datname || ' DB)' as "Query dependent objects", case when deptype = 'a' then 'Permission dependency' 
when deptype = 'o' then 'Owner dependency' else deptype::text end as "Dependency type"from pg_shdepend s 
join pg_database d on (s.dbid = d.oid) join pg_roles r on (r.oid = s.refobjid) where 
datname = current_database() and refclassid = 1260 and r.rolname = '<username>';

    2. View the owners of all tables, views, and foreign tables.

      SELECT
    n.nspname AS "Schema",
    c.relname AS "Name",
    CASE c.relkind
    WHEN 'r' THEN
        'table'
    WHEN 'v' THEN
        'view'
    WHEN 'm' THEN
        'materialized view'
    WHEN 'i' THEN
        'index'
    WHEN 'S' THEN
        'sequence'
    WHEN 's' THEN
        'special'
    WHEN 't' THEN
        'TOAST table'
    WHEN 'f' THEN
        'foreign table'
    WHEN 'p' THEN
        'partitioned table'
    WHEN 'I' THEN
        'partitioned index'
    END AS "Type",
    pg_catalog.pg_get_userbyid(c.relowner) AS "Owner",
    pg_catalog.obj_description(c.oid,'pg_class') AS "Description"
FROM
    pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE
    c.relkind IN ('r', 'p', 't', 'v', 'm', 'S', 's', 'f', '')
    AND pg_catalog.pg_table_is_visible(c.oid);

    3. View objects owned by a specific user.

      SELECT
    n.nspname AS "Schema",
    c.relname AS "Name",
    CASE c.relkind
    WHEN 'r' THEN
        'table'
    WHEN 'v' THEN
        'view'
    WHEN 'm' THEN
        'materialized view'
    WHEN 'i' THEN
        'index'
    WHEN 'S' THEN
        'sequence'
    WHEN 's' THEN
        'special'
    WHEN 't' THEN
        'TOAST table'
    WHEN 'f' THEN
        'foreign table'
    WHEN 'p' THEN
        'partitioned table'
    WHEN 'I' THEN
        'partitioned index'
    END AS "Type",
    pg_catalog.pg_get_userbyid(c.relowner) AS "Owner",
    pg_catalog.obj_description(c.oid, 'pg_class') AS "Description"
FROM
    pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE
    c.relkind IN ('r', 'p', 't', 'v', 'm', 'S', 's', 'f', '')
    AND pg_catalog.pg_table_is_visible(c.oid)
    AND pg_catalog.pg_get_userbyid(c.relowner) ='<user_name>';

      Change the object owner.

      -- For internal tables:
ALTER TABLE schema_name.table_name OWNER TO new_owner;

-- For foreign tables:
ALTER FOREIGN TABLE schema_name.foreign_table_name OWNER TO new_owner;

-- For foreign views:
ALTER VIEW schema_name.view_name OWNER TO new_owner;

    4. View and change schema owners.

      • View the owners of all schemas.

        SELECT
    n.nspname AS "Name",
    pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner",
    pg_catalog.array_to_string(n.nspacl, E'\n') AS "Access privileges",
    pg_catalog.obj_description(n.oid, 'pg_namespace') AS "Description"
FROM
    pg_catalog.pg_namespace n
ORDER BY
    1;

      • View the schemas owned by a specific user.

        SELECT
    n.nspname AS "Name",
    pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner",
    pg_catalog.array_to_string(n.nspacl, E'\n') AS "Access privileges",
    pg_catalog.obj_description(n.oid, 'pg_namespace') AS "Description"
FROM
    pg_catalog.pg_namespace n
WHERE pg_catalog.pg_get_userbyid(n.nspowner) ='<user_name>';

      • Change the owner.

        -- Change the owner of a schema.
ALTER SCHEMA schema_name OWNER TO new_owner;

    5. View and change server owners.

      • View the owners of all servers.

        SELECT
    s.srvname AS "Name",
    pg_catalog.pg_get_userbyid(s.srvowner) AS "Owner",
    f.fdwname AS "Foreign-data wrapper",
    pg_catalog.array_to_string(s.srvacl, E'\n') AS "Access privileges",
    s.srvtype AS "Type",
    s.srvversion AS "Version",
    CASE WHEN srvoptions IS NULL THEN
        ''
    ELSE
        '(' || pg_catalog.array_to_string(ARRAY (
                SELECT
                    pg_catalog.quote_ident(option_name) || ' ' || pg_catalog.quote_literal(option_value)
                FROM pg_catalog.pg_options_to_table(srvoptions)), ', ') || ')'
    END AS "FDW options",
    d.description AS "Description"
FROM
    pg_catalog.pg_foreign_server s
    JOIN pg_catalog.pg_foreign_data_wrapper f ON f.oid = s.srvfdw
    LEFT JOIN pg_catalog.pg_description d ON d.classoid = s.tableoid
        AND d.objoid = s.oid
        AND d.objsubid = 0;

      • View the servers owned by a specific user.

        SELECT
    s.srvname AS "Name",
    pg_catalog.pg_get_userbyid(s.srvowner) AS "Owner",
    f.fdwname AS "Foreign-data wrapper",
    pg_catalog.array_to_string(s.srvacl, E'\n') AS "Access privileges",
    s.srvtype AS "Type",
    s.srvversion AS "Version",
    CASE WHEN srvoptions IS NULL THEN
        ''
    ELSE
        '(' || pg_catalog.array_to_string(ARRAY (
                SELECT
                    pg_catalog.quote_ident(option_name) || ' ' || pg_catalog.quote_literal(option_value)
                FROM pg_catalog.pg_options_to_table(srvoptions)), ', ') || ')'
    END AS "FDW options",
    d.description AS "Description"
FROM
    pg_catalog.pg_foreign_server s
    JOIN pg_catalog.pg_foreign_data_wrapper f ON f.oid = s.srvfdw
    LEFT JOIN pg_catalog.pg_description d ON d.classoid = s.tableoid
        AND d.objoid = s.oid
        AND d.objsubid = 0
WHERE pg_catalog.pg_get_userbyid(s.srvowner) = '<user_name>';

      • Change the owner.

        -- Change the owner of a server.
ALTER SERVER server_name OWNER TO new_owner;

    6. Check owners of user mappings.

      • View the owners of all user mappings.

        SELECT
    um.srvname AS "Server",
    um.usename AS "User name",
    CASE WHEN umoptions IS NULL THEN
        ''
    ELSE
        '(' || pg_catalog.array_to_string(ARRAY (
                SELECT
                    pg_catalog.quote_ident(option_name) || ' ' || pg_catalog.quote_literal(option_value)
                FROM pg_catalog.pg_options_to_table(umoptions)), ', ') || ')'
    END AS "FDW options"
FROM
    pg_catalog.pg_user_mappings um;

      • View the user owning a specific user mapping.

        SELECT
    um.srvname AS "Server",
    um.usename AS "User name",
    CASE WHEN umoptions IS NULL THEN
        ''
    ELSE
        '(' || pg_catalog.array_to_string(ARRAY (
                SELECT
                    pg_catalog.quote_ident(option_name) || ' ' || pg_catalog.quote_literal(option_value)
                FROM pg_catalog.pg_options_to_table(umoptions)), ', ') || ')'
    END AS "FDW options"
FROM
    pg_catalog.pg_user_mappings um
WHERE um.usename = '<user_name>';

      • Delete a user mapping.

        -- Delete the user mapping.
DROP USER MAPPING FOR user_name SERVER server_name;