Setting up PostGIS database and opening it in QGIS on Windows?

It sounds as if your administrator has installed the database successfully and provided you with the details you will need. They may have also configured PostGIS during the install as well, but if not, you can check that as you go through these steps.

First ask your administrator to install pgAdmin this will allow you to access your PostgreSQL database with a graphical interface.

https://www.pgadmin.org/download/windows.php

Open pgAdmin

If your PostgreSQL has been installed;

• on your computer it will show up as localhost, you should be able to double click on 'PostgreSQL (version no.) (localhost:5432)' to connect to it.

• If it has been installed on the network, you will need your administrator to let you know the host address (if you don't already have that), then you will need to go to File > Add Server and fill in the fields;

Once connected go to Edit > New Object > New Database

Give it a name. It will appear in your server list.

Double-click to connect to it. Check the Extensions to see if it lists postgis.

If not;

Open up the SQL window by clicking on the SQL icon

and type in

CREATE EXTENSION PostGIS

Press 'Execute Query' - this icon

Right-click on the database in the list and disconnect, and then reconnect. Extensions should now have postgis listed.

You could set up a new Group Role, and login role at this point, it sounds as if you should given the security needs, and your users across the network.

Or if you just want to test it you can continue on with the default role postgres.

Different Groups can be set up to have different permissions e.g. admin and user. User logins can then be set up and assigned to whatever group fits the permission level you want to give the user, or you can give access to all tables to admin, and only some to users etc. You may not need an admin group.

Open up the SQL window and execute these queries;

CREATE ROLE user_group_name NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
CREATE ROLE admin_group_name NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

GRANT user_group_name TO admin_group_name

GRANT ALL ON DATABASE database_name TO admin_group_name;

Set up an admin login (logged in as the postgres/superuser)

CREATE ROLE admin_name LOGIN PASSWORD 'their_password' NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
GRANT admin_group_name TO admin_name;

Set up a user login (logged in as the postgres/superuser)

CREATE ROLE user_name LOGIN PASSWORD 'their_password' NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
GRANT users_group_name TO user_name;

Open QGIS. Start a new project.

Click on the 'Add PostGIS layer' icon in the left hand toolbar.

Click 'New' connection, and enter the connection details as above (if your database is local then host is just localhost).

You can test the connection at this point. If you check the 'Save' boxes by the username and password field it will store your credentials in your profile so you don't need to enter each time. Up to you depending on your security needs.

Press Ok and close down the 'Add PostGIS table window'

Then go to Database > DB Manager > DB Manager (Recent QGIS versions have DB Manager installed as standard, earlier versions you will need to install the DB Manager plugin)

Select PostGIS. The default public schema will be shown. It sounds as if you should create a new schema rather than use the public one.

Schema > Create schema - Give it a name (no_spaces_or_use_underscores)

Once created, you can check by going back to pgAdmin, disconnect, reconnect. The new schema should now be there.

Back to QGIS DB Manager to upload a shapefile into a table in the database.

Click the down arrow 'Import layer/file' browse to the shapefile that you want to import. Click the `...`` icon to open browse.

Give the table a name (again_no_spaces_or_use_underscores)

Select Primary key

Select Geometry Column

Select SRID and enter the CRS code

For now leave everything else and OK.

If it's a large dataset, it could take a while, especially across a network/internet connection

Again you can go back to pgAdmin, press refresh this time, and the new table will appear in Tables.

If you have set up group roles and users you will need to grant access to the schema, for example;

GRANT ALL ON SCHEMA schema_name TO admin_group_name;
GRANT USAGE ON SCHEMA schema_name TO user_group_name;

Back to QGIS and 'Add PostGIS layer'. This time select Connect. Open up the schema and highlight the table and click 'Add' and it will open up in your project space.

It seems that your project will work sufficient on a portable installation of postgres / postgis. I did this for my students a while ago, so that they can copy the postgresl folder on a usb stick and use it on every windows pc:

1. Download postgresql as a zip file
2. Unzip it
3. Download postgis (postgis-bundle-pg96-2.3.1x32.zip or x64), according to your system
4. Unzip it and copy it into the postgresql folder
5. Use this explanation to "install" Postgres as a portable version: http://www.postgresonline.com/journal/archives/172-Starting-PostgreSQL-in-windows-without-install.html
6. Optionally change pg_hba.conf and add / override "host all all 127.0.0.1/32 trust" - this allows access from your pc to the database without checking the password, which is ok in this case but it's a no-go in production environments
7. Connect to the database using pgadmin3
8. Create a new database and execute "CREATE EXTENSION postgis;"

Now you should be able to work with postgesql/postgis and access the db on your pc. Please be aware that this is a quick and dirty solution, which works for educational purposes but not in a production / network environment.