Re: [GENERAL] Syntax error in spi_prepare usage

[dipti shah]

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, \$2, \$3
> >   EXCEPT
> >   SELECT (id, txid, txtime)
> >   FROM changelogtest
> >   WHERE id = \$1
> >   AND txid = \$2
> >   AND txtime = \$3;
> >  ENDQUERY
> >
> > techdb=# SELECT insert_history_info();
> > ERROR:  error from Perl function "insert_history_info": each EXCEPT query
> must have the same number of columns at line 15.
>
> Yes of course, your select lists are different. Try:
> >  my $query = (< >   INSERT INTO changelogtest(id, txid, txtime)
> >   SELECT  \$1, \$2, \$3
> >   EXCEPT
> >   SELECT id, txid, txtime
> >   FROM changelogtest
> >   WHERE id = \$1
> >   AND txid = \$2
> >   AND txtime = \$3;
> >  ENDQUERY
>
> Alban Hertroys
>
> --
> If you can't see the forest for the trees,
> cut the trees and you'll see there is no forest.
>
>
> !DSPAM:1050,4bd3201010417204612880!
>
>
>

[GENERAL] Syntax error in spi_prepare usage

[dipti shah]

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 here.
  EXCEPT
  SELECT (id, txid, txtime)
  FROM changelogtest
  WHERE id = \$1
  AND txid = \$2
  AND txtime = \$3;
 ENDQUERY

 # Always use the prepared query if available
 if (not exists($_SHARED{$query})) {
  $_SHARED{$query} = spi_prepare($query, 'INTEGER', 'INTEGER', 'TIMESTAMP');
 }

 spi_exec_prepared($_SHARED{$query}, 5, 123, now());

 return;
$BODY$
  LANGUAGE 'plperl' VOLATILE SECURITY DEFINER

techdb=# SELECT insert_history_info();
ERROR:  error from Perl function "insert_history_info": INSERT has more
target columns than expressions at line 15.

If I remove the braces from (\$1, \$2, \$3) then it gives below error.
 my $query = (<

Re: [GENERAL] EXCEPT doesn't compare TIMESTAMP type?

[dipti shah]

Thanks again Alban. I didn't mention but if I remove the braces then it
gives below error but the meaning is same as the error when I specify
braces.

techdb=# SELECT insert_history_info();
ERROR:  error from Perl function "insert_history_info": each EXCEPT query
must have the 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 but could you tell me what is the issue when I give
> brackets in second query?
> >
> > techdb=# INSERT INTO changelogtest (id, txid, txtime) values (5, 123,
> now())
> > except select (id, txid, txtime)
> > from changelogtest
> > where id=5;
> > ERROR:  each EXCEPT query must have the same number of columns
> > LINE 2: except select (id, txid, txtime)
>
> > I need brackets because this query actually I am 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 = (< > INSERT INTO changelogtest(id, txid, txtime)
> > SELECT  (\$1, \$2, \$3)
> > EXCEPT
> > SELECT (id, txid, txtime)
> > FROM changelogtest
> > WHERE id = \$1
> > AND txid = \$2
> > AND txtime = \$3;
> > ENDQUERY
>
> You need to remove the braces from the query in your trigger too, they
> change the meaning of the query. You use brackets in this way if you need to
> reference fields from a composite type.
>
> Alban Hertroys
>
> --
> Screwing up is an excellent way to attach something to the ceiling.
>
>
> !DSPAM:1050,4bd1a0ba10411000594626!
>
>
>

Re: [GENERAL] EXCEPT doesn't compare TIMESTAMP type?

[dipti shah]

Great!  Thanks Alban, Alexander, and Thomas.

That solved the issue but could you tell me what is the issue when I give
brackets in second query?

techdb=# INSERT INTO changelogtest (id, txid, txtime) values (5, 123, now())
except *select id, txid, txtime
*from changelogtest
where id=5;
INSERT 0 1

techdb=# INSERT INTO changelogtest (id, txid, txtime) values (5, 123, now())
except select* (id, txid, txtime)*
from changelogtest
where id=5;
ERROR:  each EXCEPT query must have the same number of columns
LINE 2: except select (id, txid, txtime)
I need brackets because this query actually I am 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 first half of the expression and
> only 1 in the second:
>
> > techdb=# INSERT INTO changelogtest (id, txid, txtime)
> > values (5, 123, 'now')
> ^^  ^^^  --- 3 columns, namely int, int & text.
> > except
> > select (id, txid, txtime)
>^^^--- 1 column, a row-type containing (int, int,
> timestamp)
> > from changelogtest
> > where id=5;
> > ERROR:  each EXCEPT query must have the same number of columns
> > LINE 2: except select (id, txid, txtime)
>
> Alban Hertroys
>
> --
> If you can't see the forest for the trees,
> cut the trees and you'll see there is no forest.
>
>
> !DSPAM:1050,4bd18a8610411242712669!
>
>
>

Re: [GENERAL] EXCEPT doesn't compare TIMESTAMP type?

[dipti shah]

Thanks but I don't have text type in my table.

sysdb=# \d changelogtest
...
   Table "sysdb.changelogtest"
 Column |Type |Modifiers
+-+--
 id | integer | not null
 txid   | integer | not null default
txid_current()
 txtime | timestamp without time zone | not null default
transaction_timestamp()
Foreign-key constraints:
"changelogtest_id_fkey" FOREIGN KEY (id) REFERENCES logtable(id)
Moreover, the query behaves differently when I proive brackates after SELECT
in second query.

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 timestamp without time zone cannot be matched
LINE 2: except select id, txid, txtime
^
techdb=# INSERT INTO changelogtest (id, txid, txtime) values (5, 123, 'now')
except select (id, txid, txtime)
from changelogtest
where id=5;
ERROR:  each EXCEPT query must have the same number of columns
LINE 2: except select (id, txid, txtime)
  ^
Below works:

techdb=# INSERT INTO changelogtest (id, txid) values (5, 123)
except select id, txid
from changelogtest
where id=5;
INSERT 0 0
I don't know how to resolve my issue. 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, txid, txtime
>
> Try adding a cast to one of them.
>
> Ray.
>
>
> --
> Raymond O'Donnell :: Galway :: Ireland
> r...@iol.ie
>

[GENERAL] EXCEPT doesn't compare TIMESTAMP type?

[dipti shah]

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 timestamp without time zone cannot be matched
LINE 2: except select id, txid, txtime
^

Thanks,
Dipti

[GENERAL] Recognize the creating of duplicate row from trigger

[dipti shah]

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 is
intentional but I don't want my trigger to fail if tried to insert duplicate
row and rollback the transaction. It should not take any action(should not
insert duplication row) and returns when duplicate row is recognized.

Thanks,
Dipti

Re: [GENERAL] How to get whether user has ALL permissions on table?

[dipti shah]

Okay  I got to know from
http://www.postgresql.org/docs/8.4/interactive/functions-info.html that the
has_table_privilege returns true if any of the listed privilege is held.
Then how can I find whether user has all the specified permissions or not?

>From http://www.postgresql.org/docs/8.4/interactive/functions-info.html

has_table_privilege checks whether a user can access a table in a particular
way. The user can be specified by name or by OID (pg_authid.oid), or if the
argument is omitted current_user is assumed. The table can be specified by
name or by OID. (Thus, there are actually six variants of
has_table_privilege, which can be distinguished by the number and types of
their arguments.) When specifying by name, the name can be schema-qualified
if necessary. The desired access privilege type is specified by a text
string, which must evaluate to one of the values SELECT, INSERT, UPDATE,
DELETE, TRUNCATE, REFERENCES, or TRIGGER. Optionally, WITH GRANT OPTION can
be added to a privilege type to test whether the privilege is held with
grant option. Also, multiple privilege types can be listed separated by
commas,* in which case the result will be true if any of the listed
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 INSERT then works fine but if
> either of is there then it doesn't work.
> techdb=> SELECT has_table_privilege('user1', 'techdb.techtable', 'UPDATE,
> DELETE, TRUNCATE, REFERENCES, TRIGGER');
>
>  has_table_privilege
> -
>  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_namespace pn
>> where pc.relnamespace=pn.oid and pn.nspname='techdb' and
>> pc.relname='techtable';
>>relname|relacl
>>
>> --+--
>>  techtable| {postgres=arwdDxt/postgres,=ar/postgres,user1=ar/postgres}
>> (1 row)
>> techdb=> SELECT has_table_privilege('user1', 'techdb.techtable', 'SELECT,
>> 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, Apr 15, 2010 at 4:16 PM, dipti shah wrote:
>>
>>> 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'.
>>>> >
>>>> > techdb=# SELECT has_table_privilege('user1', 'techtable', 'ALL');
>>>> > ERROR:  unrecognized privilege type: "ALL"
>>>> >
>>>> > Do I have to run this command as below which includes all permissions
>>>> > explicitly? Did I miss anything?
>>>>
>>>> Right, you have to name all privileges.
>>>>
>>>> The desired access privilege type is specified by a text string, which
>>>> must evaluate to one of the values SELECT, INSERT, UPDATE, DELETE,
>>>> TRUNCATE, REFERENCES, or TRIGGER.
>>>>
>>>> Andreas
>>>> --
>>>> Andreas Kretschmer
>>>> Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
>>>> GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99
>>>>
>>>> --
>>>> 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 get whether user has ALL permissions on table?

[dipti shah]

It is strange. If I remove both SELECT and INSERT then works fine but if
either of is there then it doesn't work.
techdb=> SELECT has_table_privilege('user1', 'techdb.techtable', 'UPDATE,
DELETE, TRUNCATE, REFERENCES, TRIGGER');
 has_table_privilege
-
 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_namespace pn
> where pc.relnamespace=pn.oid and pn.nspname='techdb' and
> pc.relname='techtable';
>relname|relacl
>
> --+--
>  techtable| {postgres=arwdDxt/postgres,=ar/postgres,user1=ar/postgres}
> (1 row)
> techdb=> SELECT has_table_privilege('user1', 'techdb.techtable', 'SELECT,
> 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, Apr 15, 2010 at 4:16 PM, dipti shah wrote:
>
>> 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'.
>>> >
>>> > techdb=# SELECT has_table_privilege('user1', 'techtable', 'ALL');
>>> > ERROR:  unrecognized privilege type: "ALL"
>>> >
>>> > Do I have to run this command as below which includes all permissions
>>> > explicitly? Did I miss anything?
>>>
>>> Right, you have to name all privileges.
>>>
>>> The desired access privilege type is specified by a text string, which
>>> must evaluate to one of the values SELECT, INSERT, UPDATE, DELETE,
>>> TRUNCATE, REFERENCES, or TRIGGER.
>>>
>>> Andreas
>>> --
>>> Andreas Kretschmer
>>> Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
>>> GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99
>>>
>>> --
>>> 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 get whether user has ALL permissions on table?

[dipti shah]

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 and pn.nspname='techdb' and
pc.relname='techtable';
   relname|relacl
--+--
 techtable| {postgres=arwdDxt/postgres,=ar/postgres,user1=ar/postgres}
(1 row)
techdb=> SELECT has_table_privilege('user1', 'techdb.techtable', 'SELECT,
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, Apr 15, 2010 at 4:16 PM, dipti shah  wrote:

> 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'.
>> >
>> > techdb=# SELECT has_table_privilege('user1', 'techtable', 'ALL');
>> > ERROR:  unrecognized privilege type: "ALL"
>> >
>> > Do I have to run this command as below which includes all permissions
>> > explicitly? Did I miss anything?
>>
>> Right, you have to name all privileges.
>>
>> The desired access privilege type is specified by a text string, which
>> must evaluate to one of the values SELECT, INSERT, UPDATE, DELETE,
>> TRUNCATE, REFERENCES, or TRIGGER.
>>
>> Andreas
>> --
>> Andreas Kretschmer
>> Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
>> GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99
>>
>> --
>> 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 get whether user has ALL permissions on table?

[dipti shah]

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'.
> >
> > techdb=# SELECT has_table_privilege('user1', 'techtable', 'ALL');
> > ERROR:  unrecognized privilege type: "ALL"
> >
> > Do I have to run this command as below which includes all permissions
> > explicitly? Did I miss anything?
>
> Right, you have to name all privileges.
>
> The desired access privilege type is specified by a text string, which
> must evaluate to one of the values SELECT, INSERT, UPDATE, DELETE,
> TRUNCATE, REFERENCES, or TRIGGER.
>
> Andreas
> --
> Andreas Kretschmer
> Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
> GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99
>
> --
> 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 get whether user has ALL permissions on table?

[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'.

techdb=# SELECT has_table_privilege('user1', 'techtable', 'ALL');
ERROR:  unrecognized privilege type: "ALL"

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
---+---
 techtable | {postgres=arwdDxt/postgres,=ar/postgres,user1=arwdDxt/postgres}
(1 row)

Do I have to run this command as below which includes all permissions
explicitly? Did I miss anything?

techdb=# SELECT has_table_privilege('user1', 'techtable', '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 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
> >
> ---+---
> >  techtable |
> {postgres=arwdDxt/postgres,=ar/postgres,user1=arwdDxt/postgres}
> > (1 row)
> >
> > Could anyone please tell me if there is any function or command in
> PostGreSql
> > which returns True if given user has ALL permissions on given table? Is
> there
>
> Sure, read
> http://www.postgresql.org/docs/8.4/interactive/functions-info.html
>
>
> Andreas
> --
> Andreas Kretschmer
> Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
> GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99
>
> --
> 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 whether user has ALL permissions on table?

[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 pc.relnamespace=pn.oid and pn.nspname='techdb' and
pc.relname='techtable';
 relname   |  relacl
---+---
 techtable | {postgres=arwdDxt/postgres,=ar/postgres,user1=arwdDxt/postgres}
(1 row)

Could anyone please tell me if there is any function or command in
PostGreSql which returns True if given user has ALL permissions on given
table? Is there any alternative way to do this. I have table and user names
and want to know whether user has ALL permissions on table or not.

Thanks,
Ditpi

Re: [GENERAL] Grant column level permissions

[dipti shah]

Ohh...sorry. It works but I am wondering why pg_namespace doesn't display
any information.

techdb=> select description from techtable;
 description
-
(0 rows)

techdb=> select number from techtable;
ERROR:  permission denied for relation techtable

Thanks a ton.

On Thu, 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' and
> pc.relname='techtable';
>  relname   |   relacl
> ---+-
>  techtable | {postgres=arwdDxt/postgres}
> (1 row)
>
>
> techdb=# GRANT SELECT (description), UPDATE (description) ON techtable TO
> user1;
> GRANT
> 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
> ---+-
>  techtable | {postgres=arwdDxt/postgres}
> (1 row)
>
> Please note that giving select permission on description column doesn't
> made any difference in permissions set pf pg_namespace,
>
> techdb=# \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, dipti shah wrote:
>
>> Okay. I think I got it but it is not working the way it should. I have
>> given select permission on one column but still it is displaying both the
>> columns. Could you please tell me what is wrong.
>>
>>
>> techdb=# GRANT SELECT (description), UPDATE (description) ON techtable TO
>> user1;
>> GRANT
>> sysdb=> select * 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 hence, I asked for
>>> the example.
>>>
>>> GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( *column* [, ...] )
>>>
>>> [,...] | ALL [ PRIVILEGES ] ( *column* [, ...] ) }
>>> ON [ TABLE ] *tablename* [, ...]
>>> TO { [ GROUP ] *rolename* | PUBLIC } [, ...] [ WITH GRANT OPTION ]
>>>
>>>
>>> techdb=# grant select(column['description']) ON techtable TO user1;
>>> ERROR:  syntax error at or near "column"
>>> LINE 1: grant select(column['description']) 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 to know
>>>> that
>>>> > it is possible to grant column level permissions.
>>>>
>>>> 
>>>>
>>>> > Could anyone please give me the example of how to grant column level
>>>> > permissions? Basically, I want to give permissions to set of
>>>> > users(user-group) to only couple of columns in my table.
>>>>
>>>> Have you reviewed the fine documentation?
>>>> <http://www.postgresql.org/docs/8.4/interactive/sql-grant.html>
>>>>
>>>> Michael Glaesemann
>>>> grzm seespotcode net
>>>>
>>>>
>>>>
>>>>
>>>
>>
>

Re: [GENERAL] Grant column level permissions

[dipti shah]

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   |   relacl
---+-
 techtable | {postgres=arwdDxt/postgres}
(1 row)

techdb=# GRANT SELECT (description), UPDATE (description) ON techtable TO
user1;
GRANT
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
---+-
 techtable | {postgres=arwdDxt/postgres}
(1 row)

Please note that giving select permission on description column doesn't made
any difference in permissions set pf pg_namespace,

techdb=# \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, dipti shah  wrote:

> Okay. I think I got it but it is not working the way it should. I have
> given select permission on one column but still it is displaying both the
> columns. Could you please tell me what is wrong.
>
>
> techdb=# GRANT SELECT (description), UPDATE (description) ON techtable TO
> user1;
> GRANT
> sysdb=> select * 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 hence, I asked for
>> the example.
>>
>> GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( *column* [, ...] )
>>
>> [,...] | ALL [ PRIVILEGES ] ( *column* [, ...] ) }
>> ON [ TABLE ] *tablename* [, ...]
>> TO { [ GROUP ] *rolename* | PUBLIC } [, ...] [ WITH GRANT OPTION ]
>>
>>
>> techdb=# grant select(column['description']) ON techtable TO user1;
>> ERROR:  syntax error at or near "column"
>> LINE 1: grant select(column['description']) 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 to know
>>> that
>>> > it is possible to grant column level permissions.
>>>
>>> 
>>>
>>> > Could anyone please give me the example of how to grant column level
>>> > permissions? Basically, I want to give permissions to set of
>>> > users(user-group) to only couple of columns in my table.
>>>
>>> Have you reviewed the fine documentation?
>>> <http://www.postgresql.org/docs/8.4/interactive/sql-grant.html>
>>>
>>> Michael Glaesemann
>>> grzm seespotcode net
>>>
>>>
>>>
>>>
>>
>

Re: [GENERAL] Grant column level permissions

[dipti shah]

Okay. I think I got it but it is not working the way it should. I have given
select permission on one column but still it is displaying both the columns.
Could you please tell me what is wrong.


techdb=# GRANT SELECT (description), UPDATE (description) ON techtable TO
user1;
GRANT
sysdb=> select * 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 hence, I asked for
> the example.
>
> GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( *column* [, ...] )
>
> [,...] | ALL [ PRIVILEGES ] ( *column* [, ...] ) }
> ON [ TABLE ] *tablename* [, ...]
> TO { [ GROUP ] *rolename* | PUBLIC } [, ...] [ WITH GRANT OPTION ]
>
>
> techdb=# grant select(column['description']) ON techtable TO user1;
> ERROR:  syntax error at or near "column"
> LINE 1: grant select(column['description']) 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 to know
>> that
>> > it is possible to grant column level permissions.
>>
>> 
>>
>> > Could anyone please give me the example of how to grant column level
>> > permissions? Basically, I want to give permissions to set of
>> > users(user-group) to only couple of columns in my table.
>>
>> Have you reviewed the fine documentation?
>> <http://www.postgresql.org/docs/8.4/interactive/sql-grant.html>
>>
>> Michael Glaesemann
>> grzm seespotcode net
>>
>>
>>
>>
>

Re: [GENERAL] Grant column level permissions

[dipti shah]

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 hence, I asked for
the example.

GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( *column* [, ...] )
[,...] | ALL [ PRIVILEGES ] ( *column* [, ...] ) }
ON [ TABLE ] *tablename* [, ...]
TO { [ GROUP ] *rolename* | PUBLIC } [, ...] [ WITH GRANT OPTION ]


techdb=# grant select(column['description']) ON techtable TO user1;
ERROR:  syntax error at or near "column"
LINE 1: grant select(column['description']) 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 to know
> that
> > it is possible to grant column level permissions.
>
> 
>
> > Could anyone please give me the example of how to grant column level
> > permissions? Basically, I want to give permissions to set of
> > users(user-group) to only couple of columns in my table.
>
> Have you reviewed the fine documentation?
> <http://www.postgresql.org/docs/8.4/interactive/sql-grant.html>
>
> Michael Glaesemann
> grzm seespotcode net
>
>
>
>

[GENERAL] Grant column level permissions

[dipti shah]

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 connection and access
control more flexible and simpler to understand.
Column-Level Permissions DBAs can now grant permissions (SELECT, UPDATE) on
specific columns as well as on entire tables. This makes it easier to secure
sensitive data in your database.
Could anyone please give me the example of how to grant column level
permissions? Basically, I want to give permissions to set of
users(user-group) to only couple of columns in my table.

Thanks,
Dipti

Re: [GENERAL] Prevent users to drop triggers applied on table

[dipti shah]

Yah...that's correct but I am wondering if someone out there knows work
around to this.

I could make my table owner to postgres user but then is there any way
original user can drop the table. I want original users should allow to drop
their table.

Thanks,
Dipti

On Thu, Apr 1, 2010 at 12: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 from deleting the triggers
>> applied on table by create table stored procedure. Could anyone please
>> suggest me to get way out here.
>>
>
> if you don't want someone to modify tables, including dropping triggers,
> then they shouldn't be the owner of it.  the owner has full rights to a
> table, including the right to delete it, alter it, etc.
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

[GENERAL] Prevent users to drop triggers applied on table

[dipti shah]

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 suggest me to
get way out here.

Thanks,
Dipti

[GENERAL] Get the list of permissions on schema for current user

[dipti shah]

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 and pn.nspname='mydb';
 relname  |relacl
--+---
  mylog   |
{postgres=arwdDxt/postgres,=arwdDxt/postgres}
  techtable   | {postgres=arwdDxt/postgres,=ar/postgres}
  techtable_log   |
  hrtable | {postgres=arwdDxt/postgres,=ar/postgres}
  hrtable_log |
(5 rows)


mydb=> select current_user;
 current_user
--
 user1
(1 row)

mydb=>

Thanks, Dipti

Re: [GENERAL] Connect to postgresql database using Perl

[dipti shah]

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
>> this code from remote host. I get an error when trying to run it from remote
>> host. I think it is obvious because in below code there is no information
>> where to connect to. Could you please help me out.
>>
>> use DBI;
>> $DB_name= 'mydb';
>> $DB_user= 'postgres';
>> $DB_pwd = '';
>> $dbh = DBI->connect("dbi:Pg:dbname=$DB_name","$DB_user","$DB_pwd");
>> ...
>>
>
> see 
> http://search.cpan.org/~turnstep/DBD-Pg-2.16.1/Pg.pm#connect<http://search.cpan.org/%7Eturnstep/DBD-Pg-2.16.1/Pg.pm#connect><
> http://search.cpan.org/%7Eturnstep/DBD-Pg-2.16.1/Pg.pm#connect>
>
> add ;host=hostname-or-ip to the connect string...
>

Re: [GENERAL] Connect to postgresql database using Perl

[dipti shah]

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 this
code from remote host. I get an error when trying to run it from remote
host. I think it is obvious because in below code there is no information
where to connect to. Could you please help me out.

use DBI;
$DB_name= 'mydb';
$DB_user= 'postgres';
$DB_pwd = '';
$dbh = DBI->connect("dbi:Pg:dbname=$DB_name","$DB_user","$DB_pwd");
if ( !defined $dbh ) { die "Cannot connect to database!\n"; }
$sth  = $dbh->prepare("SELECT * FROM mytable");
$sth->execute();
while ( ($id,$name) = $sth->fetchrow_array() ) {  print "$id\t\t $name \n";
}
$sth->finish();
$dbh->disconnect();

*remote-host# perl pg-connect.pl
DBI connect('dbname=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 Wed, Mar 31, 2010 at 11:53 AM, Pavel Stehule wrote:

> Hello
>
> 2010/3/31 dipti shah :
> > 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.
> >
>
> http://www.felixgers.de/teaching/perl/perl_DBI.html
> http://structbio.vanderbilt.edu/chazin/wisdom/dbi_howto.html
>
> Regards
>
> Pavel Stehule
>
> > Thanks,
> > Dipti
> >
>

[GENERAL] Connect to postgresql database using Perl

[dipti shah]

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

[GENERAL] Get the list of permissions on schema for current user

[dipti shah]

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 and pn.nspname='mydb';
 relname  |relacl
--+---
  mylog   |
{postgres=arwdDxt/postgres,=arwdDxt/postgres}
  techtable   | {postgres=arwdDxt/postgres,=ar/postgres}
  techtable_log   |
  hrtable | {postgres=arwdDxt/postgres,=ar/postgres}
  hrtable_log |
(5 rows)


mydb=> select current_user;
 current_user
--
 user1
(1 row)

mydb=>

Thanks, Dipti

Re: [GENERAL] Get the list of permissions/privileges on schema

[dipti shah]

Thanks Ashesh, I ran below command and it is listing all privileges of
objects under 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 and pn.nspname='mydb';
 relname  |relacl
--+---
  mylog   |
{postgres=arwdDxt/postgres,=arwdDxt/postgres}
  techtable   | {postgres=arwdDxt/postgres,=ar/postgres}
  techtable_log   |
  hrtable | {postgres=arwdDxt/postgres,=ar/postgres}
  hrtable_log |
(5 rows)


mydb=> select current_user;
 current_user
--
 user1
(1 row)

mydb=>

Thanks,
Dipti

On Thu, Mar 25, 2010 at 2:44 PM, Ashesh Vashi  wrote:

> You should look into the pg_class table : relacl attribute for the
> permissions on any object.
>
> --
> Thanks & Regards,
>
> Ashesh Vashi
> EnterpriseDB INDIA: Enterprise Postgres Company<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
>>
>
>

[GENERAL] Designing Postgres Security Model

[dipti shah]

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 stored procedures are defined with SECURITY DEFINER in postgres user
context. I have given execute permission to set of users on these stored
procedures to achieve my goal.

*I want only privileged users to create table with foreign key
references.*==> This can be achieved using SET ROLE current user
before executing create
command but *Postgresql doesn't allow running SET ROLE in SECURITY DEFINER
function* *context* so I have created a my_sudo function which gets invoked
from my stored procedure. This sudo function creates a temporary SECURITY
DEFINER function and changes *owner to the current user before executing
create table command.
*
Now, as sudo function runs actual create command as current user context and
he/she does not have permission on mydb schema(as revoked all permission to
meet first requirement), I have to grant the ALL permissions on mydb schema
to current user temporary and then restore his/her actual privileges back to
make sure that users actual permission doesn't change.

*Problem:* Could anyone tell me how to get the schema permissions list for
current user and restore it back once store procedure execution completed.

Please feel free to let me know if you have any questions.

Thanks a lot,
Dipti

Re: [GENERAL] Get the list of permissions/privileges on schema

[dipti shah]

Okay..then could you please suggest me what could be the correct way? 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 stored procedures are defined with SECURITY DEFINER in postgres user
context. I have given execute permission to set of users to these stored
procedures to achieve my goal.

*I want only allowed users to create table with foreign key references.* ==>
This can be achieve using SET ROLE current user but *Postgresql doesn't
allow SET ROLE in SECURITY DEFINER function* *context* so I have created a
my_sudo function which gets invoked from my stored procedure. This sudo
function creates a temporary SECURITY DEFINER function and changes owner to
the current user before executing create table command.

Now, as sudo function runs actual create command as current user context and
he/she will not have permission on mydb schema, I have to grant the ALL
permissions on mydb schema to current user temporary and then restore
his/her actual privileges back to make sure that users actual permission
doesn't change.

Hence, I am asking how can I store the schema permissions list and restore
it back once store procedure execution completed.

Please let me know where I am going wrong here? I am trying to get my things
done out of what PostGreSQL supports.

It would be really nice if anyone could help 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
>> 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 that
>> thogh my store procedure manipulates schema permissions, at the end, users
>> will have their permissions intact.
>>
>>
>
> thats totally the wrong way to do things in SQL.
>
>
>

Re: [GENERAL] Get the list of permissions/privileges on schema

[dipti shah]

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 that
thogh my store procedure manipulates schema permissions, at the end, users
will have their permissions intact.

Thanks,
Dipti

On Thu, Mar 25, 2010 at 2:59 PM, Ashesh Vashi  wrote:

>
>
>  On Thu, Mar 25, 2010 at 2:54 PM, Sergey Konoplev wrote:
>
>> On 25 March 2010 12:14, Ashesh Vashi 
>> wrote:
>> > You should look into the pg_class table : relacl attribute for the
>> > permissions on any object.
>>
>> Is it correct to set it directly?
>>
>
> Of course not...
> This is just way to fetch the list of permission on any object.
>
>  --
> Thanks & Regards,
>
> Ashesh Vashi
> EnterpriseDB INDIA: Enterprise Postgres Company
>

[GENERAL] Get the list of permissions/privileges on schema

[dipti shah]

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

[GENERAL] has_schema_privilege function

[dipti shah]

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('user1', 'mydb', 'CREATE');

...But I want to check whether user has ALL permissions or not. Below
doesn;t work.

SELECT has_schema_privilege('user1', 'mydb', 'ALL');

Do I have to check both USAGE and CREATE permissions to check ALL
permissions?

Thanks.

Re: [GENERAL] Is it possible to findout actual owner of table?

[dipti shah]

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 business logic.  your user software
> calls the application server to do things, and it in turn accesses the
> database.User written software is not alloweed to directly connecct to
> the databases at all.   Classic example of a middleware platform Apache
> Tomcat
>
>
>

Re: [GENERAL] Is it possible to findout actual owner of table?

[dipti shah]

What is middleware?

Is it similar to stored procedure? That what I have been doing. I have
revoked all permissions from mydb schema from public and have SECURITY
DEFINER enable for stored procedure to allow creating/droping/altering
tables.

Thanks.

On Tue, Mar 9, 2010 at 12:19 PM, John R Pierce  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 direct commands.
>>
>>
>
> may be you should use middleware instead and not let your users connect
> directly to SQL at all.
>
> so, any DDL changes, you'd call the middleware server, it would
> authenticate the user, decide whats allowed, maintain your audit trail,
> logging, etc.  ditto, any operations that require database queries, etc,
> would all be done by this middleware.
>
>
>
>
>

Re: [GENERAL] Is it possible to findout actual owner of table?

[dipti shah]

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 direct commands.

Thanks,
Dipti

On Tue, 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 each table created by this stored
> procedure
> > to super user(postgres) so that no one will be allowed to drop/alter
> table.
> > I want to allow to drop/alter the table by actuall user who ran the
> stored
> > procedure to create the table. Could anyone please suggest me how to
> proceed
> > with this?
>
> It sounds like you are going to a great deal of trouble to reinvent the
> standard behavior.  Why not forget the stored procedure and just let
> users create and drop their own tables?
>
>regards, tom lane
>

[GENERAL] How to save existing permissions on schema and later on restore it?

[dipti shah]

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
grant the ALL permissions on mydb schema and later on revoke it and just
grant USAGE permissions. Here I have problem if users have full permissions
on schema executes the stored procedure. His original schema permissions
will be altered by my stored procedure. I want to prevent this.

Thanks,
Dipti

[GENERAL] Is it possible to findout actual owner of table?

[dipti shah]

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/alter table.
I want to allow to drop/alter the table by actuall user who ran the stored
procedure to create the table. Could anyone please suggest me how to proceed
with this?

Thanks,
Dipti

Re: [GENERAL] SET Role doesn't work from Security Definer Function...

[dipti shah]

This issue is driving me crazy. Could any one please suggest me any
workaround?

For summary of issue,

   1. I don't want any users to perform any action on mydb schema without
using my stored procedure. So I revoke ALL permissions from mydb schema and
assigned only USAGE permissions.
   2. As my stored procedure allows creating table in mydb schema and users
have only USAGE permissions on mydb schama, I have to defined my stored
procedure with SECURITY DEFINER so that it allows to create table in mydb
schema.
   3. To prevent creating unauthenticated foreign references to other
tables, 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 shah wrote:

> No, I tried that but that can't be done in my requirements because my
> function has to be run in super user context to create the table in schema
> where normal users have only USAGE permissions. If I remove SECURITY DEFINER
> then my stored procedure will be failed for all users by saying "permission
> denied on schema myschema".
>
> Moreover, I want to run only create table code in normal user context and
> other things in stored procedure should be done in super user context.
>
> I tried all possible ways but couldn't find to get out of this yet.
>
> Thanks,
> Dipti
>
>
> On Tue, Feb 23, 2010 at 8:36 PM, Alvaro Herrera <
> alvhe...@commandprompt.com> 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 Function foo2 with SECURITY DEFINER ...
>> >  spi_exe_query("select foo1()"); ==> Here it throws the error.
>>
>> Shouldn't it be the other way around?  The normal function calls the
>> security-definer one.
>>
>> --
>> Alvaro Herrera
>> http://www.CommandPrompt.com/ <http://www.commandprompt.com/>
>> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>>
>
>

Re: [GENERAL] Minor systax error but not able to resolve it...

[dipti shah]

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_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 plperl;");
>> spi_exec_query("SELECT myfunc();");
>> $BODY$
>>   LANGUAGE 'plperl';
>> CREATE FUNCTION
>> mydb=>  SELECT test_create();
>> ERROR:  error from Perl function "test_create": error from Perl function
>> "myfunc": Can't locate object method "col" via package "varchar" (perhaps
>> you forgot to load "varchar"?) at line 1. at line 3.
>>
>
> The function "myfunc" you are trying to create as plperl isn't perl. Either
> give it some perl or try "LANGUAGE SQL".
>
> --
>  Richard Huxton
>  Archonet Ltd
>

Re: [GENERAL] SET Role doesn't work from Security Definer Function...

[dipti shah]

No, I tried that but that can't be done in my requirements because my
function has to be run in super user context to create the table in schema
where normal users have only USAGE permissions. If I remove SECURITY DEFINER
then my stored procedure will be failed for all users by saying "permission
denied on schema myschema".

Moreover, I want to run only create table code in normal user context and
other things in stored procedure should be done in super user context.

I tried all possible ways but couldn't find to get out of this yet.

Thanks,
Dipti

On Tue, Feb 23, 2010 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 Function foo2 with SECURITY DEFINER ...
> >  spi_exe_query("select foo1()"); ==> Here it throws the error.
>
> Shouldn't it be the other way around?  The normal function calls the
> security-definer one.
>
> --
> Alvaro Herrera
> http://www.CommandPrompt.com/
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>

Re: [GENERAL] Minor systax error but not able to resolve it...

[dipti shah]

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 plperl;");
spi_exec_query("SELECT myfunc();");
$BODY$
  LANGUAGE 'plperl';
CREATE FUNCTION
mydb=> SELECT test_create();
ERROR:  error from Perl function "test_create": error from Perl function
"myfunc": Can't locate object method "col" via package "varchar" (perhaps
you forgot to load "varchar"?) at line 1. at line 3.

this is also related to quoting?

On Tue, Feb 23, 2010 at 9:23 PM, Tom Lane  wrote:

> Richard Huxton  writes:
> > You're interpolating $cmd here but not quoting it, so you end up with:
> > ... RETURNS void AS CREATE TABLE test-table...
> > whereas you want:
> > ... RETURNS void AS 'CREATE TABLE test-table...'...
>
> > Probably best to use dollar-quoting: $TMP$ or similar, but don't forget
> > to escapt the $ or perl will think it's a variable.
>
> Dollar-quoting is really dangerous when you're trying to quote
> nonconstant text.  I'd suggest passing the string through
> quote_literal() instead.
>
>regards, tom lane
>

[GENERAL] Minor systax error but not able to resolve it...

[dipti shah]

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() RETURNS void AS
$cmd LANGUAGE sql;");
$BODY$
  LANGUAGE 'plperl';
CREATE FUNCTION
mydb=> SELECT test_create();
ERROR:  error from Perl function "test_create": syntax error at or near
"CREATE" at line 3.
mydb=>

I want to create function from my stored procedure in any language.

Thanks,
Dipti

Re: [GENERAL] SET Role doesn't work from Security Definer Function...

[dipti shah]

You are correct Tom that I want to perform some portion of function as
postgres user and other portion as current user.

As per you suggestion I did refactor and separated the portion that needs to
be executed as superuser to another function. But the thing is PostGreSQL
recognize when I call this separated funtion from my original SECURITY
DEFINER function and gives the same error. :(

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 ...
 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" 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.
>
> This is intentional because allowing it creates security holes.
>
> > If I create function in postgres user with Security Definer enabled, it
> will
> > allow to create any table with any foreign references etc...So I am
> setting
> > role to current_user in my function and then creating a table to make
> sure
> > that user has the appropriate privilege.
>
> Well, if you are trying to set the role back to current, why don't you
> just not have the function be security definer in the first place?
>
> I suppose the answer to that is that you want it to do some things as
> superuser and some things not.  In which case, you need to refactor so
> that those two classes of things are done by different functions.
>
>regards, tom lane
>

[GENERAL] SET Role doesn't work from Security Definer Function...

[dipti shah]

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 associated logging tables and event tables
automatically. I want to make sure that when users use my stored procedure
to create table, they should be allowed only if they have permission to do
so.

If I create function in postgres user with Security Definer enabled, it will
allow to create any table with any foreign references etc...So I am setting
role to current_user in my function and then creating a table to make sure
that user has the appropriate privilege.

Since, SET Role is failing in security definer context, I am helpless now.
Could anyone suggest any workaround or solution to this issue. Is this is
known issue? Anybody already encountered it?

Thanks,
Dipti

Re: [GENERAL] What is unsecure postgres languages? How to disable them?

[dipti shah]

Sorry Albe for confusion. Yes, I meant untrusted languages like C, PL/PerlU,
PL/PythonU etc...

Thanks a lot you and Tino for nice reply. Could you guys tell me how could I
verify whether those languages are installed on my PostGreSQL server?

Thanks for being there,
Dipti

On Mon, Feb 22, 2010 at 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 - what exactly do you mean?
>
> If you mean "untrusted" languages like PL/PerlU, they are not
> installed by default *and* they are restricted to superusers.
>
> You cannot disable C functions, but to define them you must also
> have superuser privileges.
>
> Yours,
> Laurenz Albe
>

[GENERAL] What is unsecure postgres languages? How to disable them?

[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

Re: [GENERAL] Not able to access schema functions and table...

[dipti shah]

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;
> >
> > 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 user1;
> >
> > GRANT SELECT ON mydb_schema.test1 TO user1;
> >
> > $ psql -h postgresqlhost.aus -d mydb -U user1
> > psql (8.4.1)
> > Type "help" for help.
> > user1=> select mydb_schema.readtable();
> > ERROR:  permission denied for schema mydb_schema
> > user1=> select * from mydb_schema.test1;
> > ERROR:  permission denied for mydb_schema
> > LINE 1: select * from mydb_schema.test1;
> >^
> > user1=>
> >
> > Could anyone please tell me what is wrong here? *I want users to have
> only
> > select persions on tables of mydb_schema schema and function readtable
> > execute permissions for only few users(like above user1).*
> >
>
> You should at least GRANT USAGE on your schema mydb_schema to your users.
>
>
> --
> Guillaume.
>  http://www.postgresqlfr.org
>  http://dalibo.com
>

[GENERAL] Not able to access schema functions and table...

[dipti shah]

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 user1;

GRANT SELECT ON mydb_schema.test1 TO user1;

$ psql -h postgresqlhost.aus -d mydb -U user1
psql (8.4.1)
Type "help" for help.
user1=> select mydb_schema.readtable();
ERROR:  permission denied for schema mydb_schema
user1=> select * from mydb_schema.test1;
ERROR:  permission denied for mydb_schema
LINE 1: select * from mydb_schema.test1;
   ^
user1=>

Could anyone please tell me what is wrong here? *I want users to have only
select persions on tables of mydb_schema schema and function readtable
execute permissions for only few users(like above user1).*

Please let me know if there is any alternative way.

Thanks,
Dipti

Re: [GENERAL] Define permissions at database level

[dipti shah]

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 ON ALL TABLES IN SCHEMA mySchema FROM PUBLIC;
>>
>>2. Give store procedure for creating table with SECURITY DEFINER marked
>> so that all tables owner will be "postgres" user.
>>3. Grant SELECT permission to required group on created table.
>>4. Give store procedure for droping the table with SECURITY DEFINER
>> marked so that droping will happen in the context of "postgres" user.
>>
>> I think above will not allow anyone to create and/or drop tables directly
>> without using store procedures.
>>
>> Please let me know if I am missing anything.
>>
>
> Sounds about right. Always test though.
>
> --
>  Richard Huxton
>  Archonet Ltd
>

Re: [GENERAL] Define permissions at database level

[dipti shah]

Okay then I think below works:

   1. Revoke permission ALL permissions from PUBLIC on schema.

  REVOKE ALL ON ALL TABLES IN SCHEMA mySchema FROM PUBLIC;

   2. Give store procedure for creating table with SECURITY DEFINER marked
so that all tables owner will be "postgres" user.
   3. Grant SELECT permission to required group on created table.
   4. Give store procedure for droping the table with SECURITY DEFINER
marked so that droping will happen in the context of "postgres" user.

I think above will not allow anyone to create and/or drop tables directly
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
>> directly. They have to use stored procedure to drop the table.
>>
>
> Then don't let them own the table. Or rather, the role they log in to the
> database as shouldn't.
>
> --
>  Richard Huxton
>  Archonet Ltd
>

Re: [GENERAL] Define permissions at database level

[dipti shah]

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 sense. If I want to restrict DROP for any table
>> then do I need to REVOKE permissions individually on tables.
>>
>> Revoke DROP ON MyTable from PUBLIC;
>>
>> I want to avoid doing it so I am wondering if I can define/grant the
>> permission at database level so that nousers can directly use any commands
>> like CREATE, UPDATE, ALTER or DROP. They have to use stored procedure.
>> They
>> can only use SELECT. Nothing else.
>>
>
> Only table owners can drop them.
>
> You can deny permission to a whole schema, but not set permissions on
> everything in it in one go. However there are various shell-scripts and
> plpgsql functions that let you set permissions on groups of tables in one
> go. They should show up easily enough through googling.
>
> --
>  Richard Huxton
>  Archonet Ltd
>

Re: [GENERAL] Define permissions at database level

[dipti shah]

Thanks Richard. That makes sense. If I want to restrict DROP for any table
then do I need to REVOKE permissions individually on tables.

Revoke DROP ON MyTable from PUBLIC;

I want to avoid doing it so I am wondering if I can define/grant the
permission at database level so that nousers can 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 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.
>>
>
> 1. Place users into appropriate groups (makes it easier to manage later).
> Note that groups and users are actually both just roles.
>
> 2. Use GRANT/REVOKE to restrict what those users can do.
>
> 3. Write your "alter table" function owned by user "postgres" and make sure
> it's marked "SECURITY DEFINER".
>
> http://www.postgresql.org/docs/8.4/static/user-manag.html
> http://www.postgresql.org/docs/8.4/static/sql-createfunction.html
>
> --
>  Richard Huxton
>  Archonet Ltd
>

[GENERAL] Define permissions at database level

[dipti shah]

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

[GENERAL] DDL trigger kind functionality in PostGreSQL

[dipti shah]

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
of table or any database object. I want users to use stored procedures
always to drop or alter the tables and any other database objects.

Any alternative way would be really helpful.

Thanks,
Dipti

Re: [GENERAL] Set the permissions while creating database, schema, and tables

[dipti shah]

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 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 for schema or table. Please let me know if I missed
>> out something.
>>
>>CREATE DATABASE sales OWNER salesapp TABLESPACE salesspace;
>>
>>  With above command, can I grant permission to particular group of users?
>>
>
> you would use the GRANT command to grant permissions
>
>
>

[GENERAL] Set the permissions while creating database, schema, and tables

[dipti shah]

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 for schema or table. Please let me know if I missed
out something.


CREATE DATABASE sales OWNER salesapp TABLESPACE salesspace;


With above command, can I grant permission to particular group of users?


Thanks,
Dipti

Re: [GENERAL] The easiest and robust way of configuring password pass through mechanism..

[dipti shah]

Thanks John for reply. Users can connect to database server in either
way(from same computer or remote).

Currently by default all users are able to connect to database as pg_hba
file has been configured to TRUST for all users.

I am planning to remove these all users and want only specific groups 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 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 through the
>> password so that users need not to enter it everytime they connects.
>>
>
>
> are your users on the same computer as the database server, and connecting
> via unix socket as their unix account only?  if so,
>
>   LOCAL ALL ALL IDENT SAMEUSER
>
> (leave out SAMEUSER on 8.4, its implied with IDENT).   this relies on unix
> authentication, and unix user joe will only be able to connect as database
> user joe.
>
> or, if the users are connecting via tcp/ip from specific hosts, and you can
> trust all users on those hosts
>
>   HOST ALL someusername some.ip.addr TRUST
>
> or... if your users are always connecting via libpq. you can use
> $HOME/.pgpasswd per
> http://www.postgresql.org/docs/current/static/libpq-pgpass.html to specify
> passwords, when you do this, the application programs won't need to supply
> them.   I don't know if this works with clients like JDBC, however.
>
> finally, you can use ssl client certificates, this is the most complex to
> setup, see http://www.postgresql.org/docs/current/static/libpq-ssl.html
>
>
>
>
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

[GENERAL] The easiest and robust way of configuring password pass through mechanism..

[dipti shah]

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 through the
password so that users need not to enter it everytime they connects.

Thanks,
Dipti

Re: [GENERAL] Setting permissions to access schema and language

[dipti shah]

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 creating schemas? Also, is it possible to
> restrict language permissions? Suppose I want only few users should use C
> language and for rest of users it should be missing. How can I restrict
> permissions on languages?
>
> Thanks in advanced,
> Jignesh
>

Re: [GENERAL] SSL connection option from client side?

[dipti shah]

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.
> 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. In pg_hba.conf, I have to use "hostssl" for all client connections.
>
> Also, after setting above configurations, if I connect to my database using
> "psql -d Techdb -U postgres" then automatically it will be SSL connection or
> I need to specify any option from client side?
>
> Thanks,
> Dipti

[GENERAL] SSL connection option from client side?

[dipti shah]

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. In pg_hba.conf, I have to use "hostssl" for all client connections.

Also, after setting above configurations, if I connect to my database using
"psql -d Techdb -U postgres" then automatically it will be SSL connection or
I need to specify any option from client side?

Thanks,
Dipti

Re: [GENERAL] Questions on PostGreSQL Authentication mechanism...

[dipti shah]

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 if I connect to the database from server where I have
>> PostGreSQL installed. pg_read_file doesn't work in any case.
>>  Techdb=# \! exec cat /etc/postgresql/8.4/main/pg_hba.conf
>> cat: cannot open /etc/postgresql/8.4/main/pg_hba.conf
>>
>
> thats because psql runs the command on the LOCAL server that the user is
> running psql on.   would be all kinda security problems if a user could run
> commands on the remote server without having logged onto it as a regular
> user.
>
>
>

Re: [GENERAL] Does PostGreSQL support SSL inbuilt?

[dipti shah]

Thanks a lot Robert and Howard.  I got the information about how to set
server configuration to make SSL aware only. 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. In pg_hba.conf, I have to user "hostssl" method for all client
connection.

Also, after setting above configurations, if I connect to my database using
"psql -d Techdb -U postgres" then automatically it will be SSL connection or
I need to specify any option from client side?

Thanks,
Dipti

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,
>> Dipti
>>
> Yes. See http://www.postgresql.org/docs/8.4/static/ssl-tcp.html. You can
> force the server to only accept secure connections.
>
> Howard Cole
>

Re: [GENERAL] Questions on PostGreSQL Authentication mechanism...

[dipti shah]

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 server where I have PostGreSQL
installed. pg_read_file doesn't work in any case.

Techdb=# \! exec cat /etc/postgresql/8.4/main/pg_hba.conf
cat: cannot open /etc/postgresql/8.4/main/pg_hba.conf
Techdb=# select pg_read_file('pg_hba.conf', 0, 8192);
ERROR:  could not open file "pg_hba.conf" for reading: No such file or
directory
Thanks,
Dipti



On Wed, Feb 3, 2010 at 12:14 AM, Tim Bruce - 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
> >> (1 row)
> >>
> >> Moreover, is there anyway to view content of this file from stored in
> >> above
> >> location "Techdb" command prompt itself.
> >>
> >> Techdb=# cat  /etc/postgresql/8.4/main/pg_hba.conf;
> >
> > Probably pg_read_file():
> >
> > select pg_read_file('pg_hba.conf', 0, 8192);
> >
> > Note that pg_read_file only allows paths relative to $PGDATA, which is
> > what you get from SHOW data_directory;
> >
> > --
> > Alvaro Herrera
> > http://www.CommandPrompt.com/ <http://www.commandprompt.com/>
> > PostgreSQL Replication, Consulting, Custom Development, 24x7 support
> >
> > --
> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
> >
>
> Since the pg_hba.conf file is located in the /etc path, the pg_read_file
> command fails with an error of
>
> "could not open file "pg_hba.conf" for reading: No such file or direcotry"
>
> It also won't allow use of the absolute path.
>
> The answer I found was to use the following command:
>
> postgres=# \! exec cat /etc/postgresql/8.3/main/pg_hba.conf
>
> Tim
> --
> Timothy J. Bruce
>
> Registered Linux User #325725
>
>

[GENERAL] Does PostGreSQL support SSL inbuilt?

[dipti shah]

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

Re: [GENERAL] Questions on PostGreSQL Authentication mechanism...

[dipti shah]

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 such commands?
>>
>
> All the configuration settings are listed in Chapter 18:
> http://www.postgresql.org/docs/8.4/static/runtime-config.html
>
> You'll also find details in the SQL reference for SET and SHOW:
> http://www.postgresql.org/docs/8.4/static/sql-set.html
> http://www.postgresql.org/docs/8.4/static/sql-show.html
>
> "SHOW ALL" will list all the settings in one big table.
>
> --
>  Richard Huxton
>  Archonet Ltd
>

Re: [GENERAL] Questions on PostGreSQL Authentication mechanism...

[dipti shah]

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
location "Techdb" command prompt itself.

Techdb=# cat  /etc/postgresql/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 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.com> wrote:
>
>> dipti shah escribió:
>> > Thanks Richard. those chapters are very useful. I got to know most of
>> > concepts but didn't find the location of pg_hba.conf file so that I can
>> > verify it. I have connected to my database using "postgres" user. Could
>> you
>> > tell me how to open pg_hba.conf file?
>>
>> Run this:
>>SHOW hba_file;
>>
>> --
>> Alvaro Herrera
>> http://www.CommandPrompt.com/ <http://www.commandprompt.com/>
>> The PostgreSQL Company - Command Prompt, Inc.
>>
>
>

Re: [GENERAL] Questions on PostGreSQL Authentication mechanism...

[dipti shah]

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. I got to know most of
> > concepts but didn't find the location of pg_hba.conf file so that I can
> > verify it. I have connected to my database using "postgres" user. Could
> you
> > tell me how to open pg_hba.conf file?
>
> Run this:
>SHOW hba_file;
>
> --
> Alvaro Herrera
> http://www.CommandPrompt.com/ <http://www.commandprompt.com/>
> The PostgreSQL Company - Command Prompt, Inc.
>

Re: [GENERAL] Questions on PostGreSQL Authentication mechanism...

[dipti shah]

Thanks Richard. those chapters are very useful. I got to know most of
concepts but didn't find the location of pg_hba.conf file so that I can
verify it. I have connected to my database using "postgres" user. Could you
tell me how to open pg_hba.conf file?

Thanks.

On Mon, Feb 1, 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
>> them.
>> Could anyone please help me in below questions?
>>
>
> You'll want to read Chapter 19 of the manuals followed by Chapter 20.
> http://www.postgresql.org/docs/8.4/static/client-authentication.html
> http://www.postgresql.org/docs/8.4/static/user-manag.html
>
>
> >1. When user connects to TechDB database(or any other) as a "postgres"
> > user, it should ask for password.
>
> I would guess your pg_hba.conf file is set to allow "trust" access. You
> will probably want "md5" passwords. You can also GRANT access to databases
> using the permissions system.
>
>
> >2. Remove all above users(listed with \du) and create someof users and
> > they will have only table creating privileges.
>
>
> You can DROP USER (or DROP ROLE) to remove users, but you'll want to
> reallocate any tables they own. You can GRANT and REVOKE various
> permissions, but I don't think you can just create tables without being able
> to access them afterwards. You could write a function that does that for you
> though.
>
> HTH
>
> --
>  Richard Huxton
>  Archonet Ltd
>

[GENERAL] Questions on PostGreSQL Authentication mechanism...

[dipti shah]

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
---+---+-+---+-+-
 sonal  | no| no  | no| no limit|
{from_ldap}
 sundar | no| no  | no| no limit|
{from_ldap}
...
..

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?

  1. When user connects to TechDB database(or any other) as a "postgres"
user, it should ask for password.
  2. Remove all above users(listed with \du) and create someof users and
they will have only table creating privileges.

Thanks.

Re: [GENERAL] Defining permissions for tables, schema etc..

[dipti shah]

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 back if I have any questions.
>
> agreed!  you should add this commentary to the interactive document
> page mentioned above thread.  the manual sure could use with more of
> this type of explanation of how to put things together.
>
> --
> 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] Defining permissions for tables, schema etc..

[dipti shah]

What a awesome response!!! Thanks a ton all of you.

Special Thanks to Craig for absolutely brillient reply. I will test all you
said and will get back if I have any questions.

Thanks,
Dipti

On Thu, Dec 10, 2009 at 2:07 PM, Craig Ringer
wrote:

> On 10/12/2009 4:21 PM, 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 to create the separate user
>>> account for every users and want to define the permission for each of
>>> them. For example, we want particular user cannot create schema, he
>>> can create tables only in particular schema, he can updates only few
>>> tables and also updates only few columns etc. In short, we want to
>>> define all available permission options. I am not aware of anything
>>> starting from creating new user account to assigning column level
>>> permissions. Could anyone please help me to start with this. What is
>>> the best way to start?
>>>
>>>
>>
>> there are no per column privileges in postgres
>>
>
> ... pre 8.4 :-)
>
> GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column [, ...] )
>[,...] | ALL [ PRIVILEGES ] ( column [, ...] ) }
>ON [ TABLE ] tablename [, ...]
>TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
>
>
> GRANT SELECT ( fieldname ) ON sometable TO someuser;
>
> ... and use \dp tablename to show.
>
> It's made me really rather happy as I've been able to drop several
> cumbersome triggers in favour of simple column-list grants.
>
>
>
> Oh, re my earlier post:
>
> In my example I messed up the last line. You'd want adminUser to INHERIT
> too, otherwise explicit SET ROLE commands would be needed to do anything
> useful with it. Sorry about that.
>
> I also managed to make it sound like roles could specify themselves as
> non-inheritable. It's the role _member_ that controls whether or not privs
> are inherited, though sometimes an intermediate member may block inheritance
> (via NOINHERIT of roles it's a member of) for a role that is its self
> INHERIT. In practice, you'll probably want to use INHERIT almost all the
> time and won't be too worried by this.
>
> --
> Craig Ringer
>

[GENERAL] Defining permissions for tables, schema etc..

[dipti shah]

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 users and want to define the permission for each of them. For example,
we want particular user cannot create schema, he can create tables only in
particular schema, he can updates only few tables and also updates only few
columns etc. In short, we want to define all available permission options. I
am not aware of anything starting from creating new user account to
assigning column level permissions. Could anyone please help me to start
with this. What is the best way to start?

Thanks,
Dipti