Re: [GENERAL] Unable to upload backups - Mailing list pgsql-general
From | Adrian Klaver |
---|---|
Subject | Re: [GENERAL] Unable to upload backups |
Date | |
Msg-id | 5dec6c34-e38e-93a9-0a98-08ee727b5327@aklaver.com Whole thread Raw |
In response to | Re: [GENERAL] Unable to upload backups (Ron Ben <ronb910@walla.co.il>) |
List | pgsql-general |
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
pgsql-general by date: