Postgres Role Change Guide

PG_ROLE_CHANGE_GUIDE.md

Migrating from one role to another can be very confusing in Postgres. This is a personal guide with a collection of queries to help aid in this effort.

Definitions

• old_role -- Refers to a postgres role that currently owns objects.
• new_role -- Refers to a postgres role that will soon own previous objects.
• postgres -- Refers to the admin user role.
• role_database -- Refers to a database that the old_role currently owns but will soon be owned by new_role

Some useful queries

See all the tables a given role in a db owns:

SELECT
    schemaname,
    tablename,
    tableowner
FROM
    pg_tables
WHERE
    tableowner = '<role>';

Create the new_role

Using the postgres user, create the new role

CREATE ROLE "<NEW_ROLE>" WITH LOGIN PASSWORD '<ROLE_PASSWORD>';
GRANT CONNECT ON DATABASE "<ROLE_DATABASE>" TO "<NEW_ROLE>";

Using the postgres user, grant permissions on the public schema to the new_role:

"GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "<NEW_ROLE>";"
"GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO "<NEW_ROLE>";"
"ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES TO "<NEW_ROLE>";"
"ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON SEQUENCES TO "<NEW_ROLE>";"

Transfer permissions and ownership from old_role to new_role

Using the postgres user, you can transfer ownership to the new role:

  GRANT "<OLD_ROLE>" TO "<NEW_ROLE>";
  
  -- transfer ownership of any objects
  REASSIGN OWNED BY "<OLD_ROLE>" TO "<NEW_ROLE>";
  
  -- transfer ownership of any tables
  ALTER TABLE public."<TABLE_NAME>" OWNER TO "<NEW_ROLE>";