Thanks a lot Alban. That was simple!
On Sat, Apr 24, 2010 at 10:15 PM, Alban Hertroys <
dal...@solfertje.student.utwente.nl> wrote:
> > If I remove the braces from (\$1, \$2, \$3) then it gives below error.
> >
> > my $query = (< > INSERT INTO changelogtest(id, txid, txtime)
> > SELECT \$1,
I am using SPI_Prepare from trigger like below. I have some minor issue with
$query syntax. I have tried almost all combination but I am not able to
resolve. Could anyone please help me out.
CREATE OR REPLACE FUNCTION insert_history_info()
RETURNS VOID AS
$BODY$
my $query = (< there is some issue
same number of columns at line 15.
Thanks,
Dipti
On Fri, Apr 23, 2010 at 6:59 PM, Alban Hertroys <
dal...@solfertje.student.utwente.nl> wrote:
> On 23 Apr 2010, at 14:28, dipti shah wrote:
>
> > Great! Thanks Alban, Alexander, and Thomas.
> >
> > That solved the issue b
using from trigger like
below and it gives the same error: Could you please help me with it.
CREATE OR REPLACE FUNCTION insert_history_info()
RETURNS VOID AS
$BODY$
my $query = (< wrote:
> On 23 Apr 2010, at 13:17, dipti shah wrote:
>
> For this case you're using 3 values in the
ssue. Could you please help me out.
Thanks,
Dipti
On Fri, Apr 23, 2010 at 4:08 PM, Raymond O'Donnell wrote:
> On 23/04/2010 11:31, dipti shah wrote:
>
> > ERROR: EXCEPT types text and timestamp without time zone cannot be
> matched
> > LINE 2: except select id, tx
Hi, could anyone please tell me what is wrong in below query. Does it mean
that EXCEPT doesn't allow comparing TIMESTAMP type?
techdb=# INSERT INTO changelogtest (id, txid, txtime) values (5, 123, 'now')
except select id, txid, txtime
from changelogtest
where id=5;
ERROR: EXCEPT types text and t
Hi,
I have created a trigger which updates the correspondign LogTable for
PrimaryTable. If the insert to LogTable would create a duplicate row then
the trigger should take no action and should return. Could you tell me how
could I achieve this? I have all columns as a primary key in LogTable. It i
ted
privileges is held.* (Case of the privilege string is not significant, and
extra whitespace is allowed between but not within privilege names.)
Could anyone please help me out.
Thanks,
Dipti
On Fri, Apr 16, 2010 at 4:34 PM, dipti shah wrote:
> It is strange. If I remove both SELECT and INSER
t
(1 row)
techdb=>
Regards,
Dipti
On Fri, Apr 16, 2010 at 4:32 PM, dipti shah wrote:
> Hey Kretschemer, the has_table_privilege function returns true in
> following situation as well which is wrong.
>
> techdb=> select pc.relname, pc.relacl from pg_class pc, pg_na
LECT,
UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER');
has_table_privilege
-
t
(1 row)
techdb=>
Note that user1 has only insert and select permissions on techtable but
still has_table returns true for all permissions. Am I missing anything?
Thanks,
Dipti
On Thu,
Okay. Thanks.
Dipti.
On Thu, Apr 15, 2010 at 3:20 PM, A. Kretschmer <
andreas.kretsch...@schollglas.com> wrote:
> In response to dipti shah :
> > Thanks Kretschmer but I have seen those function. The below query returns
> error
> > but you could see that 'user1&
htable', 'SELECT, INSERT,
UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER');
has_table_privilege
-
t
(1 row)
Thanks,
Dipti
On Thu, Apr 15, 2010 at 1:27 PM, A. Kretschmer <
andreas.kretsch...@schollglas.com> wrote:
> In response to dipti shah :
> >
> > Hi, I ha
Hi, I have granted ALL permissions on 'techtable' to 'user1'.
techdb=# select pc.relname, pc.relacl from pg_class pc, pg_namespace pn
where pc.relnamespace=pn.oid and pn.nspname='techdb' and
pc.relname='techtable';
relname | relacl
---+-
Apr 8, 2010 at 5:22 PM, dipti shah wrote:
> I also tried below:
>
> techdb=# revoke all ON techtable from public;
> REVOKE
> techdb=# select pc.relname, pc.relacl from pg_class pc, pg_namespace pn
> where pc.relnamespace=pn.oid and pn.nspname='techdb' a
\q
> psql -h techdbdev1.lon -d techdb -E
psql (8.4.1)
Type "help" for help.
techdb=> select * from techtable;
ERROR: permission denied for relation techtable
techdb=>
...and it gives permission denied..!
Please help me to sort this out.
Thanks.
On Thu, Apr 8, 2010 at 5:11 PM, di
ect * from techtable;
number | description
+-
(0 rows)
techdb=>
Thanks.
On Thu, Apr 8, 2010 at 5:02 PM, dipti shah wrote:
> Yup. I read it and tired couple of ways but couldn't figured out how to
> specify column names. It gives me below error message and h
ption']) ON techtable TO user1;
^
Thanks,
Dipti.
On Thu, Apr 8, 2010 at 4:13 PM, Michael Glaesemann wrote:
>
> On Apr 8, 2010, at 4:22 , dipti shah wrote:
>
> > Hi, from postgesql features list mentioned at
> > http://www.postgresql.org/about/press/features84.html, I came
Hi, from postgesql features list mentioned at
http://www.postgresql.org/about/press/features84.html, I came to know that
it is possible to grant column level permissions.
PostgreSQL is "the most secure by default" and part of that is making
security tools easy to use. 8.4 makes our existing connec
58 PM, John R Pierce wrote:
> dipti shah wrote:
>
>> Hi,
>>
>> We have been using a stored procedure which allow users to create table.
>> The stored procedure creates actual table, logging tables, views, and
>> applies triggers. I want to prevent table owner
Hi,
We have been using a stored procedure which allow users to create table. The
stored procedure creates actual table, logging tables, views, and applies
triggers. I want to prevent table owner from deleting the triggers applied
on table by create table stored procedure. Could anyone please sugge
Hi,
I ran below command to list out all privileges of objects if "mydb" schema.
Actually, I want to know what are the permissions "user1" has on mydb
schema. Could you please tell me how to do this?
mydb=# select pc.relname, pc.relacl from pg_class pc, pg_namespace pn where
pc.relnamespace=pn.oid
That was cool ! Thanks a ton. Got my things done.
On Wed, Mar 31, 2010 at 1:13 PM, John R Pierce wrote:
> dipti shah wrote:
>
>> Thanks Guys. DBI works fine. I have written below code but it executes
>> only on the server where I installed postgresql. Is there any way to run
bname=sysdb','postgres',...) failed: could not connect to
server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"? at
pg-connect.pl line 7
Cannot connect to database!
*
Thanks,
Dipti
On
Hi,
Could anyone please provide me an example to connect to a postgresql
database using Perl language and accessing the tables, schemas, and other
postgresql objects.
Thanks,
Dipti
Hi,
I ran below command to list out all privileges of objects if "mydb" schema.
Actually, I want to know what are the permissions "user1" has on mydb
schema. Could you please tell me how to do this?
mydb=# select pc.relname, pc.relacl from pg_class pc, pg_namespace pn where
pc.relnamespace=pn.oid
ompany<http://www.enterprisedb.com>
>
> On Thu, Mar 25, 2010 at 2:37 PM, dipti shah wrote:
>
>> Hi,
>>
>> Could any one please tell me how to get list of all the permissions on the
>> schema (or any postgresql objects), stored them somewhere before executing
>> stored procedure and then restore them?
>>
>> Thanks,
>> Dipti
>>
>
>
Hi,
Could anyone please suggest me how to deal with my following requirements.
So far, I have done following to meet my requirements:
*
I want users to use only stored procedures to create, alter, delete tables
in mydb schema*. ==> For this, I have *revoked all permissions from
mydb *schema,
and s
lp me to achieve my requirements.
Please feel free to let me know if you have any questions.
Thanks a lot,
Dipti
On Fri, Mar 26, 2010 at 3:52 AM, John R Pierce wrote:
> dipti shah wrote:
>
>> Thanks a lot guys but I am not looking for security definer function. I
>>
Thanks a lot guys but I am not looking for security definer function. I know
it. My requirements are very complicated and I have to nailed down the
stuffs by storing schema permissions somewhere, execute my store procedure,
and restored the stored schema permissions. Like this I would make sure tha
Hi,
Could any one please tell me how to get list of all the permissions on the
schema (or any postgresql objects), stored them somewhere before executing
stored procedure and then restore them?
Thanks,
Dipti
Hi,
I refered
http://www.postgresql.org/docs/8.4/static/functions-info.html#FUNCTIONS-INFO-ACCESS-TABLE
and
got to know that I can fetch the information about permissions on schema.
Below commands work fine:
SELECT has_schema_privilege('user1', 'mydb', 'USAGE');
SELECT has_schema_privilege('user
Hmm...that would be too much work I think. Ayway, could you point me some
useful link for postgresql middleware?
Thanks.
On Tue, Mar 9, 2010 at 1:13 PM, John R Pierce wrote:
> dipti shah wrote:
>
>> What is middleware?
>>
>
> An application server that does all the bu
wrote:
> dipti shah wrote:
>
>> I don't want users to create/drop/alter anything directly. They have to
>> use stored procedure for everything. The stored procedure creates logging
>> tables and stores many other auditing information so it is madatory to
>> pr
Mar 9, 2010 at 11:54 AM, Tom Lane wrote:
> dipti shah writes:
> > I have created the stored procedure that allows particular users to
> create
> > the table. I want to prevent users to drop the tables owned by someone
> esle
> > and hence, I am making owner of e
Hi,
Could anyone please suggest me how to save the existing permissions on
schema and later on restore it? Basically, by default I have revoked all the
permissions from the mydb schema but there are some users who has all
permissions on mydb schema. I have one stored procedure which temporary
gran
Hi,
I have created the stored procedure that allows particular users to create
the table. I want to prevent users to drop the tables owned by someone esle
and hence, I am making owner of each table created by this stored procedure
to super user(postgres) so that no one will be allowed to drop/alte
es, I want to make sure that current user has the required permissions
to create table before creating table. For this I have to use SET ROLE to
current user but it is not allowed in SECURITY DEFINER context.
Any help would be much appreciated.
Thanks,
Dipti
On Tue, Feb 23, 2010 at 10:51 PM, dipti
Wonderful! Thanks.
On Wed, Feb 24, 2010 at 2:03 AM, Richard Huxton wrote:
> On 23/02/10 17:15, dipti shah wrote:
>
>> Thanks. Putting $cmd in single quote resolve the error but it generated
>> other error. :(
>>
>> mydb=> CREATE OR REPLACE FUNCTION test_creat
0 at 8:36 PM, Alvaro Herrera
wrote:
> dipti shah escribió:
>
> > For your reference I did something like this:
> >
> > 1. Create Function foo1 (this is without SECURITY DEFINER where I am
> > using SET ROLE to current user).
> >
> > 2. Create Func
Thanks. Putting $cmd in single quote resolve the error but it generated
other error. :(
mydb=> CREATE OR REPLACE FUNCTION test_create()
RETURNS void AS
$BODY$
$cmd = "CREATE TABLE testtable(col varchar not null);";
spi_exec_query("CREATE OR REPLACE FUNCTION myfunc() RETURNS void AS
'$cmd'LANGUAG
Hi, could anyone please help me to sort out below error. I have spent lot of
time but couldn't resolved it.
mydb=> CREATE OR REPLACE FUNCTION test_create()
RETURNS void AS
$BODY$
$cmd = "CREATE TABLE test-table(col varchar not null);";
spi_exec_query("CREATE OR REPLACE FUNCTION my_tmp_func() RET
...
spi_exe_query("select foo1()"); ==> Here it throws the error.
I am helpless now. Could you tell me what could be done in this situation?
Thanks,
Dipti
On Tue, Feb 23, 2010 at 2:04 AM, Tom Lane wrote:
> dipti shah writes:
> > I have just noticed that "SET ROLE&
Hi,
I have just noticed that "SET ROLE" doesn't work from security definer
function. I don;t know why but it clearly gives the error that SET role
doesn;t work in security definer context.
Basically, I am trying to write a store procedure which creates a table
asked by user along with other assoc
8:14 PM, Albe Laurenz wrote:
> dipti shah wrote:
> > Could anyone please tell me what is unsecure postgres
> > languages(like C, pgperl, pgpython??). How to disable them or
> > restrict them only for super user?
>
> I have never heard of "unsecure" languages -
Hi,
Could anyone please tell me what is unsecure postgres languages(like C,
pgperl, pgpython??). How to disable them or restrict them only for super
user?
Thanks,
Dipti
Thanks. That worked.
On Fri, Feb 19, 2010 at 2:02 PM, Guillaume Lelarge
wrote:
> Le 19/02/2010 08:21, dipti shah a écrit :
> > Hi,
> >
> > I have executed below queries.
> >
> > CREATE SCHEMA mydb_schema AUTHORIZATION postgres;
> >
> >
Hi,
I have executed below queries.
CREATE SCHEMA mydb_schema AUTHORIZATION postgres;
GRANT ALL ON SCHEMA mydb_schema TO postgres;
REVOKE ALL ON SCHEMA mydb_schema FROM PUBLIC;
REVOKE ALL ON FUNCTION mydb_schema.readtable() FROM PUBLIC;
GRANT EXECUTE ON FUNCTION mydb_schema.readtable() to use
Thanks. I will do testing.
On Thu, Feb 18, 2010 at 4:29 PM, Richard Huxton wrote:
> On 18/02/10 10:54, dipti shah wrote:
>
>> Okay then I think below works:
>>
>>1. Revoke permission ALL permissions from PUBLIC on schema.
>>
>> REVOKE ALL O
without using store procedures.
Please let me know if I am missing anything.
Thanks for being there.
Dipti
On Thu, Feb 18, 2010 at 4:09 PM, Richard Huxton wrote:
> On 18/02/10 10:34, dipti shah wrote:
>
>> Actually, I don't want table owners to drop the table using DROP command
Actually, I don't want table owners to drop the table using DROP command
directly. They have to use stored procedure to drop the table.
Thanks,
Dipti
On Thu, Feb 18, 2010 at 4:01 PM, Richard Huxton wrote:
> On 18/02/10 10:23, dipti shah wrote:
>
>> Thanks Richard. That makes
directly use any commands
like CREATE, UPDATE, ALTER or DROP. They have to use stored procedure. They
can only use SELECT. Nothing else.
Thanks,
Dipti.
On Thu, Feb 18, 2010 at 3:34 PM, Richard Huxton wrote:
> On 18/02/10 08:53, dipti shah wrote:
>
>> Hi,
>>
>> Is i
Hi,
Is it possible to define the permissions at database level such that no
users(except postgres) can execute DROP, ALTER, TRUNCATE commands directily?
Users have to use the given stored procedures.
Thanks,
Dipti
Hi,
I was looking for SQL DDL trigger kind of functionality in PostGreSQL but
couldn;t find any. Could anyone please tell me how to achieve the similar
functionality in PostGreSQL. Basically I want to make sure that no users
should use "DROP" command directly on my database even though he/she owne
That's I know but I was wondering if there is anyway to do it while creating
itself.
On Mon, Feb 15, 2010 at 1:46 PM, John R Pierce wrote:
> dipti shah wrote:
>
>> Hi,
>>
>> Could anyone please help me out here. I want to set the required
>> permissions
Hi,
Could anyone please help me out here. I want to set the required permissions
while creating PostGreSQL database, schema, and tables from stored
procedures. Is it possible to do it while creating them?
I could see that "OWNER" option is there while creating database but
couldn't see such optio
and
users to connect to database server. I haven't configured the server so I
don't know from where to start.
Thanks,
Dipti
On Mon, Feb 15, 2010 at 12:21 AM, John R Pierce wrote:
> dipti shah wrote:
>
>> Hi,
>>
>> I am aware of now almost all the authent
Hi,
I am aware of now almost all the authentication mechanisms that PostGreSQL
supports. My basic requirement is to don't ask for password (allowed users
in pg_hba file) when user connects to the server. What could be the easiest
and robust method to configure this? I am looking for passing throug
This can be done using "GRANT...ON..SCHEMA...". See
http://www.postgresql.org/docs/8.4/interactive/sql-grant.html
Cheers.
On Thu, Feb 4, 2010 at 4:00 PM, Jignesh Shah wrote:
> Hi,
>
> I know how to set the permissions for tables. Could anyone tell me how to
> restrict people accessing and creati
I got how SSL and SSH works in PostGreSQL from chapter 17.8 and 17.9.
Thanks,
Dip
On Thu, Feb 4, 2010 at 1:16 PM, dipti shah wrote:
> I got the information about how to set server configuration to make SSL
> aware only from http://www.postgresql.org/docs/8.4/static/ssl-tcp.html.
> P
I got the information about how to set server configuration to make SSL
aware only from http://www.postgresql.org/docs/8.4/static/ssl-tcp.html.
Please see below and let me know if I am missing anything.
1. I need to switch on "ssl" attribute in postgresql.conf and compile the
server again.
2
That makes sense.
Thanks,
Dipti
On Wed, Feb 3, 2010 at 12:08 PM, John R Pierce wrote:
> dipti shah wrote:
>
>> I am connected to database as postgres user.
>> '\!exec ..' doesn't work if I connect to the database from other host but
>> it does work i
On Tue, Feb 2, 2010 at 6:13 PM, Howard Cole wrote:
> dipti shah wrote:
>
>> Hi,
>> I am new to PostGreSQL authentication mechanism. Does PostGreSQL support
>> SSL? How could I make sure that psql client communicates with PostGreSQL
>> server securely?
>> Thanks
- Postgres
wrote:
> On Tue, February 2, 2010 08:23, Alvaro Herrera wrote:
> > dipti shah escribió:
> >> Techdb=# show hba_file;
> >>hba_file
> >> --
> >> /etc/postgresql/8.4/main/pg_hba.conf
> &g
Hi,
I am new to PostGreSQL authentication mechanism. Does PostGreSQL support
SSL? How could I make sure that psql client communicates with PostGreSQL
server securely?
Thanks,
Dipti
Wow!!..that was too quick. Thanks Richard.
On Tue, Feb 2, 2010 at 3:29 PM, Richard Huxton wrote:
> On 02/02/10 09:55, dipti shah wrote:
>
>> Thanks Richard and Alvaro. The "show hba_file" is great solution. Thanks a
>> ton. Could you tell me from where to get all s
l/8.4/main/pg_hba.conf;
ERROR: syntax error at or near "cat"
LINE 1: cat /etc/postgresql/8.4/main/pg_hba.conf;
^
Thanks,
Dipti
On Tue, Feb 2, 2010 at 3:25 PM, dipti shah wrote:
> Thanks Richard and Alvaro. The "show hba_file" is great solution. Thanks a
> ton. Could yo
Thanks Richard and Alvaro. The "show hba_file" is great solution. Thanks a
ton. Could you tell me from where to get all such commands?
Thanks,
Dip
On Mon, Feb 1, 2010 at 9:43 PM, Alvaro Herrera
wrote:
> dipti shah escribió:
> > Thanks Richard. those chapters are very useful.
2010 at 3:06 PM, Richard Huxton wrote:
> On 01/02/10 07:35, dipti shah wrote:
>
>>
>> Moreover, anyone can connect to databases as postgres user without giving
>> password.
>>
>> I am not aware how above setup has been made but I want to get rid of
>>
Hi, we have latest PostGreSQL setup and it allows everyone to connect. When
I do \du, it gives following output and it is same for all users.
TechDB=# \du
List of roles
Role name | Superuser | Create role | Create DB | Connections | Member
of
---
I didn't understand your meaning.
Regards,
Dipti
On Thu, Dec 10, 2009 at 8:44 PM, Vick Khera wrote:
> On Thu, Dec 10, 2009 at 4:24 AM, dipti shah
> wrote:
> > Special Thanks to Craig for absolutely brillient reply. I will test all
> you
> > said and will get ba
ote:
>
>> dipti shah wrote:
>>
>>> Hi,
>>>
>>> We have started using PostGreSQL for more than 3 months and it looks
>>> awesome. Currently, we have been suing superuser by default as login
>>> account. Now, the users are getting increased and we
Hi,
We have started using PostGreSQL for more than 3 months and it looks
awesome. Currently, we have been suing superuser by default as login
account. Now, the users are getting increased and we want to go away with
using superuser by default. We want to create the separate user account for
every
72 matches
Mail list logo
