1

I have an initializing sql script for PostgreSQL database. In the script, instead of hardcoding the name of the user, I wish to use the variable stored in my shell file. However I am not able to do it.

this is the shell file:

export
DB_HOST="xxx"
DB_PORT="xxx"
DB_USER="xxx"
DB_NAME="xxx"

psql -U $DB_USER -d $DB_NAME -v user1=$DB_USER -f initialize_postgres.sql

and i want to use it in the sql script which looks like the following:

GRANT
SELECT,
INSERT,
DELETE,
UPDATE ON ALL TABLES IN SCHEMA public TO :'user1';

GRANT USAGE,
SELECT ON ALL SEQUENCES IN SCHEMA public to :'user1';


CREATE FUNCTION save_property (IN prop varchar(50), IN val varchar(500)) RETURNS void AS $$
UPDATE properties SET "value"=val WHERE id=prop;

INSERT INTO properties (id, value)
    SELECT prop, val
    WHERE NOT EXISTS (SELECT 1 FROM properties WHERE id=prop);
$$ LANGUAGE sql;

GRANT EXECUTE ON FUNCTION public.save_property(varchar(50),varchar(500)) TO :'user1';

this is the error that is shown to me when I run the pipeline:

List of errors

2
  • Use double-quotes around your variables: psql -U "$DB_USER" -d "$DB_NAME" -v "user1=$DB_USER" -f initialize_postgres.sql Have a look at Store mysql result in a bash array variable, there are sample of variable use in both way. (Maybe you could be interested by my shell-connector.sh.) Commented Apr 10, 2024 at 7:58
  • I am getting the same error by using double quotes around my variable as you suggested :') Commented Apr 10, 2024 at 8:27

1 Answer 1

3

If you use the substitution :'user1', the result will be in single quotes as a string constant. But GRANT requires the role to be an identifier, that is an object name. So you have to use no quotes or double quotes instead.

Sign up to request clarification or add additional context in comments.

4 Comments

I am getting the same error by using no quotes or double quotes :') Error: psql:helper_scripts/datenbank/initialize_postgres.sql:188: ERROR: syntax error at or near ":" LINE 5: UPDATE ON ALL TABLES IN SCHEMA public TO :"user1"; ^ psql:helper_scripts/datenbank/initialize_postgres.sql:191: ERROR: syntax error at or near ":" LINE 2: SELECT ON ALL SEQUENCES IN SCHEMA public to :"user1"; ^
Well, it works here and everywhere else. Perhaps you are doing something different from what you show. For example, your export statement is messed up and won't export anything (but that doesn't cause such an error). The error means that the variable is not set.
Your comment looks like you are still using quotes around the variable. It should be :user1, not :'user1' or :"user1", right?
@PaulHodges Double quotes would be fine. The error message indicates that the psql variable is not set.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.