Re: postgres db permissions

This only seems to show up in pgadminIII, I am unable to see this grant using \dn+(but I am a bit of a novice).

postgres=# \dn+
                          List of schemas
  Name  |  Owner   |  Access privileges   |      Description
--------+----------+----------------------+------------------------
 public | postgres | postgres=UC/postgres+| standard public schema
        |          | =UC/postgres         |


I would seem to me granting "public" access to the schema by default is bad.  Granting access to just the required
usersis good. 

Good:
CREATE SCHEMA public
  AUTHORIZATION postgres;

GRANT ALL ON SCHEMA public TO postgres;
COMMENT ON SCHEMA public

Bad and happens to be the default:
CREATE SCHEMA public
  AUTHORIZATION postgres;

GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;
COMMENT ON SCHEMA public

Steve Pribyl


________________________________________
From: pgsql-general-owner@postgresql.org <pgsql-general-owner@postgresql.org> on behalf of Steve Pribyl
<Steve.Pribyl@akunacapital.com>
Sent: Tuesday, June 2, 2015 1:45 PM
To: Adrian Klaver; Melvin Davidson
Cc: Joshua D. Drake; pgsql-general@postgresql.org
Subject: Re: [GENERAL] postgres db permissions

Thanks for clearing that up.

I seems that any database that gets created has "GRANT ALL ON SCHEMA public TO public" by default.  These are all clean
installs.   I have found this on Ubuntu 9.3, The Postgres 9.3 and 9.4 deb packages. 

Default postgres from ubuntu, is the version I am testing on.
It seems to be the default install, though we might be a patch or two behind.
$ dpkg -l | grep postgres
ii  postgresql-9.3                                9.3.5-0ubuntu0.14.04.1                amd64        object-relational
SQLdatabase, version 9.3 server 

I found this problem on a install from the postgres repo
$ dpkg -l postgresql-9.3
Desired=Unknown/Install/Remove/Purge/Hold
| Status=Not/Inst/Conf-files/Unpacked/halF-conf/Half-inst/trig-aWait/Trig-pend
|/ Err?=(none)/Reinst-required (Status,Err: uppercase=bad)
||/ Name           Version        Description
+++-==============-==============-============================================
ii  postgresql-9.3 9.3.0-2.pgdg12 object-relational SQL database, version 9.3


$ dpkg -l postgresql-9.4
Desired=Unknown/Install/Remove/Purge/Hold
| Status=Not/Inst/Conf-files/Unpacked/halF-conf/Half-inst/trig-aWait/Trig-pend
|/ Err?=(none)/Reinst-required (Status,Err: uppercase=bad)
||/ Name           Version      Architecture Description
+++-==============-============-============-=================================
ii  postgresql-9.4 9.4.0-1.pgdg amd64        object-relational SQL database, v


Steve Pribyl



________________________________
 [http://www.akunacapital.com/images/akuna.png]
Steve Pribyl | Senior Systems Engineer
Akuna Capital LLC
36 S Wabash, Suite 310 Chicago IL 60603 USA | www.akunacapital.com <http://www.akunacapital.com>
p: +1 312 994 4646 | m: 847-343-2349 | f: +1 312 750 1667 | Steve.Pribyl@akunacapital.com

Please consider the environment, before printing this email.

This electronic message contains information from Akuna Capital LLC that may be confidential, legally privileged or
otherwiseprotected from disclosure. This information is intended for the use of the addressee only and is not offered
asinvestment advice to be relied upon for personal or professional use. Additionally, all electronic messages are
recordedand stored in compliance pursuant to applicable SEC rules. If you are not the intended recipient, you are
herebynotified that any disclosure, copying, distribution, printing or any other use of, or any action in reliance on,
thecontents of this electronic message is strictly prohibited. If you have received this communication in error, please
notifyus by telephone at (312)994-4640 and destroy the original message.