Skip to content

Instantly share code, notes, and snippets.

@omikolaj
Forked from checco/postgres-owner-grants.sql
Created April 3, 2022 23:45
Show Gist options
  • Save omikolaj/8d751b08a6ce36bb62008c616e4ad90c to your computer and use it in GitHub Desktop.
Save omikolaj/8d751b08a6ce36bb62008c616e4ad90c to your computer and use it in GitHub Desktop.
How to change OWNER for DATABASE, SCHEMA or TABLE on AWS RDS PostgreSQL. An how to REASSIGN owner for all objects on AWS RDS PostgreSQL
--
-- Change database owner
--
ALTER DATABASE "db_name" OWNER TO user;
--
-- List schemas
--
-- db_name=> \dn
-- List of schemas
-- Name | Owner
-- --------------------+-------------------------
-- public | other_user
-- other_schema | other_user
--
-- Change schema owner
--
ALTER SCHEMA other_schema OWNER TO user;
--
-- Check schema changes
--
-- db_name=> \dn
-- List of schemas
-- Name | Owner
-- --------------------+-------------------------
-- public | other_user
-- other_schema | user
--
-- List schema tables
--
-- db_name=> \dt other_schema.*
-- List of relations
-- Schema | Name | Type | Owner
-- --------------+--------+-------+----------------
-- other_schema | table1 | table | other_user
-- other_schema | table2 | table | user
-- other_schema | table3 | table | other_user1
--
-- Change table owner
--
ALTER TABLE other_schema.table1 OWNER TO user;
--
-- Check schema table changes
--
-- db_name=> \dt other_schema.*
-- List of relations
-- Schema | Name | Type | Owner
-- --------------+--------+-------+----------------
-- other_schema | table1 | table | user
-- other_schema | table2 | table | user
-- other_schema | table3 | table | other_user1
--
-- Change the ownership of database objects owned by a database role
-- It will change the ownership only for the current database, not for all instance databases
--
REASSIGN OWNED BY other_user TO user;
-- or you can change more role at once
REASSIGN OWNED BY other_user, other_user1 TO user;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment