August 20, 2019
I was trying to modify a table today when I hit an error:
SQL Error : ERROR: must be owner of table XXX.
To figure out who the owner was so that I could ask them to modify the table on my behalf, I started searching and found a blog post addressing exactly my use case written by KCully six years ago. 1
SELECT t.table_name, t.table_type, c.relname, c.relowner, u.usename FROM information_schema.tables t JOIN pg_catalog.pg_class c ON (t.table_name = c.relname) JOIN pg_catalog.pg_user u ON (c.relowner = u.usesysid) WHERE t.table_schema=‘public’;
The internet’s a beautiful place.
The results, however, were a little bit peculiar and while the table I was interested was displayed, many of the others in our database were not present.
The issue was that joins.
Interestingly, the solution was quite simple:
select tablename, tableowner from pg_catalog.pg_tables where schemaname = ‘public’ ;
All of the schemas in our db are the default
public, so to eliminate some of the tables Postgres provides, I included that filter. Other than that - the details I needed were present in the
table owner column.
Note, if I wanted to look up the owner for a single table, I could add that condition to the query or by using psql in the terminal, I could use:
\dt <table name>
postgres=> \dt metadata_rules List of relations Schema | Name | Type | Owner --------+----------------+-------+--------- public | metadata_rules | table | fmalone (1 row)
Thanks for reading! My name's Stephen Weiss. I live in Chicago with my wife, Kate, and dog, Finn.
Click here to see the archives of my weeks in review and sign up yourself!