List all objects owned by a user across all databases

The purpose of this article is to provide a function to display all objects owned by a user in all databases in the Postgres cluster.

1. Create the function included below in any database

Function only needs to be created in a single database. The function has a single parameter, the user whose objects you want listed. If the parameter passed is 'h' or 'H', an explanation of how to execute the function is provided.

2. Execute the function

The execution is a series of commands, psql and sed, to produce the output. The list of instructions is included below, and again is available by executing the function with a paramter of 'h'.

Execute the function, redirecting output to a file

psql -d <DB> -t -q -F ' ' -c "SELECT * FROM List_Object_Ownership('username')" -o COO.out

Remove trailing output from the output file

sed -ie 's/--.*$//g' COO.out

Execute the file as input to psql, to generate the list of objects

psql -d <DB> -f COO.out -t -q -F ' ' -o COO.cmds

Reformat the output file, adding indicator for each database and removing blank lines

sed -ie 's/\\c/Database: /g' COO.cmds 
grep -v -e '^$' COO.cmds > List_Objects.txt

The file List_Objects.txt has the full list of objects owned by the user Sorted by Database and then Type

For example:

| Db/user | Object | Name | 
| Database | second_db | | 
| testuser | table | public.users |
| Database | testdb | | 
| testuser | table | public.first | 
| testuser | table | sch1.first |
| testuser | index | sch1.first_uniq | 
| testuser | view | sch1.first_view | 
| testuser | materialized view | sch1.first_mv |
| testuser | sequence | public.seq |
| testuser | sequence | sch1.schema1_seq |
| testuser | synonym | public.sch1first |
| testuser | function | sch1.square |
| testuser | schema1 | sch1 |

Function Definition:

CREATE OR REPLACE function List_Object_Ownership(original_user_name varchar(255))
RETURNS TABLE (
the_command varchar(255),
the_db varchar(255),
seqnum int 
) 
-- This procedure creates a report showing all objects owned in all databases by the specified user 
-- If the original/source user does not exist an error is displayed and the function terminates
--
--if invoked with first parameter of 'h', displays purpose and how to execute the job
language plpgsql
as $$
DECLARE old_user_count int=0;
DECLARE error_flag int=0;
DECLARE test_user varchar(255);
BEGIN
--
-- check to see if user asking for help
if (original_user_name = 'h' or original_user_name = 'H')
then
raise notice ' ';
raise notice ' This function is used to list all the objects owned by the specified user in all databases';
raise notice ' ';
raise notice ' ';
raise notice 'format: List_Object_Ownership (''username'')';
raise notice 'Full execution steps:';
raise notice ' ';
raise notice 'psql -d <DB> -t -q -F '' '' -c "select * from List_Object_Ownership(''username'')" -o COO.out';
raise notice ' Remove extraneous information from the output file:';
raise notice 'sed -ie ''s/--.*$//g'' COO.out ';
raise notice ' Run this thru the database to generate the list of objects commands:';
raise notice 'psql -d <DB> -f COO.out -t -q -F '' '' -o COO.cmds';
raise notice ' The use sed and grep to reformat';
raise notice 'sed -ie ''s/\\c/Database: /g'' COO.cmds';
raise notice 'grep -v -e ''^$'' COO.cmds > List_Objects.txt ';
raise notice ' ';
raise notice ' The file List_Objects has the full list of objects owned by the user';
raise notice ' Sorted by Database and then Type';
raise notice ' ';
raise notice 'Sample Output:';
raise notice ' DATABASE: second_db; ';
raise notice ' owner: testuser table: public.users';
raise notice ' DATABASE: testdb; ';
raise notice ' owner: testuser table: public.first';
raise notice ' owner: testuser table: sch1.first';
raise notice ' owner: testuser index: sch1.first_uniq';
raise notice ' owner: testuser view: sch1.first_view';
raise notice ' owner: testuser materialized view: sch1.first_mv';
raise notice ' owner: testuser sequence: public.seq';
raise notice ' owner: testuser sequence: sch1.schema1_seq';
raise notice ' owner: testuser synonym: public.sch1first';
raise notice ' owner: testuser function: sch1.square';
raise notice ' owner: testuser schema: sch1';
raise notice '';
error_flag = 1;
end if;

