pg_dump 用户不是超级管理员和大对象 [英] pg_dump with user dont superadmin and large objects

问题描述

我与非超级管理员的用户开始了长期的数据库导出工作.但是我发现在新版本的postgresql中只有超级管理员才能访问大对象的问题.

I have started a long career for export databases with users who are not superadmins. But I found the problem that in the new versions of postgresql only superadmins can access large objects.

ERROR: permission denied for large object 5141

没有办法做到这一点:

GRANT SELECT ON ALL LARGE OBJECTS TO role_name;

我想制作一个触发器，当创建一个大对象(表 pg_catalog.pg_largeobject)时，给我的用户权限.但不允许在系统目录上触发.

I thought making a triger and when a large object was created (table pg_catalog.pg_largeobject), give my user permissions. But triggers on system catalog are not allowed.

我得出的结论是，如果您不是超级用户，则无法导出.对吗?

I have come to the conclusion that you can not export if you are not superuser. Is this right?

推荐答案

lo_compat_privileges 说明情况:

lo_compat_privileges(布尔值)

在 9.0 之前的 PostgreSQL 版本中，大对象没有访问权限，因此始终可以由所有用户.将此变量设置为 on 将禁用新权限检查与以前版本的兼容性.默认为关闭.只有超级用户才能更改此设置.

In PostgreSQL releases prior to 9.0, large objects did not have access privileges and were, therefore, always readable and writable by all users. Setting this variable to on disables the new privilege checks, for compatibility with prior releases. The default is off. Only superusers can change this setting.

设置这个变量不会禁用所有与大对象相关的安全检查——只有那些默认行为有的在 PostgreSQL 9.0 中更改.例如，lo_import() 和 lo_export()无论此设置如何，都需要超级用户权限.

Setting this variable does not disable all security checks related to large objects — only those for which the default behavior has changed in PostgreSQL 9.0. For example, lo_import() and lo_export() need superuser privileges regardless of this setting.

请注意，这可以为每个数据库设置:

Note that this can be set per database with:

ALTER DATABASE dbname SET lo_compat_privileges=on;

当lo_compat_privileges 设置为其默认的OFF 时，访问权限不仅限于超级用户，而是受控制的.每个大对象都有一个所有者，并且该所有者拥有完全的权限.可以一次(由所有者或超级用户)为每个大对象授予读取权限:

When lo_compat_privileges is set to its default OFF, access is not limited to superusers, but controlled. Each large object has an owner and that owner has full rights. Read permissions can be given for each large object at a time (by owner or superuser) with :

GRANT SELECT ON LARGE OBJECT loid TO rolename;

其中 rolename 也可以是 PUBLIC 表示每个人"，请参阅 GRANT 了解详情.

where rolename may also be PUBLIC to mean "everyone", see GRANT for the details.

另一方面，我认为不可能使用单个命令对数据库的所有大型对象设置权限，或者提前对尚不存在的内容设置权限，因为 ALTER DEFAULT PRIVILEGES 不支持大对象，正如您所注意到的，触发器不是一种选择.

On the other hand, I don't think it's possible to set permissions on all large objects of a database with a single command, or in advance on not-yet existing contents, since ALTER DEFAULT PRIVILEGES has no support for large objects, and as you note, triggers are not an option.

对数据库的所有大对象具有 SELECT 权限的非超级用户可以 pg_dump 该数据库而不会出错.

A non-superuser who has SELECT permissions on all the large objects of a database can pg_dump this database without error.

这篇关于pg_dump 用户不是超级管理员和大对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！