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 = (ENDQUERY);
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 = (ENDQUERY);
INSERT
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
On Fri, Apr 23, 2010 at 4:08 PM, Raymond O'Donnell r...@iol.ie 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, txid, txtime
Try adding a cast to one of them.
Ray.
--
Raymond O'Donnell
...@solfertje.student.utwente.nl wrote:
On 23 Apr 2010, at 13:17, dipti shah wrote:
For this case you're using 3 values in the first half of the expression and
only 1 in the second:
techdb=# INSERT INTO changelogtest (id, txid, txtime)
values (5, 123, 'now')
^^ ^^^ --- 3
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 but could you tell me what is the issue when I give
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
');
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, Apr 15, 2010 at 4:16 PM, dipti shah shahdipti1...@gmail.com wrote
,
Dipti
On Fri, Apr 16, 2010 at 4:32 PM, dipti shah shahdipti1...@gmail.com 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_namespace pn
where pc.relnamespace=pn.oid
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 shahdipti1...@gmail.com wrote:
It is strange. If I remove both
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
-
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 have granted ALL permissions on 'techtable' to 'user1'.
techdb=# select pc.relname, pc.relacl from pg_class pc, pg_namespace pn
where
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' has ALL permissions on table 'techtable
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
;
^
Thanks,
Dipti.
On Thu, Apr 8, 2010 at 4:13 PM, Michael Glaesemann g...@seespotcode.netwrote:
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 to know
that
it is possible to grant column
* from techtable;
number | description
+-
(0 rows)
techdb=
Thanks.
On Thu, Apr 8, 2010 at 5:02 PM, dipti shah shahdipti1...@gmail.com 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
(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, dipti shah shahdipti1...@gmail.com wrote:
Okay. I think I got
at 5:22 PM, dipti shah shahdipti1...@gmail.com 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' and
pc.relname='techtable';
relname
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
, John R Pierce pie...@hogranch.com 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 from deleting the triggers
applied
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
and accepting
connections on Unix domain socket /tmp/.s.PGSQL.5432? at
pg-connect.pl line 7
Cannot connect to database!
*
Thanks,
Dipti
On Wed, Mar 31, 2010 at 11:53 AM, Pavel Stehule pavel.steh...@gmail.comwrote:
Hello
2010/3/31 dipti shah shahdipti1...@gmail.com:
Hi,
Could anyone
That was cool ! Thanks a ton. Got my things done.
On Wed, Mar 31, 2010 at 1:13 PM, John R Pierce pie...@hogranch.com 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
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
On Thu, Mar 25, 2010 at 2:37 PM, dipti shah shahdipti1...@gmail.comwrote:
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 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
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
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 pie...@hogranch.com wrote:
dipti shah wrote:
Thanks a lot guys but I am not looking for security definer function. I
know it. My requirements are very
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
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 pie...@hogranch.com wrote:
dipti shah wrote:
What is middleware?
An application server that does all the business logic
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
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
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
, 2010 at 11:54 AM, Tom Lane t...@sss.pgh.pa.us wrote:
dipti shah shahdipti1...@gmail.com 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 each table
pie...@hogranch.com 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
prevent users from running any
...
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 t...@sss.pgh.pa.us wrote:
dipti shah shahdipti1...@gmail.com writes:
I have just noticed that SET
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()
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'LANGUAGE
Herrera
alvhe...@commandprompt.comwrote:
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 Function foo2 with SECURITY DEFINER
Wonderful! Thanks.
On Wed, Feb 24, 2010 at 2:03 AM, Richard Huxton d...@archonet.com 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_create()
RETURNS void AS
$BODY
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 shah
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
at 8:14 PM, Albe Laurenz laurenz.a...@wien.gv.atwrote:
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 - what exactly do you mean
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
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
Thanks. That worked.
On Fri, Feb 19, 2010 at 2:02 PM, Guillaume Lelarge
guilla...@lelarge.infowrote:
Le 19/02/2010 08:21, dipti shah a écrit :
Hi,
I have executed below queries.
CREATE SCHEMA mydb_schema AUTHORIZATION postgres;
GRANT ALL ON SCHEMA mydb_schema TO postgres
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
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 d...@archonet.com wrote:
On 18/02/10 08:53, dipti shah wrote:
Hi,
Is it possible to define
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 d...@archonet.com wrote:
On 18/02/10 10:23, dipti shah wrote:
Thanks Richard. That makes sense
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 d...@archonet.com wrote:
On 18/02/10 10:34, dipti shah wrote:
Actually, I don't want table owners to drop the table using DROP command
directly. They have to use
Thanks. I will do testing.
On Thu, Feb 18, 2010 at 4:29 PM, Richard Huxton d...@archonet.com 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 ON ALL TABLES IN SCHEMA mySchema
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 owner
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 option
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 pie...@hogranch.com wrote:
dipti shah wrote:
Hi,
Could anyone please help me out here. I want to set the required
permissions while creating PostGreSQL
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
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 pie...@hogranch.com wrote:
dipti shah wrote:
Hi,
I am aware of now almost all the authentication mechanisms
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 shahdipti1...@gmail.com 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
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 jignesh.shah1...@gmail.comwrote:
Hi,
I know how to set the permissions for tables. Could anyone tell me how to
restrict people
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.
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
alvhe...@commandprompt.comwrote:
dipti shah escribió:
Thanks Richard. those chapters are very useful
/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 shahdipti1...@gmail.com wrote:
Thanks Richard and Alvaro. The show hba_file is great solution. Thanks a
ton. Could you tell me from where
Wow!!..that was too quick. Thanks Richard.
On Tue, Feb 2, 2010 at 3:29 PM, Richard Huxton d...@archonet.com 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 such commands?
All
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
:
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
(1 row)
Moreover, is there anyway to view content of this file from stored in
above
Cole howardn...@selestial.comwrote:
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,
Dipti
Yes. See http://www.postgresql.org/docs/8.4/static
That makes sense.
Thanks,
Dipti
On Wed, Feb 3, 2010 at 12:08 PM, John R Pierce pie...@hogranch.com 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 if I connect to the database from
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
, Richard Huxton d...@archonet.com 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
them.
Could anyone please help me in below questions
, John R Pierce wrote:
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 want to go away
with using superuser by default. We want
I didn't understand your meaning.
Regards,
Dipti
On Thu, Dec 10, 2009 at 8:44 PM, Vick Khera vi...@khera.org wrote:
On Thu, Dec 10, 2009 at 4:24 AM, dipti shah shahdipti1...@gmail.com
wrote:
Special Thanks to Craig for absolutely brillient reply. I will test all
you
said and will get
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
71 matches
Mail list logo
