Permissions in PostgreSQL are explicitly granted to a group role or login user. By default, PostgreSQL does not grant any permissions to any users on the system, except for Superusers (see next section). There are three types of roles in PostgreSQL.

• Group Roles are non-login roles that can be assigned to login users (e.g. the accounting department may have a single group role). 
• Login Roles (aka users) are typically assigned to a single end user (i.e. Pam from accounting). 
• Predefined Roles provide access to commonly needed roles (e.g. read everything, edit everything). 

IMPORTANT: Group and login roles can be used to access any database in the server. For example, the same editor group role could be used to access two different databases. For this reason, we often create Group Roles for specific databases on the server.

1. Common Types of Roles in an Enterprise Environment

The following are some common types of roles in an enterprise environment:

• Superusers. Members of this group have complete control over the entire database server, including every database, object, group role, etc. It is good practice to not give these privileges to anyone but high level database administrators.
• Admin Users. Members of this group can read/edit all tables, create new database objects, and grant privileges to other group roles and login users. 
• Editors. Members of this group can connect to the DB, edit some tables, and create new tables in some schemas. 
• Read-only users. Members of this group can connect to the DB and read some tables in some schemas.

PGAdmin4 provides a simple interface to view and manage group and login roles. It also has several handy tools to grant permissions and view existing permissions on databases and objects. The following instructions use PGAdmin4 to create group and login roles. 

2. Create Group Role

1. In PGAdmin4, expand the database server you wish to modify.
2. Expand the Login/Group Roles section in the database tree. 

3. Right-click on Login/Group Roles > Create > Login/Group Role…
4. Provide a name for the new role (e.g. kamloops_editor). Note that for a basic group role, no other parameters are needed. 
5. Optionally, you can enter some comments that describe the role. 

6. Click Save and the new group role should appear in the Login/Group Roles section. 

3. Create group roles with SQL

CREATE ROLE kamloops_editor WITH
NOLOGIN
NOSUPERUSER
NOCREATEDB
NOCREATEROLE
INHERIT
NOREPLICATION
CONNECTION LIMIT -1;

4. Create a Login Role (aka user)

1. In PGAdmin4, expand the database server you wish to modify.
2. Expand the Login/Group Roles section in the database tree. 

3. Right-click on Login/Group Roles > Create > Login/Group Role…
4. Provide a name for the new login role (e.g. test_user1).
5. Click on the Definition tab and then enter a password for the login role.
6. Click on the Membership tab and then in the “Member of” section, add the kamloops_editor Group Role. With this configuration, user test_user1 will have all the permissions granted to the kamloops_editor role. 

7. Click Save and the new login role should appear in the Login/Group Roles section.

IMPORTANT: Although you have created a login and group role in these steps, without explicitly granting permission to access the database and its related objects, these roles cannot be used.

5. Network Level Security

Even if you have superuser privileges on a database, you can only connect to PostgreSQL with network access, especially if you are trying to connect from a remote location (e.g. trying to connect to a cloud-based system from your office). There are three main ways that remote access is controlled.

• PostgreSQL level security. There are several configuration files used to control access to the database server.
  • The pg_hba.conf file controls client authentication, specifically who can connect, how they can connect, and from where they can connect. 
  • The postgresql.conf file is the main configuration file and in it one can enable remote access to the server. 
  • If the correct settings are not made in both files, users may not be able to access the database server. 
• Firewall-level security. PostgreSQL works on port 5432 by default, although that can be changed to any open port. Whatever port is being used must be open on the firewall (if any) to allow remote connections. If it is locked down at the firewall level, then all access to the database server will be prevented. 
• VPN level security. Often a VPN will be set up to allow remote access to a database server. In such a configuration, the user will need to connect to the VPN before they can access the database server.
• Client-side Network Restrictions. When connecting from within a large enterprise network, it is not uncommon for outbound port access to be restricted. If a network does not allow users to connect to PostgreSQL’s port (usually 5432), then database users will not be able to connect to databases from within this network.

6. Database level permissions

Once the user or group has network access to the database server, they will require database level permissions. Database level permissions include the following:

• CONNECT. Login or Group Roles must be granted CONNECT to access databases.
• CREATE. Users can create schemas within the specified database.
• TEMPORARY. Users can create temporary tables. 

