Owner of schema public changes depending on who I'm logged in as?

Question

$ psql postgres

postgres=# \dn
        List of schemas
        Name        |  Owner
--------------------+----------
 information_schema | postgres
 pg_catalog         | postgres
 pg_toast           | postgres
 pg_toast_temp_1    | postgres
 public             | student
(5 rows)

When I log in to psql with user postgres, it shows that schema public is owned by user student. However, when I log in to psql with user student:

$ psql student

student=> \dn
        List of schemas
        Name        |  Owner
--------------------+----------
 information_schema | postgres
 pg_catalog         | postgres
 pg_toast           | postgres
 pg_toast_temp_1    | postgres
 public             | postgres
(5 rows)

It shows that schema public is owned by user postgres.

How can I get the ownership of schema public transferred to user student if the user with privileges to do so thinks that it's already done?

Answer 1

This is a misunderstanding. You are logging into two different databases.

When running

$ psql postgres

postgres is the name of the database. With default configuration the name of the database user is derived from the name of the system user using ident authentication automatically. The only parameter is assumed to be the database name. You do not want to change anything in the database postgres, it's a system database for maintenance tasks.

The other database is named student. Each database has a schema public with its respective owner.

Read the manual for psql or try a lowly man psql.

To transfer ownership of the schema public in the database student, log in as superuser:

psql -U postgres student

Or as operating system user postgres, just:

psql student

And run:

ALTER SCHEMA public OWNER TO student;

Details in the manual once more.