Re: postgres db permissions

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?

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(dot)Pribyl(at)akunacapital(dot)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.

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
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.

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(at)postgresql(dot)org <pgsql-general-owner(at)postgresql(dot)org> on behalf of Joshua D. Drake <jd(at)commandprompt(dot)com>
Sent: Tuesday, June 2, 2015 12:44 PM
To: pgsql-general(at)postgresql(dot)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
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(at)postgresql(dot)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>
p: +1 312 994 4646 | m: 847-343-2349 | f: +1 312 750 1667 | Steve(dot)Pribyl(at)akunacapital(dot)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.

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(dot)Pribyl(at)akunacapital(dot)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(at)postgresql(dot)org <
> pgsql-general-owner(at)postgresql(dot)org> on behalf of Joshua D. Drake <
> jd(at)commandprompt(dot)com>
> Sent: Tuesday, June 2, 2015 12:44 PM
> To: pgsql-general(at)postgresql(dot)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
> 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(at)postgresql(dot)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>
> p: +1 312 994 4646 | m: 847-343-2349 | f: +1 312 750 1667 |
> Steve(dot)Pribyl(at)akunacapital(dot)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.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)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.

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?

Steve Pribyl
Sr. Systems Engineer
steve(dot)pribyl(at)akunacapital(dot)com<mailto:steve(dot)pribyl(at)akunacapital(dot)com>
Desk: 312-994-4646

________________________________
From: Melvin Davidson <melvin6925(at)gmail(dot)com>
Sent: Tuesday, June 2, 2015 12:55 PM
To: Steve Pribyl
Cc: Joshua D. Drake; pgsql-general(at)postgresql(dot)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(dot)Pribyl(at)akunacapital(dot)com<mailto:Steve(dot)Pribyl(at)akunacapital(dot)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(at)postgresql(dot)org<mailto:pgsql-general-owner(at)postgresql(dot)org> <pgsql-general-owner(at)postgresql(dot)org<mailto:pgsql-general-owner(at)postgresql(dot)org>> on behalf of Joshua D. Drake <jd(at)commandprompt(dot)com<mailto:jd(at)commandprompt(dot)com>>
Sent: Tuesday, June 2, 2015 12:44 PM
To: pgsql-general(at)postgresql(dot)org<mailto:pgsql-general(at)postgresql(dot)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(at)postgresql(dot)org<mailto:pgsql-general(at)postgresql(dot)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(dot)Pribyl(at)akunacapital(dot)com<mailto:Steve(dot)Pribyl(at)akunacapital(dot)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(at)postgresql(dot)org<mailto:pgsql-general(at)postgresql(dot)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. [http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif]
________________________________
[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(dot)Pribyl(at)akunacapital(dot)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.

On 06/02/2015 10:50 AM, Steve Pribyl 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.

The burning question would be, how where they created?

>
> I have not created any database yet or assigned permissions to the roles.
>
> Steve Pribyl
>
>
>
> ________________________________________
> From: pgsql-general-owner(at)postgresql(dot)org <pgsql-general-owner(at)postgresql(dot)org> on behalf of Joshua D. Drake <jd(at)commandprompt(dot)com>
> Sent: Tuesday, June 2, 2015 12:44 PM
> To: pgsql-general(at)postgresql(dot)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
> 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(at)postgresql(dot)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>
> p: +1 312 994 4646 | m: 847-343-2349 | f: +1 312 750 1667 | Steve(dot)Pribyl(at)akunacapital(dot)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(dot)klaver(at)aklaver(dot)com

They all look like this.

CREATE ROLE dbA
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

Steve Pribyl

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

On 06/02/2015 10:50 AM, Steve Pribyl 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.

The burning question would be, how where they created?

>
> I have not created any database yet or assigned permissions to the roles.
>
> Steve Pribyl
>
>
>
> ________________________________________
> From: pgsql-general-owner(at)postgresql(dot)org <pgsql-general-owner(at)postgresql(dot)org> on behalf of Joshua D. Drake <jd(at)commandprompt(dot)com>
> Sent: Tuesday, June 2, 2015 12:44 PM
> To: pgsql-general(at)postgresql(dot)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
> 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(at)postgresql(dot)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>
> p: +1 312 994 4646 | m: 847-343-2349 | f: +1 312 750 1667 | Steve(dot)Pribyl(at)akunacapital(dot)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(dot)klaver(at)aklaver(dot)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(dot)Pribyl(at)akunacapital(dot)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.

On 06/02/2015 11:08 AM, Steve Pribyl wrote:
>
> They all look like this.
>
> CREATE ROLE dbA
> NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

And how are you connecting to the database via psql?

JD

--
Command Prompt, Inc. - http://www.commandprompt.com/ 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.

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(dot)pribyl(at)akunacapital(dot)com <mailto:steve(dot)pribyl(at)akunacapital(dot)com>
> Desk: 312-994-4646
>
> ------------------------------------------------------------------------
> *From:* Melvin Davidson <melvin6925(at)gmail(dot)com>
> *Sent:* Tuesday, June 2, 2015 12:55 PM
> *To:* Steve Pribyl
> *Cc:* Joshua D. Drake; pgsql-general(at)postgresql(dot)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(dot)Pribyl(at)akunacapital(dot)com <mailto:Steve(dot)Pribyl(at)akunacapital(dot)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(at)postgresql(dot)org
> <mailto:pgsql-general-owner(at)postgresql(dot)org>
> <pgsql-general-owner(at)postgresql(dot)org
> <mailto:pgsql-general-owner(at)postgresql(dot)org>> on behalf of Joshua D.
> Drake <jd(at)commandprompt(dot)com <mailto:jd(at)commandprompt(dot)com>>
> Sent: Tuesday, June 2, 2015 12:44 PM
> To: pgsql-general(at)postgresql(dot)org <mailto:pgsql-general(at)postgresql(dot)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(at)postgresql(dot)org
> <mailto:pgsql-general(at)postgresql(dot)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(dot)Pribyl(at)akunacapital(dot)com
> <mailto:Steve(dot)Pribyl(at)akunacapital(dot)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(at)postgresql(dot)org
> <mailto:pgsql-general(at)postgresql(dot)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(dot)Pribyl(at)akunacapital(dot)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(dot)klaver(at)aklaver(dot)com

Yes. It is NEVER a good idea to use GRANT ALL on objects for users. Some
people use that as a short cut for allowing access to schemas and tables,
but in essence, it allows the users to do much more, and that is BAD!

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

On Tue, Jun 2, 2015 at 2:08 PM, Steve Pribyl <Steve(dot)Pribyl(at)akunacapital(dot)com>
wrote:

> They all look like this.
>
> CREATE ROLE dbA
> NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
>
> Steve Pribyl
>
> ________________________________________
> From: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
> Sent: Tuesday, June 2, 2015 1:06 PM
> To: Steve Pribyl; Joshua D. Drake; pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] postgres db permissions
>
> On 06/02/2015 10:50 AM, Steve Pribyl 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.
>
> The burning question would be, how where they created?
>
> >
> > I have not created any database yet or assigned permissions to the roles.
> >
> > Steve Pribyl
> >
> >
> >
> > ________________________________________
> > From: pgsql-general-owner(at)postgresql(dot)org <
> pgsql-general-owner(at)postgresql(dot)org> on behalf of Joshua D. Drake <
> jd(at)commandprompt(dot)com>
> > Sent: Tuesday, June 2, 2015 12:44 PM
> > To: pgsql-general(at)postgresql(dot)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
> > 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(at)postgresql(dot)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>
> > p: +1 312 994 4646 | m: 847-343-2349 | f: +1 312 750 1667 |
> Steve(dot)Pribyl(at)akunacapital(dot)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(dot)klaver(at)aklaver(dot)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(dot)Pribyl(at)akunacapital(dot)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.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)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.

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?

