Unable to upload backups
<div dir='rtl'><div>Hi,</div>
<div>I'm using PostgreSQL 9.3.2</div>
<div>I'm running the command:</div>
<div> </div>
<div>
<p>psql -h testserver -U ronb -f backup.sql -q -d foldertest 2>error.txt >output.txt</p>
<p>This should generate my database in foldertest</p>
<p>However this doesn't work. It's unable to create schemas</p>
<p>in the error.txt i see "permission denied for database foldertest".</p>
<p>I know this is not an access permission issue because there is a public schema which is buildin and it does create the tables/data in there.</p>
<p>It just cant create new schemas.</p>
<p> </p>
<p>The intresting thing is that if I do:</p>
<p>psql -h testserver -U postgres -f backup.sql -q -d foldertest 2>error.txt >output.txt</p>
<p> </p>
<p>Everything works. It create all schemas and generate the database correctly.</p>
<p>I don't see any diffrent in the hba.conf between postgres and ronb users.</p>
<p>What can be the problem?</p>
</div></div>
Hi,
Seems to me your role "ronb" doesn't have the rights to create schema on your database.
Please refer to the GRANT command in the documentation. https://www.postgresql.org/docs/9.0/static/sql-grant.html
Sent from [ProtonMail](https://protonmail.ch), encrypted email based in Switzerland.
-------- Original Message --------
Subject: [GENERAL] Unable to upload backups
Local Time: 19 avril 2017 12:56 PM
UTC Time: 19 avril 2017 10:56
From: ronb910@walla.co.il
To: pgsql-general@postgresql.org
Hi,
I'm using PostgreSQL 9.3.2
I'm running the command:
psql -h testserver -U ronb -f backup.sql -q -d foldertest 2>error.txt >output.txt
This should generate my database in foldertest
However this doesn't work. It's unable to create schemas
in the error.txt i see "permission denied for database foldertest".
I know this is not an access permission issue because there is a public schema which is buildin and it does create the tables/data in there.
It just cant create new schemas.
The intresting thing is that if I do:
psql -h testserver -U postgres -f backup.sql -q -d foldertest 2>error.txt >output.txt
Everything works. It create all schemas and generate the database correctly.
I don't see any diffrent in the hba.conf between postgres and ronb users.
What can be the problem?
On 04/19/2017 03:56 AM, Ron Ben wrote:
Hi,
I'm using PostgreSQL 9.3.2
I'm running the command:psql -h testserver -U ronb -f backup.sql -q -d foldertest 2>error.txt
output.txt
What was the command that created backup.sql?
This should generate my database in foldertest
However this doesn't work. It's unable to create schemas
in the error.txt i see "permission denied for database foldertest".
What user is the foldertest owner?
In psql \l will tell you this.
I know this is not an access permission issue because there is a public
schema which is buildin and it does create the tables/data in there.
Because the public schema is by default open to all:
https://www.postgresql.org/docs/9.6/static/ddl-schemas.html
"A user can also be allowed to create objects in someone else's schema.
To allow that, the CREATE privilege on the schema needs to be granted.
Note that by default, everyone has CREATE and USAGE privileges on the
schema public. This allows all users that are able to connect to a given
database to create objects in its public schema. ... "
It just cant create new schemas.
In psql do \dn+, that will show schema owners and who else has privileges.
For what the different privileges are and how they are represented in
the above output see:
https://www.postgresql.org/docs/9.6/static/sql-grant.html
The intresting thing is that if I do:
psql -h testserver -U postgres -f backup.sql -q -d foldertest
2>error.txt >output.txtEverything works. It create all schemas and generate the database correctly.
Because the postgres user is a superuser and can do anything.
I don't see any diffrent in the hba.conf between postgres and ronb users.
That is not the issue. pg_hba determines who can connect, what you are
seeing is the Postgres privilege system determining what a user can do
once they are connected. If it had been a pg_hba rejection you would
have seen something like:
aklaver@tito:~> psql -d production -U guest -h localhost
psql: FATAL: no pg_hba.conf entry for host "::1", user "guest",
database "production", SSL on
FATAL: no pg_hba.conf entry for host "::1", user "guest", database
"production", SSL off
To get an overview of what users there are in your database cluster in
psql do \du
What can be the problem?
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
<div><div dir="rtl"><div>
<div dir="rtl">
<div>I think I may have found the problem.</div>
<div> </div>
<div>The role defined as:</div>
<div> </div>
<div>CREATE ROLE "ronb" LOGIN<br> NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;<br>GRANT users TO "ronb";</div>
<div>GRANT users2 TO "ronb";</div>
<div> </div>
<div>users is a group role:</div>
<div> </div>
<div>CREATE ROLE users <br> SUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION;</div>
<div> </div>
<div> users2 is a group role:</div>
<div>CREATE ROLE users2 <br> NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;</div>
<div>GRANT reports TO users2 ;</div>
<div> </div>
<div> </div>
<div>I think PostgreSQL doesn't know how to handle this conflicted commands.</div>
<div>What PostgreSQL does when such conflic appears? does it take the last known command of grant?</div>
</div>
<br>Sadly, when there are more than one role it's impossible to know which role was first. PostgreSQL shows them alphabeticly rather than by date so in case of overlaping instructions its impossible to know which one was first.</div></div><section class="cust_msg_end"></section><blockquote style="margin:0;margin-bottom:20px;border-top:1px solid #e0e0e0;"><br>ב אפר׳ 19, 2017 16:40, Adrian Klaver כתב:<blockquote style="margin:0;margin-bottom:20px;border-top:1px solid #e0e0e0">On 04/19/2017 03:56 AM, Ron Ben wrote:<br>> Hi,<br>> I'm using PostgreSQL 9.3.2<br>> I'm running the command:<br>><br>><br>> psql -h testserver -U ronb -f backup.sql -q -d foldertest 2>error.txt<br>>>output.txt<br><br>What was the command that created backup.sql?<br><br>><br>> This should generate my database in foldertest<br>><br>> However this doesn't work. It's unable to create schemas<br>><br>> in the error.txt i see "permission denied for database foldertest".<br><br>What user is the foldertest owner?<br><br>In psql l will tell you this.<br><br>><br>> I know this is not an access permission issue because there is a public<br>> schema which is buildin and it does create the tables/data in there.<br><br>Because the public schema is by default open to all:<br><br><a href="https://www.postgresql.org/docs/9.6/static/ddl-schemas.html">https://www.postgresql.org/docs/9.6/static/ddl-schemas.html</a><br><br>"A user can also be allowed to create objects in someone else's schema. <br>To allow that, the CREATE privilege on the schema needs to be granted. <br>Note that by default, everyone has CREATE and USAGE privileges on the <br>schema public. This allows all users that are able to connect to a given <br>database to create objects in its public schema. ... "<br><br><br>><br>> It just cant create new schemas.<br><br>In psql do dn+, that will show schema owners and who else has privileges.<br><br>For what the different privileges are and how they are represented in <br>the above output see:<br><br><a href="https://www.postgresql.org/docs/9.6/static/sql-grant.html">https://www.postgresql.org/docs/9.6/static/sql-grant.html</a><br><br>&gt;<br>&gt;<br>&gt;<br>&gt; The intresting thing is that if I do:<br>><br>> psql -h testserver -U postgres -f backup.sql -q -d foldertest<br>> 2>error.txt >output.txt<br>><br>><br>><br>> Everything works. It create all schemas and generate the database correctly.<br><br>Because the postgres user is a superuser and can do anything.<br><br>><br>> I don't see any diffrent in the hba.conf between postgres and ronb users.<br><br>That is not the issue. pg_hba determines who can connect, what you are <br>seeing is the Postgres privilege system determining what a user can do <br>once they are connected. If it had been a pg_hba rejection you would <br>have seen something like:<br><br>aklaver@tito:~> psql -d production -U guest -h localhost<br>psql: FATAL: no pg_hba.conf entry for host "::1", user "guest", <br>database "production", SSL on<br>FATAL: no pg_hba.conf entry for host "::1", user "guest", database <br>"production", SSL off<br><br><br>To get an overview of what users there are in your database cluster in <br>psql do du<br><br><br>><br>> What can be the problem?<br>><br><br><br>-- <br>Adrian Klaver<br>adrian.klaver@aklaver.com<br><br><br>-- <br>Sent via pgsql-general mailing list (pgsql-general@postgresql.org)<br>To make changes to your subscription:<br><a href="http://www.postgresql.org/mailpref/pgsql-general">http://www.postgresql.org/mailpref/pgsql-general</a><br><br><br></blockquote></blockquote><br></div>
Import Notes
Resolved by subject fallback
On 04/19/2017 06:49 AM, Ron Ben wrote:
Is it possible to get your email program to left justify text on
sending? I can figure out the right justified text, it just takes me longer.
I think I may have found the problem.
The role defined as:
CREATE ROLE "ronb" LOGIN
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
GRANT users TO "ronb";
GRANT users2 TO "ronb";users is a group role:
CREATE ROLE users
SUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION;users2 is a group role:
CREATE ROLE users2
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
GRANT reports TO users2 ;
That may or may not be the problem. See:
https://www.postgresql.org/docs/9.6/static/sql-createrole.html
"The INHERIT attribute governs inheritance of grantable privileges (that
is, access privileges for database objects and role memberships). It
does not apply to the special role attributes set by CREATE ROLE and
ALTER ROLE. For example, being a member of a role with CREATEDB
privilege does not immediately grant the ability to create databases,
even if INHERIT is set; it would be necessary to become that role via
SET ROLE before creating a database."
What you show above is part of the answer. The other parts are the
actual privileges on the objects. Also the command that created the dump
file that you are trying to restore. Permissions/privileges issues can
be complex and solving them requires a complete set of information.
I think PostgreSQL doesn't know how to handle this conflicted commands.
What PostgreSQL does when such conflic appears? does it take the last
known command of grant?Sadly, when there are more than one role it's impossible to know which
role was first. PostgreSQL shows them alphabeticly rather than by date
so in case of overlaping instructions its impossible to know which one
was first.ב אפר׳ 19, 2017 16:40, Adrian Klaver כתב:
On 04/19/2017 03:56 AM, Ron Ben wrote:
Hi,
I'm using PostgreSQL 9.3.2
I'm running the command:psql -h testserver -U ronb -f backup.sql -q -d foldertest
2>error.txt
output.txt
What was the command that created backup.sql?
This should generate my database in foldertest
However this doesn't work. It's unable to create schemas
in the error.txt i see "permission denied for database
foldertest".
What user is the foldertest owner?
In psql l will tell you this.
I know this is not an access permission issue because there is
a public
schema which is buildin and it does create the tables/data in
there.
Because the public schema is by default open to all:
https://www.postgresql.org/docs/9.6/static/ddl-schemas.html
"A user can also be allowed to create objects in someone else's
schema.
To allow that, the CREATE privilege on the schema needs to be
granted.
Note that by default, everyone has CREATE and USAGE privileges
on the
schema public. This allows all users that are able to connect to
a given
database to create objects in its public schema. ... "It just cant create new schemas.
In psql do dn+, that will show schema owners and who else has
privileges.For what the different privileges are and how they are
represented in
the above output see:https://www.postgresql.org/docs/9.6/static/sql-grant.html
The intresting thing is that if I do:
psql -h testserver -U postgres -f backup.sql -q -d foldertest
2>error.txt >output.txtEverything works. It create all schemas and generate the
database correctly.
Because the postgres user is a superuser and can do anything.
I don't see any diffrent in the hba.conf between postgres and
ronb users.
That is not the issue. pg_hba determines who can connect, what
you are
seeing is the Postgres privilege system determining what a user
can do
once they are connected. If it had been a pg_hba rejection you
would
have seen something like:aklaver@tito:~> psql -d production -U guest -h localhost
psql: FATAL: no pg_hba.conf entry for host "::1", user "guest",
database "production", SSL on
FATAL: no pg_hba.conf entry for host "::1", user "guest", database
"production", SSL offTo get an overview of what users there are in your database
cluster in
psql do duWhat can be the problem?
--
Adrian Klaver
adrian.klaver@aklaver.com--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
<div><div dir="rtl"><div dir="ltr">
<div dir="rtl">
<div dir="ltr">Here :)</div>
<div dir="ltr">I think I may have found the problem.</div>
<div dir="ltr"> </div>
<div dir="ltr">The role defined as:</div>
<div dir="ltr"> </div>
<div dir="ltr">CREATE ROLE "ronb" LOGIN<br> NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;<br>GRANT users TO "ronb";</div>
<div dir="ltr">GRANT users2 TO "ronb";</div>
<div dir="ltr"> </div>
<div dir="ltr">users is a group role:</div>
<div dir="ltr"> </div>
<div dir="ltr">CREATE ROLE users <br> SUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION;</div>
<div dir="ltr"> </div>
<div dir="ltr"> users2 is a group role:</div>
<div dir="ltr">CREATE ROLE users2 <br> NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;</div>
<div dir="ltr">GRANT reports TO users2 ;</div>
<div dir="ltr"> </div>
<div dir="ltr"> </div>
<div dir="ltr">I think PostgreSQL doesn't know how to handle this conflicted commands.</div>
<div dir="ltr">What PostgreSQL does when such conflic appears? does it take the last known command of grant?</div>
</div>
<br>Sadly, when there are more than one role it's impossible to know which role was first. PostgreSQL shows them alphabeticly rather than by date so in case of overlaping instructions its impossible to know which one was first.<br><br></div></div><section class="cust_msg_end"></section><blockquote style="margin:0;margin-bottom:20px;border-top:1px solid #e0e0e0;"><br>ב אפר׳ 19, 2017 17:01, Adrian Klaver כתב:<blockquote style="margin:0;margin-bottom:20px;border-top:1px solid #e0e0e0">On 04/19/2017 06:49 AM, Ron Ben wrote:<br><br>Is it possible to get your email program to left justify text on <br>sending? I can figure out the right justified text, it just takes me longer.<br><br>> I think I may have found the problem.<br>><br>> The role defined as:<br>><br>> CREATE ROLE "ronb" LOGIN<br>> NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;<br>> GRANT users TO "ronb";<br>> GRANT users2 TO "ronb";<br>><br>> users is a group role:<br>><br>> CREATE ROLE users<br>> SUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION;<br>><br>> users2 is a group role:<br>> CREATE ROLE users2<br>> NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;<br>> GRANT reports TO users2 ;<br><br>That may or may not be the problem. See:<br><br><a href="https://www.postgresql.org/docs/9.6/static/sql-createrole.html">https://www.postgresql.org/docs/9.6/static/sql-createrole.html</a><br><br>"The INHERIT attribute governs inheritance of grantable privileges (that <br>is, access privileges for database objects and role memberships). It <br>does not apply to the special role attributes set by CREATE ROLE and <br>ALTER ROLE. For example, being a member of a role with CREATEDB <br>privilege does not immediately grant the ability to create databases, <br>even if INHERIT is set; it would be necessary to become that role via <br>SET ROLE before creating a database."<br><br><br>What you show above is part of the answer. The other parts are the <br>actual privileges on the objects. Also the command that created the dump <br>file that you are trying to restore. Permissions/privileges issues can <br>be complex and solving them requires a complete set of information.<br><br>><br>><br>> I think PostgreSQL doesn't know how to handle this conflicted commands.<br>> What PostgreSQL does when such conflic appears? does it take the last<br>> known command of grant?<br>><br>> Sadly, when there are more than one role it's impossible to know which<br>> role was first. PostgreSQL shows them alphabeticly rather than by date<br>> so in case of overlaping instructions its impossible to know which one<br>> was first.<br>><br>><br>> ב אפר׳ 19, 2017 16:40, Adrian Klaver כתב:<br>><br>> On 04/19/2017 03:56 AM, Ron Ben wrote:<br>> > Hi,<br>> > I'm using PostgreSQL 9.3.2<br>> > I'm running the command:<br>> ><br>> ><br>> > psql -h testserver -U ronb -f backup.sql -q -d foldertest<br>> 2>error.txt<br>> >>output.txt<br>><br>> What was the command that created backup.sql?<br>><br>> ><br>> > This should generate my database in foldertest<br>> ><br>> > However this doesn't work. It's unable to create schemas<br>> ><br>> > in the error.txt i see "permission denied for database<br>> foldertest".<br>><br>> What user is the foldertest owner?<br>><br>> In psql l will tell you this.<br>><br>> ><br>> > I know this is not an access permission issue because there is<br>> a public<br>> > schema which is buildin and it does create the tables/data in<br>> there.<br>><br>> Because the public schema is by default open to all:<br>><br>> <a href="https://www.postgresql.org/docs/9.6/static/ddl-schemas.html">https://www.postgresql.org/docs/9.6/static/ddl-schemas.html</a><br>&gt;<br>&gt; "A user can also be allowed to create objects in someone else's<br>> schema.<br>> To allow that, the CREATE privilege on the schema needs to be<br>> granted.<br>> Note that by default, everyone has CREATE and USAGE privileges<br>> on the<br>> schema public. This allows all users that are able to connect to<br>> a given<br>> database to create objects in its public schema. ... "<br>><br>><br>> ><br>> > It just cant create new schemas.<br>><br>> In psql do dn+, that will show schema owners and who else has<br>> privileges.<br>><br>> For what the different privileges are and how they are<br>> represented in<br>> the above output see:<br>><br>> <a href="https://www.postgresql.org/docs/9.6/static/sql-grant.html">https://www.postgresql.org/docs/9.6/static/sql-grant.html</a><br>&gt;<br>&gt; ><br>> ><br>> ><br>> > The intresting thing is that if I do:<br>> ><br>> > psql -h testserver -U postgres -f backup.sql -q -d foldertest<br>> > 2>error.txt >output.txt<br>> ><br>> ><br>> ><br>> > Everything works. It create all schemas and generate the<br>> database correctly.<br>><br>> Because the postgres user is a superuser and can do anything.<br>><br>> ><br>> > I don't see any diffrent in the hba.conf between postgres and<br>> ronb users.<br>><br>> That is not the issue. pg_hba determines who can connect, what<br>> you are<br>> seeing is the Postgres privilege system determining what a user<br>> can do<br>> once they are connected. If it had been a pg_hba rejection you<br>> would<br>> have seen something like:<br>><br>> aklaver@tito:~> psql -d production -U guest -h localhost<br>> psql: FATAL: no pg_hba.conf entry for host "::1", user "guest",<br>> database "production", SSL on<br>> FATAL: no pg_hba.conf entry for host "::1", user "guest", database<br>> "production", SSL off<br>><br>><br>> To get an overview of what users there are in your database<br>> cluster in<br>> psql do du<br>><br>><br>> ><br>> > What can be the problem?<br>> ><br>><br>><br>> --<br>> Adrian Klaver<br>> adrian.klaver@aklaver.com<br>><br>><br>> --<br>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)<br>> To make changes to your subscription:<br>> <a href="http://www.postgresql.org/mailpref/pgsql-general">http://www.postgresql.org/mailpref/pgsql-general</a><br>&gt;<br>&gt;<br>&gt;<br><br><br>-- <br>Adrian Klaver<br>adrian.klaver@aklaver.com<br><br><br>-- <br>Sent via pgsql-general mailing list (pgsql-general@postgresql.org)<br>To make changes to your subscription:<br><a href="http://www.postgresql.org/mailpref/pgsql-general">http://www.postgresql.org/mailpref/pgsql-general</a><br><br><br></blockquote></blockquote><br></div>
Import Notes
Resolved by subject fallback
On 04/19/2017 07:16 AM, Ron Ben wrote:
Here :)
Thanks.
See my previous response. Basically we need more information before this
can be solved.
I think I may have found the problem.
The role defined as:
CREATE ROLE "ronb" LOGIN
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
GRANT users TO "ronb";
GRANT users2 TO "ronb";users is a group role:
CREATE ROLE users
SUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION;users2 is a group role:
CREATE ROLE users2
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
GRANT reports TO users2 ;I think PostgreSQL doesn't know how to handle this conflicted commands.
What PostgreSQL does when such conflic appears? does it take the last
known command of grant?Sadly, when there are more than one role it's impossible to know which
role was first. PostgreSQL shows them alphabeticly rather than by date
so in case of overlaping instructions its impossible to know which one
was first.ב אפר׳ 19, 2017 17:01, Adrian Klaver כתב:
On 04/19/2017 06:49 AM, Ron Ben wrote:
Is it possible to get your email program to left justify text on
sending? I can figure out the right justified text, it just
takes me longer.I think I may have found the problem.
The role defined as:
CREATE ROLE "ronb" LOGIN
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
GRANT users TO "ronb";
GRANT users2 TO "ronb";users is a group role:
CREATE ROLE users
SUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION;users2 is a group role:
CREATE ROLE users2
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
GRANT reports TO users2 ;That may or may not be the problem. See:
https://www.postgresql.org/docs/9.6/static/sql-createrole.html
"The INHERIT attribute governs inheritance of grantable
privileges (that
is, access privileges for database objects and role
memberships). It
does not apply to the special role attributes set by CREATE ROLE
and
ALTER ROLE. For example, being a member of a role with CREATEDB
privilege does not immediately grant the ability to create
databases,
even if INHERIT is set; it would be necessary to become that
role via
SET ROLE before creating a database."What you show above is part of the answer. The other parts are the
actual privileges on the objects. Also the command that created
the dump
file that you are trying to restore. Permissions/privileges
issues can
be complex and solving them requires a complete set of information.I think PostgreSQL doesn't know how to handle this conflicted
commands.
What PostgreSQL does when such conflic appears? does it take
the last
known command of grant?
Sadly, when there are more than one role it's impossible to
know which
role was first. PostgreSQL shows them alphabeticly rather than
by date
so in case of overlaping instructions its impossible to know
which one
was first.
ב אפר׳ 19, 2017 16:40, Adrian Klaver כתב:
On 04/19/2017 03:56 AM, Ron Ben wrote:
Hi,
I'm using PostgreSQL 9.3.2
I'm running the command:psql -h testserver -U ronb -f backup.sql -q -d foldertest
2>error.txt
output.txt
What was the command that created backup.sql?
This should generate my database in foldertest
However this doesn't work. It's unable to create schemas
in the error.txt i see "permission denied for database
foldertest".
What user is the foldertest owner?
In psql l will tell you this.
I know this is not an access permission issue because there is
a public
schema which is buildin and it does create the tables/data in
there.
Because the public schema is by default open to all:
https://www.postgresql.org/docs/9.6/static/ddl-schemas.html
"A user can also be allowed to create objects in someone else's
schema.
To allow that, the CREATE privilege on the schema needs to be
granted.
Note that by default, everyone has CREATE and USAGE privileges
on the
schema public. This allows all users that are able to connect to
a given
database to create objects in its public schema. ... "It just cant create new schemas.
In psql do dn+, that will show schema owners and who else has
privileges.For what the different privileges are and how they are
represented in
the above output see:https://www.postgresql.org/docs/9.6/static/sql-grant.html
The intresting thing is that if I do:
psql -h testserver -U postgres -f backup.sql -q -d foldertest
2>error.txt >output.txtEverything works. It create all schemas and generate the
database correctly.
Because the postgres user is a superuser and can do anything.
I don't see any diffrent in the hba.conf between postgres and
ronb users.
That is not the issue. pg_hba determines who can connect, what
you are
seeing is the Postgres privilege system determining what a user
can do
once they are connected. If it had been a pg_hba rejection you
would
have seen something like:aklaver@tito:~> psql -d production -U guest -h localhost
psql: FATAL: no pg_hba.conf entry for host "::1", user "guest",
database "production", SSL on
FATAL: no pg_hba.conf entry for host "::1", user "guest", database
"production", SSL offTo get an overview of what users there are in your database
cluster in
psql do duWhat can be the problem?
--
Adrian Klaver
adrian.klaver@aklaver.com--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general--
Adrian Klaver
adrian.klaver@aklaver.com--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
<div><div dir="rtl"><div dir="ltr"><br>OK. I think I found a bug in PostgreSQL (9.3).</div>
<div dir="ltr"> </div>
<div dir="ltr">When I do:</div>
<div dir="ltr">
<div dir="ltr"> </div>
<div dir="ltr">CREATE ROLE ronb<br> SUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION;</div>
<div dir="ltr"> </div>
<div dir="ltr">Everything works. I can create the schemas and upload the backup correclty.</div>
<div dir="ltr"> </div>
<div dir="ltr">But if I do:</div>
<div dir="ltr">
<div dir="ltr">CREATE ROLE "ronb" LOGIN<br> NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;<br>GRANT users TO "ronb";</div>
<div dir="ltr"> </div>
<div dir="ltr">
<div dir="ltr">CREATE ROLE users <br> SUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION;</div>
<div dir="ltr"> </div>
<div dir="ltr"> </div>
<div dir="ltr">It doesn't work. ronb user still can't create schemas eventhogh the role users give ronb permission to do so.</div>
<div dir="ltr">The GRANT here is worthless.</div>
<div dir="ltr"> </div>
<div dir="ltr"> </div>
<div dir="ltr">Also, PostgreSQL doesn't show the GRANTS of role in the same order as they were given (In PgAdmin). It sort them alphabeticly which is highly confusing!</div>
<div dir="ltr">If for example you run "alter table x add column" you know that the new column is added last (if you refresh the table you will see it last).</div>
<div dir="ltr">But if you add another GRANT statment to user it won't be in the last.. you have no way of knowing the correct order of GRANTS.</div>
</div>
</div>
</div></div><section class="cust_msg_end"></section><blockquote style="margin:0;margin-bottom:20px;border-top:1px solid #e0e0e0;"><br>ב אפר׳ 19, 2017 17:26, Adrian Klaver כתב:<blockquote style="margin:0;margin-bottom:20px;border-top:1px solid #e0e0e0">On 04/19/2017 07:16 AM, Ron Ben wrote:<br>> Here :)<br><br>Thanks.<br><br>See my previous response. Basically we need more information before this <br>can be solved.<br><br>> I think I may have found the problem.<br>><br>> The role defined as:<br>><br>> CREATE ROLE "ronb" LOGIN<br>> NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;<br>> GRANT users TO "ronb";<br>> GRANT users2 TO "ronb";<br>><br>> users is a group role:<br>><br>> CREATE ROLE users<br>> SUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION;<br>><br>> users2 is a group role:<br>> CREATE ROLE users2<br>> NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;<br>> GRANT reports TO users2 ;<br>><br>><br>> I think PostgreSQL doesn't know how to handle this conflicted commands.<br>> What PostgreSQL does when such conflic appears? does it take the last<br>> known command of grant?<br>><br>> Sadly, when there are more than one role it's impossible to know which<br>> role was first. PostgreSQL shows them alphabeticly rather than by date<br>> so in case of overlaping instructions its impossible to know which one<br>> was first.<br>><br>><br>> ב אפר׳ 19, 2017 17:01, Adrian Klaver כתב:<br>><br>> On 04/19/2017 06:49 AM, Ron Ben wrote:<br>><br>> Is it possible to get your email program to left justify text on<br>> sending? I can figure out the right justified text, it just<br>> takes me longer.<br>><br>> > I think I may have found the problem.<br>> ><br>> > The role defined as:<br>> ><br>> > CREATE ROLE "ronb" LOGIN<br>> > NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;<br>> > GRANT users TO "ronb";<br>> > GRANT users2 TO "ronb";<br>> ><br>> > users is a group role:<br>> ><br>> > CREATE ROLE users<br>> > SUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION;<br>> ><br>> > users2 is a group role:<br>> > CREATE ROLE users2<br>> > NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;<br>> > GRANT reports TO users2 ;<br>><br>> That may or may not be the problem. See:<br>><br>> <a href="https://www.postgresql.org/docs/9.6/static/sql-createrole.html">https://www.postgresql.org/docs/9.6/static/sql-createrole.html</a><br>&gt;<br>&gt; "The INHERIT attribute governs inheritance of grantable<br>> privileges (that<br>> is, access privileges for database objects and role<br>> memberships). It<br>> does not apply to the special role attributes set by CREATE ROLE<br>> and<br>> ALTER ROLE. For example, being a member of a role with CREATEDB<br>> privilege does not immediately grant the ability to create<br>> databases,<br>> even if INHERIT is set; it would be necessary to become that<br>> role via<br>> SET ROLE before creating a database."<br>><br>><br>> What you show above is part of the answer. The other parts are the<br>> actual privileges on the objects. Also the command that created<br>> the dump<br>> file that you are trying to restore. Permissions/privileges<br>> issues can<br>> be complex and solving them requires a complete set of information.<br>><br>> ><br>> ><br>> > I think PostgreSQL doesn't know how to handle this conflicted<br>> commands.<br>> > What PostgreSQL does when such conflic appears? does it take<br>> the last<br>> > known command of grant?<br>> ><br>> > Sadly, when there are more than one role it's impossible to<br>> know which<br>> > role was first. PostgreSQL shows them alphabeticly rather than<br>> by date<br>> > so in case of overlaping instructions its impossible to know<br>> which one<br>> > was first.<br>> ><br>> ><br>> > ב אפר׳ 19, 2017 16:40, Adrian Klaver כתב:<br>> ><br>> > On 04/19/2017 03:56 AM, Ron Ben wrote:<br>> > > Hi,<br>> > > I'm using PostgreSQL 9.3.2<br>> > > I'm running the command:<br>> > ><br>> > ><br>> > > psql -h testserver -U ronb -f backup.sql -q -d foldertest<br>> > 2>error.txt<br>> > >>output.txt<br>> ><br>> > What was the command that created backup.sql?<br>> ><br>> > ><br>> > > This should generate my database in foldertest<br>> > ><br>> > > However this doesn't work. It's unable to create schemas<br>> > ><br>> > > in the error.txt i see "permission denied for database<br>> > foldertest".<br>> ><br>> > What user is the foldertest owner?<br>> ><br>> > In psql l will tell you this.<br>> ><br>> > ><br>> > > I know this is not an access permission issue because there is<br>> > a public<br>> > > schema which is buildin and it does create the tables/data in<br>> > there.<br>> ><br>> > Because the public schema is by default open to all:<br>> ><br>> > <a href="https://www.postgresql.org/docs/9.6/static/ddl-schemas.html">https://www.postgresql.org/docs/9.6/static/ddl-schemas.html</a><br>&gt; ><br>> > "A user can also be allowed to create objects in someone else's<br>> > schema.<br>> > To allow that, the CREATE privilege on the schema needs to be<br>> > granted.<br>> > Note that by default, everyone has CREATE and USAGE privileges<br>> > on the<br>> > schema public. This allows all users that are able to connect to<br>> > a given<br>> > database to create objects in its public schema. ... "<br>> ><br>> ><br>> > ><br>> > > It just cant create new schemas.<br>> ><br>> > In psql do dn+, that will show schema owners and who else has<br>> > privileges.<br>> ><br>> > For what the different privileges are and how they are<br>> > represented in<br>> > the above output see:<br>> ><br>> > <a href="https://www.postgresql.org/docs/9.6/static/sql-grant.html">https://www.postgresql.org/docs/9.6/static/sql-grant.html</a><br>&gt; ><br>> > ><br>> > ><br>> > ><br>> > > The intresting thing is that if I do:<br>> > ><br>> > > psql -h testserver -U postgres -f backup.sql -q -d foldertest<br>> > > 2>error.txt >output.txt<br>> > ><br>> > ><br>> > ><br>> > > Everything works. It create all schemas and generate the<br>> > database correctly.<br>> ><br>> > Because the postgres user is a superuser and can do anything.<br>> ><br>> > ><br>> > > I don't see any diffrent in the hba.conf between postgres and<br>> > ronb users.<br>> ><br>> > That is not the issue. pg_hba determines who can connect, what<br>> > you are<br>> > seeing is the Postgres privilege system determining what a user<br>> > can do<br>> > once they are connected. If it had been a pg_hba rejection you<br>> > would<br>> > have seen something like:<br>> ><br>> > aklaver@tito:~> psql -d production -U guest -h localhost<br>> > psql: FATAL: no pg_hba.conf entry for host "::1", user "guest",<br>> > database "production", SSL on<br>> > FATAL: no pg_hba.conf entry for host "::1", user "guest", database<br>> > "production", SSL off<br>> ><br>> ><br>> > To get an overview of what users there are in your database<br>> > cluster in<br>> > psql do du<br>> ><br>> ><br>> > ><br>> > > What can be the problem?<br>> > ><br>> ><br>> ><br>> > --<br>> > Adrian Klaver<br>> > adrian.klaver@aklaver.com<br>> ><br>> ><br>> > --<br>> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)<br>> > To make changes to your subscription:<br>> > <a href="http://www.postgresql.org/mailpref/pgsql-general">http://www.postgresql.org/mailpref/pgsql-general</a><br>&gt; ><br>> ><br>> ><br>><br>><br>> --<br>> Adrian Klaver<br>> adrian.klaver@aklaver.com<br>><br>><br>> --<br>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)<br>> To make changes to your subscription:<br>> <a href="http://www.postgresql.org/mailpref/pgsql-general">http://www.postgresql.org/mailpref/pgsql-general</a><br>&gt;<br>&gt;<br>&gt;<br><br><br>-- <br>Adrian Klaver<br>adrian.klaver@aklaver.com<br><br><br>-- <br>Sent via pgsql-general mailing list (pgsql-general@postgresql.org)<br>To make changes to your subscription:<br><a href="http://www.postgresql.org/mailpref/pgsql-general">http://www.postgresql.org/mailpref/pgsql-general</a><br><br><br></blockquote></blockquote><br></div>
Import Notes
Resolved by subject fallback
Please don't top-post, and trim some of the cruft while you are at it...
On Wednesday, April 19, 2017, Ron Ben <ronb910@walla.co.il
<javascript:_e(%7B%7D,'cvml','ronb910@walla.co.il');>> wrote:
OK. I think I found a bug in PostgreSQL (9.3).
No, it's just that roles and grants are complicated.
When I do:
CREATE ROLE ronb
SUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION;Everything works. I can create the schemas and upload the backup correclty.
If you define a superuser the rest are redundant...
But if I do:
CREATE ROLE "ronb" LOGIN
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
GRANT users TO "ronb";CREATE ROLE users
SUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION;It doesn't work. ronb user still can't create schemas eventhogh the role
users give ronb permission to do so.
The GRANT here is worthless.
Not sure where your schema reference is coming from but none of the items
specified during the create role command are inheritable. Only grants
(object permissions) are. If Ron were to "set user to users" they would
gain superuser and could then do what they need. But if they remain
calling themselves "ronb" they will remain limited. The notes section of
the create role docs emphasize this dynamic.
Also, PostgreSQL doesn't show the GRANTS of role in the same order as they
were given (In PgAdmin). It sort them alphabeticly which is highly
confusing!
If for example you run "alter table x add column" you know that the new
column is added last (if you refresh the table you will see it last).
But if you add another GRANT statment to user it won't be in the last..
you have no way of knowing the correct order of GRANTS.
Grants don't have any significant order - they are purely additive so
either you've got it or you don't.
David J.
On 04/19/2017 11:24 PM, Ron Ben wrote:
OK. I think I found a bug in PostgreSQL (9.3).
When I do:
CREATE ROLE ronb
SUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION;Everything works. I can create the schemas and upload the backup correclty.
But if I do:
CREATE ROLE "ronb" LOGIN
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
GRANT users TO "ronb";CREATE ROLE users
SUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION;It doesn't work. ronb user still can't create schemas eventhogh the role
users give ronb permission to do so.
The GRANT here is worthless.
The below may help you out:
https://www.postgresql.org/docs/9.6/static/role-membership.html
In particular:
"
The role attributes LOGIN, SUPERUSER, CREATEDB, and CREATEROLE can be
thought of as special privileges, but they are never inherited as
ordinary privileges on database objects are. You must actually SET ROLE
to a specific role having one of these attributes in order to make use
of the attribute. Continuing the above example, we might choose to grant
CREATEDB and CREATEROLE to the admin role. Then a session connecting as
role joe would not have these privileges immediately, only after doing
SET ROLE admin.
"
All of this is in aid of solving a dump/restore problem for which still
do not have enough information to help you with. The bare minimum:
1) The command used to make the dump file.
2) The privileges information on the schema you cannot restore as ronb.
3) The error messages from the Postgres log(not your error file) at the
time the restore is done.
Also, PostgreSQL doesn't show the GRANTS of role in the same order as
they were given (In PgAdmin). It sort them alphabeticly which is highly
confusing!
If for example you run "alter table x add column" you know that the new
column is added last (if you refresh the table you will see it last).
But if you add another GRANT statment to user it won't be in the last..
you have no way of knowing the correct order of GRANTS.ב אפר׳ 19, 2017 17:26, Adrian Klaver כתב:
On 04/19/2017 07:16 AM, Ron Ben wrote:
Here :)
Thanks.
See my previous response. Basically we need more information
before this
can be solved.I think I may have found the problem.
The role defined as:
CREATE ROLE "ronb" LOGIN
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
GRANT users TO "ronb";
GRANT users2 TO "ronb";users is a group role:
CREATE ROLE users
SUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION;users2 is a group role:
CREATE ROLE users2
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
GRANT reports TO users2 ;I think PostgreSQL doesn't know how to handle this conflicted
commands.
What PostgreSQL does when such conflic appears? does it take
the last
known command of grant?
Sadly, when there are more than one role it's impossible to
know which
role was first. PostgreSQL shows them alphabeticly rather than
by date
so in case of overlaping instructions its impossible to know
which one
was first.
ב אפר׳ 19, 2017 17:01, Adrian Klaver כתב:
On 04/19/2017 06:49 AM, Ron Ben wrote:
Is it possible to get your email program to left justify text on
sending? I can figure out the right justified text, it just
takes me longer.I think I may have found the problem.
The role defined as:
CREATE ROLE "ronb" LOGIN
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
GRANT users TO "ronb";
GRANT users2 TO "ronb";users is a group role:
CREATE ROLE users
SUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION;users2 is a group role:
CREATE ROLE users2
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
GRANT reports TO users2 ;That may or may not be the problem. See:
https://www.postgresql.org/docs/9.6/static/sql-createrole.html
"The INHERIT attribute governs inheritance of grantable
privileges (that
is, access privileges for database objects and role
memberships). It
does not apply to the special role attributes set by CREATE ROLE
and
ALTER ROLE. For example, being a member of a role with CREATEDB
privilege does not immediately grant the ability to create
databases,
even if INHERIT is set; it would be necessary to become that
role via
SET ROLE before creating a database."What you show above is part of the answer. The other parts are the
actual privileges on the objects. Also the command that created
the dump
file that you are trying to restore. Permissions/privileges
issues can
be complex and solving them requires a complete set ofinformation.
I think PostgreSQL doesn't know how to handle this conflicted
commands.
What PostgreSQL does when such conflic appears? does it take
the last
known command of grant?
Sadly, when there are more than one role it's impossible to
know which
role was first. PostgreSQL shows them alphabeticly rather than
by date
so in case of overlaping instructions its impossible to know
which one
was first.
ב אפר׳ 19, 2017 16:40, Adrian Klaver כתב:
On 04/19/2017 03:56 AM, Ron Ben wrote:
Hi,
I'm using PostgreSQL 9.3.2
I'm running the command:psql -h testserver -U ronb -f backup.sql -q -d foldertest
2>error.txt
output.txt
What was the command that created backup.sql?
This should generate my database in foldertest
However this doesn't work. It's unable to create schemas
in the error.txt i see "permission denied for database
foldertest".
What user is the foldertest owner?
In psql l will tell you this.
I know this is not an access permission issue because there is
a public
schema which is buildin and it does create the tables/data in
there.
Because the public schema is by default open to all:
https://www.postgresql.org/docs/9.6/static/ddl-schemas.html
"A user can also be allowed to create objects in someone else's
schema.
To allow that, the CREATE privilege on the schema needs to be
granted.
Note that by default, everyone has CREATE and USAGE privileges
on the
schema public. This allows all users that are able to connect to
a given
database to create objects in its public schema. ... "It just cant create new schemas.
In psql do dn+, that will show schema owners and who else has
privileges.For what the different privileges are and how they are
represented in
the above output see:https://www.postgresql.org/docs/9.6/static/sql-grant.html
The intresting thing is that if I do:
psql -h testserver -U postgres -f backup.sql -q -d foldertest
2>error.txt >output.txtEverything works. It create all schemas and generate the
database correctly.
Because the postgres user is a superuser and can do anything.
I don't see any diffrent in the hba.conf between postgres and
ronb users.
That is not the issue. pg_hba determines who can connect, what
you are
seeing is the Postgres privilege system determining what a user
can do
once they are connected. If it had been a pg_hba rejection you
would
have seen something like:aklaver@tito:~> psql -d production -U guest -h localhost
psql: FATAL: no pg_hba.conf entry for host "::1", user "guest",
database "production", SSL on
FATAL: no pg_hba.conf entry for host "::1", user "guest",database
"production", SSL off
To get an overview of what users there are in your database
cluster in
psql do duWhat can be the problem?
--
Adrian Klaver
adrian.klaver@aklaver.com--
Sent via pgsql-general mailing list(pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general--
Adrian Klaver
adrian.klaver@aklaver.com--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general--
Adrian Klaver
adrian.klaver@aklaver.com--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general