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/pgadmin-4-windows/
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.