Re: priviliges issues

[Mohammed Afsar <vmdapsar@xxxxxxxxx>]

Hi ankush,

Execute below mentioned commands with postgres user and try access to tables with required user it will work.

1.GRANT USAGE ON SCHEMA schema_name TO username;

2.GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO username;

Regards,

Mohammed Afsar

Database Engineer

On Wed, Apr 15, 2020, 11:35 PM Ankush Chawla <ankushchawla03@xxxxxxxxx> wrote:

hi Pgsql-Admin,

I am new to Postgres and naive in understanding

I tried to create a table in a schema and then given privilege to some other user, still he is not able to access. Below are the things I tried:

postgres=# alter schema s1 owner to user1;
ALTER SCHEMA

postgres=# \c postgres user1
You are now connected to database "postgres" as user "user1".

postgres=# create  table s1.new (a integer);
CREATE TABLE

postgres=# grant select on s1.new to user2
postgres-# ;
GRANT

postgres=# \c postgres user2
You are now connected to database "postgres" as user "user2".
postgres=> select * from s1.new;
ERROR:  permission denied for schema s1
LINE 1: select * from s1.new;
                      ^

Regards

Ankush

On Tue, Apr 14, 2020 at 12:36 PM postadmin2020 S <postgadm2020@xxxxxxxxx> wrote:

Thanks To all of you. 

On Mon, Apr 13, 2020, 10:54 PM Jeff Janes <jeff.janes@xxxxxxxxx> wrote:

On Sun, Apr 12, 2020 at 8:37 AM postgann2020 s <postgann2020@xxxxxxxxx> wrote:

Hi All,

Good Evening.

Could someone please suggest the process to resolve the issue.

Issue : proc state as "Idle_in_transaction"

Env: Postgres 9.5.15
Job Schedulers : Tomcat ( For running procs).

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

If we ran same proc from psql and pgadmin we could able to run successfully and able to get response and no "Idle_in_transaction" state.

proc: select msg,rescode from schema.proc_name('arg1','arg2');

If you run just this in psql, you will be in autocommit mode.  The statement will run in its own transaction which commit as soon as the statement finishes.

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.

 

Cheers,

Jeff

--

Best Regards,
Ankush Chawla