Hi I'm trying to create a login role (user) with a fairly minimal ability to modify the database. They should be able to select, delete, create triggers, insert and update. I would then like to have multiple accounts which are members of user so they have the same permissions but I can log edits for individual users.
I'm using pgadmin for my database design and my program accesses this with QtSQL.
The error message I get is caught by Qt when trying to select some data as a test. When I run the same code as the database owner and superuser I have no problems.
bool run_query(const QString & q){
QSqlDatabase db = QSqlDatabase::database("default");
QSqlQuery query(db);
query.exec(q);
if (!query.isActive()){
QMessageBox::warning(0, QObject::tr("Database Error"),
query.lastError().text());
return false;
}
while (query.next()){
QString title = query.value(0).toString();
std::cerr << qPrintable(title) << std::endl;
}
return true;
}
run_query("Select forename from contacts;");
ERROR: permission denied for relation contacts QPSQL: Unable to create query
So far I've created the role (user).
-- Role: user
-- DROP ROLE "user";
CREATE ROLE "user" LOGIN
ENCRYPTED PASSWORD 'md54d45974e13472b5a0be3533de4666414'
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
COMMENT ON ROLE "user" IS 'Low level user';
As this gave the previous problems. I tried adding permissions for public for the database but this didn't work either.
Now I've no idea where to look for a solution. Any help is much appreciated. Thanks.
public
to the database is redundant (aspublic
hasconnect, temporary
by default) and will NOT result in a recursive grant to schemas or to tables within those schemas. See postgresql.org/docs/current/static/sql-grant.html and postgresql.org/docs/current/static/…