Re: Unable to upload backups

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.txt
> > >
> > >
> > >
> > > Everything 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(at)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(dot)klaver(at)aklaver(dot)com
> >
> >
> > --
> > 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
> >
> >
> >
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>
>
> --
> 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
>
>
>

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