This section assumes command line access to the PostgreSQL server from a terminal window. The server used in this section is running Ubuntu Linux 18.10.
Once logged into the terminal either locally or over SSH, log in as the root database user “postgres” by typing:
sudo -i -u postgres
Or if you don’t have access to the root PostgreSQL login, log in as the user that created the schema in QGIS by typing:
psql -U <postgre username> -d <dbname>
** This method does not require the user to log into the database cluster with psql as explained below.
Logging into the database cluster and database
Once logged in as postgres, it is possible to log into the PostgreSQL database cluster and connect to your database to make alterations as needed.
- Type “psql” into the terminal
- Type “\connect <databasename>” into the sql prompt
Listing all schemas in the database
To list all the available schemas in the database type the following:
SELECT nspname FROM pg_catalog.pg_namespace;
** Make sure to add the semicolon (;) to the end of the statement
Granting access to users or groups for a schema
If a user creates a schema in QGIS, they are assigned the owner and other users are not permitted any usage rights to that schema. They see that it exists, but cannot see or access the contents.
You can tell if you don’t have access using the database manager in QGIS.
To grant this usage to another user, the database admin must assign the permissions.
When connected to the database the DB admin types:
GRANT USAGE ON SCHEMA “<schema>” TO <user>;
**Use the quotation marks if the schema name has capitalized letters.
After this is done, you can reconnect to the database and the schema should now show full privileges.
If you need to extend this to all the tables in the schema the command to follow up is:
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA <schema> TO <user>;
You can replace <user> with <user_group> as well if your DB administrator has created user groups in the database. This is highly recommended.