Re: postgres db permissions - Mailing list pgsql-general

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



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

On 06/02/2015 11:04 AM, Steve Pribyl wrote:
> None of the roles have permissions on the postgres database.  At this
> point they don't have any permissions on any databases.
>
>
> I have noted that  "GRANT ALL ON SCHEMA public TO public" is granted
> on postgres.schemas.public.  I am looking at this in pgadmin so excuse
> my nomenclature.
>
>
> Is this what is allowing write access to the database?

Yes, though that should not be the default. See here:

http://www.postgresql.org/docs/9.4/interactive/sql-grant.html

PostgreSQL grants default privileges on some types of objects to PUBLIC.
No privileges are granted to PUBLIC by default on tables, columns,
schemas or tablespaces. For other types, the default privileges granted
to PUBLIC are as follows: CONNECT and CREATE TEMP TABLE for databases;
EXECUTE privilege for functions; and USAGE privilege for languages. The
object owner can, of course, REVOKE both default and expressly granted
privileges. (For maximum security, issue the REVOKE in the same
transaction that creates the object; then there is no window in which
another user can use the object.) Also, these initial default privilege
settings can be changed using the ALTER DEFAULT PRIVILEGES command.

So how exactly was Postgres installed and where there any scripts run
after the install?


>
>
> Steve Pribyl
> Sr. Systems Engineer
> steve.pribyl@akunacapital.com <mailto:steve.pribyl@akunacapital.com>
> Desk: 312-994-4646
>
> ------------------------------------------------------------------------
> *From:* Melvin Davidson <melvin6925@gmail.com>
> *Sent:* Tuesday, June 2, 2015 12:55 PM
> *To:* Steve Pribyl
> *Cc:* Joshua D. Drake; pgsql-general@postgresql.org
> *Subject:* Re: [GENERAL] postgres db permissions
> Your problem is probably the "INHERIT" and
> GRANT dbA TO bob;
> GRANT dbA_ro TO bob;
> GRANT dbB TO bob;
> GRANT dbB_ro TO bob;
>
> options. If any of the dbA's have the permission to CREATE tables (and I
> suspect they do), so will bob.
>
>
> On Tue, Jun 2, 2015 at 1:50 PM, Steve Pribyl
> <Steve.Pribyl@akunacapital.com <mailto:Steve.Pribyl@akunacapital.com>>
> wrote:
>
>     Josh,
>
>     Via psql:
>     CREATE ROLE bob LOGIN
>        NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
>     GRANT dbA TO bob;
>     GRANT dbA_ro TO bob;
>     GRANT dbB TO bob;
>     GRANT dbB_ro TO bob;
>
>     dbA, dbA_ro, dbB, and dbB_ro are roles.
>
>     I have not created any database yet or assigned permissions to the
>     roles.
>
>     Steve Pribyl
>
>
>
>     ________________________________________
>     From: pgsql-general-owner@postgresql.org
>     <mailto:pgsql-general-owner@postgresql.org>
>     <pgsql-general-owner@postgresql.org
>     <mailto:pgsql-general-owner@postgresql.org>> on behalf of Joshua D.
>     Drake <jd@commandprompt.com <mailto:jd@commandprompt.com>>
>     Sent: Tuesday, June 2, 2015 12:44 PM
>     To: pgsql-general@postgresql.org <mailto:pgsql-general@postgresql.org>
>     Subject: Re: [GENERAL] postgres db permissions
>
>     On 06/02/2015 10:36 AM, Steve Pribyl wrote:
>      >
>      > Good Afternoon,
>      >
>      > Built a fresh 9.3. postgres server and added some users and
>     noticed that any user can create tables in any database including
>     the postgres database by default.
>      >
>      > Have I missed some step in securing the default install?
>
>     How exactly did you add the users?
>
>     JD
>
>
>
>     --
>     Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564
>     <tel:503-667-4564>
>     PostgreSQL Centered full stack support, consulting and development.
>     Announcing "I'm offended" is basically telling the world you can't
>     control your own emotions, so everyone else should do it for you.
>
>
>     --
>     Sent via pgsql-general mailing list (pgsql-general@postgresql.org
>     <mailto:pgsql-general@postgresql.org>)
>     To make changes to your subscription:
>     http://www.postgresql.org/mailpref/pgsql-general
>     ________________________________
>       [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> <http://www.akunacapital.com>
>     p: +1 312 994 4646 <tel:%2B1%20312%20994%204646> | m: 847-343-2349
>     <tel:847-343-2349> | f: +1 312 750 1667
>     <tel:%2B1%20312%20750%201667> | Steve.Pribyl@akunacapital.com
>     <mailto: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 otherwise protected
>     from disclosure. This information is intended for the use of the
>     addressee only and is not offered as investment advice to be relied
>     upon for personal or professional use. Additionally, all electronic
>     messages are recorded and stored in compliance pursuant to
>     applicable SEC rules. If you are not the intended recipient, you are
>     hereby notified that any disclosure, copying, distribution, printing
>     or any other use of, or any action in reliance on, the contents of
>     this electronic message is strictly prohibited. If you have received
>     this communication in error, please notify us by telephone at
>     (312)994-4640 <tel:%28312%29994-4640> and destroy the original message.
>
>
>     --
>     Sent via pgsql-general mailing list (pgsql-general@postgresql.org
>     <mailto:pgsql-general@postgresql.org>)
>     To make changes to your subscription:
>     http://www.postgresql.org/mailpref/pgsql-general
>
>
>
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
> ------------------------------------------------------------------------
>
> *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 otherwise protected from
> disclosure. This information is intended for the use of the addressee
> only and is not offered as investment advice to be relied upon for
> personal or professional use. Additionally, all electronic messages are
> recorded and stored in compliance pursuant to applicable SEC rules. If
> you are not the intended recipient, you are hereby notified that any
> disclosure, copying, distribution, printing or any other use of, or any
> action in reliance on, the contents of this electronic message is
> strictly prohibited. If you have received this communication in error,
> please notify us by telephone at (312)994-4640 and destroy the original
> message.


--
Adrian Klaver
adrian.klaver@aklaver.com
________________________________
 [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.