Geek Answers Handbook

Is there a way to prevent users from making bulk entries in the Postgresql database

I have 4 new data entry users who use a specific GUI to create / update / delete records in our main database. The GUI client allows them to see the database records on the map and make changes to it, which is an excellent and preferred way to execute it.

But lately, many guys accessed the local database directly using PGAdmin and launching bulk queries (e.g. update, insert, delete, etc.), which leads to many problems, such as people who update many records, not Knowing and not mistaken when setting values. It also affects our registration procedures, as we calculate the average and time stamps for reporting purposes, which are very important to us.

So, is there a way to prevent users from using PGAdmin (remember that many of these guys work from home and we don’t have access to their machines) and run SQL queries directly in the database.

We still have to give them access to certain tables and allow them to execute sql as long as it passes through a specific client, but denies access to the same user when it tries to execute a query directly in db.

+5
source share
5 answers

- db 3- . (, - API - ) API . , . DB , - (pgadmin, psql - ). , .

+1

, ( , , , ).

, geqo_pool_size = 1001 ( 1000).

, , , " PGAdmin", ( ).

: .

0

, , ( ).

.

- , SQL . API, , - ( , , , ) .

, .

0

pg_hba.conf .

. , IP, .

  • GSSAPI
  • Radius
  • pam

, , somthing API , . , , . , kerberos .

0

, , - , , CREATE FUNCTION SECURITY DEFINER, , , GRANT EXECUTE .

This section has a response to ServerFault that links to the next blog post with a detailed description.

I believe that using middleware, as the other answers say, is an overkill in your situation. The above solution does not require users to change the way they access the database, they simply restrict their right to change data only using predefined server-side methods.

0
source

More articles:


All Articles