To: pgsql-admin@lists.postgresql.org
References: 
 <CALhHtcA5CYQU0SLU673qAdZiixAJJ8GF2ZEJV7YSiq03h5W-jw@mail.gmail.com>
 <87bldgbv9r.fsf@gmail.com>
From: Holger Jakobs <holger@jakobs.com>
Subject: Re: Create Read only user
Message-ID: <9e9f3cd6-bd51-29b3-887e-c3af2db91f54@jakobs.com>
Date: Fri, 22 Jan 2021 21:09:44 +0100
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101
 Thunderbird/78.6.0
MIME-Version: 1.0
In-Reply-To: <87bldgbv9r.fsf@gmail.com>
Content-Type: multipart/signed; micalg=pgp-sha256;
 protocol="application/pgp-signature";
 boundary="cv2cyqup8rEBrxnslyW3kptuZuP92hYiU"
Precedence: bulk

This is an OpenPGP/MIME signed message (RFC 4880 and 3156)
--cv2cyqup8rEBrxnslyW3kptuZuP92hYiU
Content-Type: multipart/mixed; boundary="pOxbNQqpFP4e7ixmUmWkDNwd3gTjDD9ra";
 protected-headers="v1"
From: Holger Jakobs <holger@jakobs.com>
To: pgsql-admin@lists.postgresql.org
Message-ID: <9e9f3cd6-bd51-29b3-887e-c3af2db91f54@jakobs.com>
Subject: Re: Create Read only user
References: <CALhHtcA5CYQU0SLU673qAdZiixAJJ8GF2ZEJV7YSiq03h5W-jw@mail.gmail.com>
 <87bldgbv9r.fsf@gmail.com>
In-Reply-To: <87bldgbv9r.fsf@gmail.com>

--pOxbNQqpFP4e7ixmUmWkDNwd3gTjDD9ra
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: quoted-printable
Content-Language: de-DE

Yes, the PUBLIC schema is a remnant from times when no schemas were=20
used, but all table were created in the database itself. This was the=20
case in the early 7.x versions.

There are still some applications around which have no notion of a=20
schema. This is especially true for those which are expected work with a =

bunch of very weak database servers, for instance with MySQL.=20
MySQL/MariaDB only have schemas and just a single database per server=20
instance. They call their schemas databases, though.

If you have an application which is made to run with PostgreSQL, the=20
configuration will allow to specify a schema to put the tables into.

Otherwise, you'll have to set up a search path including the schema you=20
want the database objects to end up in. You can configure the default=20
search path for the application user appropriately using ALTER ROLE.

Hardly any application forces the PUBLIC schema to be around. It's just=20
that the PUBLIC schema exists in the template1 database with full access =

to PUBLIC and thus will exist in every database you create, unless you=20
remove it (or change the access rights) in the template1 database. And=20
the default search path includes PUBLIC.

Actually, this only exists to be compatible with *very* old applications.=


Regards,

Holger

--=20

Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012


--pOxbNQqpFP4e7ixmUmWkDNwd3gTjDD9ra--

--cv2cyqup8rEBrxnslyW3kptuZuP92hYiU
Content-Type: application/pgp-signature; name="OpenPGP_signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="OpenPGP_signature"

-----BEGIN PGP SIGNATURE-----

wmMEABEIACMWIQScuJCxWJK1p5BOebGKV5yTsxlhugUCYAsxCAUDAAAAAAAKCRCKV5yTsxlhuo49
AJ4wWzYSiuZT36lSJkY7JbD+JRRfqQCfeek80a61iD0GeupjR9BinaOB1OQ=
=xlBs
-----END PGP SIGNATURE-----

--cv2cyqup8rEBrxnslyW3kptuZuP92hYiU--