Thursday, February 18, 2021

How to alter Postgresql table owner

 All this while I have been changing table owners individually in a specific schema. One fine day, there was a requirement to transfer all tables to another owner without changing the database ownership.

The existing tables where having owner as "postgres", but to allow other application to access, a separate user is created and allowed access. Here is how its done to assign the user;

\c mydatabase;

ALTER TABLE public.users OWNER TO contractors;

In the case where ALL tables are to have a new owner, then following can be done

select 'ALTER TABLE ' || table_name || ' OWNER TO contractors;' from information_schema.tables where table_schema = 'public' \gexec ;

No comments:

Blog Archive