Subject: Tables in public schema

From: Edwin UY <[email-masked]>
Date: Wed, 28 May 2025 02:01:23 +1200

Hi,

In one of the PostgreSQL Instances, I just found out that the tables are
created in the public schema even though there is a user database.
New tables are also getting created in the public schema.
There is no non-public schema when I run \dn, it only shows public.
I assume this is a risk of some sort and want to create a user schema and
move them from public to the new schema.
Will I have to redo the grants?

Regards,
Ed

---

From: "David G. Johnston" <[email-masked]>
Date: Tue, 27 May 2025 07:11:16 -0700

> Will I have to redo the grants?

If you use "alter table set schema" the table OID will not change and the
grants should follow it.  Users would be subject to whatever new
permissions apply to the newly created schema rather than the public schema
though.

David J.

---

From: Ron Johnson <[email-masked]>
Date: Tue, 27 May 2025 11:31:43 -0400

On Tue, May 27, 2025 at 10:02 AM Edwin UY <[email-masked]> wrote:

> Hi,
>
> the tables are created in the public schema *even though there is a user
> database.*
>

schemata are subsidiary to databases.  Every table *must* be created in
*some* schema.

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

---

From: Ron Johnson <[email-masked]>
Date: Tue, 27 May 2025 11:33:17 -0400

> permissions apply to the newly created schema rather than the public schema

 And he'll either have to SET SEARCH_PATH or manually qualify every CREATE
TABLE statement with the correct schema name.