Re: Restricting user to see schema structure

[Bryn Llewellyn <bryn@xxxxxxxxxxxx>]

david.g.johnston@xxxxxxxxx wrote:

bryn@xxxxxxxxxxxx wrote:

Right, I see the importance of this now. I now realize that when a database is created, CONNECT on it is automatically granted to PUBLIC. But there's no mention of this (or what to read to learn that this is the case) in the "pg_hba.conf" chapter. Nor does the section on the CREATE DATABASE statement mention this. How is the neophyte supposed to know about this behavior?

By reading the documentation, specifically the chapter linked before, where this is discussed.

https://www.postgresql.org/docs/current/ddl-priv.html

Not saying there isn't room for improvement here, I tend to agree that the SQL Command Reference section should be considered a primary jumping off point.  But the chapter on Data Definition is basically mandatory reading once a DBA wants to do any non-trivial modifications to their cluster.

Thanks again, David. Yes... the section "5.7. Privileges" is essential reading. And I do see what I'd missed now. I broke it into bullets to make it (very much) easier for me at least to parse.

«

PostgreSQL grants privileges on some types of objects to PUBLIC by default when the objects are created.

* No privileges are granted to PUBLIC by default on tables, table columns, sequences, foreign data wrappers, foreign servers, large objects, schemas, or tablespaces.

For other types of objects, the default privileges granted to PUBLIC are as follows:

* CONNECT and TEMPORARY (create temporary tables) privileges for databases;

* EXECUTE privilege for functions and procedures;

* USAGE privilege for languages and data types (including domains).

The object owner can, of course, REVOKE both default and expressly granted privileges. (For maximum security, issue the REVOKE in the same transaction that creates the object; then there is no window in which another user can use the object.) Also, these default privilege settings can be overridden using the ALTER DEFAULT PRIVILEGES command.

»

(I'd already been burned by the fact that EXECUTE is granted to PUBLIC on newly-created functions and procedures.)

The paragraph describes very surprising behavior in the present era of "secure by default". The sentence "For maximum security..." at the end emphasizes this and has you go to some effort (CREATE and REVOKE in the same txn) to undo the "insecurity by default" paradigm. I s'pose that compatibility on upgrade means that nothing can change here.

Might it be possible to give the paragraph more prominence (like make it a note and start it with "WARNING" in large letters). And to x-ref it from the CREATE DATABASE section? And from the "pg_hba.conf" section? And correspondingly from the CREATE accounts for the objects of the other types?