Should have added to previous post-

Log into the postgres database using psql and do:

\dn+

>
>
> Steve Pribyl
> Sr. Systems Engineer
> steve(dot)pribyl(at)akunacapital(dot)com <mailto:steve(dot)pribyl(at)akunacapital(dot)com>
> Desk: 312-994-4646

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com

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 SQL database, 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(dot)klaver(at)aklaver(dot)com>
Sent: Tuesday, June 2, 2015 1:20 PM
To: Steve Pribyl; Melvin Davidson
Cc: Joshua D. Drake; pgsql-general(at)postgresql(dot)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(dot)pribyl(at)akunacapital(dot)com <mailto:steve(dot)pribyl(at)akunacapital(dot)com>
> Desk: 312-994-4646
>
> ------------------------------------------------------------------------
> *From:* Melvin Davidson <melvin6925(at)gmail(dot)com>
> *Sent:* Tuesday, June 2, 2015 12:55 PM
> *To:* Steve Pribyl
> *Cc:* Joshua D. Drake; pgsql-general(at)postgresql(dot)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(dot)Pribyl(at)akunacapital(dot)com <mailto:Steve(dot)Pribyl(at)akunacapital(dot)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(at)postgresql(dot)org
> <mailto:pgsql-general-owner(at)postgresql(dot)org>
> <pgsql-general-owner(at)postgresql(dot)org
> <mailto:pgsql-general-owner(at)postgresql(dot)org>> on behalf of Joshua D.
> Drake <jd(at)commandprompt(dot)com <mailto:jd(at)commandprompt(dot)com>>
> Sent: Tuesday, June 2, 2015 12:44 PM
> To: pgsql-general(at)postgresql(dot)org <mailto:pgsql-general(at)postgresql(dot)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(at)postgresql(dot)org
> <mailto:pgsql-general(at)postgresql(dot)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(dot)Pribyl(at)akunacapital(dot)com
> <mailto:Steve(dot)Pribyl(at)akunacapital(dot)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(at)postgresql(dot)org
> <mailto:pgsql-general(at)postgresql(dot)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(dot)Pribyl(at)akunacapital(dot)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(dot)klaver(at)aklaver(dot)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(dot)Pribyl(at)akunacapital(dot)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(dot)klaver(at)aklaver(dot)com> writes:
> On 06/02/2015 11:04 AM, Steve Pribyl wrote:
>> 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.

