[GENERAL] Install pgbench on the client server.Please helps thanks.

2012-07-25 Thread leaf_yxj 
Currently we use the postgresql database verion 8.2.15. And We want to
install the pgbench on the client server. I don't have any idea which
pgbench version is compatible with 8.2.15 and How to install it on Linux
server and Sun solaries server.  Please helps thanks.



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Install-pgbench-on-the-client-server-Please-helps-thanks-tp5717911.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to create c language in postgresql database. Thanks.

2012-06-13 Thread leaf_yxj 
Hi Chris and Guys,
 
Thanks for your answers. I really appreciate it. Although I don't understand 
the whole things you guys mentioned to me. I think maybe I should do it by 
myself. I need to do a test. If there is any good guide/white paper, please 
give me a link for me to study.
 
Thanks.
 
Regards.
 
Grace




At 2012-06-13 15:29:21,Chris Travers-5 [via PostgreSQL] 
ml-node+s1045698n5712359...@n5.nabble.com wrote:
On Wed, Jun 13, 2012 at 12:19 AM, Craig Ringer [hidden email] wrote:

 On 06/13/2012 12:45 PM, Chris Travers wrote:

 On Tue, Jun 12, 2012 at 11:47 AM, John R Pierce [hidden email]
 wrote:

 On 06/12/12 11:25 AM, leaf_yxj wrote:

 Thanks. You guys are right. I check the database. The C programm is
 there.
   - but why our application team keep ask me to give them the
 superuser
 privileges to create the C function. Should they use the superuser to
 create
 the C function. if yes , why they need it?


 yes, only a sql superuser can define a C function, as these have total
 access to crashing postgres's innards.

 Not just the innards, but the file system (could be used to overwrite
 data files), arbitrary system commands, etc..

 Hopefully not arbitrary system commands, in that I really hope nobody's nuts
 enough to run PostgreSQL as root or with write access to its own binaries.
 The data files are fair game, though, and replacement/modification of
 commands is probably possible in weaker installations.

Maybe not as arbitrary as it would as root, but at least arbitrary in
the sense of able to do or access anything that the system will let
the Postgres process access.  That means all binaries an ordinary
user can access and all system calls that don't require root unless
you lock things down using something like SELinux.

Best Wishes,
Chris Travers

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



If you reply to this email, your message will be added to the discussion below:
http://postgresql.1045698.n5.nabble.com/How-to-create-c-language-in-postgresql-database-Thanks-tp5712221p5712359.html
To unsubscribe from How to create c language in postgresql database. Thanks., 
click here.
NAML

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/How-to-create-c-language-in-postgresql-database-Thanks-tp5712221p5712447.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

[GENERAL] How to create c language in postgresql database. Thanks.

2012-06-12 Thread leaf_yxj 
I need to create  c language in the postgresql database. I only know to
create as follows.

Create language c ;


Is there anything else I need to do before I create the C language?


Thanks.

Regards.

Grace

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/How-to-create-c-language-in-postgresql-database-Thanks-tp5712221.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to create c language in postgresql database. Thanks.

2012-06-12 Thread leaf_yxj 
Hi Raymond,
 
 
Thanks. You guys are right. I check the database. The C programm is there.   
- but why our application team keep ask me to give them the superuser 
privileges to create the C function. Should they use the superuser to create 
the C function. if yes , why they need it?
 
Thanks.
Regards.
 
Grace





At 2012-06-13 01:35:49,Raymond O'Donnell [via PostgreSQL] 
ml-node+s1045698n5712228...@n5.nabble.com wrote:
On 12/06/2012 17:53, leaf_yxj wrote:
 I need to create  c language in the postgresql database. I only know
 to create as follows.

 Create language c ;


 Is there anything else I need to do before I create the C language?

Hello Grace,

C isn't available (AFAIK) as a procedural language; however, you have a
wide choice of other languages to choose from - see the docs under
Server programming for some of them.

In fact, C is the language used to write PostgreSQL.

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
[hidden email]

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



If you reply to this email, your message will be added to the discussion below:
http://postgresql.1045698.n5.nabble.com/How-to-create-c-language-in-postgresql-database-Thanks-tp5712221p5712228.html
To unsubscribe from How to create c language in postgresql database. Thanks., 
click here.
NAML

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/How-to-create-c-language-in-postgresql-database-Thanks-tp5712221p5712240.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: [GENERAL] How to create c language in postgresql database. Thanks.

2012-06-12 Thread leaf_yxj 
Hi John,

 
Thanks. You guys are right. I check the database. The C programm is there.   
- but why our application team keep ask me to give them the superuser 
privileges to create the C function. Should they use the superuser to create 
the C function. if yes , why they need it?
 
Thanks.
Regards.
 
Grace




At 2012-06-13 02:14:06,John R Pierce [via PostgreSQL] 
ml-node+s1045698n5712238...@n5.nabble.com wrote:
On 06/12/12 9:53 AM, leaf_yxj wrote:
 I need to create  c language in the postgresql database. I only know to
 create as follows.

 Create language c ;


to clarify what tom said,   you externally compile your C functions into
.so/.dll files, then bind them in with CREATE FUNCTION name(args)
RETURNS .  AS 'path/filename'  LANGUAGE C

see http://www.postgresql.org/docs/current/static/xfunc-c.html


--
john r pierceN 37, W 122
santa cruz ca mid-left coast


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



If you reply to this email, your message will be added to the discussion below:
http://postgresql.1045698.n5.nabble.com/How-to-create-c-language-in-postgresql-database-Thanks-tp5712221p5712238.html
To unsubscribe from How to create c language in postgresql database. Thanks., 
click here.
NAML

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/How-to-create-c-language-in-postgresql-database-Thanks-tp5712221p5712242.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: [GENERAL] How to create c language in postgresql database. Thanks.

2012-06-12 Thread leaf_yxj 
Hi Tom,
 
Thanks. You guys are right. I check the database. The C programm is there.  
- but why our application team keep ask me to give them the superuser
privileges to create the C function. Should they use the superuser to create
the C function. if yes , why they need it?
 
Thanks.
Regards.
 
Grace


--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/How-to-create-c-language-in-postgresql-database-Thanks-tp5712221p5712244.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to create c language in postgresql database. Thanks.

2012-06-12 Thread leaf_yxj 
John,
 
So can I ( the superuser) do it by myself and how can I do it?
 
Thanks.
 
Grace




At 2012-06-13 02:48:20,John R Pierce [via PostgreSQL] 
ml-node+s1045698n5712251...@n5.nabble.com wrote:
On 06/12/12 11:25 AM, leaf_yxj wrote:
 Thanks. You guys are right. I check the database. The C programm is
 there.   - but why our application team keep ask me to give them
 the superuser privileges to create the C function. Should they use the
 superuser to create the C function. if yes , why they need it?

yes, only a sql superuser can define a C function, as these have total
access to crashing postgres's innards.



--
john r pierceN 37, W 122
santa cruz ca mid-left coast


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



If you reply to this email, your message will be added to the discussion below:
http://postgresql.1045698.n5.nabble.com/How-to-create-c-language-in-postgresql-database-Thanks-tp5712221p5712251.html
To unsubscribe from How to create c language in postgresql database. Thanks., 
click here.
NAML

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/How-to-create-c-language-in-postgresql-database-Thanks-tp5712221p5712254.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: [GENERAL] How to create c language in postgresql database. Thanks.

2012-06-12 Thread leaf_yxj 

Hi John,
 
one more question: so it's one time jobs or it need keep  doing.
 
Thanks.
 
Grace



