All Products
Search

This Product

    Hologres:Delete an account

    Document Center

    Hologres:Delete an account

    Last Updated:Jul 03, 2023

    This topic describes how to delete an account in Hologres by using the DROP USER statement and how to troubleshoot the errors that may occur when you delete an account.

    Background information

    You may need to delete accounts in the daily use of Hologres. If an account that you want to delete belongs to the owner of an object such as a database, schema, table, or view, an error occurs when you delete the account.

    • The following error message is returned if the account belongs to the owner of specific objects: 

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

    • The following error message is returned if specific objects depend on the account: 

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

    • The following error message is returned if the account has permissions on specific objects: 

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

    If an error message is returned when you delete an account, specific objects still depend on the account, or the account still has permissions on specific objects. The objects include databases, schemas, views, and tables. In this case, the account cannot be directly deleted.

    Specify a cause for deleting an account

    Before you delete an account, you must specify why you want to delete the account. In most cases, you delete an account due to the following causes:

    • Cause 1: The account is granted improper permissions. You want to delete the account and create another account to grant permissions.

    • Cause 2: The owner of the account resigns or the account is no longer used.

    Recommended solutions:

    • Solution for cause 1:

      In this case, you do not need to delete the account. We recommend that you revoke the current permissions from the account and then grant new permissions to the account.

      • For more information about how to revoke the current permissions and grant new permissions by using the standard PostgreSQL authorization model, see Standard PostgreSQL authorization model.

      • For more information about how to revoke the current permissions and grant new permissions by using the simple permission model (SPM), see Use the SPM and Use the SLPM.

      • To change the role of an account from a regular user to a superuser, execute the following statement: 

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

        The uid parameter specifies the ID of the account. For more information, see Account IDs.

      • To change the role of an account from a superuser to a regular user, execute the following statement:

        Note

        After you change the role of the account from a superuser to a regular user, the user has no permissions and you need to grant new permissions to the user. 

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

        The uid parameter specifies the ID of the account. For more information, see Account IDs.

    • Solution for cause 2:

      If you need to retain the objects in the account, transfer the objects to another account. Then, you can delete the original account. For more information, see Delete an account but retain the objects in the account.

    Delete an account but retain the objects in the account

    If you want to delete an account but retain the objects in the account, you can transfer the objects to another account and then delete the original account. The objects in the account include tables, views, and functions.

    1. Execute the following statement to transfer objects from the original account to another account: 

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

      The uid parameter specifies the ID of the account. For more information, see Account IDs.

    2. Execute the following statement to delete the original account: 

      DROP USER "<uid>";

    View the objects that depend on an account

    1. Execute the following statement to view the objects that depend on an account: 

      select 'select * from ' || s.classid::regclass || ' where oid = ' || s.objid || '; (Perform a query on the '
 || d.datname || ' database)' as "Query the objects that depend on the account", case when deptype = 'a' then 'Permissions of the account' 
when deptype = 'o' then 'Owner of the account' 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. Execute the following statement to view the owners of all internal 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. Execute the following statement to view the internal tables, views, and foreign tables whose owner is a specified 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>';

      Execute the following statements to change the owners. 

      -- Execute the following statement to change the owner of an internal table:
ALTER TABLE schema_name.table_name OWNER TO new_owner;

-- Execute the following statement to change the owner of a foreign table:
ALTER FOREIGN TABLE schema_name.foreign_table_name OWNER TO new_owner;

-- Execute the following statement to change the owner of a view:
ALTER VIEW schema_name.view_name OWNER TO new_owner;

    4. Execute the following statements to view the owners of schemas.

      • Execute the following statement to 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;

      • Execute the following statement to view schemas whose owner is a specified 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>';

      • Execute the following statement to change the owner of a schema. 

        -- Execute the following statement to change the owner of a schema.
ALTER SCHEMA schema_name OWNER TO new_owner;

    5. Execute the following statements to view the owners of servers.

      • Execute the following statement to 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;

      • Execute the following statement to view the servers whose owner is a specified 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>';

      • Execute the following statement to change the owner of a server. 

        -- Execute the following statement to change the owner of a server:
ALTER SERVER server_name OWNER TO new_owner;

    6. Execute the following statements to view the owners of user mappings.

      • Execute the following statement to view 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;

      • Execute the following statement to view user mappings whose owner is a specified 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>';

      • Execute the following statement to delete a user mapping. 

        -- Execute the following statement to delete the user mapping:
DROP USER MAPPING FOR user_name SERVER server_name;