From: Rohit Rajput <rht.rajput@yahoo.com>
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
To: Yambu <hyambu@gmail.com>
Subject: Re: Create Read only user
Date: Fri, 22 Jan 2021 09:05:13 +0000 (UTC)
Message-ID: <1549478926.1190866.1611306313653@mail.yahoo.com> (raw)
In-Reply-To: <CALhHtcA5CYQU0SLU673qAdZiixAJJ8GF2ZEJV7YSiq03h5W-jw@mail.gmail.com>
References: <CALhHtcA5CYQU0SLU673qAdZiixAJJ8GF2ZEJV7YSiq03h5W-jw@mail.gmail.com>
By default, every user has permission to do anything on public schema. Because users are created outside of databases here. If you want to users not to have any permission when those are created, use this:
REVOKE ALL ON SCHEMA public FROM PUBLIC;
and now provide permissions what you want to specific users.
GRANT ALL/SELECT/.... ON SCHEMA public to your_user;
Cheers On Friday, 22 January, 2021, 02:29:31 pm IST, Yambu <hyambu@gmail.com> wrote:
Hello
I created a user like this
CREATE USER user1 WITH PASSWORD '<password>';
GRANT CONNECT ON DATABASE db1 TO user1;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO user1;
What amazes me is that when I connect using user1, I'm able to create a table and drop it. How can this be?
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: rht.rajput@yahoo.com, pgsql-admin@lists.postgresql.org, hyambu@gmail.com
Subject: Re: Create Read only user
In-Reply-To: <1549478926.1190866.1611306313653@mail.yahoo.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