At 2012-06-13 02:48:20,John R Pierce [via PostgreSQL] 
ml-node+s1045698n5712251...@n5.nabble.com wrote:
On 06/12/12 11:25 AM, leaf_yxj wrote:
 Thanks. You guys are right. I check the database. The C programm is
 there.   - but why our application team keep ask me to give them
 the superuser privileges to create the C function. Should they use the
 superuser to create the C function. if yes , why they need it?

yes, only a sql superuser can define a C function, as these have total
access to crashing postgres's innards.



--
john r pierceN 37, W 122
santa cruz ca mid-left coast


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



If you reply to this email, your message will be added to the discussion below:
http://postgresql.1045698.n5.nabble.com/How-to-create-c-language-in-postgresql-database-Thanks-tp5712221p5712251.html
To unsubscribe from How to create c language in postgresql database. Thanks., 
click here.
NAML

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/How-to-create-c-language-in-postgresql-database-Thanks-tp5712221p5712255.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: [GENERAL] How to create c language in postgresql database. Thanks.

2012-06-12 Thread leaf_yxj 
Hi John,
 
Thanks for your quick reply. It's really help me a lot. 
 
1) What's the info in .dll/.so ?
2)  .sql scrap  is the binary installation file?
3) For the same database, we need update C functions frequently ?
 
Thanks.
 
Regards.
 
Grace 




At 2012-06-13 03:08:26,John R Pierce [via PostgreSQL] 
ml-node+s1045698n5712256...@n5.nabble.com wrote:
On 06/12/12 12:04 PM, leaf_yxj wrote:
 So can I ( the superuser) do it by myself and how can I do it?

the devs would give you the .dll/.so file, and a .sql scrap to do the
install.  you'd put the .so/.dll in an appropriate place, and run the
.sql scrap to install it into a given database.



--
john r pierceN 37, W 122
santa cruz ca mid-left coast


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



If you reply to this email, your message will be added to the discussion below:
http://postgresql.1045698.n5.nabble.com/How-to-create-c-language-in-postgresql-database-Thanks-tp5712221p5712256.html
To unsubscribe from How to create c language in postgresql database. Thanks., 
click here.
NAML

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/How-to-create-c-language-in-postgresql-database-Thanks-tp5712221p5712259.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: [GENERAL] How to create c language in postgresql database. Thanks.

2012-06-12 Thread leaf_yxj 
Hi John,
 
Thanks for your useful info. I really apprecaite it.  I got this problem when 
the SAS application try to install the scoring model.  So I am very confused.
 1) What's the info in .dll/.so ?

thats the binary code compiled and linked from C,   windows calls this
DLL (Dynamic Link Library), while unix usually calls it SO (Shared
Object).  Mac OSX has yet another name (dylib or something).

 Grace comments : this need the superuser privileges on the server to copy 
those .so ( we are on the linux server)  to the postgresql server ?
correct 
 
 2)  .sql scrap  is the binary installation file?

the .sql is the CREATE FUNCTION statement mostly.
--- Grace comments : we already grant create privileges on the saslib schema. 
So it means they can this scrap by themselves ???

 3) For the same database, we need update C functions frequently ?

you tell me?  how often do your programmers change them?
 Grace commends : they tried to SAS to create lot of models. So I amn't 
sure about this issues. But John, thanks a lot for your answers.
 
Thanks.
 
Regards.
 
Grace





At 2012-06-13 03:27:45,John R Pierce [via PostgreSQL] 
ml-node+s1045698n5712261...@n5.nabble.com wrote:
On 06/12/12 12:22 PM, leaf_yxj wrote:
 1) What's the info in .dll/.so ?

thats the binary code compiled and linked from C,   windows calls this
DLL (Dynamic Link Library), while unix usually calls it SO (Shared
Object).  Mac OSX has yet another name (dylib or something).

 2)  .sql scrap  is the binary installation file?

the .sql is the CREATE FUNCTION statement mostly.


 3) For the same database, we need update C functions frequently ?

you tell me?  how often do your programmers change them?



--
john r pierceN 37, W 122
santa cruz ca mid-left coast


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



If you reply to this email, your message will be added to the discussion below:
http://postgresql.1045698.n5.nabble.com/How-to-create-c-language-in-postgresql-database-Thanks-tp5712221p5712261.html
To unsubscribe from How to create c language in postgresql database. Thanks., 
click here.
NAML

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/How-to-create-c-language-in-postgresql-database-Thanks-tp5712221p5712264.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

[GENERAL] Re: How to debug the performance issues via which system catalog.Thanks.

2012-05-18 Thread leaf_yxj 



Thanks Steve.

At 2012-05-18 03:04:02,Steve Crawford [via PostgreSQL] 
ml-node+s1045698n5709069...@n5.nabble.com wrote:
On 05/17/2012 11:54 AM, leaf_yxj wrote:
 I know we can know the currenct activity via pg_stat_activity. What's else
 you guys use to debug.
 And for some times back, how can we check the activities?

 Thanks.

 Grace
Performance is a complex enough issue to warrant its own mailing list
(CPU type, number of cores, OS, OS settings, PG configuration, disk
type/count/filesystem/raid-configuration and so on).

Greg Smith's PostgreSQL 9.0 High Performance is worth every penny.

While waiting for it to arrive, peruse the pages at
http://wiki.postgresql.org/wiki/Performance_Optimization

Cheers,
Steve

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



If you reply to this email, your message will be added to the discussion below:
http://postgresql.1045698.n5.nabble.com/How-to-debug-the-performance-issues-via-which-system-catalog-Thanks-tp5709065p5709069.html
To unsubscribe from How to debug the performance issues via which system 
catalog.Thanks., click here.
NAML

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/How-to-debug-the-performance-issues-via-which-system-catalog-Thanks-tp5709065p5709115.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

[GENERAL] Re: How to write a script to analyze and vacuum all the tables in the system catalog?Thanks.

2012-05-18 Thread leaf_yxj 
Thanks. Steve




At 2012-05-18 02:54:36,Steve Crawford [via PostgreSQL] 
ml-node+s1045698n5709066...@n5.nabble.com wrote:
On 05/17/2012 11:30 AM, leaf_yxj wrote:
 Hi Guys. Please help me about this.

 For postgres database, it looks like we need analyze and vacuum all the
 tables periodly.  I need to write a script which can be executed in crontab.
 I don't have any clues about that. I only know the command :
 analyze tablename;
 vacuum tablename;
Leave off the tablename to do the whole db. But (unless you are on a
really old version of PG) you really shouldn't need to manually vacuum
things as that is the job of the autovacuum process.

There are unusual situations that suggest a manual analyze. It should be
run right after a cluster for instance. But they are limited special cases.

What indicates that you need to vacuum or analyze via cron?

Cheers,
Steve


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



If you reply to this email, your message will be added to the discussion below:
http://postgresql.1045698.n5.nabble.com/How-to-write-a-script-to-analyze-and-vacuum-all-the-tables-in-the-system-catalog-Thanks-tp5709059p5709066.html
To unsubscribe from How to write a script to analyze and vacuum all the tables 
in the system catalog?Thanks., click here.
NAML

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/How-to-write-a-script-to-analyze-and-vacuum-all-the-tables-in-the-system-catalog-Thanks-tp5709059p5709117.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

[GENERAL] How to write a script to analyze and vacuum all the tables in the system catalog?Thanks.

2012-05-17 Thread leaf_yxj 
Hi Guys. Please help me about this.

For postgres database, it looks like we need analyze and vacuum all the
tables periodly.  I need to write a script which can be executed in crontab.
I don't have any clues about that. I only know the command :
analyze tablename;
vacuum tablename;


Please helps. 

Thanks.

Regards.

Grace

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/How-to-write-a-script-to-analyze-and-vacuum-all-the-tables-in-the-system-catalog-Thanks-tp5709059.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] How to debug the performance issues via which system catalog.Thanks.

2012-05-17 Thread leaf_yxj 
I know we can know the currenct activity via pg_stat_activity. What's else
you guys use to debug. 
And for some times back, how can we check the activities?

Thanks.

Grace

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/How-to-debug-the-performance-issues-via-which-system-catalog-Thanks-tp5709065.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Re: How to know there is any ODBC Driver installed in the Linux redhat 5.7 server? Thanks

2012-05-07 Thread leaf_yxj 
Hi Adrian,

Thanks. I tried to understand how the ODBC driver works. Is that true that
there should be some odbc driver manager and otherwise the ODBC driver can't
work properly.  Our ODBC driver is installed on linux 5.7 for the informatic
use. Before we deploy the informatic, we want to test the ODBC driver works
or not? I was told that there should be some odbc driver manager to test the
function of ODBC. 

Thanks.

Regards.
Grace

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/How-to-know-there-is-any-ODBC-Driver-installed-in-the-Linux-redhat-5-7-server-Thanks-tp5686486p5691146.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Re: How to know there is any ODBC Driver installed in the Linux redhat 5.7 server? Thanks

2012-05-07 Thread leaf_yxj 





At 2012-05-05 01:37:50,Adrian Klaver-3 [via PostgreSQL] 
ml-node+s1045698n5686519...@n5.nabble.com wrote:
On 05/04/2012 10:23 AM, leaf_yxj wrote:
 I tried test the ODBC driver. I failed and I was told I should install the
 odbc driver manager to configure the ODBC driver first, then I can test the
 ODBC drive. And the following ODBC driver were installed on my server :
 psqlodbc-08.02.0400  psqlodbc-08.03.0400  psqlodbc-09.00.0200
 psqlodbc-08.02.0500  psqlodbc-08.04.0200


 Please give me some help of the ODBC driver, Should I install the ODBC
 driver manager? something like data direct???

First, are you sure you want the Driver Manager on the Linux server not
the Windows clients?

Second if you are looking for Linux Driver Managers, the two I know of are:

unixODBC
http://www.unixodbc.org/

iODBC
http://www.iodbc.org/dataspace/iodbc/wiki/iODBC/

Third there is a Postgres list dedicated to ODBC:
http://archives.postgresql.org/pgsql-odbc/


 Thanks.

 Grace




--
Adrian Klaver
[hidden email]

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



If you reply to this email, your message will be added to the discussion below:
http://postgresql.1045698.n5.nabble.com/How-to-know-there-is-any-ODBC-Driver-installed-in-the-Linux-redhat-5-7-server-Thanks-tp5686486p5686519.html
To unsubscribe from How to know there is any ODBC Driver installed in the Linux 
redhat 5.7 server? Thanks, click here.
NAML
Hi Adrian,

Thanks. I tried to understand how the ODBC driver works. Is that true that 
there should be some odbc driver manager and otherwise the ODBC driver can't 
work properly.  Our ODBC driver is installed on linux 5.7 for the informatic 
use. Before we deploy the informatic, we want to test the ODBC driver works or 
not? I was told that there should be some odbc driver manager to test the 
function of ODBC.

Thanks.

Regards.
Grace


--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/How-to-know-there-is-any-ODBC-Driver-installed-in-the-Linux-redhat-5-7-server-Thanks-tp5686486p5691152.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

[GENERAL] How to insert random character data into tables.

2012-05-02 Thread leaf_yxj 


--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/How-to-insert-random-character-data-into-tables-tp5680962.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] How to insert random character data into tables for testing purpose. THanks.

2012-05-02 Thread leaf_yxj 
Hi Guys, I want to insert the random character data into tables for testing
purpose.
I created a table as follows :

create table test ( id int, b char(100)); 

I need to insert 10 rows into this table. I know how to insert
generate_series  into coloumn ID. But I don't how to insert the Random
string data into column b. 

Please help. Thanks. Grace

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/How-to-insert-random-character-data-into-tables-for-testing-purpose-THanks-tp5680973.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Re: How to insert random character data into tables for testing purpose. THanks.

2012-05-02 Thread leaf_yxj 
Hi Andreas,

Thanks a lot.

I tried those following , they work.

1) insert into test values ( generate_series(1,1000),
md5(random()::text));


2) create table t_random as select s, md5(random()::text) from
generate_Series(1,5) s;

Thanks.
Regards.

Grace

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/How-to-insert-random-character-data-into-tables-for-testing-purpose-THanks-tp5680973p5681242.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Re: how to set up automatically startup database when the server boot or reboot.

2012-04-30 Thread leaf_yxj 
Hi Guys, I got the support from Greenplum. I will give a feedback to
everybody after I test. Thanks. 

They told me to add in /etc/rc.d. 

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/how-to-set-up-automatically-startup-database-when-the-server-boot-or-reboot-tp5670442p5676185.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Test ODBC connection failed. Pleae help me to take a look. Thanks.

2012-04-30 Thread leaf_yxj 
These odbc drivers (psqlodbc-08.02.0400  psqlodbc-08.03.0400 
psqlodbc-09.00.0200 psqlodbc-08.02.0500  psqlodbc-08.04.0200) were
installed. But I can't fine where the odbc.ini. After I set up my .odbc.ini.
And setup the driver and driver manager environment variables, I still can't
get the isql command. It still give me the error message that the isql
command can't be found. Please help. Thanks. Grace


--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Test-ODBC-connection-failed-Pleae-help-me-to-take-a-look-Thanks-tp5676587.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Re: Test ODBC connection failed. Pleae help me to take a look. Thanks.

2012-04-30 Thread leaf_yxj 
Hi Guys:

I installed this odbc driver :psqlodbc-08.02.0400

and datadirect-x_xx. I want to test this two things work or not. 

Please give me some idea.

Thanks.
Grace

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Test-ODBC-connection-failed-Pleae-help-me-to-take-a-look-Thanks-tp5676587p5677250.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] how to set up automatically startup database when the server boot or reboot.

2012-04-27 Thread leaf_yxj 
My company want to setup automatically startup database. For oracle database,
there is a bullitin script and configuration of auto start when the server
boot or reboot? Hi Guys, Please help. Thanks. Regards.Grace

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/how-to-set-up-automatically-startup-database-when-the-server-boot-or-reboot-tp5670442p5670442.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] How to get the all the activities running during specific time range.Thanks!Plese help!

2012-04-27 Thread leaf_yxj 
I was asked to run a report to my boss all the activities running during a
specific time range. I want to create a script to run it. I know I can query
from  pg_stat_activity for my previous postgres version. But I can't find
this system table in 8.2.15. Please help. Thanks. Regards. Grace.

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/How-to-get-the-all-the-activities-running-during-specific-time-range-Thanks-Plese-help-tp5670460p5670460.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Re: how to set up automatically startup database when the server boot or reboot.

2012-04-27 Thread leaf_yxj 

David, Thanks for your reminder. My database version is 8.2.15. And My os 
platform is Linux 5.5.  Thanks I really appreciate it. Grace

 


At 2012-04-27 22:38:11,David Johnston [via PostgreSQL] 
ml-node+s1045698n5670481...@n5.nabble.com wrote:
PostgreSQL runs on numerous operating systems.  If you do not specify which one 
you are using, as well as how you went about installing PostgreSQl, no one is 
going to be able to help you.  The  PostgreSQL version would probably help as 
well.

