Postgresql – How to backup PostgreSQL database (a schema) where a user has access to only one schema

backuppostgresqlschema

I have created a sales schema in Postgres.
(so in total there are sales and the default public schemas)

I added the user salesuser to Postgres, and granted it the access to the sales schema.

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 salesuser.
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 --schema= or -n parameter)

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 -n or --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?

Best Answer

Looks like usage privilege on public schema has been revoked from salesuser. So, you are getting the permission denied error while dumping the data. RESTRICTING PUBLIC SCHEMA

pg_dump, in general will backup all the schemas(sales and public) of your database. Hence, you need to explicitly mention -n sales or --schema=schema_name while taking the pg_dump and it will work fine.

You can issue command \dn+ in psql to view the schemas and access permissions.

Related Question