For PostgreSQL powerusers, automating repeated steps is becoming more and more necessary, and gexec can help. This blog will show you how to use the || operator and the gexec command to avoid unnecessary repetition in your workflow.
Table of Contents
The CLI client that ships with PostgreSQL is called psql. Like many CLI clients, it is often overlooked and replaced with something with a GUI, or it is only used for the most basic tasks, while more complex operations are carried out elsewhere. However, psql is a very capable tool with lots of useful features.
One common pattern is the need to run the same command with different arguments. Often, users simply rewrite the command over and over, or sometimes they may opt to use a text editor to write the command once, then copy and paste and edit it to accommodate different arguments.
Sometimes it can be useful to automate such steps, not only in the interest of saving time, but also in the interest of avoiding errors due to typos or copy-pasting. PostgreSQL can take the results of queries and add text to create commands with those results as arguments.
For this purpose, we can prepend or append text to any query result using the || operator.
|| operatorLet's assume a new user needs access to some tables in a schema, e.g. all those tables that match a certain prefix.
Now, we could do this manually, or ask the database to automate the boring stuff.
1. Let's retrieve the relevant tables with names starting with pgbench
|
1 2 3 4 5 6 7 8 |
postgres=# SELECT tablename FROM pg_tables WHERE tablename~'^pgbench'; tablename ------------------ pgbench_accounts pgbench_branches pgbench_history pgbench_tellers (4 rows) |
2. Let's use || to prepend and append command fragments to create a valid command with the tablename as a parameter.
|
1 2 3 4 5 6 7 8 |
postgres=# SELECT 'GRANT SELECT ON TABLE ' || tablename || ' TO someuser;' FROM pg_tables WHERE tablename~'^pgbench'; ?column? ----------------------------------------------------- GRANT SELECT ON TABLE pgbench_accounts TO someuser; GRANT SELECT ON TABLE pgbench_branches TO someuser; GRANT SELECT ON TABLE pgbench_history TO someuser; GRANT SELECT ON TABLE pgbench_tellers TO someuser; (4 rows) |
Note that the strings end or begin with additional spaces, as the tablename itself does not contain the necessary spaces for argument separation. The semicolon ; was also added so these commands could be run straight away.
Please keep in mind that, while it is convenient to use || to concatenate things, it is not considered good practice, as it can be vulnerable to SQL injection attacks, as a helpful commenter detailed below:
Do NOT blindly concatenate table names with queries. Use quote_ident(), or format() with %I, instead. These apply correct escaping as necessary.
A safer approach to achieve the same results would be something like this:
|
1 2 3 4 5 6 7 8 |
postgres=# SELECT format('GRANT SELECT ON TABLE %I TO someuser;', tablename) FROM pg_tables WHERE tablename~'^pgbench'; format ----------------------------------------------------- GRANT SELECT ON TABLE pgbench_accounts TO someuser; GRANT SELECT ON TABLE pgbench_branches TO someuser; GRANT SELECT ON TABLE pgbench_history TO someuser; GRANT SELECT ON TABLE pgbench_tellers TO someuser; (4 rows) |
Now, these commands could be copied and then pasted straight into the prompt.
I've even seen people take such lines, store them into a file and then have psql execute all commands from the file.
But thankfully, a much easier way exists.
gexecIn psql, there are many shortcuts and helpers to quickly gather info about the database, schemas, tables, privileges and much more.
The psql shell allows for working on the input and output buffers, and this can be used together with gexec to have psql execute each command from the output buffer.
gexecReusing the query to generate the necessary commands, we can call gexec to execute each line from the previous output.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
postgres=# SELECT 'GRANT SELECT ON TABLE ' || tablename || ' TO someuser;' FROM pg_tables WHERE tablename~'^pgbench'; ?column? ----------------------------------------------------- GRANT SELECT ON TABLE pgbench_accounts TO someuser; GRANT SELECT ON TABLE pgbench_branches TO someuser; GRANT SELECT ON TABLE pgbench_history TO someuser; GRANT SELECT ON TABLE pgbench_tellers TO someuser; (4 rows) postgres=# gexec GRANT GRANT GRANT GRANT |
gexecAssuming that you want to do something involving more arguments, you can always add more || to add more command fragments around the results from a query.
Suppose you need to grant privileges to insert, update, and delete from those tables as well.
A simple cross join gives us the desired action (constructed as a relation using the VALUES constructor) for each of the table names.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
postgres=# SELECT action, tablename FROM pg_tables CROSS JOIN (VALUES ('INSERT'),('UPDATE'),('DELETE')) AS t(action) WHERE tablename~'^pgbench'; action | tablename --------+------------------ INSERT | pgbench_accounts UPDATE | pgbench_accounts DELETE | pgbench_accounts INSERT | pgbench_branches UPDATE | pgbench_branches DELETE | pgbench_branches INSERT | pgbench_history UPDATE | pgbench_history DELETE | pgbench_history INSERT | pgbench_tellers UPDATE | pgbench_tellers DELETE | pgbench_tellers (12 rows) |
Note that we explicitly assign the action column name using AS t(action) to the table generated using VALUES.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
postgres=# SELECT 'GRANT ' || action || ' ON TABLE ' || tablename || ' TO someuser;' FROM pg_tables CROSS JOIN (VALUES ('INSERT'),('UPDATE'),('DELETE')) AS t(action) WHERE tablename~'^pgbench'; ?column? ----------------------------------------------------- GRANT INSERT ON TABLE pgbench_accounts TO someuser; GRANT UPDATE ON TABLE pgbench_accounts TO someuser; GRANT DELETE ON TABLE pgbench_accounts TO someuser; GRANT INSERT ON TABLE pgbench_branches TO someuser; GRANT UPDATE ON TABLE pgbench_branches TO someuser; GRANT DELETE ON TABLE pgbench_branches TO someuser; GRANT INSERT ON TABLE pgbench_history TO someuser; GRANT UPDATE ON TABLE pgbench_history TO someuser; GRANT DELETE ON TABLE pgbench_history TO someuser; GRANT INSERT ON TABLE pgbench_tellers TO someuser; GRANT UPDATE ON TABLE pgbench_tellers TO someuser; GRANT DELETE ON TABLE pgbench_tellers TO someuser; (12 rows) |
This output can then again be executed using gexec.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
postgres=# gexec GRANT GRANT GRANT GRANT GRANT GRANT GRANT GRANT GRANT GRANT GRANT GRANT |
Depending on the circumstances, it may be required to add additional quotes to the output, for example when table names contain capitalization or spaces. In such cases, matching double quotes " can be added to the strings prepended and appended to arguments.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
postgres=# SELECT 'GRANT SELECT ON TABLE '' || tablename || '' TO someuser;' FROM pg_tables WHERE schemaname='public'; ?column? ----------------------------------------------------- GRANT SELECT ON TABLE 'with spaces' TO someuser; GRANT SELECT ON TABLE 'Capitalization' TO someuser; GRANT SELECT ON TABLE 'capitalization' TO someuser; (3 rows) postgres=# gexec GRANT GRANT GRANT |
Now that you know how to use gexec, why not take the next step? Take a look at our blog on column order in PostgreSQL to see it used in another practical example.
If you would like to learn more about security in PostgreSQL, see my blog about Transport Layer Security.
You need to load content from reCAPTCHA to submit the form. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from Facebook. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from X. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More Information
Do NOT blindly concatenate table names with queries. Use quote_ident(), or format() with %I, instead. These apply correct escaping as necessary.
Blog articles that teach users to create SQL injection vulnerabilities are actively harmful.
Thanks for the input.
This is supposed to be a simple demonstration, and every DBA should read carefully what they have in their buffer before executing gexec .
I'm not suggesting anybody put this in their automation to be executed blindly, where it could be susceptible to these issues.
I'll add a section to use format(), including a disclaimer.
You'll want to use the %I.%I construct in a format() and pass in the nspname and relname rather than ever blindly putting double-quotes in your format strings.