Largeobject Access Controls (r2460)
in [PgSql]
|
Prev: explain output infelicity in psql
Next: Winflex
From: Takahiro Itagaki on 11 Dec 2009 00:54 Jaime Casanova <jcasanov(a)systemguards.com.ec> wrote: > besides if a normal user can read from pg_class why we deny pg_largeobject pg_class and pg_largeobject_metadata contain only metadata of objects. Tables and pg_largeobject contain actual data of the objects. A normal user can read pg_class, but cannot read contents of tables without privilege. Regards, --- Takahiro Itagaki NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: KaiGai Kohei on 11 Dec 2009 02:17 KaiGai Kohei wrote: > Takahiro Itagaki wrote: >> KaiGai Kohei <kaigai(a)ak.jp.nec.com> wrote: >> >>> Tom Lane wrote: >>>> Takahiro Itagaki <itagaki.takahiro(a)oss.ntt.co.jp> writes: >>>>> <structname>pg_largeobject</structname> should not be readable by the >>>>> public, since the catalog contains data in large objects of all users. >>>> This is going to be a problem, because it will break applications that >>>> expect to be able to read pg_largeobject. Like, say, pg_dump. >>> Is it a right behavior, even if we have permission checks on large objects? >> Can we use column-level access control here? >> >> REVOKE ALL ON pg_largeobject FROM PUBLIC; >> => GRANT SELECT (loid) ON pg_largeobject TO PUBLIC; > > Indeed, it seems to me reasonable. > >> We use "SELECT loid FROM pg_largeobject LIMIT 1" in pg_dump. We could >> replace pg_largeobject_metadata instead if we try to fix only pg_dump, >> but it's no wonder that any other user applications use such queries. >> I think to allow reading loid is a balanced solution. > > Right, I also remind this query has to be fixed up by other reason right now. > If all the large objects are empty, this query can return nothing, even if > large object entries are in pg_largeobject_metadata. > > Please wait for a while. The attached patch fixes these matters. * It adds "GRANT SELECT (loid) ON pg_largeobject TO PUBLIC;" during initdb phase to resolve the matter pointed out. * A few queries in pg_dump were fixed to select pg_largeobject_metadata instead of pg_largeobject. If a dumpable large obejct is empty (it means no page frames are on pg_largeobject), pg_dump misunderstand no such large object is here. We have to reference pg_largeobject_metadata to check whether a certain large objct exists, or not. Thanks, $ diffstat ~/pgsql-blob-priv-fix.patch doc/src/sgml/catalogs.sgml | 3 !!! src/bin/initdb/initdb.c | 1 + src/bin/pg_dump/pg_dump.c | 8 !!!!!!!! src/test/regress/expected/privileges.out | 15 +++++++++++++++ src/test/regress/sql/privileges.sql | 8 ++++++++ 5 files changed, 24 insertions(+), 11 modifications(!) -- OSS Platform Development Division, NEC KaiGai Kohei <kaigai(a)ak.jp.nec.com>
From: Takahiro Itagaki on 11 Dec 2009 02:35 KaiGai Kohei <kaigai(a)ak.jp.nec.com> wrote: > The attached patch fixes these matters. I'll start to check it. > We have to reference pg_largeobject_metadata to check whether a certain > large objct exists, or not. What is the situation where there is a row in pg_largeobject_metadata and no corresponding rows in pg_largeobject? Do we have a method to delete all rows in pg_largeobject but leave some metadata? Regards, --- Takahiro Itagaki NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: KaiGai Kohei on 11 Dec 2009 02:46 Takahiro Itagaki wrote: > KaiGai Kohei <kaigai(a)ak.jp.nec.com> wrote: > >> The attached patch fixes these matters. > > I'll start to check it. Thanks, >> We have to reference pg_largeobject_metadata to check whether a certain >> large objct exists, or not. > > What is the situation where there is a row in pg_largeobject_metadata > and no corresponding rows in pg_largeobject? Do we have a method to > delete all rows in pg_largeobject but leave some metadata? It is a case when we create a new large object, but write nothing. postgres=# SELECT lo_create(1234); lo_create ----------- 1234 (1 row) postgres=# SELECT * FROM pg_largeobject_metadata WHERE oid = 1234; lomowner | lomacl ----------+-------- 10 | (1 row) postgres=# SELECT * FROM pg_largeobject WHERE loid = 1234; loid | pageno | data ------+--------+------ (0 rows) In this case, the following two queries are not equivalent. * SELECT oid FROM pg_largeobject_metadata * SELECT DISTINCT loid FROM pg_largeobject The second query does not return the loid of empty large objects. The prior implementation inserted a zero-length page to show here is a large object with this loid, but it got unnecessary with this enhancement. If we need compatibility in this level, we can insert a zero-length page into pg_largeobject on LargeObjectCreate(). It is harmless, but its worth is uncertain. Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei <kaigai(a)ak.jp.nec.com> -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Takahiro Itagaki on 11 Dec 2009 03:34
KaiGai Kohei <kaigai(a)ak.jp.nec.com> wrote: > >> We have to reference pg_largeobject_metadata to check whether a certain > >> large objct exists, or not. > It is a case when we create a new large object, but write nothing. OK, that makes sense. In addition of the patch, we also need to fix pg_restore with --clean option. I added DropBlobIfExists() in pg_backup_db.c. A revised patch attached. Please check further mistakes. BTW, we can optimize lo_truncate because we allow metadata-only large objects. inv_truncate() doesn't have to update the first data tuple to be zero length. It only has to delete all corresponding tuples like as: DELETE FROM pg_largeobject WHERE loid = {obj_desc->id} Regards, --- Takahiro Itagaki NTT Open Source Software Center |