0

My desktop app includes a postgres database and a front app. I need the db to be accessed only by my front app. The credentials of this user are going to reside in the front app binaries.

I realized it's possible for anyone to use createuser.exe with -s to create a superuser. After that, it would be enough to create a db with createdb.exe, connect to it, and change ownership of my db to the new user, or do whatever.

The only effective way to avoid illegal access I know is to change template0 and template1 names, so that createdb.exe throws 'template0 not found'. That way anyone could create superusers, but no db's to connect to.

In addition, it would be needed to drop postgres database and postgres role, and execute these sentences:

REVOKE ALL ON DATABASE template1 FROM public;
REVOKE ALL ON SCHEMA public FROM public;

REVOKE ALL ON pg_user FROM public;
REVOKE ALL ON pg_roles FROM public;
REVOKE ALL ON pg_group FROM public;
REVOKE ALL ON pg_authid FROM public;
REVOKE ALL ON pg_auth_members FROM public;

REVOKE ALL ON pg_database FROM public;
REVOKE ALL ON pg_tablespace FROM public;
REVOKE ALL ON pg_settings FROM public;

Can I do something else?

1

It is not necessary to break your database to make it secure.

Just configure pg_hba.conf to make sure that nobody except the operating system user postgres can log in, and restrict login to that account.

There is no way to protect the database from an administrative user who can change operating system passwords and override file permissions. Don't even try.

  • I think there is no point on configuring pg_hba.conf, since users will have access to it. Postgres data and binaries are included with the app – nosferatu Dec 11 '19 at 10:58
  • When the application is installed (as Administrator), create a PostgreSQL operating system user (postgres) and a PostgreSQL service that runs as that user. Then the end user can connect to the database, but has no access to the database files. – Laurenz Albe Dec 11 '19 at 11:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.