It is usually best to at least grant CONNECT for all group roles and restrict the CREATE permission for administrators. The following is are commonly used permissions for editor, read-only, and admin roles.

--Revoke public (all user) access to the DB
REVOKE ALL PRIVILEGES ON DATABASE training FROM public;
--Grant connect to members of kamloops_editor and kamloops_read_only
GRANT CONNECT ON DATABASE training TO kamloops_editor;
GRANT CONNECT ON DATABASE training TO kamloops_read_only;
GRANT ALL ON DATABASE training TO kamloops_admin;

7. Schema level permission

Once a user has permission to connect to a database, that does not automatically give them access to all the objects therein — far from it. Within the database are any number of schemas, and users and groups must have explicit permission to access EACH schema. Schema level permissions include the following:

• CREATE: permission to create new tables within the schema (best for editor or admin roles).
• USAGE: permission to view existing tables (best for read-only users).

IMPORTANT: granting access to a schema does not automatically grant access to the tables and objects within the schema. Table permissions also need to be explicitly granted.

8. Table Permissions

Within each schema are any number of tables. All users and groups must have explicit permission to access tables. Table level permissions include the following:

• INSERT: Allows INSERT of a new row into a table.
• SELECT: Allows SELECT from any column.
• UPDATE: Allows UPDATE of any column.
• DELETE: Allows DELETE of a row from a table.
• TRUNCATE: Allows TRUNCATE on a table (i.e. delete every row).
• REFERENCES: Allows creation of a foreign key constraint referencing a table, or specific column(s) of a table.
• TRIGGER: Allows creation of a trigger on a table, view, etc.

9. Sequence permissions

Sequences generate unique numbers even when users are editing concurrently. They are used to automatically generate unique ID values for each row and area created when the SERIAL data type is used (e.g. when we use “id SERIAL PRIMARY KEY” when defining a table). If a user is going to edit a table, they must also have access to the sequence associated with that table. Granting USAGE on sequences is required to use the nextval function, which generates the next value in a sequence. Read-only roles do not need access to the sequence since they do not have permission to add new rows.

In QGIS, when a user tries to create a new feature or row on a table that has a sequence on the primary key, the nextval() function shows up in the ID field, like this:

IMPORTANT: If a user is unable to edit a table, or QGIS shows a blank line in the id field, the user or associated group role may not have explicit permission to use the sequence.

10. Public Schema and Tables

The PUBLIC schema contains important objects that must be accessible to users, notably the QGIS layer_styles table and all PostGIS functions. Although most users should NOT have write access to the public schema, some users require write access in order to manage some objects. The following permissions are usually granted on the PUBLIC schema to all users:

• USAGE. All users except for Superadmin (maybe) will never need to create or modify objects within the PUBLIC schema. Doing so could seriously damage the database. Thus, it is best to only grant USAGE at the schema level. 
• SELECT on tables. All group roles should have select access on the tables within the public schema, notably the QGIS styles table. 
• GRANT UPDATE ON TABLE public.layer_styles to the editor group only, assuming they should have permission to update QGIS layer styles. In some configurations, that right is reserved for admin users only.
• GRANT EXECUTE ON ALL FUNCTIONS. It is best to give users the right to execute functions that reside within the public schema. This includes PostGIS functions.

11. Schema, table, and sequence permissions

The following statements exemplify how one might set up schema, table, and sequence permissions for an editor and a read-only group. 

--Permissions on all data schemas (Repeat for all schemas except public)
GRANT ALL ON SCHEMA boundaries TO editor;
GRANT USAGE ON SCHEMA boundaries TO read_only;
--Permissions for all objects within each data schema (Repeat for all schemas except public)
GRANT ALL ON ALL TABLES IN SCHEMA boundaries TO editor;
GRANT SELECT ON ALL TABLES IN SCHEMA boundaries TO read_only;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA boundaries TO editor, read_only;
--Public schema permissions
GRANT USAGE ON SCHEMA public TO editor, read_only;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO editor, read_only;
GRANT UPDATE ON TABLE public.layer_styles TO editor;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO editor, read_only;

12. Column Level Permissions

Column level permissions offer even more granular control over tables by controlling who can read/write specific columns. For example, one might want to add a “notes” field to the parcel dataset and allow any user and any group role member to edit that field. However, those users should not have the ability to update or delete any other fields in the parcel table. The statements for this situation would look like this:

