What is the best thing to do with PUBLIC schema in Postgresql database

On 11/4/16 3:58 PM, Hu, Patricia wrote:

Since it could potentially be a security loop hole. So far the action taken to address it falls into these two categories:

drop the PUBLIC schema altogether. One of the concerns is with some of the system objects that have been exposed through PUBLIC schema previously, now they will need other explicit grants to be accessible to users. e.g pg_stat_statements.
keep the PUBLIC schema but revoke all privileges to it from public role, then grant as necessity comes up.

Any feedback and lessons from those who have implemented this?

I always drop the public schema as the first step of any build and have
never seen any ill effects.

Nothing is exposed by default in the public schema unless you install
extensions into it.

--
-David
david@pgmasters.net

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

----- Original Message -----

From: "Patricia Hu" <Patricia.Hu@finra.org>
Sent: Friday, November 4, 2016 9:58:10 AM

Since it could potentially be a security loop hole. So far the action taken
to address it falls into these two categories:

drop the PUBLIC schema altogether. ...
keep the PUBLIC schema but revoke all privileges to it from public role,
then grant as necessity comes up.

Any feedback and lessons from those who have implemented this?

Admittedly, this may be TMI (...or maybe not enough...), but FWIW (and YMMV), I use the PUBLIC schema, along with the PUBLIC role, to expose a very limited view into the data base for the purpose of anonymous login and creation of user accounts.

There is one view in the PUBLIC schema (and it has appropriate triggers and permissions to make the view writeable):

fairwinds=# set search_path to public;
fairwinds=# \d
List of relations
Schema | Name | Type | Owner
--------+---------+------+----------
public | fairian | view | postgres
(1 row)

fairwinds=# \dp public.fairian
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+---------+------+-----------------------+-------------------+----------
public | fairian | view | =ar/postgres | |
(1 row)

Then revoke unneeded privilege on the PUBLIC schema, and grant the read and write privileges on that one view:

REVOKE CREATE ON SCHEMA public FROM public;
GRANT SELECT,INSERT ON TABLE fairian TO PUBLIC;

The special user role "fairwinds" is allowed trusted login in pg_hba.conf:

# TYPE DATABASE USER ADDRESS METHOD
host fairwinds fairwinds all trust

In summary, then, new users connect the first time with the "fairwinds" user and no password, and then create an account by inserting a row in the "fairian" view. Newly-created users subequently login with a password and then have an expanded view into the data base by GRANT USAGE on a different schema that contains more data base objects.

If that write-up is not clear enough, there is a test server where you can try it at http://fairwinds.btober.net and see what I'm talking about.

--B

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Patricia Hu wrote:

Since it could potentially be a security loop hole. So far the action taken to address it falls into
these two categories:

drop the PUBLIC schema altogether. One of the concerns is with some of the system objects that
have been exposed through PUBLIC schema previously, now they will need other explicit grants to be
accessible to users. e.g pg_stat_statements.
keep the PUBLIC schema but revoke all privileges to it from public role, then grant as necessity
comes up.

Any feedback and lessons from those who have implemented this?

I'd prefer the second approach as it is less invasive and prevents
undesirable objects in schema "public" just as well.

Confidentiality Notice:: This email, including attachments, may include non-public, proprietary,
confidential or legally privileged information. If you are not an intended recipient or an authorized
agent of an intended recipient, you are hereby notified that any dissemination, distribution or
copying of the information contained in or transmitted with this e-mail is unauthorized and strictly
prohibited.

You are hereby notified that any dissemination, distribution or copying of the information
contained in or transmitted with your e-mail is hunky-dory.

Yours,
Laurenz Albe

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general