Thread: database "cdf_100_1313" does not exist
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
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:
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
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"
/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 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: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 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
ERROR: database "cdf_100_1313" does not exist
On Mon, Sep 9, 2019 at 6:38 PM Dan Livingston <danlivingstone@gmail.com> 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 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: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
Can we see the output of psql’s \l ?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
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:
Can we see the output of psql’s \l ?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 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
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
^
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 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
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:
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
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.