David J.

On Apr 27, 2012, at 10:26, leaf_yxj [hidden email] wrote:


 My company want to setup automatically startup database. For oracle database,
 there is a bullitin script and configuration of auto start when the server
 boot or reboot? Hi Guys, Please help. Thanks. Regards.Grace

 --
 View this message in context: 
 http://postgresql.1045698.n5.nabble.com/how-to-set-up-automatically-startup-database-when-the-server-boot-or-reboot-tp5670442p5670442.html
 Sent from the PostgreSQL - general mailing list archive at Nabble.com.

 --
 Sent via pgsql-general mailing list ([hidden email])
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



If you reply to this email, your message will be added to the discussion below:
http://postgresql.1045698.n5.nabble.com/how-to-set-up-automatically-startup-database-when-the-server-boot-or-reboot-tp5670442p5670481.html
To unsubscribe from how to set up automatically startup database when the 
server boot or reboot., click here.
NAML

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/how-to-set-up-automatically-startup-database-when-the-server-boot-or-reboot-tp5670442p5670496.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

[GENERAL] Re: how to set up automatically startup database when the server boot or reboot.

2012-04-27 Thread leaf_yxj 
David, Thanks for your reminder. My database version is 8.2.15. And My os
platform is Linux 5.5.  Thanks I really appreciate it. Grace


--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/how-to-set-up-automatically-startup-database-when-the-server-boot-or-reboot-tp5670442p5670504.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Re: how to set up automatically startup database when the server boot or reboot.

2012-04-27 Thread leaf_yxj 
My os is redhat linux 5.5. And My database is greenplum 4.2.1( postgresql
8.2.15).  I will take a look about the init.d directory.

Thanks. Guys. Any opinion is welcome. Please help.


--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/how-to-set-up-automatically-startup-database-when-the-server-boot-or-reboot-tp5670442p5670905.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] how to list all the schema and the privileges which is granted to users.THanks.

2012-04-13 Thread leaf_yxj 
how to list all the schema and the privileges which is granted to
users.THanks.

I know how to list all the objects privileges :
\dp

But I don't know how to list the schema's privileges??

Thanks.
Regards.

Grace

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/how-to-list-all-the-schema-and-the-privileges-which-is-granted-to-users-THanks-tp5639852p5639852.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Re: Questions of the privileges to use the pg_cancel_backend and pg_terminate_backend function. Thanks.

2012-04-09 Thread leaf_yxj 
Thanks Guys. I wrote this function in a specific schema and granted to the
dba users only. Thanks. Problem solved. 

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Questions-of-the-privileges-to-use-the-pg-cancel-backend-and-pg-terminate-backend-function-Thanks-tp5618129p5627387.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Questions of the privileges to use the pg_cancel_backend and pg_terminate_backend function. Thanks.

2012-04-04 Thread leaf_yxj 
Hi Guys. I got one problem. I need to give some of the non-super users( kind
of dba) to get the privileges
to can cancel other users's query, DML.  After I granted the execute on
pg_cancel_backend and pg_terminate_backend function to them, they still get
the error message as follows when they call these two function :

ERROR : must be superuser to signal other server processes. 

QUestion : is it possible to make the non superuser to have these two
privileges??

Thanks.

Regards.

Grace

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Questions-of-the-privileges-to-use-the-pg-cancel-backend-and-pg-terminate-backend-function-Thanks-tp5618129p5618129.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Re: Questions of the privileges to use the pg_cancel_backend and pg_terminate_backend function. Thanks.

2012-04-04 Thread leaf_yxj 
Hi Aaron: thanks. I tried the security definer. it works well as follows :

CREATE FUNCTION kill_process(integer) RETURNS boolean AS 'select
pg_cancel_backend($1);' LANGUAGE SQL SECURITY DEFINER;

 One more question about this function : if non-super user get the
execute this function, he/her will have privilege to kill all the processes
which belong to the postgresql process. How can we avoid that happing.

Thanks.

Grace

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Questions-of-the-privileges-to-use-the-pg-cancel-backend-and-pg-terminate-backend-function-Thanks-tp5618129p5618473.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Re: Please help me to take a look of the erros in my functions. Thanks.

2012-04-03 Thread leaf_yxj 
***

CREATE OR REPLACE FUNCTION truncate_t (IN tablename text)
RETURNS VOID
AS
$$
BEGIN
EXECUTE 'TRUNCATE TABLE ' || quote_ident(tablename) || 'CASCADE;';
EXCEPTION
WHEN undefined_table THEN
RAISE EXCEPTION 'Table % does not exists', tablename;
END;
$$
LANGUAGE plpgsql SECURITY DEFINER STRICT;

***

This works . Thank you very much.


***

CREATE OR REPLACE FUNCTION truncate_t (tablename text)
RETURNS VOID
AS
$$
BEGIN
EXECUTE 'TRUNCATE TABLE ' || quote_ident(tablename) || 'CASCADE;';
EXCEPTION
WHEN undefined_table THEN
RAISE EXCEPTION 'Table % does not exists', tablename;
END;
$$
LANGUAGE plpgsql SECURITY DEFINER STRICT;

***
This works,too. Thank you very much.   What's the difference between ( IN
tablename text)
and ( tablename text).



-- one more questions  thanks.



After I created the function sucessfully , I want to execute the function. I
get errors as follows :

rrp= select truncate_t(t1);
ERROR: column t1 does not exist
LINE 1 : select truncate_t(t1);
^



rrp= select truncate_t(rrp.t1);
ERROR:missing FROM-clause entry for table rrp 
LINE 1 : select truncate_t(rrp.t1);
^


--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Please-help-me-to-take-a-look-of-the-erros-in-my-functions-Thanks-tp5613507p5615212.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Re: Please help me to take a look of the erros in my functions. Thanks.

2012-04-03 Thread leaf_yxj 
Bartek, Thanks. The reason I use the cursor is that I want to check the table
is in the pg_tables or not,
If it exists, the function will execute successfully, if not, it will raise 
the message that the table doesn't exist.  For the schema part, I assume the
people has set the search_path to that schema which the table is in and
because our database only has one schema. So I assume they have the right
search_path.

Thanks for your advice. It helps me  a lot.

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Please-help-me-to-take-a-look-of-the-erros-in-my-functions-Thanks-tp5613507p5615238.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Re: Please help me to take a look of the erros in my functions. Thanks.

2012-04-03 Thread leaf_yxj 
Alban, Thanks.

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Please-help-me-to-take-a-look-of-the-erros-in-my-functions-Thanks-tp5613507p5615244.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Re: Please help me to take a look of the erros in my functions. Thanks.

2012-04-03 Thread leaf_yxj 
Adrian, Thanks.  Even I try use '' to quote the character. I still get the
error as follows :

rrp= truncate table t1;
TRUNCATE TABLE
rrp= select truncate_t('t1');
ERROR: table t1 does not exist

Thanks.

Grace

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Please-help-me-to-take-a-look-of-the-erros-in-my-functions-Thanks-tp5613507p5615292.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Re: Please help me to take a look of the erros in my functions. Thanks.

2012-04-03 Thread leaf_yxj 
Tom,

Thanks.  I found out the key issue it. It's because the truncate command
can't have the cascade.

For the other people reference. The right funcitons are :

*** 

CREATE OR REPLACE FUNCTION truncate_t (IN tablename text) 
RETURNS VOID 
AS 
$$ 
BEGIN 
EXECUTE 'TRUNCATE TABLE ' || quote_ident(tablename) || ';'; 
EXCEPTION 
WHEN undefined_table THEN 
RAISE EXCEPTION 'Table % does not exists', tablename; 
END; 
$$ 
LANGUAGE plpgsql SECURITY DEFINER STRICT; 

