Postgres Table Ownership

Question

I'm new to PostgreSQL and I'm trying to change the table owner for a bunch of tables. I was able to change the ownership by logging in a the postgres user and executing alter table owner to user1, but when I login as user1 it still shows postgres as the owner. For instance, when logged in as the postgres user and executing \dt or select * from pg_tables where tableowner = 'user1' it shows user1 as the owner, but when I login as user1 and do the same thing it still shows postgres as the table owner. I've tried restarting the service and that didn't change anything. What am I missing?

• Joishi Bodio: Are both your postgres and user1 connected to the same database within the cluster?

Answer 1

You must be seeing different tables from different database clusters, databases or schemas.

Either you are logging into a different database by mistake or you got yourself confused with tables of the same name in different schemas of the same database.

The latter would typically happen with two different roles that have different search_path settings or even the (default) setting:

search_path = "$user",public

Without schema-qualification, tables of the same name are found in the "current" schema first ...

Your test would also find the "wrong" table:

(削除) select * from pg_tables where tableowner = 'user1'; (削除ここまで)

Test instead with:

select * from pg_tables where tablename = 'my_tbl';

You should see two or more rows for the same table name ...

Of just use schema-qualified table names to avoid possible confusion:

ALTER TABLE my_schema.my_tbl OWNER TO user1;
SELECT * FROM my_schema.my_tbl;

Start by reading about schemas in the manual.

• Basil Bourque: For more information about the structural hierarchy of Cluster > Catalog > Schema > Table in Postgres, see my answer on StackOverflow. Includes a diagram that should help.
• timcase: When trying your suggestion of querying based on tablename it still only shows one table. The only schema I have is the public schema.