keum/postgres windows installation

## postgres windows installation
Installing postgres in windows box -

Installed on to this windows box:  itgisqldev01.dnrp.kingcounty.lcl

1. This is to connect PostGIS connection from QGIS 2.+

Name: ITGISQLDEV01
Host: itgisqldev01.kingcounty.lcl
Port: 5432
Database: kcgis
SSL mode: disable
Username: (your own)
Password: admin

then hit Test Connect


2. Connecting using ArcCatalog 10.1
http://resources.arcgis.com/en/help/main/10.1/index.html#//002p0000003q000000


3. http://dev.horizon.opengeo.org/opengeo-docs/dataadmin/pgGettingStarted/shp2pgsql.html


=============Debbie's NOTE================

Thanks Paul. As you mention that the server is 64bit PostgreSQL, I think it’s worth emphasizing here that the client stuff should be 32 bit for use with ArcGIS Desktop:
In the steps on http://resources.arcgis.com/en/help/main/10.1/index.html#//002p0000003q000000
Be sure to download the correct libraries for your ArcGIS client. For ArcGIS for Desktop and ArcGIS Engine, you need the 32-bit libraries. For ArcGIS for Server, you need the 64-bit client libraries.

If we write stuff up nicely. We can separate client from server notes.

Thanks again.
Debbie
From: McCombs, Paul
Sent: Tuesday, March 25, 2014 4:03 PM
To: Bull, Debbie; O'Neil, Shaun; Keum, Peter; Ostanski, David
Subject: RE: PostgreSQL Client for Windows

Here are the notes that I took. I tried to weed out the redundant. Also I’ll answer a couple of your questions below in red.


=========== Paul’s Notes =======================
64bit
Windows Server 2012 R2
Had to install  PostGreSQL
Postgres user has password admin

How to create a user
https://chartio.com/docs/datasources/connections/details/pgadmin
A login role (User Role) = user
Nonlogin role (Group Role)  = role as we know it from MSSQL Server


The following statements are needed to grant access to groups, even though the table is in the “PUBLIC” schema. For some reason [Debbie] was surprised by that. On data set creation, it is accessible only by its creator.

GRANT ALL ON TABLE public.city_3co TO postgres;
GRANT SELECT ON TABLE public.city_3co TO gisuser;


Enable postgresql server to allow remote connections:
http://www.postgresql.org/docs/9.3/static/auth-pg-hba-conf.html
http://www.cyberciti.biz/tips/postgres-allow-remote-access-tcp-connection.html


To access PostGIS stuff from ArcMap:
http://resources.arcgis.com/en/help/main/10.1/index.html#/Setting_up_a_connection_to_PostgreSQL/002p0000003q000000/
Debbie has downloaded the files and confirmed that they can be used to access PostGIS data without doing any SDE stuff.
A regular person cannot download the files.
To connect to PostGreSQL on ITGISQLDEV01.dnrp.kingcounty.lcl (note, “Instance” server name needs to be fully qualified for users outside of DNRP domain):


To fix a bad SRID (spatial reference) on  a postGIS layer
http://gis.stackexchange.com/questions/34612/how-to-change-the-srid-of-exisisting-data-in-postgis


From: Bull, Debbie
Sent: Tuesday, March 25, 2014 3:51 PM
To: McCombs, Paul; O'Neil, Shaun; Keum, Peter; Ostanski, David
Subject: PostgreSQL Client for Windows

If we’re really good, we’ll start a SharePoint site for this or something. Keeping helpful and up-to-date information like this stuff is always a challenge.
Install PostgreSQL Client to work with ArcGIS 10.1 Desktop
I put the executable to install the PostgreSQL Client for Windows on gisdw\kclib:
\\gisdw\kclib\Resources\Executables\PostgreSQL Client for Windows

Here are steps to install client libraries
http://resources.arcgis.com/en/help/main/10.1/index.html#//002p0000003q000000

Please help me write up what we did
I’d appreciate your help in building a knowledge base about this stuff. When my fingers are on a keyboard, I never capture good enough notes.

