Search Postgresql Archives

Re: Unable to upload backups

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxx
        >
        >
        > --
        > Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
        > To make changes to your subscription:
        > http://www.postgresql.org/mailpref/pgsql-general
        >
        >
        >


        --
        Adrian Klaver
        adrian.klaver@xxxxxxxxxxx


        --
        Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
        To make changes to your subscription:
        http://www.postgresql.org/mailpref/pgsql-general





--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux