Thread: allowing users access to a trusted C function
I created (as a superuser) a set of C functions to handle boolean
arithmetic (has to do with simplifying search queries of mine). I would
like users to be able to access those functions somehow. They are very
simple functions so I can't imagine they would be exploited (unless I am
missing something). Is this possible? Otherwise I could use PL/pgSQL,
but I assume that would be significantly slower for a mathematical
operation.
Thanks,
Jeff Davis
Jeff Davis <jdavis@wasabimg.com> writes:
> I created (as a superuser) a set of C functions to handle boolean
> arithmetic (has to do with simplifying search queries of mine). I would
> like users to be able to access those functions somehow.
If the functions are listed in pg_proc, anyone can call them.
regards, tom lane
Tom Lane wrote:
> Jeff Davis <jdavis@wasabimg.com> writes:
> > I created (as a superuser) a set of C functions to handle boolean
> > arithmetic (has to do with simplifying search queries of mine). I would
> > like users to be able to access those functions somehow.
>
> If the functions are listed in pg_proc, anyone can call them.
>
> regards, tom lane
I should have mentioned this, but all my users have their own DB. I have
entries for the functions in pg_proc for template1 and postgres (DB of my
superuser), but they won't be recognized in other DBs.
Thanks,
Jeff Davis
Jeff Davis <jdavis@wasabimg.com> writes:
>>>> I created (as a superuser) a set of C functions to handle boolean
>>>> arithmetic (has to do with simplifying search queries of mine). I would
>>>> like users to be able to access those functions somehow.
>>
>> If the functions are listed in pg_proc, anyone can call them.
> I should have mentioned this, but all my users have their own DB. I have
> entries for the functions in pg_proc for template1 and postgres (DB of my
> superuser), but they won't be recognized in other DBs.
Connect to each other DB (as superuser) and issue the CREATE FUNCTION
commands in that DB.
If you have created the functions in template1, subsequently-created DBs
will inherit the definitions automatically, since CREATE DATABASE clones
the state of template1. But for a pre-existing DB, you gotta make the
pg_proc rows over again.
regards, tom lane
Hi (Merry Christmas and Happy New Millennium everyone!),
I've been wondering what would be the best way to do things like threaded
messages on Postgresql- e.g. where you have the usual parent-child
relationships between entities- each entity has a unique id and a parent id.
So far I'm considering two reasonable options.
<options>
Option 1 - do it the obvious "proper way" - have the application do selects
recursively.
e.g. To select child items
<pseudocode>
traverse ( id, orderspec) {
select id,subdata from tablename where parentid=id order by orderspec
for each id
display(id,subdata)
traverse(id, orderspec)
}
</pseudocode>
Option 2 - do it the kludgy limited way.
Keep the same structure as Option 1 but add a new text column I'll call
geneaology (or gene for short).
For a structure like
1
|_
| |
2 3
|_
| |
4 5
Item 4 will have gene=00000001,00000003,00000004,
Where the ids are all zeropadded _hexadecimal_ and delimited by commas (for
easier human processing if necessary).
So to select all items with parent id=3 (including item id=3) one would use
a query like:
select * from tablename where gene like '00000001,00000003,%' order by
orderspec;
Option 2.1 - use a combination of the two, e.g. use option 1 when the depth
gets crazy.
Option X - any ideas?
</options>
The trouble with option 1 is it seems it will be quite slow when you have
lots of items at significant depths.
The trouble with option 2 is the geneaology column can get quite huge (9 X
depth bytes) if the depth increases (e.g. there is a flame war ;) ). What
is the recommended max indexable text length in Postgresql 7.0? If it won't
index well then option 1 may actually be better.
If option 2 indexes well, I'm thinking of using it and limiting the depth
to say maybe a hundred. I figure in practice it's time for a new thread
once the depth is > 100 - it's mutated enough to warrant a new geneaology ;).
Oracle has a "start with" feature, but even so I'm not sure if the
performance is better that way - it may just be for application level
convenience.
Any comments or suggestions welcome.
Thanks,
Link.
Lincoln Yeoh <lyeoh@pop.jaring.my> writes:
> What is the recommended max indexable text length in Postgresql 7.0?
btree indexes have a hard limit at 1/3rd of a block --- it works out
to be exactly 2700 bytes for 8K blocks. Not sure whether performance
drops off before that.
regards, tom lane
hello all. I suppose I should introduce myself, since I am new around here, and as such will probably be asking a lot of silly questions. I apologize for that in advance. My name is Chris Hayner and I work for Temple University doing various work on all flavors of UNIX. My current task is to work with postgreSQL, specifically on binding it to various LDAP services and replacing mySQL for this task. My question relates to the LD_LIBRARY_PATH problem discussed in number eight on the INSTALL tasks of the recently installed 7.0.3 What I really want to know is how others have gotten around this problem. Everything I have read says that setting this env variable is a bad and dangerous thing to do, but I need a large amount of users to be able to access and use the database engine. Any help on this problem would be greatly appreciated. Thank you for your time, ch
* Chris Hayner <hayner80@astro.ocis.temple.edu> [001227 11:41]: > hello all. > > I suppose I should introduce myself, since I am new around here, and as > such will probably be asking a lot of silly questions. I apologize for > that in advance. My name is Chris Hayner and I work for Temple University > doing various work on all flavors of UNIX. My current task is to work with > postgreSQL, specifically on binding it to various LDAP services and > replacing mySQL for this task. > My question relates to the LD_LIBRARY_PATH problem discussed in number > eight on the INSTALL tasks of the recently installed 7.0.3 > > What I really want to know is how others have gotten around this > problem. Everything I have read says that setting this env variable is a > bad and dangerous thing to do, but I need a large amount of users to be > able to access and use the database engine. Any help on this problem would > be greatly appreciated. In 7.1 (Beta now), it has LD_RUN_PATH (aka -R) set on those operating systems that support it. I was able to remove my LD_LIBRARY_PATH settings when I went to 7.1beta1 on my UnixWare box. Larry > > Thank you for your time, > ch -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: ler@lerctr.org US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
I have a server for which PG_DATA = /home/maxtor, an NFS mounted disk. Normally it works great, but when I try to vacuum, it takes FOREVER. Should I not even try to use remote storage like this? Has anybody else run into a similar problem? Thanks in advance, -- Webb Sprague Programmer O1 Communications
> I have a server for which PG_DATA = /home/maxtor, an NFS mounted > disk. Normally it works great, but when I try to vacuum, it takes > FOREVER. Should I not even try to use remote storage like this? Has > anybody else run into a similar problem? NFS is slow, and very CPU-intensive. On a 100-megabit switched link with multiple, fast processors on the NFS machine, you get up to a maximum of around 1/4 of the throughput that you would having a single, fast drive in the database machine. A handicap like that is bad for a database. : ) I imagine that you are mounting the data directory from a file server for backup purposes and disk redundancy protection. We spent a little more money, and put another RAID array in our database server for disk redundancy, and each night a pg_dumpall runs, with the output gzipped and THEN copied via NFS to the file server, to be included on the nightly DAT backup. steve
I guess this is a little off topic, but anyhow... See: man 8 ld.so man 8 ldconfig LD_LIBRARY_PATH is generally harmless. Set inside a user's .profile, it allows a user to have user-specific searchable lib directories in addition to global lib directories kept in /etc/ld.so.conf and/or a globally set LD_LIBRARY_PATH. You can just put the postgresql lib directory in /etc/ld.so.conf and run ldconfig. Recently, I was starting to run two versions of postgres on the same computer, so I had to take the lib out of /etc/ld.so.conf and start putting it into user .profiles' LD_LIBRARY_PATH. Some users will use one pg lib directory for one version of postgres, and other users have a different pg lib dir in LD_LIBRARY_PATH so that the right libraries are used for the different versions installed. Likewise, users of different versions of pg have different $PGHOME, $PGLIB, $PGDATA, $PGPORT, and $PATH settings so that the right versions are executed on their paths. Each version/instance has to use a different port. The only danger that I could imagine, if it is even possible, is some local users compiling their own versions of system libraries with compromising code in them. Then, putting those libs in their LD_LIBRARY_PATH and running a program that uses those libs and is suid root. Again, I'm not sure that kind of breach is possible though or if it is, how to lock down an environment variable so it can't be abused. On Wednesday 27 December 2000 12:40, Chris Hayner wrote: > hello all. > > I suppose I should introduce myself, since I am new around here, and as > such will probably be asking a lot of silly questions. I apologize for > that in advance. My name is Chris Hayner and I work for Temple University > doing various work on all flavors of UNIX. My current task is to work with > postgreSQL, specifically on binding it to various LDAP services and > replacing mySQL for this task. > My question relates to the LD_LIBRARY_PATH problem discussed in number > eight on the INSTALL tasks of the recently installed 7.0.3 > > What I really want to know is how others have gotten around this > problem. Everything I have read says that setting this env variable is a > bad and dangerous thing to do, but I need a large amount of users to be > able to access and use the database engine. Any help on this problem would > be greatly appreciated. > > Thank you for your time, > ch -- -------- Robert B. Easter reaster@comptechnews.com --------- - CompTechNews Message Board http://www.comptechnews.com/ - - CompTechServ Tech Services http://www.comptechserv.com/ - ---------- http://www.comptechnews.com/~reaster/ ------------
On Wed, Dec 27, 2000 at 01:40:09PM -0500, Robert B. Easter wrote:
> See:
> man 8 ld.so
> man 8 ldconfig
>
> LD_LIBRARY_PATH is generally harmless. Set inside a user's .profile, it
Coming from a linux world?
hawkeye:/export/home/b015414$ ls /etc/ld*
/etc/ld*: No such file or directory
Linux acts somewhat differently with respect to LD_* variables when
compared to other systems.
> Recently, I was starting to run two versions of postgres on the same
> computer, so I had to take the lib out of /etc/ld.so.conf and start putting
> it into user .profiles' LD_LIBRARY_PATH. Some users will use one pg lib
This is probably the hardest way of doing this.
You, as an admin, should never try to maintain the users .profiles.
Too many things can go wrong. User can mis-edit it. User can accidently
erase it. User can change shell and not even use a .profile.
Instead, put that stuff into the system profiles (/etc/profile,
/etc/csh.login). Either base it upon primary group, for instance, or
a couple of list of files or something. But this is definitely asking
for administrative nightmares.
Just think if you have to move all users from one version to another. You
would prefer to edit each person's .profile rather than one central one?
Speaking of administrative nightmares, did I read correctly that 7.1
now users -R by default when linking? Ack! Doesn't that make it nearly
impossible for an administrator to move libraries around as necessary?
I'm thinking especially if trying to use a pre-packaged binary, and
trying to put it into a different location. I.e., maybe a shared /opt
style directory structure where the path may include arch/os information
that doesn't match what the original builder used.
mrc
--
Mike Castle Life is like a clock: You can work constantly
dalgoda@ix.netcom.com and be right all the time, or not work at all
www.netcom.com/~dalgoda/ and be right at least twice a day. -- mrc
We are all of us living in the shadow of Manhattan. -- Watchmen
Mike Castle writes: > Linux acts somewhat differently with respect to LD_* variables when > compared to other systems. Not really. > Speaking of administrative nightmares, did I read correctly that 7.1 > now users -R by default when linking? Ack! Doesn't that make it nearly > impossible for an administrator to move libraries around as necessary? If you move files to a place that is different from the one they are supposed to be in you're going to have more severe problems than this one. This is not only true for PostgreSQL. -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
On Wednesday 27 December 2000 15:09, Mike Castle wrote: > On Wed, Dec 27, 2000 at 01:40:09PM -0500, Robert B. Easter wrote: > Just think if you have to move all users from one version to another. You > would prefer to edit each person's .profile rather than one central one? When I set it up, I did make central files, like /etc/pgsql.d/pgsql.sh and pgcvs.sh. In user's .profiles, I added ". /etc/pgsql.d/pgsql.sh" or ". /etc/pgsql.d/pgcvs.sh". One sh file is for the production release, and the other is for users I have setup to play with the cvs version. I'm the only user on the system here, so I'm not worried. But yes, I can change the central files to affect all user .profiles. -- -------- Robert B. Easter reaster@comptechnews.com --------- - CompTechNews Message Board http://www.comptechnews.com/ - - CompTechServ Tech Services http://www.comptechserv.com/ - ---------- http://www.comptechnews.com/~reaster/ ------------
Peter Eisentraut wrote:
: Mike Castle writes:
:
: > Speaking of administrative nightmares, did I read correctly that 7.1
: > now users -R by default when linking? Ack! Doesn't that make it nearly
: > impossible for an administrator to move libraries around as necessary?
:
: If you move files to a place that is different from the one they are
^^^^^
: supposed to be in you're going to have more severe problems than this one.
: This is not only true for PostgreSQL.
As I understand Mike speak not about files in general but about
libraries (and possible have in mind executables too). And hardcoded
patch to libraries is nightmare, IMO.
--
Andrew W. Nosenko (awn@bcs.zp.ua)
Andrew W. Nosenko wrote: : Peter Eisentraut wrote: : : Mike Castle writes: : : : : > Speaking of administrative nightmares, did I read correctly that 7.1 : : > now users -R by default when linking? Ack! Doesn't that make it nearly : : > impossible for an administrator to move libraries around as necessary? : : : : If you move files to a place that is different from the one they are : ^^^^^ : : supposed to be in you're going to have more severe problems than this one. : : This is not only true for PostgreSQL. : : As I understand Mike speak not about files in general but about : libraries (and possible have in mind executables too). And hardcoded : patch to libraries is nightmare, IMO. ^^^^^ typo, path of course. -- Andrew W. Nosenko (awn@bcs.zp.ua)
Andrew W. Nosenko writes: > As I understand Mike speak not about files in general but about > libraries (and possible have in mind executables too). And hardcoded > patch to libraries is nightmare, IMO. The deal is this: As a general rule, in any software package that you build from source via ./configure; make; make install you *must* install the files at the location that you told configure (with --prefix, etc.). It may be the case that the particular package doesn't care (e.g., GNU make doesn't care because it only installs a single executable and some documentation), but just about any package that installs more than one file has this requirement. It's not just hardcoded library paths, it's executables containing paths to data and configuration files, data files containing paths to library files and other data files, libraries containing paths to configuration files, etc. So the discussion about what happens when you move your libraries is essentially pointless. Certainly, the interface offered by most linkers to control runtime paths isn't ideal, but it's much better than not having one at all. I'm aware that there are circumstances where you really don't want to hardcode the library path. Then you can use 'configure --disable-rpath'. -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/