Re: [GENERAL] User Privileges

[W. van den Akker]

How do I retrieve this privilages?
I want to disable menu-options within a program. For that I have to
retrieve the privilages for some tables.

gr,

Willem

- Original Message -
From: "Dan Wilson" <[EMAIL PROTECTED]>
To: "Niral Trivedi" <[EMAIL PROTECTED]>;
<[EMAIL PROTECTED]>
Sent: Tuesday, December 26, 2000 7:25 PM
Subject: Re: [GENERAL] User Privileges


> > For example I have 5 tables in database A. And now I want to give
> > SELECT/UPDATE/INSERT privileges to a user to all 5 tables. But according
> to
> > documentation, I have to execute 'GRANT' query 3 times(for
> > select/update/insert) per table. meaning total of 15 times
>
> That's incorrect... you can do it all in one statement:
>
> GRANT select,update,insert TO "" ON table_1,table_2,table_3,etc
>
> http://www.postgresql.org/users-lounge/docs/7.0/user/sql-grant.htm
>
> -Dan
>
>
>

Re: [GENERAL] Tuning Database

[Kevin Lo]

Joseph Shraibman wrote:

> Anand Raman wrote:
> >
> > there is a white paper present on the great bridge site
> > (http://www.greatbridge.com/) where u can find some tuning tips .
>
> Where, persactly?

http://www.greatbridge.com/download/gbpg70wp2.pdf

> --
> Joseph Shraibman
> [EMAIL PROTECTED]
> Increase signal to noise ratio.  http://www.targabot.com

- Kevin

--
À

Re: [GENERAL] LD_LIBRARY_PATH

[Robert B. Easter]

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  [EMAIL PROTECTED] -
- CompTechNews Message Board   http://www.comptechnews.com/ -
- CompTechServ Tech Services   http://www.comptechserv.com/ -
-- http://www.comptechnews.com/~reaster/ 

Re: [GENERAL] Tuning Database

[Joseph Shraibman]

Anand Raman wrote:
> 
> there is a white paper present on the great bridge site
> (http://www.greatbridge.com/) where u can find some tuning tips .

Where, persactly?

-- 
Joseph Shraibman
[EMAIL PROTECTED]
Increase signal to noise ratio.  http://www.targabot.com

[GENERAL] phpPgAdmin 2.2.1 Released!

[Dan Wilson]

We are pleased to announce the release of phpPgAdmin version 2.2.1.  Check
us out at our new home @ http://www.greatbridge.org/project/phppgadmin.
Feel free to use all the new functionality available through the
greatbridge.org site (Thanks GreatBridge!)

The new version includes many updates and fixes, as well as several new
features including support for triggers and table ACL (priviledges).  In
addition, enhancements have been made to key functionality such as
functions, database/table dumps and the advanced authentication.

Download:
http://www.greatbridge.org/project/phppgadmin/download/download.php
Tar: ftp://ftp.greatbridge.org/pub/phppgadmin/stable/phpPgAdmin_2-2-1.tar.gz
Zip: ftp://ftp.greatbridge.org/pub/phppgadmin/stable/phpPgAdmin_2-2-1.zip

ChangeLog: http://pgdemo.acucore.com/ChangeLog

-Dan Wilson
phpPgAdmin Development Team
[EMAIL PROTECTED]

PS.  If you would like to help us in development, please join
greatbridge.org and request to become a member of our team.

Re: [GENERAL] How I can join between the other database's tables?

[Adam Haberlach]

On Tue, Dec 26, 2000 at 09:24:37PM +0530, Anand Raman wrote:
> On Wed, Dec 27, 2000 at 12:15:56AM +0900, Ioseph Kim wrote:
> ># createdb a
> ># createdb b
> ># psql a
> >create table a (name text);
> >\q
> ># psql b
> >create table a (name text);
> >
> >---
> >Now, I want to join table a of database a and table a of database b.
> >How?

> According to my knowledge this cant yet be achieved in postgresql..
> I sincerly hope i am wrong..

I'm pretty sure you are right.  If your data is related enough to be
joined, it should be related enough to be in the same database.

-- 
Adam Haberlach|A cat spends her life conflicted between a
[EMAIL PROTECTED]   |deep, passionate, and profound desire for
http://www.newsnipple.com |fish and an equally deep, passionate, and
'88 EX500 |profound desire to avoid getting wet.

[GENERAL] Re: Auto Increment

[postgres]

I had looked everywhere to find the answer to this question.  Now that I
look at the FAQ again I see it in there.  I tryed searching the FAQ for
"auto increment" when it turns out to be "auto-increment" on the FAQs
page.  Being a MySQL user who was dead ended and had no choice but to find
a new database with Transactions or something simliar because of MySQL's
limitations I had several questions that seemed difficult to find during
the switchover. 


1. Auto increment - now serial datatype

2. 'SHOW TABLES' in mysql - now '\d' (I got lucky to stumble apon this.)

3. 'DESCRIBE' in mysql - now '\d tablename' (same as above.)


As more people realize MySQL's limitations I think there will be more
people making the switchover.  As a result I'm sure many people find
themselves asking the same questions I did.  I would recommend these 3
things be easy to find the the FAQ for MySQL familiar users.

My 2 cents...


On Tue, 26 Dec 2000, Brett W. McCoy wrote:

> On Tue, 26 Dec 2000 [EMAIL PROTECTED] wrote:
> 
> > Is there a way set a primary key to Auto Increment like you can
> > with MySQL?
> 
> See the documentation on CREATE SEQUENCE, and also on CREATE TABLE and the
> SERIAL type.
> 
> [An aside:  this is something definitely that qualifies as a frequently
> asked question, as this is like the 4th time in a week this question has
> been asked.  Would posting a FAQ list on a regular basis to the list be
> helpful for things like this, like is done on the Perl newsgroups?]
> 
> -- Brett
>  http://www.chapelperilous.net/~bmccoy/
> ---
> Hanson's Treatment of Time:
>   There are never enough hours in a day, but always too many days
>   before Saturday.
> 

Re: [GENERAL] LD_LIBRARY_PATH

[Peter Eisentraut]

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  [EMAIL PROTECTED]   http://yi.org/peter-e/

[GENERAL] PostgreSQL on AIX

[Rick Vlahov]

I am interested in using PostgreSQL on IBM AIX 4.1 4.3

I am using it to post from legacy(non- SQL based) applications on the AIX
that I will have access to the source code.  The applications work great and
I'm looking only to add functionality(Web access). I will be using
Asta(www.astatech.com) in Delphi applications to access a web database as
well as the database that is to be created in PostgreSQL on the IBM AIX.

My thought is to insert calls into the legacy programs. These calls will
send onfo to new programs(perl, java or other). The new programs will then
pass the varialbes as parameters and Post/insert/delete/etc the PostgreSQL
database.

When do I start.

Thanks,
Rick Vlahov

[GENERAL] Functions in Postgre

[Uro Gruber]

Hi!

I have 2 questions.

1. I want to store some data in database (8 different fields) and then
check some other thig and if something is ok i wan't to update some
data. I think i have to use function for this, because i don't want to
send so many queries over the net.
But now i'm confused. Is it ok if i use function with like 8 arguments
or is it better to insert data with normal INSERT query and then use
triger on INSERT wich calls some function to do all the work.
I would like someone to tell me more about this. Because document are
realy bad. Is it some great documents or book out in the net or book
store.


2. I have 3 SELECT queries


- SELECT id,name FROM users WHERE id = '40';
- SELECT id FROM users WHERE id > '40' ORDER BY id ASC LIMIT 1;
- SELECT id FROM users WHERE id < '40' ORDER BY id DESC LIMIT 1;

Now i want to create a function who wil execute al tree.
something like this

CREATE FUNCTION get_user(int4) RETURNS ?? AS '

..
..
..

' LANGUAGE = 'plpgsql'

I want output like this when i call this function ( SELECT
get_user('40');

id nameidprev   idnext
--
40 user1 35   42


id's are not one by one but its generated and stored as 23characters
(this 40 is just an example.)

What i want to know is how can i write that function and What should i
put after RETURNS and then how to return data.


Thanks for help
-- 

Uros

Re: [GENERAL] LD_LIBRARY_PATH

[Mike Castle]

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
  [EMAIL PROTECTED]  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

[GENERAL] Re: SSL

[drevil]

I would also like to use SSL, or better SSH, with Postgres.  I know I
could use SSH tunneling, but it would be even better if it were
integrated.  Does anyone know if this is possible?

Thanks

Re: [GENERAL] LD_LIBRARY_PATH

[Robert B. Easter]

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  [EMAIL PROTECTED] -
- CompTechNews Message Board   http://www.comptechnews.com/ -
- CompTechServ Tech Services   http://www.comptechserv.com/ -
-- http://www.comptechnews.com/~reaster/ 

Re: [GENERAL] NFS mounted DBs and Vacuum

[Steve Wolfe]

> 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

[GENERAL] NFS mounted DBs and Vacuum

[Webb Sprague]

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

Re: [GENERAL] LD_LIBRARY_PATH

[Larry Rosenman]

* Chris Hayner <[EMAIL PROTECTED]> [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: [EMAIL PROTECTED]
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

[GENERAL] Re: Permissions on Stored Procedures ==> current_user

[jmoschet]

After re-reading my original *idea* about using rules to grant access to 
stored procedures, I think the better way would be not to implement the code 
as stored procedures/functions at all.  

Leave that for the more complex low-level add-ons to database functionality .

Using rules and views along with the current_user variable may be cleaner. 
(and easier?)

The CREATE_RULE manpage describes this kinda rule that ckecks to see who the 
current_user is.  I suppose this can also be used in any SQL fashion.
i,e, current_user IN ('a','b','c') Or a subselect.  Is there a current_group 
variable?

>Deny Joe access to the salary of employees in the shoe department 
>(current_user returns the name  of  the current user):
>
>CREATE RULE example_3 AS
>ON
>SELECT TO emp.salary WHERE old.dept = "shoe" AND current_user = "Joe"
>DO INSTEAD NOTHING; 

After some experimenting, is seems that current_user also works with VIEWS 
which see to be the way to go for ON SELECT RULES anyway.

So with this it seems safe to say that you can grant access to SQL queries in 
Postgres through RULES and VIEWS but this does not seems as robust as actual 
functions whose capabilities are nto as limited as code/queries stored in 
RULES or VIEW.

Still, does anyone have any comments on adding entries to pg_class for 
functions to have ACLs or adding an ACL column to the pg_proc class?

__
Get your own FREE, personal Netscape Webmail account today at 
http://webmail.netscape.com/

[GENERAL] LD_LIBRARY_PATH

[Chris Hayner]

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 

[GENERAL] RE: PQsetnonblocking

[Tim Barnard]

Never mind. I figured it out via the src 
code.
Thanks anyway!
 
Tim
S.E. Mgr.
Hartford Communications

Re: [GENERAL] NEXTVAL function Bug

[Tom Lane]

"Dan Wilson" <[EMAIL PROTECTED]> writes:
> test_db=# insert into "mixed_Case" (mix_id, mix_var) values
> (nextval('mixed_Case_mix_id_seq'), 'not working');
> ERROR:  Relation 'mixed_case_mix_id_seq' does not exist


You need to do it like this:
nextval('"mixed_Case_mix_id_seq"')

IMHO it's only a historical artifact that nextval wants a quoted name
at all.  The syntax should have been something like nextval(seqname) or
seqname.nextval, with no string literal.

regards, tom lane

Re: [GENERAL] SSL

[Tom Lane]

Alvaro Herrera <[EMAIL PROTECTED]> writes:
> I couldn't find any other info about using SSL with PostgreSQL.

http://www.postgresql.org/devel-corner/docs/postgres/ssl-tcp.htm

I seem to recall hearing that the SSL code was broken in 7.0.*.  If so,
you may want to try 7.1beta.

regards, tom lane

[GENERAL] postgres JDBC Serialize()

[Jarek]

I have problems with using Serialize class from postgres JDBC API.
Can anybody tell me what should be done in order to serialize objects into 
postgres data base ?


-- 
MS is not the answer.
MS is the question.
NO (or Linux) is the answer.

:) Jarek (:

[GENERAL] SSL

[Alvaro Herrera]

Hi

I wanted to use SSL with Postgres, and while searching through the
maillists I found a very old post (1998) Brett McCormick. I found his
patch in chicken.org, but it seems to be somewhat old (it's for PGSQL
6.3.2; I'm using 7.0.3). I wrote him directly
([EMAIL PROTECTED]), but the MTA said that address didn't
exist.

I couldn't find any other info about using SSL with PostgreSQL. Do you
have a clue as to where can I find some? I need to connect from Perl and
PHP, and just don't know where to go from here.

Pointer to docs and anything will be very much appreciated.

-- 
Alvaro Herrera ()

[GENERAL] This locale setting will prevent use of index optimization for LIKE and regexp searches. (sl_SI.ISO_8859-2)

[Tomaz Borstnar]

Hello!

Installed 7.1beta1, set LC_ALL to sl_SI.ISO_8859-2 and initialized the 
database with initdb and got this:

NOTICE:  Initializing database with sl_SI.ISO_8859-2 collation order.
 This locale setting will prevent use of index optimization for
 LIKE and regexp searches.  If you are concerned about speed of
 such queries, you may wish to set LC_COLLATE to "C" and
 re-initdb.  For more information see the Administrator's Guide.


How much can this affect and if there is any way to make this as fast as 
possible?

Tomaz

List metadata tags (was Re: [GENERAL] Auto Increment)

[Peter Eisentraut]

Tom Lane writes:

> At one time Marc was arranging for the list bot to append automatic
> tags to all postings.  I'd be in favor of an automatic tag that read
> something like
>
>   list administrivia: [EMAIL PROTECTED]
>   Postgres FAQs: http://...
>
> Suggestions anyone?  (Keep in mind that brevity is the soul of wit
> here...)

RFC 2369  

This essentially says that you add headers like this to every mail:

List-Help: 
List-Post: 
List-Subscribe: ,

List-Id: Discussion list for the autoconf build system 
List-Unsubscribe: ,

List-Archive: 

(There's isn't anything for FAQ, but there could be a link on the archive
page maybe.)

If a mail with these headers shows up in my mail client I get a link "This
message contains email list management information." that I can click on
and which in turn explains to me what an email list is and then allows me
to click on the URLs above.  Very nice.

-- 
Peter Eisentraut  [EMAIL PROTECTED]   http://yi.org/peter-e/