database "cdf_100_1313" does not exist

On Mon, Sep 09, 2019 at 05:16:20PM +0530, nikhil raj wrote:

The DB is already there and getting the error of the DB does not exist
where I execute it via shell.
DBLIST=/usr/lib/postgresql/11/bin/psql -p 5432 -U postgres -d postgres -q
-t -c "SELECT datname from pg_database where datname in
('CDF_100_1313')"/usr/lib/postgresql/11/bin/psql
-p 5432 -U postgres -d $DBLIST -c "GRANT CONNECT ON DATABASE "$DBLIST" TO
cpupdate"

ERROR: database "cdf_100_1313" does not exist

Likely a quoting issue.

Karsten Hilbert
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B

Hi Karsten,

yup you are right can you help in i tried by removing quotion and also
adding extra having same issue.

On Mon, Sep 9, 2019 at 5:25 PM Karsten Hilbert <Karsten.Hilbert@gmx.net>
wrote:

I think you need to add parens around the command to populate DBLIST and
remove the quotes in the GRANT command.

In bash this works for me:

DBLIST=$(/usr/lib/postgresql/11/bin/psql -p 5432 -U postgres -d postgres -q
-t -c "SELECT datname from pg_database where datname in ('tempdb')")
/usr/lib/postgresql/11/bin/psql -p 5432 -U postgres -d $DBLIST -c "GRANT
CONNECT ON DATABASE $DBLIST TO dbuser1"

On Mon, Sep 9, 2019 at 5:58 AM nikhil raj <nikhilraj474@gmail.com> wrote:

Hi Dan,

Still facing the same issue.

/usr/lib/postgresql/11/bin/psql -p 5432 -U postgres -d $DBLIST -c "GRANT
CONNECT ON DATABASE $DBLIST TO cpupdate"
ERROR: database "cdf_100_1313" does not exist

On Mon, Sep 9, 2019 at 6:38 PM Dan Livingston <danlivingstone@gmail.com>
wrote:

On Sep 9, 2019, at 7:16 AM, nikhil raj <nikhilraj474@gmail.com> wrote:

Hi Dan,

Still facing the same issue.

/usr/lib/postgresql/11/bin/psql -p 5432 -U postgres -d $DBLIST -c "GRANT CONNECT ON DATABASE $DBLIST TO cpupdate"
ERROR: database "cdf_100_1313" does not exist

Can we see the output of psql’s \l ?

Hi Rob,

Please find the out put.

Name | Owner | Encoding | Collate | Ctype |
Access privileges
----------------------+----------+----------+-------------+-------------+-----------------------
CDF_10_11 | cpuser | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
cpuser=CTc/cpuser
CDF_History | cpuser | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
cpuser=CTc/cpuser
CDF_100_1313 | cpuser | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
cpuser=CTc/cpuser

On Mon, Sep 9, 2019 at 6:58 PM Rob Sargent <robjsargent@gmail.com> wrote:

On Sep 9, 2019, at 7:16 AM, nikhil raj <nikhilraj474@gmail.com> wrote:

/usr/lib/postgresql/11/bin/psql -p 5432 -U postgres -d $DBLIST -c "GRANT CONNECT ON DATABASE $DBLIST TO cpupdate"
ERROR: database "cdf_100_1313" does not exist

This still isn't quoted properly. It's tricky since double-quote is
special to both the shell and SQL. You need something like

/usr/lib/postgresql/11/bin/psql -p 5432 -U postgres -d "$DBLIST" -c "GRANT CONNECT ON DATABASE \"$DBLIST\" TO cpupdate"

regards, tom lane

Hi All,

just did few modification of the shell command still having the same issue.
Having the issue with the Grant command only its not taking the parameters.

/usr/lib/postgresql/11/bin/psql -p 5432 -U postgres -d $DBLIST -c "GRANT
CONNECT ON DATABASE \"$DBLIST\" TO cpupdate"
ERROR: database " CDF_100_1313" does not exist

*** For the above one we have its taking space in the Databases name
starting.*

/usr/lib/postgresql/11/bin/psql -p 5432 -U postgres -d $DBLIST -c "GRANT
CONNECT ON DATABASE "$DBLIST" TO cpupdate"
psql: warning: extra command-line argument "ASD_100_1313 TO cpupdate"
ignored
ERROR: syntax error at end of input
LINE 1: GRANT CONNECT ON DATABASE
^