PostgreSQL’s default data directory for PostgreSQL’s metadata is c:\Profiles\PostGresql\<version number>\share. Because I was confused, I thought that this default data directory would be where postgresql would place user database data files, I selected the designated data location on ITGISQLDEV01: E:\DATA\postgresql. So while posgresql made the adjustment, the documentation points to the default location for key configuration files so it took us a while to figure all that out. .  What are your thoughts on this, is it worth uninstalling and re-installing, or attempting to relocate the default data folder back to its “factory-default” location?

I don’t think so. Paul


pg_hba.conf = PostgreSQL Client Authentication Configuration File
Here, we set/modified the following security configurations:
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# KCWAN
host    all             all             146.129.0.0/16            password

# VPN
host    all             all             10.177.0.0/16 password

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#host    replication     postgres        127.0.0.1/32            md5
#host    replication     postgres        ::1/128                 md5

postgresql.conf = PostgreSQL configuration file
Honestly, I know we changed the postgresql.conf file, but I can’t recall whether it was before or after we figured out the default data location problem.  Here’s the section I think we changed so that it listens on all (‘*’) addresses. I think that it was already set to listen to ‘*’ and when we tried to change it is when we realized we were not working with the actual configuration files and found that this was already set to listen to ‘*’. Paul
# - Connection Settings -

listen_addresses = '*'            # what IP address(es) to listen on;
                                  # comma-separated list of addresses;
                                  # defaults to 'localhost'; use '*' for all
                                  # (change requires restart)
port = 5432                       # (change requires restart)
max_connections = 100                    # (change requires restart)

There are lines for Security and Authentication and we read a little, but didn’t change anything yet: authentication_timeout, ssl_*,  tcp_keepalives_*, logging(* omg, there are lots of optional parameters for logging),


I think it would be good to broaden the audience to anyone on the ZZGrp, GIS Developers group who might be interested, but I also want to minimize exposure until we get security figured out a little bit better, and tightened down a bit.  Paul informed/reminded me today that we can use operating system authentication, in theory, but we’re not yet set up that way. I think that’s a good milestone/threshold to use as a trigger for broadening exposure to the GIS Developers Group.

Thoughts?


