Re: PostGres Doubt

Are you using crypt on the connection?

Unfortunately, crypt is not reentrant.

> -----Original Message-----
> From: David Ford [mailto:david+cert(at)blue-labs(dot)org]
> Sent: Monday, June 10, 2002 6:16 PM
> To: Dann Corbit
> Cc: vikas p verma; pgsql-hackers(at)postgresql(dot)org
> Subject: Re: [HACKERS] PostGres Doubt
>
>
> Is libpq/PQconnectdb() reentrant? I've tried repeatedly over
> time and
> it seems to incur segfaults every single time.
>
> -d
>
> Dann Corbit wrote:
>
> >The libpq functions are reentrant. These will be useful for
> just about
> >any project.
> >
> >
>
>

I.e. "md5" in pg_hba.conf? This is rather disappointing.

Here are a few references:

http://lists.initd.org/pipermail/psycopg/2002-January/000673.html
http://lists.initd.org/pipermail/psycopg/2002-January/000674.html
http://archives.postgresql.org/pgsql-bugs/2002-03/msg00048.php

And finally..
http://archives.postgresql.org/pgsql-bugs/2002-01/msg00153.php

So reentrancy in libpq basically is put on hold until 7.3.

David

Dann Corbit wrote:

>Are you using crypt on the connection?
>
>Unfortunately, crypt is not reentrant.
>
>
>
>>-----Original Message-----
>>From: David Ford [mailto:david+cert(at)blue-labs(dot)org]
>>Sent: Monday, June 10, 2002 6:16 PM
>>To: Dann Corbit
>>Cc: vikas p verma; pgsql-hackers(at)postgresql(dot)org
>>Subject: Re: [HACKERS] PostGres Doubt
>>
>>
>>Is libpq/PQconnectdb() reentrant? I've tried repeatedly over
>>time and
>>it seems to incur segfaults every single time.
>>
>>-d
>>
>>Dann Corbit wrote:
>>
>>
>>
>>>The libpq functions are reentrant. These will be useful for
>>>
>>>
>>just about
>>
>>
>>>any project.
>>>
>>>
>>>
>>>
>>
>>

David Ford <david+cert(at)blue-labs(dot)org> writes:
> So reentrancy in libpq basically is put on hold until 7.3.

Only if you insist on using "crypt", which is deprecated anyway.
md5 is the preferred encryption method.

My feeling about the proposed patch was that crypt is now a legacy auth
method, and it's not clear that we should create platform/library
dependencies just to support making multiple connections simultaneously
under crypt auth. (Note that *using* connections concurrently is not
at issue, only whether you can execute the authentication phase of
startup concurrently.)

regards, tom lane

On Wed, 2002-06-12 at 19:38, Tom Lane wrote:
> David Ford <david+cert(at)blue-labs(dot)org> writes:
> > So reentrancy in libpq basically is put on hold until 7.3.
>
> Only if you insist on using "crypt", which is deprecated anyway.
> md5 is the preferred encryption method.
>
> My feeling about the proposed patch was that crypt is now a legacy auth
> method, and it's not clear that we should create platform/library
> dependencies just to support making multiple connections simultaneously
> under crypt auth. (Note that *using* connections concurrently is not
> at issue, only whether you can execute the authentication phase of
> startup concurrently.)

can't this be solved by simple locking ?

I know that postgres team can do locking properly ;)

--------------
Hannu

I'm using md5 in pg_hba.conf. That is the method, no?

I'm writing a milter application which instantiates a private resource
for each thread upon thread startup. I have priv->conn which I
establish as priv->conn=PQconnectdb(connstr), connstr is const char
*connstr="host=10.0.0.5 dbname=bmilter user=username password=password";

It segfaults depending on it's mood but it tends to happen about 50-70%
of the time. I switched to PQsetdbLogin() which has worked perfectly.
I don't really want to use that however, I would much prefer using my
connstr.

Am I missing something?

Thanks,
David

Tom Lane wrote:

>David Ford <david+cert(at)blue-labs(dot)org> writes:
>
>
>>So reentrancy in libpq basically is put on hold until 7.3.
>>
>>
>
>Only if you insist on using "crypt", which is deprecated anyway.
>md5 is the preferred encryption method.
>
>My feeling about the proposed patch was that crypt is now a legacy auth
>method, and it's not clear that we should create platform/library
>dependencies just to support making multiple connections simultaneously
>under crypt auth. (Note that *using* connections concurrently is not
>at issue, only whether you can execute the authentication phase of
>startup concurrently.)
>
> regards, tom lane
>
>

David Ford wrote:
> I'm using md5 in pg_hba.conf. That is the method, no?
>
> I'm writing a milter application which instantiates a private resource
> for each thread upon thread startup. I have priv->conn which I
> establish as priv->conn=PQconnectdb(connstr), connstr is const char
> *connstr="host=10.0.0.5 dbname=bmilter user=username password=password";
>
> It segfaults depending on it's mood but it tends to happen about 50-70%
> of the time. I switched to PQsetdbLogin() which has worked perfectly.
> I don't really want to use that however, I would much prefer using my
> connstr.

