From: John Scalia <jayknowsunix@gmail.com>
To: Ankush Chawla <ankushchawla03@gmail.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Cc: Jeff Janes <jeff.janes@gmail.com>
Cc: postgann2020 s <postgann2020@gmail.com>
Subject: Re: priviliges issues
Date: Wed, 15 Apr 2020 14:19:45 -0400
Message-ID: <091B62ED-5A46-4AD3-BA1A-62A4C9229507@gmail.com> (raw)
In-Reply-To: <CAOY8JhCzLCCM8hSwsK0soCEnRMSV-Ov53LG5btNbHAH7w=AgOg@mail.gmail.com>
References: <CAOY8JhCzLCCM8hSwsK0soCEnRMSV-Ov53LG5btNbHAH7w=AgOg@mail.gmail.com>
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 you use the template0 or template1 databases unless you understand what those are going to do. Now, grant the user the ability to connect to the database, then grant permission to the schema if this table is not in the public schema.
Easiest way:
GRANT ALL ON DATABASE <db name> TO user2;
GRANT ALL ON SCHEMA s1 TO user2:
But again, I wouldn’t use the postgres database for any work.
—
Jay
Sent from my iPad
> On Apr 15, 2020, at 2:05 PM, Ankush Chawla <ankushchawla03@gmail.com> 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@gmail.com> wrote:
>> Thanks To all of you.
>>
>>> On Mon, Apr 13, 2020, 10:54 PM Jeff Janes <jeff.janes@gmail.com> wrote:
>>>> On Sun, Apr 12, 2020 at 8:37 AM postgann2020 s <postgann2020@gmail.com> 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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: jayknowsunix@gmail.com, ankushchawla03@gmail.com, jeff.janes@gmail.com, postgann2020@gmail.com
Subject: Re: priviliges issues
In-Reply-To: <091B62ED-5A46-4AD3-BA1A-62A4C9229507@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox