Menu
$ 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?
448
When a new database is created, PostgreSQL by default creates a schema named public and grants access on this schema to a backend role named public . All new users and roles are by default granted this public role, and therefore can create objects in the public schema.
To change the schema of a table by using SQL Server Management Studio, in Object Explorer, right-click on the table and then click Design. Press F4 to open the Properties window. In the Schema box, select a new schema. ALTER SCHEMA uses a schema level lock.
PostgreSQL roles and users A user is a role with the ability to login (the role has the LOGIN attribute). Because all roles Cloud SQL creates have the LOGIN attribute, Cloud SQL uses the terms "role" and "user" interchangeably.
By default, the schema owner has privileges to create objects within a schema. Additionally, the schema owner can drop any object in the schema, requiring no additional privilege on the object. The schema owner is typically the user who creates the schema. Schema owners cannot access objects in the schema.
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.
146
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
