CODEWITHSUNDEEP
bashpostgresqlpsql
user3541631
user3541631

Reputation: 4008

Grant read-only privileges to a PostgreSQL role using bash

Using bash scripting I'm trying to set read-only permissions to a PostgreSQL role:

The psql command is called using pgexec function:

pgexec() {
    local cmd=$1
    sudo -u postgres psql  -c "$cmd" 
}

I use the function above for multiple psql commands (one or multiple lines)

function set_permissions(){
...
    if [[ $permissions == read ]]
        then
            output=$(pgexec "GRANT CONNECT ON DATABASE ${database} TO ${role};
                    \c ${database};
                    GRANT USAGE ON SCHEMA ${schema} TO ${role};
                    GRANT SELECT ON ALL TABLES IN SCHEMA ${schema} TO ${role};
                    GRANT SELECT ON ALL SEQUENCES IN SCHEMA ${schema} TO ${role};
                    ALTER DEFAULT PRIVILEGES IN SCHEMA ${schema} GRANT SELECT ON TABLES TO ${role};
                    ALTER DEFAULT PRIVILEGES IN SCHEMA ${schema} GRANT SELECT ON SEQUENCES TO ${role};" 2>1)
            if [[ "$?" -eq 0 ]]
                then
                    echo "${role_name} was granted permissions $perm_read"
                    return 0    
                else    
                    echo "Error - $output"
                    return 4    
            fi      
...
}

The result I get is 

Error - .

I expect $output to return the error.

I used debug and the SQL queries look ok

Upvotes: 0

Views: 404

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246493

output catches the standard output of your command, but not the standard error.

To capture standard error along with standard output, use

output=$(pgexec "..." 2>&1)

Your mistake was to omit the &. That way standard error ends up in a file called 1.

Upvotes: 1

Related Questions