-- create temporary objects
CREATE temporary sequence _myseq;
CREATE temporary table _database_names (the_db varchar(255));
CREATE temporary table _user (usename varchar(255), usesysid int);
CREATE temporary table _the_command(seqnum int, the_db varchar(255), command varchar(255));
CREATE temporary table _old_user_table( the_user varchar(255));
--
INSERT INTO _database_names SELECT datname FROM pg_database WHERE datallowconn='t' AND datistemplate='f';
INSERT INTO _user SELECT usename, usesysid FROM pg_user WHERE usename = original_user_name;

SELECT count(*) FROM _user into old_user_count;
IF (old_user_count = 0) AND error_flag = 0
THEN
raise notice 'The user specified as owning objects % does not exist in the database', original_user_name;
error_flag = 1;
END IF;

INSERT INTO _old_user_table values (original_user_name);
IF error_flag =0
THEN
-- build up the commands and insert them into the_table
-- dbname and sequence number used later to ensure that
-- the \c command is issued before any dml executed against the database
--
INSERT INTO _the_command
SELECT nextval('_myseq') , db.the_db , '\c ' || db.the_db ||'; --''' 
FROM _database_names db
UNION
SELECT nextval('_myseq'), db.the_db, 'select ''' || '\c ' || db.the_db || '; ''; --''' 
FROM _database_names db
UNION 
SELECT nextval('_myseq'), db.the_db,
'select '' owner: ' || (select the_user from _old_user_table) 
|| ' table: '' ||' || ' namespace.nspname' || ' || ''.''||' || 'class.relname'
|| ' from pg_class class, pg_namespace namespace '
||' where class.relnamespace = namespace.oid and relkind = ''r'''
|| ' and relowner = '
|| (select usesysid from _user)
|| '; --'''
FROM _database_names db
UNION 
SELECT nextval('_myseq'), db.the_db,
'select '' owner: ' || (select the_user from _old_user_table)
|| ' partitioned table: '' ||' || ' namespace.nspname' || ' || ''.''||' || 'class.relname'
|| ' from pg_class class, pg_namespace namespace '
||' where class.relnamespace = namespace.oid and relkind = ''p'''
|| ' and relowner = '
|| (select usesysid from _user)
|| '; --'''
FROM _database_names db
UNION 
SELECT nextval('_myseq'), db.the_db,
'select '' owner: ' || (select the_user from _old_user_table)
|| ' foreign table: '' ||' || ' namespace.nspname' || ' || ''.''||' || 'class.relname'
|| ' from pg_class class, pg_namespace namespace '
||' where class.relnamespace = namespace.oid and relkind = ''f'''
|| ' and relowner = '
|| (select usesysid from _user)
|| '; --'''
FROM _database_names db
UNION 
SELECT nextval('_myseq'), db.the_db,
'select '' owner: ' || (select the_user from _old_user_table)
|| ' toast table: '' ||' || ' namespace.nspname' || ' || ''.''||' || 'class.relname'
|| ' from pg_class class, pg_namespace namespace '
||' where class.relnamespace = namespace.oid and relkind = ''t'''
|| ' and relowner = '
|| (select usesysid from _user)
|| '; --'''
FROM _database_names db
UNION
SELECT nextval('_myseq'), db.the_db,
'select '' owner: ' || (select the_user from _old_user_table)
|| ' index: '' ||' || ' namespace.nspname' || ' || ''.''||' || 'class.relname'
|| ' from pg_class class, pg_namespace namespace '
||' where class.relnamespace = namespace.oid and relkind = ''i'''
|| ' and relowner = '
|| (select usesysid from _user)
|| '; --'''
FROM _database_names db
UNION
SELECT nextval('_myseq'), db.the_db,
'select '' owner: ' || (select the_user from _old_user_table)
|| ' partitioned index: '' ||' || ' namespace.nspname' || ' || ''.''||' || 'class.relname'
|| ' from pg_class class, pg_namespace namespace '
||' where class.relnamespace = namespace.oid and relkind = ''I'''
|| ' and relowner = '
|| (select usesysid from _user)
|| '; --'''
FROM _database_names db
UNION
SELECT nextval('_myseq'), db.the_db,
'select '' owner: ' || (select the_user from _old_user_table)
|| ' view: '' ||' || ' namespace.nspname' || ' || ''.''||' || 'class.relname'
|| ' from pg_class class, pg_namespace namespace '
||' where class.relnamespace = namespace.oid and relkind = ''v'''
|| ' and relowner = '
|| (select usesysid from _user)
|| '; --'''
FROM _database_names db
UNION
SELECT nextval('_myseq'), db.the_db,
'select '' owner: ' || (select the_user from _old_user_table)
|| ' materialized view: '' ||' || ' namespace.nspname' || ' || ''.''||' || 'class.relname'
|| ' from pg_class class, pg_namespace namespace '
||' where class.relnamespace = namespace.oid and relkind = ''m'''
|| ' and relowner = '
|| (select usesysid from _user)
|| '; --'''
FROM _database_names db
UNION
SELECT nextval('_myseq'), db.the_db,
'select '' owner: ' || (select the_user from _old_user_table)
|| ' sequence: '' ||' || ' namespace.nspname' || ' || ''.''||' || 'class.relname'
|| ' from pg_class class, pg_namespace namespace '
||' where class.relnamespace = namespace.oid and relkind = ''S'''
|| ' and relowner = '
|| (select usesysid from _user)
|| '; --'''
FROM _database_names db
UNION
SELECT nextval('_myseq'), db.the_db,
'select '' owner: ' || (select the_user from _old_user_table)
|| ' synonym: '' ||' || ' namespace.nspname' || ' || ''.''||' || 'synonym.synname'
|| ' from pg_synonym synonym, pg_namespace namespace '
||' where synonym.synnamespace = namespace.oid '
|| ' and synowner = '
|| (select usesysid from _user)
|| '; --'''
FROM _database_names db
UNION
SELECT nextval('_myseq'), db.the_db,
'select '' owner: ' || (select the_user from _old_user_table)
|| ' function: '' ||' || ' namespace.nspname' || ' || ''.''||' || 'function.funname'
|| ' from pg_function function, pg_namespace namespace '
||' where function.funnamespace = namespace.oid '
|| ' and function.funowner = '
|| (select usesysid from _user)
|| '; --'''
FROM _database_names db
UNION
SELECT nextval('_myseq'), db.the_db,
'select '' owner: ' || (select the_user from _old_user_table)
|| ' procedure: '' ||' || ' namespace.nspname' || ' || ''.''||' || 'procedure.proname'
|| ' from pg_procedure procedure, pg_namespace namespace '
||' where procedure.pronamespace = namespace.oid '
|| ' and procedure.proowner = '
|| (select usesysid from _user)
|| '; --'''
FROM _database_names db
UNION
SELECT nextval('_myseq'), db.the_db,
'select '' owner: ' || (select the_user from _old_user_table)
|| ' schema: '' ||' || ' namespace.nspname' 
|| ' from pg_namespace namespace '
||' where nspowner = '
|| (select usesysid from _user)
|| '; --'''
FROM _database_names db
UNION
SELECT nextval('_myseq'), db.the_db,
'select '' owner: ' || (select the_user from _old_user_table)
|| ' composite type: '' ||' || ' namespace.nspname' || ' || ''.''||' || 'class.relname'
|| ' from pg_class class, pg_namespace namespace '
||' where class.relnamespace = namespace.oid and relkind = ''c'''
|| ' and relowner = '
|| (select usesysid from _user)
|| '; --'''
FROM _database_names db
;
END IF;

RETURN QUERY SELECT tc.command, tc.the_db , tc.seqnum FROM _the_command tc order by 2,3;
END;
$$

Search

1. Create the function included below in any database

2. Execute the function

Was this article helpful?