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.
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://www.w3resource.com/PostgreSQL/snippets/change-owner-all-tables-postgresql.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics