Search code examples
postgresqlpsql

How to execute GRANT commands from shell?

I can restore a whole db using shell commands like

sudo -u postgres psql -c "DROP DATABASE mynewdbname with (FORCE);"
sudo -u postgres psql -c "CREATE DATABASE mynewdbname;"
sudo -u postgres pg_restore -d mynewdbname /home/user/restore_20230824.sql -v

Than I need to execute some grants. Actually I am executing these manually, from shell, every single time

sudo -u postgres psql

mynewdbname 

GRANT ALL ON schema name_of_schema TO application_user_name;
GRANT ALL ON ALL TABLES IN schema name_of_schema TO application_user_name;

and a lot of others

I tried to execute command from normal shell user but they failed

sudo -u postgres psql -c "GRANT ALL ON schema name_of_schema TO application_user_name;"

ERROR:  schema "name_of_schema " does not exist

But schema name_of_schema exists, I think it's because i've not selected the db. So I tried

sudo -u postgres psql -c "\c mynewdbname; GRANT ALL ON schema name_of_schema TO application_user_name;"

But it fails with a cryptic error

\connect: invalid integer value "ON" for connection option "port"

Briefly: how can I execute command against a postgres db from shell?

If usefull, I'm working under a Debian 12. I've full access to it, but I'd like to execute all commands from normal user because these commands must occasionally executed from not-so-intelligent users [monkeys co-workers]

Solution

  • Like the psql documentation describes:

    -c command
    --command=command

    Specifies that psql is to execute the given command string, command. [...]

    command must be either a command string that is completely parsable by the server (i.e., it contains no psql-specific features), or a single backslash command. Thus you cannot mix SQL and psql meta-commands within a -c option. To achieve that, you could use repeated -c options or pipe the string into psql, for example:

    psql -c '\x' -c 'SELECT * FROM foo;'

    But the best solution is to avoid the backslash command at all and connect to the correct database right away:

    sudo -u postgres psql -d mynewdbname -c "GRANT ALL ON schema name_of_schema TO application_user_name"