Re: [GENERAL] 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@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
>         >
>         >
>         >
>
>
>         --
>         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