Setup new user and database on a new Postgresql 15 server, but no permission to create table in publ-CodePudding

I just installed a new Postgresql 15 server on Debian 11 and configured postgresql.conf to allow remote connections by un-commenting the following line.

listen_addresses = '*'

I also created a new user and database using the commands below

su postgres psql
postgres=# create database exampledomain;
postgres=# create user exampledomain with encrypted password 'mypassword';
postgres=# grant all privileges on database exampledomain to exampledomain;

I also added the following line to pg_hba.conf so the new user can connect from a remote connection

host    exampledomain     exampledomain     0.0.0.0/0               md5

I am able to connect to the exampledomain database using username "exampledomain". However when trying to create a new table under the public schema using DBeaver

CREATE TABLE public.newtable (
    id varchar NULL
);

I get the following message

ERROR: permission denied for schema public
  Position: 14

With older versions of Postgresql, that's all the steps I had to do in order to create tables. With Postgresql 15 do I need to any additional steps?

CodePudding user response：

With Postgresql 15 do I need to any additional steps?

Yes. Postgres 15 changed the permissions on the public schema for security reasons:

Quote from the release notes

Remove PUBLIC creation permission on the public schema (Noah Misch)

The new default is one of the secure schema usage patterns that Section 5.9.6 has recommended since the security release for CVE-2018-1058. The change applies to new database clusters and to newly-created databases in existing clusters. Upgrading a cluster or restoring a database dump will preserve public's existing permissions.

For existing databases, especially those having multiple users, consider revoking CREATE permission on the public schema to adopt this new default. For new databases having no need to defend against insider threats, granting CREATE permission will yield the behavior of prior releases.

So if you want a user to be able to create table in the public schema, you need to grant those privileges:

grant usage,create on schema public to exampledomain