Wow, I am confused. md5 should be fine. Certainly sounds like there is
a thread problem with PQconnectdb(). Are you using 7.2.X?

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

David Ford <david+cert(at)blue-labs(dot)org> writes:
> I'm using md5 in pg_hba.conf. That is the method, no?
> I'm writing a milter application which instantiates a private resource
> for each thread upon thread startup. I have priv->conn which I
> establish as priv->conn=PQconnectdb(connstr), connstr is const char
> *connstr="host=10.0.0.5 dbname=bmilter user=username password=password";

> It segfaults depending on it's mood but it tends to happen about 50-70%
> of the time.

Could you dig out ye olde gdb and figure out *why* it's segfaulting?
At the very least, give us a stack backtrace from a debug-enabled build.

regards, tom lane

pg_auth=# select version();
version
------------------------------------------------------------
PostgreSQL 7.2 on i686-pc-linux-gnu, compiled by GCC 3.0.2

Which btw has a curious grant/revoke bug. create foo; grant select on
foo to bar; results in all rights being granted. You must revoke and
grant again in order to get the correct rights set.

If this rights bug has been fixed, I'll upgrade, but I don't consider it
a big problem since I am well aware of the bug.

David

Bruce Momjian wrote:

>David Ford wrote:
>
>
>>I'm using md5 in pg_hba.conf. That is the method, no?
>>
>>I'm writing a milter application which instantiates a private resource
>>for each thread upon thread startup. I have priv->conn which I
>>establish as priv->conn=PQconnectdb(connstr), connstr is const char
>>*connstr="host=10.0.0.5 dbname=bmilter user=username password=password";
>>
>>It segfaults depending on it's mood but it tends to happen about 50-70%
>>of the time. I switched to PQsetdbLogin() which has worked perfectly.
>> I don't really want to use that however, I would much prefer using my
>>connstr.
>>
>>
>
>Wow, I am confused. md5 should be fine. Certainly sounds like there is
>a thread problem with PQconnectdb(). Are you using 7.2.X?
>
>
>

David Ford <david+cert(at)blue-labs(dot)org> writes:
> Which btw has a curious grant/revoke bug. create foo; grant select on
> foo to bar; results in all rights being granted. You must revoke and
> grant again in order to get the correct rights set.

I see no bug.

test72=# select version();
version
---------------------------------------------------------------
PostgreSQL 7.2.1 on hppa-hp-hpux10.20, compiled by GCC 2.95.3
(1 row)

test72=# create user bar;
CREATE USER
test72=# create table foo (f1 int);
CREATE
test72=# grant select on foo to bar;
GRANT
test72=# \z foo
Access privileges for database "test72"
Table | Access privileges
-------+----------------------------
foo | {=,postgres=arwdRxt,bar=r}
(1 row)

test72=#

regards, tom lane

My apologies, I was too brief in my example:

heakin=> create table interviewers ( interviewer varchar );
CREATE
heakin=> insert into interviewers values ('Ryan');
INSERT 932846 1
heakin=> select * from interviewers ;
interviewer
-------------
Ryan
(1 row)

heakin=> \z
Access privileges for database "heakin"
Table | Access privileges
-------------------+-------------------
clients | {=,heakin=arwd}
completed_surveys | {=,heakin=arwd}
interviewers |
respondents | {=,heakin=arwd}
users | {=,heakin=ar}
(5 rows)

heakin=> grant select,insert,update on interviewers to heakin;
GRANT
heakin=> \z
Access privileges for database "heakin"
Table | Access privileges
-------------------+--------------------
clients | {=,heakin=arwd}
completed_surveys | {=,heakin=arwd}
interviewers | {=,heakin=arwdRxt}
respondents | {=,heakin=arwd}
users | {=,heakin=ar}
(5 rows)

heakin=> revoke all on interviewers from heakin;
REVOKE
heakin=> \z
Access privileges for database "heakin"
Table | Access privileges
-------------------+-------------------
clients | {=,heakin=arwd}
completed_surveys | {=,heakin=arwd}
interviewers | {=}
respondents | {=,heakin=arwd}
users | {=,heakin=ar}
(5 rows)

heakin=> grant select,insert,update on interviewers to heakin;
GRANT
heakin=> \z
Access privileges for database "heakin"
Table | Access privileges
-------------------+-------------------
clients | {=,heakin=arwd}
completed_surveys | {=,heakin=arwd}
interviewers | {=,heakin=arw}
respondents | {=,heakin=arwd}
users | {=,heakin=ar}
(5 rows)

David

Tom Lane wrote:

>David Ford <david+cert(at)blue-labs(dot)org> writes:
>
>
>>Which btw has a curious grant/revoke bug. create foo; grant select on
>>foo to bar; results in all rights being granted. You must revoke and
>>grant again in order to get the correct rights set.
>>
>>
>
>I see no bug.
>
>test72=# select version();
> version
>---------------------------------------------------------------
> PostgreSQL 7.2.1 on hppa-hp-hpux10.20, compiled by GCC 2.95.3
>(1 row)
>
>test72=# create user bar;
>CREATE USER
>test72=# create table foo (f1 int);
>CREATE
>test72=# grant select on foo to bar;
>GRANT
>test72=# \z foo
>Access privileges for database "test72"
> Table | Access privileges
>-------+----------------------------
> foo | {=,postgres=arwdRxt,bar=r}
>(1 row)
>
>test72=#
>
> regards, tom lane
>
>

My apologies, I was too brief in my example:

heakin=> create table interviewers ( interviewer varchar );
CREATE
heakin=> insert into interviewers values ('Ryan');
INSERT 932846 1
heakin=> select * from interviewers ;
interviewer
-------------
Ryan
(1 row)

heakin=> \z
Access privileges for database "heakin"
Table | Access privileges
-------------------+-------------------
clients | {=,heakin=arwd}
completed_surveys | {=,heakin=arwd}
interviewers |
respondents | {=,heakin=arwd}
users | {=,heakin=ar}
(5 rows)

heakin=> grant select,insert,update on interviewers to heakin;
GRANT
heakin=> \z
Access privileges for database "heakin"
Table | Access privileges
-------------------+--------------------
clients | {=,heakin=arwd}
completed_surveys | {=,heakin=arwd}
interviewers | {=,heakin=arwdRxt}
respondents | {=,heakin=arwd}
users | {=,heakin=ar}
(5 rows)

heakin=> revoke all on interviewers from heakin;
REVOKE
heakin=> \z
Access privileges for database "heakin"
Table | Access privileges
-------------------+-------------------
clients | {=,heakin=arwd}
completed_surveys | {=,heakin=arwd}
interviewers | {=}
respondents | {=,heakin=arwd}
users | {=,heakin=ar}
(5 rows)

heakin=> grant select,insert,update on interviewers to heakin;
GRANT
heakin=> \z
Access privileges for database "heakin"
Table | Access privileges
-------------------+-------------------
clients | {=,heakin=arwd}
completed_surveys | {=,heakin=arwd}
interviewers | {=,heakin=arw}
respondents | {=,heakin=arwd}
users | {=,heakin=ar}
(5 rows)

David

Tom Lane wrote:

>David Ford <david+cert(at)blue-labs(dot)org> writes:
>
>
>>Which btw has a curious grant/revoke bug. create foo; grant select on
>>foo to bar; results in all rights being granted. You must revoke and
>>grant again in order to get the correct rights set.
>>
>>
>
>I see no bug.
>
>test72=# select version();
> version
>---------------------------------------------------------------
> PostgreSQL 7.2.1 on hppa-hp-hpux10.20, compiled by GCC 2.95.3
>(1 row)
>
>test72=# create user bar;
>CREATE USER
>test72=# create table foo (f1 int);
>CREATE
>test72=# grant select on foo to bar;
>GRANT
>test72=# \z foo
>Access privileges for database "test72"
> Table | Access privileges
>-------+----------------------------
> foo | {=,postgres=arwdRxt,bar=r}
>(1 row)
>
>test72=#
>
>
regards, tom lane
>
>

David Ford <david(at)blue-labs(dot)org> writes:
> heakin=> \z
> Access privileges for database "heakin"
> Table | Access privileges
> -------------------+-------------------
> interviewers |

> heakin=> grant select,insert,update on interviewers to heakin;
> GRANT
> heakin=> \z
> Access privileges for database "heakin"
> Table | Access privileges
> -------------------+--------------------
> interviewers | {=,heakin=arwdRxt}

I take it heakin is the owner of the table in question. As such,
he implicitly has all privileges --- the initial null privilege list
is a shorthand for what you see explicitly in the second case.

The GRANT man page in current development sources has an example about
this; see the Notes section of
http://developer.postgresql.org/docs/postgres/sql-grant.html

regards, tom lane

Gotcha. 'twas the first time I encountered it, I wasn't expecting it.

Thank you for the clarification. I hadn't paid attention to that
paragraph when I read over it.

David

Tom Lane wrote:

>David Ford <david(at)blue-labs(dot)org> writes:
>
>
>>heakin=> \z
>>Access privileges for database "heakin"
>> Table | Access privileges
>>-------------------+-------------------
>> interviewers |
>>
>>
>
>
>
>>heakin=> grant select,insert,update on interviewers to heakin;
>>GRANT
>>heakin=> \z
>>Access privileges for database "heakin"
>> Table | Access privileges
>>-------------------+--------------------
>> interviewers | {=,heakin=arwdRxt}
>>
>>
>
>I take it heakin is the owner of the table in question. As such,
>he implicitly has all privileges --- the initial null privilege list
>is a shorthand for what you see explicitly in the second case.
>
>The GRANT man page in current development sources has an example about
>this; see the Notes section of
>http://developer.postgresql.org/docs/postgres/sql-grant.html
>
> regards, tom lane
>
>