*** 

*** 

CREATE OR REPLACE FUNCTION truncate_t (tablename text) 
RETURNS VOID 
AS 
$$ 
BEGIN 
EXECUTE 'TRUNCATE TABLE ' || quote_ident(tablename) || ';'; 
EXCEPTION 
WHEN undefined_table THEN 
RAISE EXCEPTION 'Table % does not exists', tablename; 
END; 
$$ 
LANGUAGE plpgsql SECURITY DEFINER STRICT; 

*** 


usage : select truncate_t ('aaa');


Thanks everybody's help.

Regards.

Grace

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Please-help-me-to-take-a-look-of-the-erros-in-my-functions-Thanks-tp5613507p5615529.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Re: Please help me to take a look of the erros in my functions. Thanks.

2012-04-03 Thread leaf_yxj 
Bartek,
Thanks for your reminding. I don't know why CASCASE doesn't work in my 
greenplum postgresql database (version 8.2.14).  I can create the function 
successfully without any errors. But when i call it, I alwasy got errors if I 
include the CASCADE. If I delete the CASCADE, it will works. I don't know why. 
 
 And I read your link. CASCADE means that the child table will be 
delete,too.  I will do a test again.  If possible , Could you help me to do a 
test of creation and usage of that function? if so, please share me your result.
 
For  the truncate and delete ,  in Oracle , the truncate table can reset the 
high water mark and the space can be reused. the delete can't reset the high 
water mark and the space can't be reused. I guess : oracle truncate= truncate + 
vacuum
 
--- I amn't sure what's differences between truncate and delete in postgresql. 
Could you do me a favour to tell me about this.
 
Thanks.
Regards.
 
Grace






At 2012-04-04 01:15:40,Bartosz Dmytrak [via PostgreSQL] 
ml-node+s1045698n561586...@n5.nabble.com wrote:
One more thing:
TRUNCATE has option CASCASE:
http://www.postgresql.org/docs/9.1/static/sql-truncate.html


I don't remember since when, but 9.X has this option.


Another thing: Do You really need this function.
AFAIK since 8.4 postgres has TRUNCATE privilage on Table 
http://www.postgresql.org/docs/9.1/static/sql-grant.html
this is not the same as DELETE so, I think it is enough to grant this privilage 
to user

Regards,
Bartek



2012/4/3 leaf_yxj [hidden email]
Tom,

Thanks.  I found out the key issue it. It's because the truncate command
can't have the cascade.

For the other people reference. The right funcitons are :


***

CREATE OR REPLACE FUNCTION truncate_t (IN tablename text)
RETURNS VOID
AS
$$
BEGIN

   EXECUTE 'TRUNCATE TABLE ' || quote_ident(tablename) || ';';

EXCEPTION
   WHEN undefined_table THEN
   RAISE EXCEPTION 'Table % does not exists', tablename;

END;
$$
LANGUAGE plpgsql SECURITY DEFINER STRICT;

***


***

CREATE OR REPLACE FUNCTION truncate_t (tablename text)

RETURNS VOID
AS
$$
BEGIN

   EXECUTE 'TRUNCATE TABLE ' || quote_ident(tablename) || ';';

EXCEPTION
   WHEN undefined_table THEN
   RAISE EXCEPTION 'Table % does not exists', tablename;

END;
$$
LANGUAGE plpgsql SECURITY DEFINER STRICT;

***



usage : select truncate_t ('aaa');


Thanks everybody's help.

Regards.

Grace

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Please-help-me-to-take-a-look-of-the-erros-in-my-functions-Thanks-tp5613507p5615529.html

Sent from the PostgreSQL - general mailing list archive at Nabble.com.


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general






If you reply to this email, your message will be added to the discussion below:
http://postgresql.1045698.n5.nabble.com/Please-help-me-to-take-a-look-of-the-erros-in-my-functions-Thanks-tp5613507p5615860.html
To unsubscribe from Please help me to take a look of the erros in my functions. 
Thanks., click here.
NAML

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Please-help-me-to-take-a-look-of-the-erros-in-my-functions-Thanks-tp5613507p5615952.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

[GENERAL] Re: Please help me to take a look of the erros in my functions. Thanks.

2012-04-03 Thread leaf_yxj 
Hi Bartek
One more question, In oracle, when you create table using the default option, 
the parent table can't be delete if there is any child table exist. Usually, I 
won't use the cascade option.  I will truncate or delete one by one. what is 
postgresql default for these???
 
 
Thanks.
 
Regards.
 
Grace




At 2012-04-04 01:15:40,Bartosz Dmytrak [via PostgreSQL] 
ml-node+s1045698n561586...@n5.nabble.com wrote:
One more thing:
TRUNCATE has option CASCASE:
http://www.postgresql.org/docs/9.1/static/sql-truncate.html


I don't remember since when, but 9.X has this option.


Another thing: Do You really need this function.
AFAIK since 8.4 postgres has TRUNCATE privilage on Table 
http://www.postgresql.org/docs/9.1/static/sql-grant.html
this is not the same as DELETE so, I think it is enough to grant this privilage 
to user

Regards,
Bartek



2012/4/3 leaf_yxj [hidden email]
Tom,

Thanks.  I found out the key issue it. It's because the truncate command
can't have the cascade.

For the other people reference. The right funcitons are :


***

CREATE OR REPLACE FUNCTION truncate_t (IN tablename text)
RETURNS VOID
AS
$$
BEGIN

   EXECUTE 'TRUNCATE TABLE ' || quote_ident(tablename) || ';';

EXCEPTION
   WHEN undefined_table THEN
   RAISE EXCEPTION 'Table % does not exists', tablename;

END;
$$
LANGUAGE plpgsql SECURITY DEFINER STRICT;

***


***

CREATE OR REPLACE FUNCTION truncate_t (tablename text)

RETURNS VOID
AS
$$
BEGIN

   EXECUTE 'TRUNCATE TABLE ' || quote_ident(tablename) || ';';

EXCEPTION
   WHEN undefined_table THEN
   RAISE EXCEPTION 'Table % does not exists', tablename;

END;
$$
LANGUAGE plpgsql SECURITY DEFINER STRICT;

***



usage : select truncate_t ('aaa');


Thanks everybody's help.

Regards.

Grace

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Please-help-me-to-take-a-look-of-the-erros-in-my-functions-Thanks-tp5613507p5615529.html

Sent from the PostgreSQL - general mailing list archive at Nabble.com.


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general






If you reply to this email, your message will be added to the discussion below:
http://postgresql.1045698.n5.nabble.com/Please-help-me-to-take-a-look-of-the-erros-in-my-functions-Thanks-tp5613507p5615860.html
To unsubscribe from Please help me to take a look of the erros in my functions. 
Thanks., click here.
NAML

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Please-help-me-to-take-a-look-of-the-erros-in-my-functions-Thanks-tp5613507p5615961.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

[GENERAL] Re: Please help me to take a look of the erros in my functions. Thanks.

2012-04-03 Thread leaf_yxj 
Hi John, Thanks for your reply. Just to confirm :  so truncate table means the 
space will be reclaim for reuse ???
 
Thanks.
 
Grace




At 2012-04-04 02:01:59,John R Pierce [via PostgreSQL] 
ml-node+s1045698n5615977...@n5.nabble.com wrote:
On 04/03/12 10:49 AM, leaf_yxj wrote:
 --- I amn't sure what's differences between truncate and delete in
 postgresql. Could you do me a favour to tell me about this.

