<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>><br>> "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>> ><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>> ><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(at)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(dot)klaver(at)aklaver(dot)com<br>> ><br>> ><br>> > --<br>> > Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)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>> ><br>><br>><br>> --<br>> Adrian Klaver<br>> adrian(dot)klaver(at)aklaver(dot)com<br>><br>><br>> --<br>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)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>><br><br><br>-- <br>Adrian Klaver<br>adrian(dot)klaver(at)aklaver(dot)com<br><br><br>-- <br>Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)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>