Search Postgresql Archives

Re: Unable to upload backups

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

 



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



[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