Huh? Of course it's the default. I'm not really sure why the OP is
surprised at this. A database that won't let you create any tables
is not terribly useful.

If you don't like this, you can get rid of the database's public schema
and/or restrict who has CREATE permissions on it. But I can't see us
shipping a default configuration in which only superusers can create
tables. That would just encourage people to operate as superusers, which
overall would be much less secure.

regards, tom lane

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 users is 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(at)postgresql(dot)org <pgsql-general-owner(at)postgresql(dot)org> on behalf of Steve Pribyl <Steve(dot)Pribyl(at)akunacapital(dot)com>
Sent: Tuesday, June 2, 2015 1:45 PM
To: Adrian Klaver; Melvin Davidson
Cc: Joshua D. Drake; pgsql-general(at)postgresql(dot)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 SQL database, 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(dot)Pribyl(at)akunacapital(dot)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.

On 06/02/2015 11:46 AM, Tom Lane wrote:
> Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> writes:
>> On 06/02/2015 11:04 AM, Steve Pribyl wrote:
>>> 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.
>
> Huh? Of course it's the default. I'm not really sure why the OP is
> surprised at this. A database that won't let you create any tables
> is not terribly useful.

The owner (or super user) should always have access, anybody with access
should not. This argument has actually come up before and you held a
similar view. This should not be possible:

postgres(at)sqitch:/# psql -U postgres
psql (9.2.11)
Type "help" for help.

postgres=# create user foo;
CREATE ROLE
postgres=# \q

root(at)sqitch:/# psql -U foo postgres
psql (9.2.11)
Type "help" for help.
postgres=> create table bar (id text);
CREATE TABLE
postgres=>

We can adjust this capability with pg_hba.conf but that is external to
this behavior.

Sincerely,

JD

--
Command Prompt, Inc. - http://www.commandprompt.com/ 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.

As Tom advised, it's called a "public" schema for a reason. It means the
general public (any user) has access to it and can create objects/tables in
it.

On Tue, Jun 2, 2015 at 2:58 PM, Joshua D. Drake <jd(at)commandprompt(dot)com>
wrote:

>
> On 06/02/2015 11:46 AM, Tom Lane wrote:
>
>> Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> writes:
>>
>>> On 06/02/2015 11:04 AM, Steve Pribyl wrote:
>>>
>>>> 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.
>>>
>>
>> Huh? Of course it's the default. I'm not really sure why the OP is
>> surprised at this. A database that won't let you create any tables
>> is not terribly useful.
>>
>
> The owner (or super user) should always have access, anybody with access
> should not. This argument has actually come up before and you held a
> similar view. This should not be possible:
>
> postgres(at)sqitch:/# psql -U postgres
> psql (9.2.11)
> Type "help" for help.
>
> postgres=# create user foo;
> CREATE ROLE
> postgres=# \q
>
> root(at)sqitch:/# psql -U foo postgres
> psql (9.2.11)
> Type "help" for help.
> postgres=> create table bar (id text);
> CREATE TABLE
> postgres=>
>
> We can adjust this capability with pg_hba.conf but that is external to
> this behavior.
>
> Sincerely,
>
> JD
>
>
>
> --
> Command Prompt, Inc. - http://www.commandprompt.com/ 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.
>

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

On 06/02/2015 11:46 AM, Tom Lane wrote:
> Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> writes:
>> On 06/02/2015 11:04 AM, Steve Pribyl wrote:
>>> 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.
>
> Huh? Of course it's the default. I'm not really sure why the OP is
> surprised at this. A database that won't let you create any tables
> is not terribly useful.

Aah, me being stupid.

>
> If you don't like this, you can get rid of the database's public schema
> and/or restrict who has CREATE permissions on it. But I can't see us
> shipping a default configuration in which only superusers can create
> tables. That would just encourage people to operate as superusers, which
> overall would be much less secure.

>
> regards, tom lane
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com