delete has to go through and flag each tuple for deletion so vacuum can
eventually go through and reclaim them for reuse.   truncate wipes the
whole table out, including 0 length the files.



--
john r pierceN 37, W 122
santa cruz ca mid-left coast


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



If you reply to this email, your message will be added to the discussion below:
http://postgresql.1045698.n5.nabble.com/Please-help-me-to-take-a-look-of-the-erros-in-my-functions-Thanks-tp5613507p5615977.html
To unsubscribe from Please help me to take a look of the erros in my functions. 
Thanks., click here.
NAML

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Please-help-me-to-take-a-look-of-the-erros-in-my-functions-Thanks-tp5613507p5616006.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

[GENERAL] Please help me to take a look of the erros in my functions. Thanks.

2012-04-02 Thread leaf_yxj 
I tried to create function to truncate table 
1) when the user call the function just specify the tablename 
2) the user can use the function owner privilege to execute the function.

But I got the errors as follows. Please help me to take a look.

Thanks.

Regards.

Grace 
-- function : 

CREATE OR REPLACE FUNCTION truncate_t(tablename IN VARCHAR) RETURNS void AS
$$ 
 DECLARE 
 stmt RECORD; 
 statements CURSOR FOR SELECT tablename FROM pg_catalog.pg_tables; 
 BEGIN 
 IF stmt IN statements then 
 EXECUTE 'TRUNCATE TABLE ' || quote_ident(stmt.tablename) || '
CASCADE;'; 
 ELSE 
 The tablename doesn't exist.doesn 
 END IF ; 
 END;   
 $$ LANGUAGE 'plpgsql' security definer; 

 errors. 
ERROR:  syntax error at or near $2 
LINE 1: SELECT   $1  IN  $2 
 ^ 
QUERY:  SELECT   $1  IN  $2 
CONTEXT:  SQL statement in PL/PgSQL function truncate_t near line 6 

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Please-help-me-to-take-a-look-of-the-erros-in-my-functions-Thanks-tp5613507p5613507.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] How to check the role has been granted to which role. Help me to double check . Thanks.

2012-03-31 Thread leaf_yxj 
I want to check the role has been granted to which role. In my working
environment, the all the normal is assigned to role group. when i issue dp,
it only give me the role group privilege. So I need to check which user is
in which user group.  THe following is my sql to do that. Is there anybody
has a better way to do it. Thanks. Grace

select  DISTINCT user, group_name, grantor, admin_option 
from 
(select usename AS user,roleid,admin_option from pg_user join
pg_auth_members on ( pg_user.usesysid=pg_auth_members.member)) a, 
(select usename AS group_name,roleid from  pg_user join pg_auth_members on
(pg_user.usesysid=pg_auth_members.roleid)) b,
(select usename AS grantor,roleid from  pg_user join pg_auth_members on
(pg_user.usesysid=pg_auth_members.grantor)) c

where a.roleid=b.roleid
and b.roleid=c.roleid; 

member | group_name | grantor | admin_option 
++-+--
 user1  | grace  | gpadmin | f
 user_1 | grace  | gpadmin | f
(2 rows)


--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/How-to-check-the-role-has-been-granted-to-which-role-Help-me-to-double-check-Thanks-tp5608906p5608906.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Re: double check the role has what's kind of the privilege? And the same for the objects. Thanks.

2012-03-30 Thread leaf_yxj 
Hi Albe,
 
My bosses ask me to list
 
1)all the users and the roles associated with the users.
2) all the users  and the privileges associated with that users.
 
Thanks.
 
Regards.
 
Grace


At 2012-03-30 16:07:08,Albe Laurenz *EXTERN* [via PostgreSQL] 
ml-node+s1045698n560596...@n5.nabble.com wrote:
leaf_yxj wrote:
 My bosses ask me  to list all the users and all the privilege which
the superuser granted  to the
 users.
 Then they can double check that I did right thing or not?

Unlike Oracle, PostgreSQL does not have a concept of grantor,
so it is not possible to find out which privileges were granted
by a superuser.

It is possible to find out all privileges for a certain user,
but it's probably a bit complicated.

What exactly should be checked?

Yours,
Laurenz Albe

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



If you reply to this email, your message will be added to the discussion below:
http://postgresql.1045698.n5.nabble.com/double-check-the-role-has-what-s-kind-of-the-privilege-And-the-same-for-the-objects-Thanks-tp5605564p5605960.html
To unsubscribe from double check the role has what's kind of the privilege? And 
the same for the objects. Thanks., click here.
NAML

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/double-check-the-role-has-what-s-kind-of-the-privilege-And-the-same-for-the-objects-Thanks-tp5605564p5606709.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

[GENERAL] Re: how to pass the function caller's parameter to inside the function. syntax error at or near $1

2012-03-30 Thread leaf_yxj 
merlin : Thanks.

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/how-to-pass-the-function-caller-s-parameter-to-inside-the-function-syntax-error-at-or-near-1-tp5601045p5606816.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Re: double check the role has what's kind of the privilege? And the same for the objects. Thanks.

2012-03-30 Thread leaf_yxj 
I think they don't care about the grantee. they only care about the users (
for example the application team user, develop team user) and the privileges
they have. Thanks. Guys.  Grace

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/double-check-the-role-has-what-s-kind-of-the-privilege-And-the-same-for-the-objects-Thanks-tp5605564p5606831.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Re: system catalog privilege and create privilege ??? how to control them?? thanks

2012-03-29 Thread leaf_yxj 

Thank you very much!!! I really appreicate it.
 
Grace
At 2012-03-29 16:18:23,Albe Laurenz *EXTERN* [via PostgreSQL] 
ml-node+s1045698n5602979...@n5.nabble.com wrote:
leaf_yxj wrote:
 For oracle, the normal user can't see all the system catalog. but for
 postgresql, it looks like all the user can see the system catalog.
Should
 we limit the user read privilege to system catalog?

You can try that, but things may break in unexpected ways.
For example, psql's utility commands will probably stop working.
I would test any such change thoroughly.

Not all system catalogs are visible for everybody, tables and views
containing passwords for example can only be read by superusers.

PostgreSQL has fewer restrictions on reading system catalogs than
Oracle.
I can see how a seasoned Oracle DBA might feel uneasy if everybody
can find out all user names on the database cluster.

 In oracle, the system privilege has create table, create view,create
 function.  For postgresql database, how to control the user who only
can
 create table but can't create view. Based on the test I did, once the
user
 has the create privilege on the schema, the user will have any create
 privilege on that schema. In postgresql, Rule is used to control that
???
 very confused!

PostgreSQL's permission system is different from Oracle's.
Oracle has a lot of system privileges which PostgreSQL does not
have or need.

In Oracle, every user automatically has a schema of the same name
and there are no permissions on schema basis.  So you need system
privileges if you want to keep users from creating objects.

In PostgreSQL you can use schema permissions.
True, as soon as you have CREATE on a schema, you can create any
kind of object there.  That is, any kind of object that does not
depend on anything else.
To create a function, you need the USAGE privilege on the
procedural function.  You can revoke this right from PUBLIC and
only give it to the users you want.
To create a trigger, you need the TRIGGER privilege on the
table involved and the EXECUTE privilege on the trigger function.

So you see, most of what Oracle handles with system privileges is
handled with object privileges in PostgreSQL.  And you usually
can assign permissions in a finer granularity that way.

Of course it is confusing at first, but once you understand
PostgreSQL's permission system, there are few meaningful things
that you cannot achieve with it.
What's the use case for granting somebody CREATE TABLE, but
not CREATE VIEW?

