# PostgreSQL access control and query approval > How to give developers PostgreSQL production access without sharing a password, and what QueryProxy blocks, approves and masks on the way. - Source: https://queryproxy.com/postgresql-access-control/ - Language: en-US - Author: Muhammet Şafak --- PostgreSQL decides who may read or change which objects. It does not decide whether *this particular statement*, typed by a person at 3 a.m., should run. QueryProxy adds that decision: developers submit SQL, the statement is parsed and checked before it can enter the queue, a DBA approves it, and the result is masked before it is stored. The PostgreSQL password stays in QueryProxy's vault and is never handed to a developer. ## What do GRANT and row-level security cover? `GRANT` gives a role privileges on objects: tables, columns, schemas, functions. It is the right tool for scoping what a role may reach, and any production setup should use it. Row-level security goes further. The PostgreSQL 18.6 documentation says [row security policies](https://www.postgresql.org/docs/18/ddl-rowsecurity.html) restrict "which rows can be returned by normal queries or inserted, updated, or deleted by data modification commands". The same page notes that superusers and roles with the `BYPASSRLS` attribute always bypass it, and table owners normally do too. (Read on 2026-10-13.) What neither does is look at the statement. A role that may `UPDATE` a table can run an `UPDATE` with no `WHERE` clause. A role that may `SELECT` the `users` table receives every email address in it, unmasked, on a laptop. Neither involves a second person. These gaps are the argument of [what database access control is](/what-is-database-access-control/): roles say who may act, and cannot review the statement. The [post on stopping `UPDATE` and `DELETE` without `WHERE`](/blog/prevent-update-delete-without-where/) looks at that gap in detail. ## What does QueryProxy block on PostgreSQL? Every submission is parsed before it reaches the approval queue, and a request that breaks a guard is rejected at submission with an explicit message. The [SQL guards reference](/docs/sql-guards/) lists the rules per dialect. The ones that matter most on PostgreSQL: - `UPDATE` and `DELETE` without a `WHERE`, or with one that is always true (`WHERE 1 = 1`), are rejected. A `SELECT` with no `LIMIT` gets `LIMIT 1000` injected, and a limit above `10000` is clamped. - `COPY`, including `COPY ... TO PROGRAM`, is blocked. - File functions are blocked: `pg_read_file()`, `pg_read_binary_file()`, `pg_ls_dir()`, `lo_import()` and `lo_export()`. - `dblink*`, and the administration functions `pg_terminate_backend`, `pg_cancel_backend`, `pg_reload_conf`, `pg_rotate_logfile`, `pg_promote` and `pg_switch_wal`, are blocked. - Functions that run SQL handed to them as a string, such as `query_to_xml`, `xpath_table`, `ts_stat` and `crosstab*`, are blocked, because the guard cannot read the statement inside. - `CREATE EXTENSION` is judged by name. Untrusted procedural languages (`plpythonu`, `plperlu`, and anything matching the `pl...u` convention) are refused; `pgcrypto`, `uuid-ossp` or `postgis` install normally. - `SET` of a guarded setting such as `session_preload_libraries`, `dynamic_library_path` or `session_replication_role` is refused in every scope. ```sql DELETE FROM customers; -- rejected at submission DELETE FROM customers WHERE id = 42; -- allowed, still needs a DBA SELECT pg_read_file('/etc/passwd'); -- rejected at submission ``` The table names are examples; the verdicts follow the documented rules. ## How are DO blocks handled? A `DO` block, a `CREATE FUNCTION` or a `CREATE PROCEDURE` carries a body in another language. QueryProxy reads that body with PostgreSQL's own lexical rules, applies the blocked-function and guarded-setting rules to it, and accepts only the `plpgsql` and `sql` languages. A `DO` block is classified as a write and always goes through human approval, where the reviewer sees the full body. A body that uses dynamic SQL (`EXECUTE`) is refused outright, since the statement it runs is built at run time. Everything else a plpgsql block can do stays decided at approval, not by the parser. ## How do you connect a PostgreSQL database? A DBA registers the database under **Connections** with the `pgsql` driver. The default port is `5432` ([`DbDriver.php`](https://github.com/QueryProxy/QueryProxy/blob/v0.2.5/app/Enums/DbDriver.php), v0.2.5). The default Docker image ships `pdo_pgsql`, so no image change is needed; the [configuration guide](/docs/configuration/) covers the rest. Credentials are encrypted at rest with AES-256. A developer sees the connection only after a DBA grants it. For read-only teams, register a separate connection with a database user that has `SELECT` privileges only, and point it at a replica where you can. The [production hardening guide](/docs/production-hardening/) recommends this. ## What happens to the result? Rows are masked while they stream from the cursor to the result file, so an unmasked copy never exists on disk. Masking rules match a column name (such as `*email*`) or the content of a value, and a value that no rule matches comes back unchanged. [Dynamic data masking](/dynamic-data-masking/) explains the idea, and the [data masking documentation](/docs/data-masking/) lists the rule types and strategies. Masking also covers failures. A PostgreSQL error such as `Failing row contains (...)` can echo the row that broke a constraint, so when the connection has any masking rule the whole row is masked in the stored error message. Every step, from submission to download, is written to an immutable audit log. ## What are the limits on PostgreSQL? - **The denylist is incomplete.** It names dangerous syntax known today, and a spelling it does not name passes. The [SQL guards reference](/docs/sql-guards/) says so itself: what authorizes a statement is the DBA approval, not the guard. - **No read-only transaction or statement timeout on the database side yet.** Both are listed as planned for v0.3.0. Until then, use a `SELECT`-only database user for read connections. - **TLS is not configurable yet.** The connection uses `sslmode=prefer` today, and a TLS setting is planned for v0.3.0. Check the transport yourself. - **Masking is rule-based.** A value that no rule matches is returned as it is, so QueryProxy does not claim to mask all personal data. - **The `DO` body scan reads plain text only.** A `DO` block in `plpgsql` is still arbitrary server-side code, and a plainly written `DELETE` without a `WHERE` in its body passes, because blocks are decided at approval. For PostgreSQL, the guard's verdicts are also checked against a live server (the `live-pgsql` test group). The reference states that MySQL, MariaDB and SQL Server have not been run against live servers. ## When do you not need QueryProxy? If one person owns the database and is the only one who connects, there is no second person to approve anything, and `GRANT` plus a read-only role is enough. The same holds for a development database that holds no real data. If your problem is only that applications keep passwords in config files, a secrets manager or [IAM database authentication](/compare/aws-iam-database-authentication/) solves that without an approval queue. QueryProxy earns its place when people need ad-hoc access to data that matters, and you want a second pair of eyes and a record of what ran. Read the [approval workflow](/sql-query-approval-workflow/) for how a request moves, or [compare QueryProxy with a bastion host](/compare/bastion-host/). MySQL and MariaDB teams should start from [MySQL and MariaDB access control](/mysql-mariadb-access-control/). ## Frequently asked ### How do I give developers access to a production PostgreSQL database without sharing the password? Register the database once in QueryProxy, where the credential is stored AES-256 encrypted, and grant developers the connection instead of the password. They submit SQL, a DBA approves it, and a worker runs it with the stored credential. Developers never see the password. ### Is GRANT SELECT enough to protect personal data in PostgreSQL? No. GRANT SELECT lets the role read every column it was granted, including email addresses and phone numbers, and returns them unmasked to the client. Row-level security can hide rows, but not mask a column in the rows it allows. Masking is a separate layer, which QueryProxy applies while the result is written. ### Does QueryProxy block COPY ... TO PROGRAM? Yes. The SQL guard refuses PostgreSQL COPY, including COPY ... TO PROGRAM, at submission, so the request never reaches a DBA. The same applies to pg_read_file(), pg_read_binary_file(), pg_ls_dir(), lo_import() and lo_export(). ### How does QueryProxy handle DO blocks in PostgreSQL? A DO block is classified as a write and always goes to a DBA, who sees the full body. The body is scanned for blocked functions and guarded settings, and a body that uses dynamic SQL (EXECUTE) is refused because the statement it runs cannot be read in advance. ### Does QueryProxy replace PostgreSQL roles and row-level security? No. It adds a layer in front of them. Give the QueryProxy connection a database user with only the privileges the work needs, ideally SELECT only for read connections, because the database-side read-only transaction and statement timeout are not available yet.