Обсуждение: 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.GRANT dbA TO bob;
GRANT dbA_ro TO bob;
GRANT dbB TO bob;
GRANT dbB_ro TO bob;
On Tue, Jun 2, 2015 at 1:50 PM, Steve Pribyl <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 <pgsql-general-owner@postgresql.org> on behalf of Joshua D. Drake <jd@commandprompt.com>
Sent: Tuesday, June 2, 2015 12:44 PM
To: 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
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)
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.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.
--
Sent via pgsql-general mailing list (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.
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?
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
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.GRANT dbA TO bob;
GRANT dbA_ro TO bob;
GRANT dbB TO bob;
GRANT dbB_ro TO bob;
On Tue, Jun 2, 2015 at 1:50 PM, Steve Pribyl <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 <pgsql-general-owner@postgresql.org> on behalf of Joshua D. Drake <jd@commandprompt.com>
Sent: Tuesday, June 2, 2015 12:44 PM
To: 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
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)
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.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.
--
Sent via pgsql-general mailing list (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.
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
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.
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
http://www.postgresql.org/docs/9.3/interactive/sql-grant.html
On Tue, Jun 2, 2015 at 2:08 PM, Steve Pribyl <Steve.Pribyl@akunacapital.com> wrote:
They all look like this.
CREATE ROLE dbA
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
Steve Pribyl
________________________________________
From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Tuesday, June 2, 2015 1:06 PM
To: Steve Pribyl; Joshua D. Drake; pgsql-general@postgresql.orgSubject: 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@postgresql.org <pgsql-general-owner@postgresql.org> on behalf of Joshua D. Drake <jd@commandprompt.com>
> Sent: Tuesday, June 2, 2015 12:44 PM
> To: 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
> 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)
> 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.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 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@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.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to 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@commandprompt.com> wrote:
On 06/02/2015 11:46 AM, Tom Lane wrote:Adrian Klaver <adrian.klaver@aklaver.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@sqitch:/# psql -U postgres
psql (9.2.11)
Type "help" for help.
postgres=# create user foo;
CREATE ROLE
postgres=# \q
root@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-4564PostgreSQL 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.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.