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

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
throughPUBLIC 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?

Confidentiality Notice::  This email, including attachments, may include non-public, proprietary, confidential or
legallyprivileged information.  If you are not an intended recipient or an authorized agent of an intended recipient,
youare hereby notified that any dissemination, distribution or copying of the information contained in or transmitted
withthis e-mail is unauthorized and strictly prohibited.  If you have received this email in error, please notify the
senderby replying to this message and permanently delete this e-mail, its attachments, and any copies of it
immediately. You should not retain, copy or use this e-mail or any attachment for any purpose, nor disclose all or any
partof the contents to any other person. Thank you. 

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
throughPUBLIC 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

----- 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
PUBLICrole, 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
accountby inserting a row in the "fairian" view. Newly-created users subequently login with a password and then have an
expandedview 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
seewhat I'm talking about. 

--B

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