Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




----- Original Message -----
> From: "Patricia Hu" <Patricia.Hu@xxxxxxxxx>
> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux