1 |
dpavlin |
1.1 |
After you created a poll, you will also probably want to access it's data |
2 |
|
|
which is stored in PostgreSQL. If you are doing that from Windows machine, |
3 |
|
|
easiest way is to install PostgreSQL ODBC driver and than create datasource |
4 |
|
|
on your Windows box that connects directly to database. That datasource |
5 |
|
|
can be used from any ODBC-enabled application like M$ Access, M$ Excel, |
6 |
|
|
OpenOffice etc, etc... |
7 |
|
|
|
8 |
|
|
Download PostgreSQL ODBC driver from |
9 |
|
|
|
10 |
|
|
http://odbc.postgresql.org |
11 |
|
|
|
12 |
|
|
and install it. Version 7_03_0200 is used here. |
13 |
|
|
|
14 |
|
|
Bare in mind that PostgreSQL from time to time change protocol specification, |
15 |
|
|
so you will have to download driver for your particular PostgreSQL version. |
16 |
|
|
|
17 |
|
|
Create ODBC source: |
18 |
|
|
|
19 |
|
|
Select ODBC Data Sources in Control Panel and then System DSN. This will |
20 |
|
|
create source which will be available to all users and applications on this |
21 |
|
|
computer. |
22 |
|
|
|
23 |
|
|
Click Add and under "Create New Data Source" select "PostgreSQL" (don't use |
24 |
|
|
Legacy or beta which might also be installed). Click on Finish and you will |
25 |
|
|
get options for this source: |
26 |
|
|
|
27 |
|
|
- Data Source - descriptive name used by Windows side of ODBC connection |
28 |
|
|
- Description - oh, well... description! |
29 |
|
|
- Database - name of PostgreSQL database, usually, wopi_[poll name] |
30 |
|
|
- Server - hostname or IP address of PostgreSQL server |
31 |
|
|
- Port - 5432 is default |
32 |
|
|
- User Name - user which has to be created on PostgreSQL and have permissions |
33 |
|
|
to access this database from IP address of Windows machine (or via some |
34 |
|
|
other authorisation method) |
35 |
|
|
- Password - password for that user |
36 |
|
|
|
37 |
|
|
Click on Save and proceed to create user. |
38 |
|
|
|
39 |
|
|
For example if your /etc/postgresql/pg_hba.conf has line like: |
40 |
|
|
|
41 |
|
|
host all all 0.0.0.0 0.0.0.0 md5 |
42 |
|
|
|
43 |
|
|
you can use following SQL to add user with permissions to wopi_[poll name] |
44 |
|
|
databse: |
45 |
|
|
|
46 |
|
|
$ psql template1 |
47 |
|
|
template1=# create user _user_name_ with encrypted password '_password_' nocreatedb nocreateuser ; |
48 |
|
|
CREATE USER |
49 |
|
|
|
50 |
|
|
Where _user_name_ and _password_ are user name and password that you specified |
51 |
|
|
in ODBC Setup. You can test connection using: |
52 |
|
|
|
53 |
|
|
$ psql -U _user_name_ -W _database_ |
54 |
|
|
|
55 |
|
|
Now, add permissions to select data to your newly created user: |
56 |
|
|
|
57 |
|
|
_database_=# grant select on _poll_name_ to _user_name_ ; |
58 |
|
|
GRANT |
59 |
|
|
|
60 |
|
|
|
61 |
|
|
Now it's time to define ODBC source in M$ Excel (yak!). Use: Data -> Get |
62 |
|
|
External Data -> New Database Query. There, on tab "Databases" select name of |
63 |
|
|
your ODBC source. |
64 |
|
|
|
65 |
|
|
To be written... |