Thread: Tables in public schema
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
On Tue, May 27, 2025 at 7:02 AM Edwin UY <edwin.uy@gmail.com> wrote:
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.
On Tue, May 27, 2025 at 10:02 AM Edwin UY <edwin.uy@gmail.com> 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!
On Tue, May 27, 2025 at 10:12 AM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Tue, May 27, 2025 at 7:02 AM Edwin UY <edwin.uy@gmail.com> wrote: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.
And he'll either have to SET SEARCH_PATH or manually qualify every CREATE TABLE statement with the correct schema name.
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!