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.
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:
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.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4