Обсуждение: 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? Many thanks in advance and sorry for my English Miguel
> -----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.
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.
> 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
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
> -----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.
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