Changing ownership of all tables in PostgreSQL

How to modify the owner of all tables in a PostgreSQL Database?

In PostgreSQL, ownership of database objects, including tables, is tied to the privileges that control who can access and modify them. If you need to change the ownership of all tables in a database to another user, you can automate this process with a simple SQL command.

Description

Modifying the owner of every table in a PostgreSQL database can be achieved by running a command that iterates through each table, changing its owner to a specified user. This approach is especially useful when transferring database ownership to another team member or role.

Syntax:

The basic structure to change the owner of a table in PostgreSQL is:

ALTER TABLE table_name OWNER TO new_owner;

However, to change the owner of all tables, you need to dynamically generate this command for each table.

Example: Changing the Owner of All Tables in a Schema

Following example uses PostgreSQL’s pg_catalog.pg_tables to retrieve all table names and change the owner of each to the new specified user.

Code:

DO
$$
DECLARE
    tbl RECORD;
BEGIN
    -- Loop through each table in the specified schema
    FOR tbl IN
        SELECT tablename
        FROM pg_catalog.pg_tables
        WHERE schemaname = 'public'  -- Replace 'public' with your schema name
    LOOP
        -- Alter the owner of each table
        EXECUTE 'ALTER TABLE public.' || quote_ident(tbl.tablename) || ' OWNER TO new_owner';
        -- Replace 'public' with your schema and 'new_owner' with the new owner's name
    END LOOP;
END;
$$;

Explanation:

• DO $$ DECLARE ... BEGIN ... END; $$: Starts an anonymous code block in PostgreSQL for running PL/pgSQL statements.
• tbl RECORD;: Declares a variable tbl that will hold each table record iteratively.
• FOR tbl IN SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname = 'public': Selects each table in the specified schema (public by default).
• EXECUTE 'ALTER TABLE public.' || quote_ident(tbl.tablename) || ' OWNER TO new_owner';: Dynamically constructs and executes the ALTER TABLE command for each table to change its owner to new_owner.
• Replace public with your schema name and new_owner with the desired new owner’s name.

Full Example:

Code:

-- Begin an anonymous PL/pgSQL code block
DO
$$
-- Declare a record variable to hold table names
DECLARE
    tbl RECORD;
-- Begin the procedural block
BEGIN
    -- Loop through each table in the specified schema (e.g., 'public')
    FOR tbl IN
        -- Select all tables from the schema 'public'
        SELECT tablename
        FROM pg_catalog.pg_tables
        WHERE schemaname = 'public'  -- Change 'public' to target a different schema if needed
    LOOP
        -- Execute the ALTER TABLE command to change the owner
        EXECUTE 'ALTER TABLE public.' || quote_ident(tbl.tablename) || ' OWNER TO new_owner';
        -- Note: Replace 'public' with your schema and 'new_owner' with the new owner's username
    END LOOP;
-- End the procedural block
END;
$$; 

All PostgreSQL Questions, Answers, and Code Snippets Collection.