Yours,
Laurenz Albe

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



If you reply to this email, your message will be added to the discussion below:
http://postgresql.1045698.n5.nabble.com/system-catalog-privilege-and-create-privilege-how-to-control-them-thanks-tp5601150p5602979.html
To unsubscribe from system catalog privilege and create privilege ??? how to 
control them?? thanks, click here.
NAML

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/system-catalog-privilege-and-create-privilege-how-to-control-them-thanks-tp5601150p5605499.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

[GENERAL] double check the role has what's kind of the privilege? And the same for the objects. Thanks.

2012-03-29 Thread leaf_yxj 
Hi All friends. Thanks for you guys reply my questions. You helps me a lot.
THank you billions.  Another help. THanks.

After I create table and roles. I want to double check I grant the proper
privileges to the users. I can 
use \dp and \z command to check the objects privilege.  I can user pg_authid
to check the system privilege grantee to the users. Am I right??? the
below is what I get from my test database.



Q : what's the  differences between \dp and \z.

Thanks.
Regards.

Grace



rrp=# \dp
 Access privileges for database rrp
 Schema | Name |   Type   | Access privileges 
+--+--+---
 rrp| c| sequence | 
 rrp| p| sequence | 
 rrp| se1  | sequence | 
 rrp| t1   | table|
{grace=arwdxt/grace,user1=ar/grace,user2=r/grace}
 rrp| t2   | table| {grace=arwdxt/grace,user1=r/grace}

rrp=# \z
 Access privileges for database rrp
 Schema | Name |   Type   | Access privileges 
+--+--+---
 rrp| c| sequence | 
 rrp| p| sequence | 
 rrp| se1  | sequence | 
 rrp| t1   | table|
{grace=arwdxt/grace,user1=ar/grace,user2=r/grace}
 rrp| t2   | table| {grace=arwdxt/grace,user1=r/grace}


(5 rows)rrp=# select
rolname,rolsuper,rolinherit,rolcreaterole,rolcreatedb,rolcanlogin from
pg_authid;
 rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin 
-+--++---+-+-
 gpadmin | t| t  | t | t   | t
 grace   | f| t  | f | t   | t
 user1   | f| t  | f | f   | t
 user2   | f| t  | f | f   | t
 user4   | f| t  | f | f   | t
(5 rows)





--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/double-check-the-role-has-what-s-kind-of-the-privilege-And-the-same-for-the-objects-Thanks-tp5605564p5605564.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Re: double check the role has what's kind of the privilege? And the same for the objects. Thanks.

2012-03-29 Thread leaf_yxj 

Hi Chris,
 
My bosses ask me  to list all the users and all the privilege which the 
superuser granted  to the users.
Then they can double check that I did right thing or not?
 
Thanks.
 
Grace
At 2012-03-30 10:54:50,Chris Travers-5 [via PostgreSQL] 
ml-node+s1045698n5605567...@n5.nabble.com wrote:
On Thu, Mar 29, 2012 at 7:49 PM, leaf_yxj [hidden email] wrote:
 Hi All friends. Thanks for you guys reply my questions. You helps me a lot.
 THank you billions.  Another help. THanks.

 After I create table and roles. I want to double check I grant the proper
 privileges to the users. I can
 use \dp and \z command to check the objects privilege.  I can user pg_authid
 to check the system privilege grantee to the users. Am I right??? the
 below is what I get from my test database.

Cna you be specific about what you are trying to check.  Are you
trying to check whether a user has access to a given role?  If so
check out pg_has_role().

Best Wishes,
Chris Travers

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



If you reply to this email, your message will be added to the discussion below:
http://postgresql.1045698.n5.nabble.com/double-check-the-role-has-what-s-kind-of-the-privilege-And-the-same-for-the-objects-Thanks-tp5605564p5605567.html
To unsubscribe from double check the role has what's kind of the privilege? And 
the same for the objects. Thanks., click here.
NAML
Hi

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/double-check-the-role-has-what-s-kind-of-the-privilege-And-the-same-for-the-objects-Thanks-tp5605564p5605597.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

[GENERAL] what's difference between vacuum analyze and analyze?

2012-03-28 Thread leaf_yxj 
I was asked to write a security definer function to make other user can issue
vacuum and analyze command? Friends in this community said vacuum doesn't
work inside the function or multi command. How about analyze.  I know vacuum
is something much like oracle shrink which is used to reorganize the space
in the disk.  Analyze is used to collect the statistic info of the table. 
Does analyze works in Function or mulit-commands?  


Thanks.

Regards.


Grace

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/what-s-difference-between-vacuum-analyze-and-analyze-tp5600887p5600887.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] how to pass the function caller's parameter to inside the function. syntax error at or near $1

2012-03-28 Thread leaf_yxj 
I want to create a function which use to truncate the table specified by the
caller. I very confused how postgresql pass this parameter into the function
:

as a superuser to execute : 
1) 
create or replace function d() returns void as $$
analyze;
$$ language sql;


- this works when i issue select d()

2) this doesn't work 

create or replace function v(text) returns void as $$
analyze $1;
$$ language sql;

 why and how to correct it?  


I tried another function for insert 

1) this works.

create or replace function insert_f(integer) returns void as $$
insert into t1 values($1);
$$ language sql;

this works when i issue select insert_f(20);

2) this doesn't work.

create or replace function insert_f(text,integer) returns void as $$
insert into $1 values($2);
$$ language sql;

 it failed to create the function and give me error : syntax error at or
near $1.

Please help. 

Thanks.

Regards.

Grace




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/how-to-pass-the-function-caller-s-parameter-to-inside-the-function-syntax-error-at-or-near-1-tp5601045p5601045.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] how postgresql passes the parameter to the function. syntax error near or at $1.

2012-03-28 Thread leaf_yxj 
I want to create a function which use to truncate the table specified by the
caller. I very confused how postgresql pass this parameter into the function
: 

as a superuser to execute : 
1) 
create or replace function d() returns void as $$ 
analyze; 
$$ language sql; 


- this works when i issue select d() 

2) this doesn't work 

create or replace function v(text) returns void as $$ 
analyze $1; 
$$ language sql; 

 why and how to correct it?   


I tried another function for insert 

1) this works. 

create or replace function insert_f(integer) returns void as $$ 
insert into t1 values($1); 
$$ language sql; 

this works when i issue select insert_f(20); 

2) this doesn't work. 

create or replace function insert_f(text,integer) returns void as $$ 
insert into $1 values($2); 
$$ language sql; 

 it failed to create the function and give me error : syntax error at or
near $1. 

Please help. 

Thanks. 

Regards. 

Grace 



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/how-postgresql-passes-the-parameter-to-the-function-syntax-error-near-or-at-1-tp5601053p5601053.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Re: how postgresql passes the parameter to the function. syntax error near or at $1.

2012-03-28 Thread leaf_yxj 
Pavel,
 
Thanks a lot.
 
Regards.
 
Grace




At 2012-03-29 00:27:12,Pavel Stehule [via PostgreSQL] 
ml-node+s1045698n5601077...@n5.nabble.com wrote:
Hello

parameter - $n cannot be used on table name or column name position.
Some statements - DROP, VACUUM, ANALYZE doesn't support parameters
ever.

You have to use dynamic SQL in these cases.

CREATE OR REPLACE FUNCTION foo(tablename text, value text)
RETURNS void AS $$
BEGIN
  EXECUTE 'insert into ' || quote_ident(tablename) || ' VALUES($1)' USING value;
END;
$$ LANGUAGE plpgsql

Regards

Pavel Stehule

