----- Original Message -----
> From: "Patricia Hu" <patricia...@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
