0

an application with postgres database is installed in a personal pc of a person (os: windows), how to prevent this person from accessing the db directly (using pgAdmin)?

the database cluster is already protected by a password (superadmin role) and pg_hba.conf auth-option is set to md5, but that person can set it to trust and bypass the password!

geek11
  • 41
  • 1
  • 5
  • 1
    When the user has local access to the computer the DBMS runs on and has enough rights or can give themselves enough rights, there's nothing you can do. Eventually they can access the data, one way or another. – sticky bit Jun 28 '20 at 18:58
  • @stickybit but this is not the case with other DBMS like SQL Server – geek11 Jun 28 '20 at 19:02
  • With SQL Server you had that problem too. The DBMS doesn't matter. – sticky bit Jun 28 '20 at 19:04
  • @stickybit in SQL Server you can disable the windows authentication as explained in this response https://stackoverflow.com/questions/21700337/how-to-protect-database-from-windows-authentication – geek11 Jun 28 '20 at 19:51
  • 1
    So? That doesn't make the DB inaccessible to anyone having enough local rights. Even if you don't have the sa password. Stop the service, get the MDF and LDF files and attach them to a server you have the sa login for. If you cannot stop the service, you can stop the computer and attach it to a computer you have admin rights on and get the MDF and LDF that way. And possibly even more tricks. – sticky bit Jun 28 '20 at 19:54
  • Why do they have write access to pg_hba.conf? Are they a local admin? Are they the one who installed the database in the first place? Did they boot with a rescue disk and then mount the old boot drive and do whatever they want with it? – jjanes Jun 28 '20 at 20:27
  • @stickybit I didn't think about attaching files to another server, this way sql server db won't be protected too! so what would be a good solution in this case, I mean if you want to deliver a product to persons while prevent them from direct access to the db? I think about password-encrypted SQLite db but it is very limited in terms of: concurrency, network access, multi-user auth,... – geek11 Jun 29 '20 at 06:45
  • @jjanes they have write access to pg_hba.conf because they are the owner of the computer. but the app developer must prevent them from directly viewing the database objects (tables, columns, tokens, keys and private data) – geek11 Jun 29 '20 at 06:48
  • The whole thing doesn't make much sense. If the database contains secrets, don't deliver it. Otherwise it's the users' data and the users' computer and they can do with it what they want. They can also set the computer on fire or throw it out of the window, if they wish, that's none of your concern. The worst thing that can happen to you is that they damage the database therefore break the application and blame you (the application) for it. – sticky bit Jun 29 '20 at 10:14
  • Encryption won't do you any good also. If they should be able to work with that data the data needs to be decrypted. Ans that requires the key which they either must know or is stored somewhere, where they can get it too. As said earlier, if you want to keep something secret, just don't deliver it to people you want to keep it secret from in any form or shape... – sticky bit Jun 29 '20 at 10:16
  • @stickybit what about HFSQL? I think you can protect the database files with a password, so even you import them in a different server you always prompted to enter the password! – geek11 Jun 30 '20 at 19:10
  • Of course you can encrypt data and don't ship the key. But then how does your application access the data then? – sticky bit Jun 30 '20 at 20:47
  • the key is hard-coded in the app. the user own the data and can view and use it only using the app, but the database structure is owned by the developer and he want to prevent the user from knowing about it and prevent other people from viewing sensitive and private data like stored keys, money,... – geek11 Jul 01 '20 at 08:15

0 Answers0