database "cdf_100_1313" does not exist

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|

database "cdf_100_1313" does not exist

nikhil raj

Hi All,

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

Reply | Threaded
Open this post in threaded view
|

Re: database "cdf_100_1313" does not exist

Karsten Hilbert
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


Reply | Threaded
Open this post in threaded view
|

Re: database "cdf_100_1313" does not exist

nikhil raj
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 <[hidden email]> wrote:
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


Reply | Threaded
Open this post in threaded view
|

Re: database "cdf_100_1313" does not exist

Dan Livingston

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 <[hidden email]> wrote:
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 <[hidden email]> wrote:
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


Reply | Threaded
Open this post in threaded view
|

Re: database "cdf_100_1313" does not exist

nikhil raj
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 <[hidden email]> 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 <[hidden email]> wrote:
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 <[hidden email]> wrote:
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


lup
Reply | Threaded
Open this post in threaded view
|

Re: database "cdf_100_1313" does not exist

lup


On Sep 9, 2019, at 7:16 AM, nikhil raj <[hidden email]> 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 ?
Reply | Threaded
Open this post in threaded view
|

Re: database "cdf_100_1313" does not exist

nikhil raj
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 <[hidden email]> wrote:


On Sep 9, 2019, at 7:16 AM, nikhil raj <[hidden email]> 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 ?
Reply | Threaded
Open this post in threaded view
|

Re: database "cdf_100_1313" does not exist

Tom Lane-2
In reply to this post by lup
> On Sep 9, 2019, at 7:16 AM, nikhil raj <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: database "cdf_100_1313" does not exist

nikhil raj
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 <[hidden email]> wrote:
> On Sep 9, 2019, at 7:16 AM, nikhil raj <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: database "cdf_100_1313" does not exist

Adrian Klaver-4
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 <[hidden email]
> <mailto:[hidden email]>> wrote:
>
>      > On Sep 9, 2019, at 7:16 AM, nikhil raj <[hidden email]
>     <mailto:[hidden email]>> 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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: database "cdf_100_1313" does not exist

nikhil raj
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 <[hidden email]> 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 <[hidden email]
> <mailto:[hidden email]>> wrote:
>
>      > On Sep 9, 2019, at 7:16 AM, nikhil raj <[hidden email]
>     <mailto:[hidden email]>> 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
[hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: database "cdf_100_1313" does not exist

Daniel Verite
In reply to this post by nikhil raj
        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


Reply | Threaded
Open this post in threaded view
|

RE: database "cdf_100_1313" does not exist

Klint Gore-2
In reply to this post by nikhil raj

From: nikhil raj [mailto:[hidden email]]
Sent: Tuesday, 10 September 2019 12:39 AM
To: Adrian Klaver
Cc: Tom Lane; Rob Sargent; Dan Livingston; Karsten Hilbert; [hidden email]
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.