2012/3/28 leaf_yxj [hidden email]:

 I want to create a function which use to truncate the table specified by the
 caller. I very confused how postgresql pass this parameter into the function
 :

 as a superuser to execute :
 1)
 create or replace function d() returns void as $$
 analyze;
 $$ language sql;


 - this works when i issue select d()

 2) this doesn't work

 create or replace function v(text) returns void as $$
 analyze $1;
 $$ language sql;

  why and how to correct it?


 I tried another function for insert

 1) this works.

 create or replace function insert_f(integer) returns void as $$
 insert into t1 values($1);
 $$ language sql;

 this works when i issue select insert_f(20);

 2) this doesn't work.

 create or replace function insert_f(text,integer) returns void as $$
 insert into $1 values($2);
 $$ language sql;

  it failed to create the function and give me error : syntax error at or
 near $1.

 Please help.

 Thanks.

 Regards.

 Grace



 --
 View this message in context: 
 http://postgresql.1045698.n5.nabble.com/how-postgresql-passes-the-parameter-to-the-function-syntax-error-near-or-at-1-tp5601053p5601053.html
 Sent from the PostgreSQL - general mailing list archive at Nabble.com.

 --
 Sent via pgsql-general mailing list ([hidden email])
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



If you reply to this email, your message will be added to the discussion below:
http://postgresql.1045698.n5.nabble.com/how-postgresql-passes-the-parameter-to-the-function-syntax-error-near-or-at-1-tp5601053p5601077.html
To unsubscribe from how postgresql passes the parameter to the function. syntax 
error near or at $1., click here.
NAML
T

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/how-postgresql-passes-the-parameter-to-the-function-syntax-error-near-or-at-1-tp5601053p5601106.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

[GENERAL] Limit the normal user to see system catalog or not??? And create privilege???

2012-03-28 Thread leaf_yxj 
For oracle, the normal user can't see all the system catalog. but for
postgresql, it looks like all the user can see the system catalog.  Should
we limit the user read privilege to system catalog? 

In oracle, the system privilege has create table, create view,create
function.  For postgresql database, how to control the user who only can
create table but can't create view. Based on the test I did, once the user
has the create privilege on the schema, the user will have any create
privilege on that schema. In postgresql, Rule is used to control that ???
very confused!

Thanks.
Regards.

Grace

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Limit-the-normal-user-to-see-system-catalog-or-not-And-create-privilege-tp5601146p5601146.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] system catalog privilege and create privilege ??? how to control them?? thanks

2012-03-28 Thread leaf_yxj 
For oracle, the normal user can't see all the system catalog. but for
postgresql, it looks like all the user can see the system catalog.  Should
we limit the user read privilege to system catalog? 

In oracle, the system privilege has create table, create view,create
function.  For postgresql database, how to control the user who only can
create table but can't create view. Based on the test I did, once the user
has the create privilege on the schema, the user will have any create
privilege on that schema. In postgresql, Rule is used to control that ???
very confused! 

Thanks. 
Regards. 

Grace  



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/system-catalog-privilege-and-create-privilege-how-to-control-them-thanks-tp5601150p5601150.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] create one function to let other user execute vacuum command. got such an error.

2012-03-27 Thread leaf_yxj 
create one function to let other user execute vacuum command. got such an
error. Please help. Thanks. Regards . Grace

rrp= create function vacuum_f ( tablename char(100))  
 Returns char(100) AS $$
   
vacuum tablename; 
$$ Language plpgsql security definer;
ERROR:  syntax error at or near vacuum
LINE 3:   vacuum tablename;
  ^
rrp= 

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/create-one-function-to-let-other-user-execute-vacuum-command-got-such-an-error-tp5599318p5599318.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] postgresql commit.

2012-03-27 Thread leaf_yxj 

Hi Andrew,
 
Thanks. I got it.
 
Regards.
 
Grace
At 2012-03-27 12:36:18,Andrew Sullivan-8 [via PostgreSQL] 
ml-node+s1045698n559693...@n5.nabble.com wrote:
On Mon, Mar 26, 2012 at 06:22:20PM -0700, leaf_yxj wrote:
 Oracle database, the other user can't see the uncommited data,  for
 postgresql 8.2 , it looks like once you issue insert , the other user will
 automatically see new record. was it wrong? I am pretty new to postgresql?

Did you actually start a transaction (i.e. issue BEGIN)?  If not, your
statement COMMITs automatically.  

Also, you might want to note the remark on the PostgreSQL security
pages: Please note that versions prior to 8.3 are no longer supported
and vulnerabilities for these versions may not be included in this
list. New vulnerabilities in these versions are no longer patched.
See http://www.postgresql.org/support/security/.  I'd plan to upgrade
soon.

Best,

A

--
Andrew Sullivan
[hidden email]

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



If you reply to this email, your message will be added to the discussion below:
http://postgresql.1045698.n5.nabble.com/postgresql-commit-tp5596729p5596931.html
To unsubscribe from postgresql commit., click here.
NAML

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/postgresql-commit-tp5596729p5599238.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

[GENERAL] postgresql commit.

2012-03-26 Thread leaf_yxj 
Oracle database, the other user can't see the uncommited data,  for
postgresql 8.2 , it looks like once you issue insert , the other user will
automatically see new record. was it wrong? I am pretty new to postgresql?
Thanks.

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/postgresql-commit-tp5596729p5596729.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Re: postgresql 8.2 security definer is a built-in function. very confused??

2012-03-22 Thread leaf_yxj 

I am very confused after I read the guide as follows. It means I only need
to set the search_path to make the pg_temp as the last entry. or I need
configure search_path and at the same time, I need create the security
definer?  Is thers anybody help me? 

Thank you very much. I really appreciate it.


The following is what I got from the postgresql 8.2 guide.
Writing SECURITY DEFINER Functions Safely

Because a SECURITY DEFINER function is executed with the privileges of the
user that created it, care is
needed to ensure that the function cannot be misused. For security,
search_path should be set to exclude
any schemas writable by untrusted users. This prevents malicious users from
creating objects that mask
objects used by the function. Particularly important in this regard is the
temporary-table schema, which is
searched first by default, and is normally writable by anyone. A secure
arrangement can be had by forcing
the temporary schema to be searched last. To do this, write pg_temp as the
last entry in search_path.
This function illustrates safe usage:


CREATE FUNCTION check_password(uname TEXT, pass TEXT)
RETURNS BOOLEAN AS $$
DECLARE passed BOOLEAN;
old_path TEXT;
BEGIN
-- Save old search_path; notice we must qualify current_setting
-- to ensure we invoke the right function
old_path := pg_catalog.current_setting(’search_path’);
-- Set a secure search_path: trusted schemas, then ’pg_temp’.
-- We set is_local = true so that the old value will be restored
-- in event of an error before we reach the function end.
PERFORM pg_catalog.set_config(’search_path’, ’admin, pg_temp’, true);
-- Do whatever secure work we came for.
SELECT (pwd = $2) INTO passed
FROM pwds
WHERE username = $1;
-- Restore caller’s search_path
PERFORM pg_catalog.set_config(’search_path’, old_path, true);
RETURN passed;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/postgresql-8-2-security-definer-is-a-built-in-function-very-confused-tp5588409p5588420.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] postgresql 8.2 security definer is a built-in function. very confused??

2012-03-22 Thread leaf_yxj 
the security definer is built-in function, or I need create security definer
first, then user call it. How it works?   I am pretty new in Postgresql.
Please help. Thanks.

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/postgresql-8-2-security-definer-is-a-built-in-function-very-confused-tp5588409p5588409.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general