backup - minimum postgresql permissions for pg_dump --schema-only - Stack Overflow

admin管理员组

文章数量:1296278

I'm trying to create a dedicated role/user in postgresql and I want to grant only the minium privileges for a schema inside a database, but I don't really understand how this works.

Of course I know I need to grant the connect privilege, but then things become somehow contorted when it comes to privileges on schemas only or on a certain schema and I'm not sure how the default privileges works in this case.

Ideally I would like to be able to have read-only access to all schemas in that particular database, but just one schema would suffice initially.

I also see that pg_dump --schema-only implies temporarily locking the database, so I probably also need a privilege for that too.

I'm using postgresql 17.2.1 I'm looking forward to someone helping me :)

I'm trying to create a dedicated role/user in postgresql and I want to grant only the minium privileges for a schema inside a database, but I don't really understand how this works.

Of course I know I need to grant the connect privilege, but then things become somehow contorted when it comes to privileges on schemas only or on a certain schema and I'm not sure how the default privileges works in this case.

Ideally I would like to be able to have read-only access to all schemas in that particular database, but just one schema would suffice initially.

I also see that pg_dump --schema-only implies temporarily locking the database, so I probably also need a privilege for that too.

I'm using postgresql 17.2.1 I'm looking forward to someone helping me :)

• postgresql
• backup
• pg-dump

Share Improve this question Follow asked Feb 12 at 9:41 LethargosLethargos 73277 silver badges1616 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

For most PostgreSQL metadata, you don't need any special privileges to read them. So to dump those objects, all you need for a schema-only dump is the right to connect to the database.

In addition, pg_dump puts an ACCESS SHARE lock on all tables, so that they cannot be changed or removed while pg_dump is at work.

The only catalog tables with restricted privileges are pg_authid (roles), pg_largeobject (large objects), pg_statistic, pg_statistic_ext_data (both contain optimizer statistics) and pg_subscription (logical replication subscriptions). The only one that is relevant here is pg_subscription, and in particular the column that contains the connect information to the publisher.

So I would say that unless you need a schema-only dump of a database with subscriptions in it, all you need is the SELECT privilege on all the tables you want to dump and the USAGE privilege on the schemas that contain these tables. It takes a superuser to dump subscriptions.

本文标签： backupminimum postgresql permissions for pgdump schemaonlyStack Overflow