On Mon, Sep 9, 2019 at 7:47 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

On 9/9/19 7:30 AM, nikhil raj wrote:

Hi All,

just did few modification of the shell command still having the same
issue. Having the issue with the Grant command only its not taking the
parameters.

 /usr/lib/postgresql/11/bin/psql -p 5432 -U postgres -d $DBLIST -c
"GRANT CONNECT ON DATABASE \"$DBLIST\" TO cpupdate"
ERROR:  database " CDF_100_1313" does not exist

You are not showing how you are currently generating DBLIST. From the
looks of it you have a leading space in the database name:

" CDF_100_1313"

*
*
*** For the above one we have its taking space in the Databases name
starting.*

/usr/lib/postgresql/11/bin/psql -p 5432 -U postgres -d $DBLIST -c "GRANT
CONNECT ON DATABASE "$DBLIST" TO cpupdate"
psql: warning: extra command-line argument "ASD_100_1313 TO cpupdate"
ignored
ERROR:  syntax error at end of input
LINE 1: GRANT CONNECT ON DATABASE
                                  ^

On Mon, Sep 9, 2019 at 7:47 PM Tom Lane <tgl@sss.pgh.pa.us
<mailto:tgl@sss.pgh.pa.us>> wrote:

On Sep 9, 2019, at 7:16 AM, nikhil raj <nikhilraj474@gmail.com

<mailto:nikhilraj474@gmail.com>> wrote:

/usr/lib/postgresql/11/bin/psql -p 5432 -U postgres -d $DBLIST

-c "GRANT CONNECT ON DATABASE $DBLIST TO cpupdate"

ERROR:  database "cdf_100_1313" does not exist

This still isn't quoted properly.  It's tricky since double-quote is
special to both the shell and SQL.  You need something like

/usr/lib/postgresql/11/bin/psql -p 5432 -U postgres -d "$DBLIST" -c
"GRANT CONNECT ON DATABASE \"$DBLIST\" TO cpupdate"

                        regards, tom lane

--
Adrian Klaver
adrian.klaver@aklaver.com

Hi Adrian,

DBLIST=/usr/lib/postgresql/11/bin/psql -p 5432 -U postgres -d postgres -q
-t -c "SELECT datname from pg_database where datname in
('CDF_100_1313')"/usr/lib/postgresql/11/bin/psql
-p 5432 -U postgres -d $DBLIST -c "GRANT CONNECT ON DATABASE \"$DBLIST\" TO
cpupdate"
only in the grant its taking space rest in -d its perfectly all right.

On Mon, Sep 9, 2019 at 8:04 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

nikhil raj wrote:

/usr/lib/postgresql/11/bin/psql -p 5432 -U postgres -d $DBLIST -c "GRANT
CONNECT ON DATABASE \"$DBLIST\" TO cpupdate"
ERROR: database " CDF_100_1313" does not exist

That's because you don't use the unaligned format to get the
list of results. You should add -A to psql options.

Or better yet, use a simpler method that does not use
shell variables at all and work with a single psql call:

$ psql -U postgres -d postgres <<EOF

select format('GRANT CONNECT ON DATABASE %I TO cpupdate',
datname) FROM pg_database WHERE <insert conditions here>
\gexec

EOF

That's possible because you don't need to be connected to
a database to grant the right to connect to that database.

Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

From: nikhil raj [mailto:nikhilraj474@gmail.com]
Sent: Tuesday, 10 September 2019 12:39 AM
To: Adrian Klaver
Cc: Tom Lane; Rob Sargent; Dan Livingston; Karsten Hilbert; pgsql-general@lists.postgresql.org
Subject: Re: database "cdf_100_1313" does not exist

DBLIST=/usr/lib/postgresql/11/bin/psql -p 5432 -U postgres -d postgres -q -t -c "SELECT datname from pg_database where datname in ('CDF_100_1313')"

/usr/lib/postgresql/11/bin/psql -p 5432 -U postgres -d $DBLIST -c "GRANT CONNECT ON DATABASE \"$DBLIST\" TO cpupdate"

Ø only in the grant its taking space rest in -d its perfectly all right.

Add –A to the options of the “select datname...” call to psql. The aligned output is adding a space to the front of the value.