--Add a new column for notes
ALTER TABLE IF EXISTS boundaries.property_parcels
    ADD COLUMN notes character varying;
--Revoke previous permissions on the parcel table for kamloops_read_only
REVOKE ALL ON TABLE boundaries.property_parcels FROM kamloops_read_only;
--Grant SELECT and UPDATE (notes) on the parcel table for kamloops_read_only
GRANT SELECT, UPDATE (notes) ON TABLE boundaries.property_parcels TO kamloops_read_only;

13. Manage Column Level Permissions in PGAdmin4

Column level permissions can also be managed via PGAdmin4 using the following instructions:

1. Expands a database, schema, and table in the database tree.
2. Expand the Columns section to see a list of columns. 
3. Right-click on a column and open the Properties (e.g. the name field in the city_boundary table).
4. Click on the Security tab and add a new security rule. 
5. In the example below, members of the kamloops_editor role will be able to update the name field in the city_boundary table.

14. Row level security

Row level security (RLS) controls permissions on rows based on custom policies, and as such it is arguably the most complex permission configuration in PostgreSQL. RLS must be explicitly enabled on a table and policies can refer to SELECT, INSERT, UPDATE, DELETE or ALL. For example, if we wanted employees to only see work orders that are assigned to them in a table, all we need to do is have a column that stores their username and then create an RLS policy that says “users can only access and update rows that are assigned to them.” In SQL, a simple work order system would look like this:

Step 1: Create work order table

-- Step 1: Create work order table
CREATE TABLE admin.work_orders (
	id SERIAL PRIMARY KEY,
	geom geometry(point,3157),
	work_order_type varchar, 
	work_order_description text,
	--Metadata fields
	created_by varchar DEFAULT "current_user"(),
	created_at timestamp DEFAULT now()
);

Step 2: Enable RLS and create the RLS policy

ALTER TABLE admin.work_orders ENABLE ROW LEVEL SECURITY; CREATE POLICY work_order_managers ON admin.work_orders TO kamloops_editor
    USING (created_by = current_user);

15. QGIS Project Table Permissions

It is important to evaluate who should have read and write access to QGIS project tables, the central symbology table, and the metadata table. The following presents a suggested approach to granting permissions on these tables for a data warehouse database design.

QGIS project tables are created in each schema and the table contains any number of projects. The table is always called qgis_projects. It is often the best policy to limit write access to this table to a small group of users because it is very easy to overwrite changes made by another user. However, if we want to grant read access to the qgis_projects table to read-only users and grant all for editors, the SQL statements would look like this:

--Revoke all permissions on the table. This is just in case rights were granted 
--previously to this role. 
REVOKE ALL ON TABLE maps.qgis_projects FROM kamloops_read_only;
--Grant read-only permissions on the table to the read-only role. 
GRANT SELECT ON TABLE maps.qgis_projects TO kamloops_read_only;
--Grant full access on the table to the editor role. 
GRANT ALL ON TABLE maps.qgis_projects TO kamloops_editor;

IMPORTANT: Even if we give a user or group role access to a QGIS project table, they still need to have explicit permission to open EVERY layer within the project. If they try to open a project in which they do not have at least SELECT access to the layers therein, they will get a permission error when loading the project. 

16. QGIS Symbology Permissions

The QGIS symbology table is created in the Public schema and is called layer_styles. Granting access to the symbology table requires some thought because anyone with CREATE or UPDATE permissions will be able to add additional style or update existing styles, respectively. Further, if one has DELETE or TRUNCATE permissions, they can severely alter the core symbology used in an enterprise environment. For these reasons, it is usually best to limit access to the symbology table to a core group of advanced users. The following SQL statement exemplifies how we can grant read access to the read-only role and grant write access to the editor role on the symbology table. 

--Revoke all permissions on the table. This is just in case rights were granted --previously to this role. 
REVOKE ALL ON TABLE public.layer_styles FROM kamloops_read_only;
--Grant read-only permissions on the table to the read-only role. 
GRANT SELECT ON TABLE public.layer_styles TO kamloops_read_only;
--Grant full access on the table to the editor role. 
GRANT ALL ON TABLE public.layer_styles TO kamloops_editor;