作者
digoal
日期
2021-05-07
标签
PostgreSQL , 大对象
背景
PostgreSQL 9.0 开始大对象的改进 - 增加 pg_largeobject_metadata 表用于查询大对象oid和对应的owner与权限.
以前的版本pg_largeobject是对public开放的, 所以存在很大的风险, 所有用户都能查看pg_largeobject的内容(包括数据).
如果你的应用程序还停留在9.0以前, 你会发现迁移到大版本后, 没法查pg_largeobject这个表了, 不要着急, 改一下查询pg_largeobject_metadata就可以, 这是更安全的做法.
https://www.postgresql.org/docs/13/catalog-pg-largeobject.html
例子
1、导入大对象
```
vi abc.txt
hello world, i am digoal.
i love postgresql.
today is 2021-05-07
```
```
IT-C02YW2EFLVDL:~ digoal$ psql
psql (13.2)
Type "help" for help.
postgres=> \lo_import pwd/abc.txt
lo_import 16404
postgres=> \lo_import pwd/abc.txt
lo_import 16405
```
2、在table中引用大对象
postgres=> create table abc(id int, file oid);
CREATE TABLE
postgres=> insert into abc values(1, 16404);
INSERT 0 1
3、查询大对象属性
```
postgres=> \lo_list
Large objects
ID | Owner | Description
-------+--------+-------------
16404 | digoal |
16405 | digoal |
(2 rows)
postgres=> select * from pg_largeobject_metadata ;
oid | lomowner | lomacl
-------+----------+--------
16404 | 16394 |
16405 | 16394 |
(2 rows)
```
4、直接查询pg_largeobject报权限出错
postgres=> select * from pg_largeobject;
ERROR: permission denied for table pg_largeobject
postgres=> select loid from pg_largeobject;
ERROR: permission denied for table pg_largeobject
5、大对象权限配置
```
postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# create role new;
CREATE ROLE
postgres=# alter role new login;
ALTER ROLE
postgres=# \c postgres digoal
You are now connected to database "postgres" as user "digoal".
postgres=> \h alter large
Command: ALTER LARGE OBJECT
Description: change the definition of a large object
Syntax:
ALTER LARGE OBJECT large_object_oid OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
URL: https://www.postgresql.org/docs/13/sql-alterlargeobject.html
postgres=> \h grant large
Command: GRANT
Description: define access privileges
```
```
postgres=> grant select on large object 16404 to new;
GRANT
postgres=> select * from pg_largeobject_metadata ;
oid | lomowner | lomacl
-------+----------+---------------------------------
16405 | 16394 |
16404 | 16394 | {digoal=rw/digoal,new=r/digoal}
(2 rows)
```
6、owner导出大对象
```
postgres=# \c postgres digoal
You are now connected to database "postgres" as user "digoal".
postgres=> \lo_export 16405 pwd/abc1.txt
lo_export
IT-C02YW2EFLVDL:~ digoal$ cat abc1.txt
hello world, i am digoal.
i love postgresql.
today is 2021-05-07
```
7、非owner导出大对象
``
postgres=> \c postgres new
You are now connected to database "postgres" as user "new".
postgres=> \lo_export 16405pwd`/abc2.txt
ERROR: permission denied for large object 16405
postgres=> \lo_export 16404 pwd/abc2.txt
lo_export
IT-C02YW2EFLVDL:~ digoal$ cat abc2.txt
hello world, i am digoal.
i love postgresql.
today is 2021-05-07
```
8、查询没有link的大对象
实际上就是检查oid类型的字段中是否有大对象对应的oid. 没有就说明这个大对象没有被关联
IT-C02YW2EFLVDL:~ digoal$ vacuumlo -n -v -U postgres postgres
Connected to database "postgres"
Test run: no large objects will be removed!
Checking file in public.abc
Would remove 1 large objects from database "postgres".
9、清除没有link的大对象大对象
```
客户端命令 :
vacuumlo -v -U postgres postgres
或 SQL :
select lo_unlink(oid);
或 psql :
\lo_unlink oid
```
vacuumlo -v -U postgres postgres
Connected to database "postgres"
Checking file in public.abc
Successfully removed 1 large objects from database "postgres".
202012/20201205_01.md 《PostgreSQL 大对象使用》
201906/20190607_04.md 《PostgreSQL psql的元素周期表 - 包括大对象操作》
201802/20180201_03.md 《[转] 关于入侵PostgreSQL的那些事儿（文件读取写入、命令执行的办法） -大对象》
201702/20170213_01.md 《大对象 - 数据库common安全自动渗透测试与防范 - sqlmap》
201610/20161018_02.md 《大对象攻击 - Hacking PostgreSQL》
201606/20160614_01.md 《在java中正确使用PostgreSQL大对象和字节流(bytea)类型的姿势》
201306/20130626_01.md 《PostgreSQL 大对象或bytea存取pdf,jpg等文件 - PostgreSQL export regular file like pdf, word, text, doc stored in bytea type》
201305/20130510_02.md 《大对象 - PostgreSQL 9.3 Add API for 64-bit large object access》
201303/20130306_01.md 《大对象 - Use PostgreSQL server program import binary data into database bytea type》
201301/20130109_01.md 《PostgreSQL large row|column performance tuning case - 包括大对象》
说明
1、large object 不支持逻辑订阅, 因为数据在pg_largeobjects中, 不是在publication的table中, 也许未来可以支持. 但还需要解决权限问题, 因为每条object的权限都是可以单独赋予的.
https://www.postgresql.org/docs/current/logical-replication-restrictions.html
2、pg_dump逻辑导出支持导出大对象, 导出导入时支持大对象的权限、owner相关属性的备份和恢复.
```
-- Name: 16404; Type: BLOB; Schema: -; Owner: digoal
SELECT pg_catalog.lo_create('16404');
ALTER LARGE OBJECT 16404 OWNER TO digoal;
-- -- Name: 16416; Type: BLOB; Schema: -; Owner: digoal --
SELECT pg_catalog.lo_create('16416');
ALTER LARGE OBJECT 16416 OWNER TO digoal;
-- -- Name: 16417; Type: BLOB; Schema: -; Owner: digoal --
SELECT pg_catalog.lo_create('16417');
ALTER LARGE OBJECT 16417 OWNER TO digoal;
-- -- Data for Name: abc; Type: TABLE DATA; Schema: public; Owner: digoal --
COPY public.abc (id, file) FROM stdin; 1 16404 .
-- -- Data for Name: BLOBS; Type: BLOBS; Schema: -; Owner: - --
BEGIN;
SELECT pg_catalog.lo_open('16404', 131072); SELECT pg_catalog.lowrite(0, '\x68656c6c6f20776f726c642c206920616d206469676f616c2e0a69206c6f766520706f737467726573716c2e0a746f64617920697320323032312d30352d30370a'); SELECT pg_catalog.lo_close(0);
SELECT pg_catalog.lo_open('16416', 131072); SELECT pg_catalog.lowrite(0, '\x68656c6c6f20776f726c642c206920616d206469676f616c2e0a69206c6f766520706f737467726573716c2e0a746f64617920697320323032312d30352d30370a'); SELECT pg_catalog.lo_close(0);
SELECT pg_catalog.lo_open('16417', 131072); SELECT pg_catalog.lowrite(0, '\x68656c6c6f20776f726c642c206920616d206469676f616c2e0a69206c6f766520706f737467726573716c2e0a746f64617920697320323032312d30352d30370a'); SELECT pg_catalog.lo_close(0);
COMMIT;
-- -- Name: LARGE OBJECT 16404; Type: ACL; Schema: -; Owner: digoal --
GRANT SELECT ON LARGE OBJECT 16404 TO new;
```
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
