Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jOmdh-0002hs-Cu for pgsql-admin@arkaria.postgresql.org; Wed, 15 Apr 2020 18:19:57 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jOmdg-0000j5-9t for pgsql-admin@arkaria.postgresql.org; Wed, 15 Apr 2020 18:19:56 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jOmdf-0000iy-RQ for pgsql-admin@lists.postgresql.org; Wed, 15 Apr 2020 18:19:56 +0000 Received: from mail-qt1-x842.google.com ([2607:f8b0:4864:20::842]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1jOmdZ-0005dr-8b for pgsql-admin@postgresql.org; Wed, 15 Apr 2020 18:19:54 +0000 Received: by mail-qt1-x842.google.com with SMTP id q17so13843033qtp.4 for ; Wed, 15 Apr 2020 11:19:49 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=content-transfer-encoding:from:mime-version:subject:date:message-id :references:cc:in-reply-to:to; bh=hPsVWDC968kjQDqq1R2zDzbnz/Ei0oVFXsRXAjIZsq4=; b=Gauwnmj8uuHWFpmJ7/rHd10XqyUQP3Prbr+Oza6bKyTnRjDhmFJVHxfTd0auHFVw45 ZE0bXtmYZSQD0j5AMw5e8+tydL8d53kmx0imBOyYDxuvnz/oF0Pou/bPoMCqWlk0FfI5 f6p7QNvdBia1btAVobKZzH/bpQc8SxlJmQi4tO2BkWRCrfdxCK9VlOgqNTnb/ddDlEpU pUAvMG7w3GkP7b4ALlxW7Z+sm5/TdKgWah2QLbPXFnlI4sTHkW+DLv+SSXB9CBnULSMJ E2pW9lcMB797ay9lJu+BAShqKhcyIRNe9qrHJl8BSPVg+FwbGIorsRmqqFXQXio33p3t 2mPA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:content-transfer-encoding:from:mime-version :subject:date:message-id:references:cc:in-reply-to:to; bh=hPsVWDC968kjQDqq1R2zDzbnz/Ei0oVFXsRXAjIZsq4=; b=Jypa2S5Iv8QB0MUkkKnvmcD9zVhdSZqrRbK9XUaZMRV14axzZ6P1/aR4zxsuwXBk75 tncWtwtKFkip2oCmBBPEsWChGVVKIBvgqu7dGYvxpov5KaR9XVPcFJI9LxsyJRkD6r6d xXLsZzGQpGkMNQdwys5bjWOQg32mSODWwxdzpR0e2Vv/Ypn3ZSuPYl4KTOIGLUf3CA8+ gL/EvIYeYYhbzYO4q1llvInNfPRyeqyZiCqsRMsTdPvQGrE75hHA3vxLcaibvdxx7h9R mXJex8GAtqSK2c18TkQCDThCvc8DpjOJAMzoiRLZpIfoexhf2iwocUAfPosmhZRHj/lN +0wg== X-Gm-Message-State: AGi0PuaNCxc8vdZSIOemIRAzcatz6vyRX9Q0QFYgRRYa2A9dvNQ5U5sM k/mBxh2qh2ZXLDWctDgc6EU= X-Google-Smtp-Source: APiQypI2OCfsR9Ck7CeBH7V+kCefTWNVPZBDZxS89qsRdTF8jP5m5pBegAD2PIbZK523AhHGNKfgXg== X-Received: by 2002:ac8:1757:: with SMTP id u23mr22399062qtk.138.1586974788198; Wed, 15 Apr 2020 11:19:48 -0700 (PDT) Received: from ?IPv6:2603:9000:f609:a03d:b8a2:1cd3:6fe4:6db5? ([2603:9000:f609:a03d:b8a2:1cd3:6fe4:6db5]) by smtp.gmail.com with ESMTPSA id u65sm13053288qkh.100.2020.04.15.11.19.47 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 15 Apr 2020 11:19:47 -0700 (PDT) Content-Type: multipart/alternative; boundary=Apple-Mail-918FCC1F-0EE2-4251-A421-DD1963F7DBB3 Content-Transfer-Encoding: 7bit From: John Scalia Mime-Version: 1.0 (1.0) Subject: Re: priviliges issues Date: Wed, 15 Apr 2020 14:19:45 -0400 Message-Id: <091B62ED-5A46-4AD3-BA1A-62A4C9229507@gmail.com> References: Cc: pgsql-admin , Jeff Janes , postgann2020 s In-Reply-To: To: Ankush Chawla X-Mailer: iPad Mail (17D50) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail-918FCC1F-0EE2-4251-A421-DD1963F7DBB3 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable A couple of things not noted. First do NOT use the postgres database for any= work. That is a system db, not intended for use by any roles. Nor should yo= u use the template0 or template1 databases unless you understand what those a= re going to do. Now, grant the user the ability to connect to the database, t= hen grant permission to the schema if this table is not in the public schema= . Easiest way: GRANT ALL ON DATABASE TO user2; GRANT ALL ON SCHEMA s1 TO user2: But again, I wouldn=E2=80=99t use the postgres database for any work. =E2=80=94 Jay Sent from my iPad > On Apr 15, 2020, at 2:05 PM, Ankush Chawla wrot= e: >=20 > =EF=BB=BF > hi Pgsql-Admin, >=20 > I am new to Postgres and naive in understanding > I tried to create a table in a schema and then given privilege to some oth= er user, still he is not able to access. Below are the things I tried: >=20 > postgres=3D# alter schema s1 owner to user1; > ALTER SCHEMA > postgres=3D# \c postgres user1 > You are now connected to database "postgres" as user "user1". > postgres=3D# create table s1.new (a integer); > CREATE TABLE > postgres=3D# grant select on s1.new to user2 > postgres-# ; > GRANT > postgres=3D# \c postgres user2 > You are now connected to database "postgres" as user "user2". > postgres=3D> select * from s1.new; > ERROR: permission denied for schema s1 > LINE 1: select * from s1.new; > ^ >=20 > Regards > Ankush >=20 >=20 >=20 >=20 >> On Tue, Apr 14, 2020 at 12:36 PM postadmin2020 S = wrote: >> Thanks To all of you.=20 >>=20 >>> On Mon, Apr 13, 2020, 10:54 PM Jeff Janes wrote: >>>> On Sun, Apr 12, 2020 at 8:37 AM postgann2020 s = wrote: >>>=20 >>>> Hi All, >>>>=20 >>>> Good Evening. >>>>=20 >>>> Could someone please suggest the process to resolve the issue. >>>>=20 >>>> Issue : proc state as "Idle_in_transaction" >>>>=20 >>>> Env: Postgres 9.5.15 >>>> Job Schedulers : Tomcat ( For running procs). >>>>=20 >>>> We are using tomcat as scheduler for running few jobs.we are observing o= ne of the proc state as "Idle_in_transaction" and due to this remaining othe= r dependent procs get stucked and causing waiting for ever. >>>>=20 >>>> If we ran same proc from psql and pgadmin we could able to run successf= ully and able to get response and no "Idle_in_transaction" state. >>>>=20 >>>> proc: select msg,rescode from schema.proc_name('arg1','arg2'); >>>=20 >>> If you run just this in psql, you will be in autocommit mode. The state= ment will run in its own transaction which commit as soon as the statement f= inishes. >>>=20 >>> The problem is not with the line you show, it is in what happens before= (a transaction is opened) and after (it is not committed) that line. >>> =20 >>> Cheers, >>>=20 >>> Jeff >=20 >=20 > --=20 > Best Regards, > Ankush Chawla >=20 --Apple-Mail-918FCC1F-0EE2-4251-A421-DD1963F7DBB3 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable A couple of things not noted. First do NOT u= se the postgres database for any work. That is a system db, not intended for= use by any roles. Nor should you use the template0 or template1 databases u= nless you understand what those are going to do. Now, grant the user the abi= lity to connect to the database, then grant permission to the schema if this= table is not in the public schema.

Easiest way:

G= RANT ALL ON DATABASE <db name> TO user2;
GRANT ALL ON SCHEMA= s1 TO user2:

But again, I wouldn=E2=80=99t use the postgres database for any w= ork.
=E2=80=94
Jay

Sent from my iPad
=
On Apr 15, 2020, at 2:05 PM, Ankush Chawla <= ;ankushchawla03@gmail.com> wrote:

=EF=BB=BF
hi Pgsql-A= dmin,

I am new to Postgres and naive in understandi= ng
I tried to create a table in a schema and then given privi= lege to some other user, still he is not able to access. Below are the thing= s I tried:

postgres=3D# alter schema s1 owner t= o user1;
ALTER SCHEMA
postgres=3D# \c postgres user1
You= are now connected to database "postgres" as user "user1".
pos= tgres=3D# create  table s1.new (a integer);
CREATE TABLE
postgres=3D# grant select on s1.new to user2
postgres-# ;
GRANT
=
postgres=3D# \c postgres user2
You are now connected to databa= se "postgres" as user "user2".
postgres=3D> select * from s1.new;
E= RROR:  permission denied for schema s1
LINE 1: select * from s1.new;=
                    &n= bsp; ^

Regards
Ankush
<= br>



On Tue, Apr 14, 2020 at 12:36 PM postadmin20= 20 S <postgadm2020@gmail.com> wrote:

=
On Sun, Apr 12, 2020 at 8:37 AM pos= tgann2020 s <postgann2020@gmail.com> wrote:
Hi All,

Good Evening.

Could someone please suggest the process to resolve t= he issue.

Issue : proc state as "Idle_in_transaction"

E= nv: Postgres 9.5.15
Job Schedulers : Tomcat ( For running procs).

= We are using tomcat as scheduler for running few jobs.we are observing one o= f the proc state as "Idle_in_transaction" and due to this remaining other de= pendent procs get stucked and causing waiting for ever.

If we ran sam= e proc from psql and pgadmin we could able to run successfully and able to g= et response and no "Idle_in_transaction" state.

proc: select msg,resc= ode from schema.proc_name('arg1','arg2');
<= br>
If you run just this in psql, you will be in autocommit mode.&= nbsp; The statement will run in its own transaction which commit as soon as t= he statement finishes.

The problem is not with the&= nbsp;line you show,  it is in what happens before (a transaction is ope= ned) and after (it is not committed) that line.
 
C= heers,

Jeff


--
Best Regards,
Ankush Chawla

= --Apple-Mail-918FCC1F-0EE2-4251-A421-DD1963F7DBB3--