==========================
	Installing postgres in windows box -

	Installed on to this windows box: itgisqldev01.dnrp.kingcounty.lcl

	1. This is to connect PostGIS connection from QGIS 2.+

	Name: ITGISQLDEV01
	Host: itgisqldev01.kingcounty.lcl
	Port: 5432
	Database: kcgis
	SSL mode: disable
	Username: (your own)
	Password: admin

	then hit Test Connect


	2. Connecting using ArcCatalog 10.1
	http://resources.arcgis.com/en/help/main/10.1/index.html#//002p0000003q000000


	3. http://dev.horizon.opengeo.org/opengeo-docs/dataadmin/pgGettingStarted/shp2pgsql.html



	=============Debbie's NOTE================

	Thanks Paul. As you mention that the server is 64bit PostgreSQL, I think it’s worth emphasizing here that the client stuff should be 32 bit for use with ArcGIS Desktop:
	In the steps on http://resources.arcgis.com/en/help/main/10.1/index.html#//002p0000003q000000
	Be sure to download the correct libraries for your ArcGIS client. For ArcGIS for Desktop and ArcGIS Engine, you need the 32-bit libraries. For ArcGIS for Server, you need the 64-bit client libraries.

	If we write stuff up nicely. We can separate client from server notes.

	Thanks again.
	Debbie
	From: McCombs, Paul
	Sent: Tuesday, March 25, 2014 4:03 PM
	To: Bull, Debbie; O'Neil, Shaun; Keum, Peter; Ostanski, David
	Subject: RE: PostgreSQL Client for Windows

	Here are the notes that I took. I tried to weed out the redundant. Also I’ll answer a couple of your questions below in red.


	=========== Paul’s Notes =======================
	64bit
	Windows Server 2012 R2
	Had to install PostGreSQL
	Postgres user has password admin

	How to create a user
	https://chartio.com/docs/datasources/connections/details/pgadmin
	A login role (User Role) = user
	Nonlogin role (Group Role) = role as we know it from MSSQL Server


	The following statements are needed to grant access to groups, even though the table is in the “PUBLIC” schema. For some reason [Debbie] was surprised by that. On data set creation, it is accessible only by its creator.

	GRANT ALL ON TABLE public.city_3co TO postgres;
	GRANT SELECT ON TABLE public.city_3co TO gisuser;


	Enable postgresql server to allow remote connections:
	http://www.postgresql.org/docs/9.3/static/auth-pg-hba-conf.html
	http://www.cyberciti.biz/tips/postgres-allow-remote-access-tcp-connection.html


	To access PostGIS stuff from ArcMap:
	http://resources.arcgis.com/en/help/main/10.1/index.html#/Setting_up_a_connection_to_PostgreSQL/002p0000003q000000/
	Debbie has downloaded the files and confirmed that they can be used to access PostGIS data without doing any SDE stuff.
	A regular person cannot download the files.
	To connect to PostGreSQL on ITGISQLDEV01.dnrp.kingcounty.lcl (note, “Instance” server name needs to be fully qualified for users outside of DNRP domain):



	To fix a bad SRID (spatial reference) on a postGIS layer
	http://gis.stackexchange.com/questions/34612/how-to-change-the-srid-of-exisisting-data-in-postgis


	From: Bull, Debbie
	Sent: Tuesday, March 25, 2014 3:51 PM
	To: McCombs, Paul; O'Neil, Shaun; Keum, Peter; Ostanski, David
	Subject: PostgreSQL Client for Windows

	If we’re really good, we’ll start a SharePoint site for this or something. Keeping helpful and up-to-date information like this stuff is always a challenge.
	Install PostgreSQL Client to work with ArcGIS 10.1 Desktop
	I put the executable to install the PostgreSQL Client for Windows on gisdw\kclib:
	\\gisdw\kclib\Resources\Executables\PostgreSQL Client for Windows

	Here are steps to install client libraries
	http://resources.arcgis.com/en/help/main/10.1/index.html#//002p0000003q000000

	Please help me write up what we did
	I’d appreciate your help in building a knowledge base about this stuff. When my fingers are on a keyboard, I never capture good enough notes.

	PostgreSQL’s default data directory for PostgreSQL’s metadata is c:\Profiles\PostGresql\<version number>\share. Because I was confused, I thought that this default data directory would be where postgresql would place user database data files, I selected the designated data location on ITGISQLDEV01: E:\DATA\postgresql. So while posgresql made the adjustment, the documentation points to the default location for key configuration files so it took us a while to figure all that out. . What are your thoughts on this, is it worth uninstalling and re-installing, or attempting to relocate the default data folder back to its “factory-default” location?

	I don’t think so. Paul


	pg_hba.conf = PostgreSQL Client Authentication Configuration File
	Here, we set/modified the following security configurations:
	# TYPE DATABASE USER ADDRESS METHOD

	# KCWAN
	host all all 146.129.0.0/16 password

	# VPN
	host all all 10.177.0.0/16 password

	# IPv4 local connections:
	host all all 127.0.0.1/32 md5
	# IPv6 local connections:
	host all all ::1/128 md5
	# Allow replication connections from localhost, by a user with the
	# replication privilege.
	#host replication postgres 127.0.0.1/32 md5
	#host replication postgres ::1/128 md5

	postgresql.conf = PostgreSQL configuration file
	Honestly, I know we changed the postgresql.conf file, but I can’t recall whether it was before or after we figured out the default data location problem. Here’s the section I think we changed so that it listens on all (‘’) addresses. I think that it was already set to listen to ‘’ and when we tried to change it is when we realized we were not working with the actual configuration files and found that this was already set to listen to ‘*’. Paul
	# - Connection Settings -

	listen_addresses = '*' # what IP address(es) to listen on;
	# comma-separated list of addresses;
	# defaults to 'localhost'; use '*' for all
	# (change requires restart)
	port = 5432 # (change requires restart)
	max_connections = 100 # (change requires restart)

	There are lines for Security and Authentication and we read a little, but didn’t change anything yet: authentication_timeout, ssl_, tcp_keepalives_, logging(* omg, there are lots of optional parameters for logging),


	I think it would be good to broaden the audience to anyone on the ZZGrp, GIS Developers group who might be interested, but I also want to minimize exposure until we get security figured out a little bit better, and tightened down a bit. Paul informed/reminded me today that we can use operating system authentication, in theory, but we’re not yet set up that way. I think that’s a good milestone/threshold to use as a trigger for broadening exposure to the GIS Developers Group.

	Thoughts?


	==========================