Postgres Pro
Downloads
Home   >   mailing lists

Thread: pg_largeobjects

pg_largeobjects

From
James Sewell
Date:
Hello all,

I have a table which makes use of pg_largeobjects. I am inserting rows into the table as user1. If I connect to the database as user2 I can SELECT data, but can not open the large object for reading (user1 can do this). I don't want to set lo_compat_privileges as then user3 (who can't SELECT from the services tables) would be able to read the large object.

I realise that pg_largeobjects is "partially obsolete" and I could solve the problem by just using TOAST, but this still seems incorrect.

As an aside, is there any reason to use pg_largeobjects if I am storing data under 1GB which doesn't require random reads any more? My impression is no?

SELECT * from services where payload = '51414';
-[ RECORD 1 ]--------------+------------------------
id                         | 2263
insert_date                | 2013-08-16 15:39:56.774
instance                   | createApp4
payload                    | 51414
update_date                | 2013-09-09 09:39:31.454
pe_service_transactions_id | 2262]

\lo_list
Large objects
-[ RECORD 1 ]--------
ID          | 51414
Owner       | user1
Description |

SELECT loread(lo_open(51414, 262144), 999999999);
ERROR:  permission denied for large object 51414


James Sewell,
PostgreSQL Team Lead / Solutions Architect
______________________________________
 

Level 2, 50 Queen St, Melbourne VIC 3000

P (03) 8370 8000  W www.lisasoft.com  F(03) 8370 8000
 

The contents of this email are confidential and may be subject to legal or professional privilege and copyright. No representation is made that this email is free of viruses or other defects. If you have received this communication in error, you may not copy or distribute any part of it or otherwise disclose its contents to anyone. Please advise the sender of your incorrect receipt of this correspondence.

Re: pg_largeobjects

From
John R Pierce
Date:
On 9/10/2013 9:49 PM, James Sewell wrote:
>
> As an aside, is there any reason to use pg_largeobjects if I am
> storing data under 1GB which doesn't require random reads any more? My
> impression is no?

one good reason to use LO is so you can read the data like its a
file.    me, I'd as soon use NFS or whatever for that, with a file path
in the database, but I understand there's scenarios where thats not
practical.

--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

Re: pg_largeobjects

From
Raghavendra
Date:


On Wed, Sep 11, 2013 at 10:19 AM, James Sewell <james.sewell@lisasoft.com> wrote:
Hello all,

I have a table which makes use of pg_largeobjects. I am inserting rows into the table as user1. If I connect to the database as user2 I can SELECT data, but can not open the large object for reading (user1 can do this). I don't want to set lo_compat_privileges as then user3 (who can't SELECT from the services tables) would be able to read the large object.


GRANT SELECT,UPATE ON LARGE OBJECT to user2; 

Will this work...

---
Regards,
Raghavendra
EnterpriseDB Corporation

Re: pg_largeobjects

From
Kevin Grittner
Date:
James Sewell <james.sewell@lisasoft.com> wrote:

> is there any reason to use pg_largeobjects if I am storing data
> under 1GB which doesn't require random reads any more?

If individual large objects might need to be referenced from
multiple locations, it gives you an easy way to do that without
needing to create a new table with id and document columns (and
possibly a comment column).

It gives you the ability to stream documents in rather than
including them in a SQL statement, which can reduce stress on RAM.

It gives you the ability to set the security for individual
documents, although it sounds like that is not something you find
useful.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: pg_largeobjects

From
James Sewell
Date:
Hey,

This does work, but as I'm using DEFAULT PRIVs to give access to tables it becomes a (the only) step which can't be done at schema creation time and has to be done at data insertion time.

It feels to me that ALTER DEFAULT PRIVILEGES should be extended to support large objects (either by default from the table permissions or as a new GRANT option). Thoughts on this?

Cheers,
 


James Sewell,
PostgreSQL Team Lead / Solutions Architect
______________________________________
 

Level 2, 50 Queen St, Melbourne VIC 3000

P (03) 8370 8000  W www.lisasoft.com  F(03) 8370 8000
 


On Wed, Sep 11, 2013 at 6:40 PM, Raghavendra <raghavendra.rao@enterprisedb.com> wrote:


On Wed, Sep 11, 2013 at 10:19 AM, James Sewell <james.sewell@lisasoft.com> wrote:
Hello all,

I have a table which makes use of pg_largeobjects. I am inserting rows into the table as user1. If I connect to the database as user2 I can SELECT data, but can not open the large object for reading (user1 can do this). I don't want to set lo_compat_privileges as then user3 (who can't SELECT from the services tables) would be able to read the large object.


GRANT SELECT,UPATE ON LARGE OBJECT to user2; 

Will this work...

---
Regards,
Raghavendra
EnterpriseDB Corporation



The contents of this email are confidential and may be subject to legal or professional privilege and copyright. No representation is made that this email is free of viruses or other defects. If you have received this communication in error, you may not copy or distribute any part of it or otherwise disclose its contents to anyone. Please advise the sender of your incorrect receipt of this correspondence.