All Products
Search

This Product

    Hologres:Delete account

    Document Center

    Hologres:Delete account

    Last Updated:Feb 04, 2026

    This topic describes how to delete a user in Hologres and how to troubleshoot errors that may occur during the process.

    Background information

    You may need to delete a user for business reasons. In Hologres, an error occurs if you attempt to delete a user that owns any database (DB) objects, such as databases, schemas, tables, or views. Common errors include the following:

    • The user cannot be deleted because they own one or more objects. The following error is returned:

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

    • The user cannot be deleted because other objects depend on them. The following error is returned:

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

    • The user cannot be deleted because they have been granted permissions. The following error is returned:

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

    These errors occur because the user still has dependent objects or permissions within the instance. Therefore, you cannot directly delete the user.

    Identify the reason for deleting the user

    Before you delete a user, identify the reason. Common reasons include the following:

    • Reason 1: The user was granted incorrect permissions, and you want to delete the user to grant the correct permissions.

    • Reason 2: The user must be deleted for business reasons, such as when an employee leaves the company or the user account is no longer in use.

    Recommended solutions:

    • The reason for deleting the account is Reason 1.

      You do not need to delete the user in this case. Instead, you can revoke the user's current permissions and then grant new permissions.

      • To revoke and grant permissions in the standard PostgreSQL authorization model, see Standard PostgreSQL authorization model.

      • To revoke and grant permissions in the simple permission model, see Use the simple permission model and Use the schema-level simple permission model.

      • If a user was incorrectly set as a regular user and you want to change them to a Superuser, execute the following statement:

        -- If the user is a RAM user, change uid to p4_id.
alter user "<uid>" superuser;

        uid specifies the user ID. For more information, see User ID.

      • If a user was incorrectly set as a Superuser and you want to change them to a regular user, execute the following statement:

        Note

        After you change a Superuser to a regular user, the user has no permissions. You must grant the required permissions to the user.

        -- If the user is a RAM user, change uid to p4_id.
alter user "<uid>" nosuperuser;

        uid specifies the user ID. For more information, see User ID.

    • The reason for deleting the account is Reason 2.

      You can keep the objects owned by the user by transferring the ownership of the objects to another user and then deleting the original user. For more information, see Delete a user but keep the objects owned by the user.

    Delete a user but keep the objects owned by the user

    If you must delete a user but want to keep the objects they own, such as tables, views, and functions, you can transfer the ownership of these objects to another user and then delete the original user. The command syntax is as follows:

    1. Transfer the ownership of the objects to another user.

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

      uid specifies the user ID. For more information, see User ID.

    2. Delete the account.

      DROP USER "<uid>";

    View user dependencies

    1. Execute the following statement to view the dependent objects of a user.

      select 'select * from ' || s.classid::regclass || ' where oid = ' || s.objid || '; (Execute in the '
 || d.datname || ' DB)' as "Query for 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 the tables, views, and foreign tables 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 owner.

      -- Change the owner of a table.
ALTER TABLE schema_name.table_name OWNER TO new_owner;

-- Change the owner of a foreign table.
ALTER FOREIGN TABLE schema_name.foreign_table_name OWNER TO new_owner;

-- Change the owner of a view.
ALTER VIEW schema_name.view_name OWNER TO new_owner;

    4. View the owner of a schema.

      • 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 the owner of a server.

      • 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. View the owner of a user mapping.

      • 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 mappings owned by a specific user.

        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 the user mapping.

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