Thread: Creating users

Creating users

From
Miguel Gonzalez
Date:
dear all,

 I am developing a client application with C++ Builder 3 to access a
Database designed with PostgreSQL 7.0.4 using a BDE alias through the ODBC
driver.

 I would like to have a form where I can add users to my users table of my
database and also create the corresponding entry to the postgres pg_user
table.

 What I have tried through the Database Explorer is the following
transaction (I use begin and commit, otherwise the parser complains):

 BEGIN;
 CREATE USER user1 WITH PASSWORD 'passwd_user1' IN GROUP users_invui;
 COMMIT;

 I check in the pg_user table but no entry is added. I execute without the
begin and the commit comands the same query in the backend (psql) and it
works perfectly. What am I doing wrong?

 Many thanks in advance and sorry for my English

 Miguel







RE: Creating users

From
Dave Page
Date:

> -----Original Message-----
> From: Miguel Gonzalez [mailto:jffernandez@usuarios.retecal.es]
> Sent: 11 August 2001 17:10
> To: pgsql-odbc@postgresql.org
> Subject: [ODBC] Creating users
>
>
> dear all,
>
>  I am developing a client application with C++ Builder 3 to
> access a Database designed with PostgreSQL 7.0.4 using a BDE
> alias through the ODBC driver.
>
>  I would like to have a form where I can add users to my
> users table of my database and also create the corresponding
> entry to the postgres pg_user table.
>
>  What I have tried through the Database Explorer is the
> following transaction (I use begin and commit, otherwise the
> parser complains):
>
>  BEGIN;
>  CREATE USER user1 WITH PASSWORD 'passwd_user1' IN GROUP
> users_invui;  COMMIT;
>
>  I check in the pg_user table but no entry is added. I
> execute without the begin and the commit comands the same
> query in the backend (psql) and it works perfectly. What am I
> doing wrong?

It used to be the case that you couldn't execute certain queries within a
transaction - specifically CREATE/ALTER/DROP USER/DATA. I haven't noticed
that it's changed, but the ODBC driver also wraps queries in a transaction
if they're not already so you can't just drop the BEGIN; COMMIT;

In pgAdmin, the workaround we use is to place a single space before the
CREATE/ALTER/DROP eg.

CREATE USER dpage

becomes

 CREATE USER dpage

This works currently but may not in the future releases of PostgreSQL if the
behaviour is changed.

>  Many thanks in advance and sorry for my English

Why? It sounds better than mine and I've lived in .uk all my life!

Regards, Dave.

RE: Creating users

From
Miguel Gonzalez
Date:
Dave,

 I have used the facility that the C++ Builder suite provides, called
Database Explorer. As the name shows, you can explore
databases, connecting through the BDE alias (through the ODBC driver) and
execute queries.

 I have tried using this tool, and I can add a user. What I do not
understand is why I cannot inside my C++ Builder application. I assume that
if It is possible through the ODBC driver, It can be also in my application.

 By the way, where I can get the pgAdmin tool? I have read that it is the
best tool for administrating PostgreSQL in Windows.

 Also I would like to ask something OFF-TOPIC. I have searched on the web,
read docs and ask in other mailing list but nobody tells me how the password
politics in PostgreSQL works. I would like to have some kind on encryption
accessing the database, I have tried to set in the pg_hba.conf an entry for
the host where my C++ application is, but when I use the crypt option rather
than password, I cannot connect through the ODBC driver.

 Many thanks in advance

 Miguel

pd: Dave - thanks for the compliment about my English.







> It used to be the case that you couldn't execute certain queries within a
> transaction - specifically CREATE/ALTER/DROP USER/DATA. I haven't noticed
> that it's changed, but the ODBC driver also wraps queries in a transaction
> if they're not already so you can't just drop the BEGIN; COMMIT;
>
> In pgAdmin, the workaround we use is to place a single space before the
> CREATE/ALTER/DROP eg.
>
> CREATE USER dpage
>
> becomes
>
>  CREATE USER dpage
>
> This works currently but may not in the future releases of PostgreSQL if
the
> behaviour is changed.
>
> >  Many thanks in advance and sorry for my English
>
> Why? It sounds better than mine and I've lived in .uk all my life!
>
> Regards, Dave.


RE: Creating users

From
Cedar Cox
Date:
>  By the way, where I can get the pgAdmin tool? I have read that it is the
> best tool for administrating PostgreSQL in Windows.

hmm.. Dave?  Where is it?  Google says www.pgadmin.freeserve.co.uk (which
doesn't work).  I couldn't find it on the PG website.

>  Also I would like to ask something OFF-TOPIC. I have searched on the web,
> read docs and ask in other mailing list but nobody tells me how the password
> politics in PostgreSQL works. I would like to have some kind on encryption
> accessing the database, I have tried to set in the pg_hba.conf an entry for
> the host where my C++ application is, but when I use the crypt option rather
> than password, I cannot connect through the ODBC driver.

The pg_hba.conf crypt option is only for encrypting during authentication
(ie. login).  The windows ODBC driver doesn't support crypt (IMHO,
pointless anyway as everything after that is not encrypted.  There have
been discussions about this in the past).  How passwords are stored inside
the database is an entirely different issue.  I think someone wrote a
patch to do this or something similar.  IIRC it was a 'crypt' (character)
data type, or maybe just some functions.  In any case, you must be a
database superuser to read the passwords.

-Cedar




Re: Creating users

From
Jochem van Dieten
Date:
Cedar Cox wrote:

>> By the way, where I can get the pgAdmin tool? I have read that it is the
>>best tool for administrating PostgreSQL in Windows.
>>
>
> hmm.. Dave?  Where is it?  Google says www.pgadmin.freeserve.co.uk (which
> doesn't work).  I couldn't find it on the PG website.


http://www.greatbridge.org/

Jochem


RE: Creating users

From
Dave Page
Date:

> -----Original Message-----
> From: Jochem van Dieten [mailto:jochemd@oli.tudelft.nl]
> Sent: 12 August 2001 22:11
> To: pgsql-odbc@postgresql.org
> Subject: Re: [ODBC] Creating users
>
>
> Cedar Cox wrote:
>
> >> By the way, where I can get the pgAdmin tool? I have read
> that it is
> >>the best tool for administrating PostgreSQL in Windows.
> >>
> >
> > hmm.. Dave?  Where is it?  Google says www.pgadmin.freeserve.co.uk
> > (which doesn't work).  I couldn't find it on the PG website.
>
>
> http://www.greatbridge.org/
>
> Jochem

Yes, this is the original version. For the brave, I have just released a
preview of pgAdmin II which is *completely* new/redesigned code. This is at
http://pgadmin.postgresql.org. It is a preview release though so not
guarantees about it's quality (though I think it's pretty good).

Regards, Dave.

pgAdmin

From
Jean-Michel POURE
Date:
At 22:23 12/08/01 +0300, you wrote:
> >  By the way, where I can get the pgAdmin tool? I have read that it is the
> > best tool for administrating PostgreSQL in Windows.
>
>hmm.. Dave?  Where is it?  Google says www.pgadmin.freeserve.co.uk (which
>doesn't work).  I couldn't find it on the PG website.

You can get pgAdmin stable branch from
http://www.greatbridge.org/project/pgadmin/download/download.php.

The latest CVS version provides advanced rebuilding features. A short howto
which explains how to install the CVS snapshot
http://www.greatbridge.org/project/pgadmin/viewcvs/~checkout~/binaries/readm
e.html?rev=1.4&content-type=text/html

Hope this helps,

Best regards,
Jean-Michel POURE,
pgAdmin development team