I have created a
sales schema in Postgres.
(so in total there are
sales and the default
I added the user
salesuser to Postgres, and granted it the access to the
This is how I did that:
CREATE ROLE salesuser WITH LOGIN PASSWORD 'password';
GRANT CONNECT ON DATABASE database_name TO salesuser;
GRANT USAGE ON SCHEMA sales TO salesuser;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA sales TO salesuser;
ALTER ROLE salesuser SET search_path to sales;
Now, I'm trying to do a backup of the schema
sales using the pg_dump and the user
The expected result is to backup only the schema
sales, as it's the only schema the user
salesuser is allowed access to.
The command I use is:
pg_dump -h localhost -p 5432 -U salesuser -d database_name > /tmp/2223.dmp (note there is no
Unfortunately, somehow the pg_dump tries to dump also the
public schema, to which the user
salesuser doesn't have access rights to, as the error below is displayed:
pg_dump: [archiver (db)] query failed: ERROR: permission denied for relation customer
pg_dump: [archiver (db)] query was: LOCK TABLE public.customer IN ACCESS SHARE MODE
I assumed that the pg_dump command with a user which has only access to a single specific schema, would backup only this schema, and other schemas (such as
public) would be omitted.
I need to avoid defining the
--schema= parameter for the pg_dump.
I need to focus on access rights of the user with the use case to backup all the schemas the user
salesuser is allowed to access to.
How is it possible? What am I missing?