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

----- Original Message -----
> From: "Patricia Hu" <Patricia(dot)Hu(at)finra(dot)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