I've created user profiles for my teammates to connect to my postGIS database with QGIS.
with my own user account I am able to successfully connect to my postGIS database on AWS RDS using QGIS 2.18.
user creation SQL:
CREATE USER foobar WITH LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT NOREPLICATION CONNECTION LIMIT -1 PASSWORD 'xxxxxx';
And granted permissions:
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA test TO foobar;
EDIT: In addition to the accepted answer, one can also change the default privileges so newly created tables are shown automatically in QGIS:
ALTER DEFAULT PRIVILEGES IN SCHEMA test GRANT ALL ON TABLES to foobar;