pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedGrant all privileges to user on a database
7+ messages / 4 participants
[nested] [flat]
* Grant all privileges to user on a database
@ 2023-07-24 13:15 Kaushal Shriyan <kaushalshriyan@gmail.com>
2023-07-24 13:21 ` Re: Grant all privileges to user on a database Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Kaushal Shriyan @ 2023-07-24 13:15 UTC (permalink / raw)
To: pgsql-general
Hi,
I am running postgresql15-server 15.3 on Red Hat Enterprise Linux
release 8.7 (Ootpa)
# rpm -qa | grep -i post
postgresql15-server-15.3-2PGDG.rhel8.x86_64
postgresql15-libs-15.3-2PGDG.rhel8.x86_64
postgresql15-15.3-2PGDG.rhel8.x86_64
#
$psql
psql (15.3)
Type "help" for help.
postgres=# GRANT ALL PRIVILEGES ON DATABASE cbdevdb TO cbdevdbadmin;
postgres=# \du
List of roles
Role name | Attributes
| Member of
--------------+------------------------------------------------------------+-----------
cbdevdbadmin | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres=#
Am I missing something? Please suggest. Thanks in advance.
Best Regards,
Kaushal
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Grant all privileges to user on a database
2023-07-24 13:15 Grant all privileges to user on a database Kaushal Shriyan <kaushalshriyan@gmail.com>
@ 2023-07-24 13:21 ` Ron <ronljohnsonjr@gmail.com>
2023-07-24 13:38 ` Re: Grant all privileges to user on a database Kaushal Shriyan <kaushalshriyan@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Ron @ 2023-07-24 13:21 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
On 7/24/23 08:15, Kaushal Shriyan wrote:
> Hi,
>
> I am running postgresql15-server 15.3 on Red Hat Enterprise Linux
> release 8.7 (Ootpa)
>
> # rpm -qa | grep -i post
> postgresql15-server-15.3-2PGDG.rhel8.x86_64
> postgresql15-libs-15.3-2PGDG.rhel8.x86_64
> postgresql15-15.3-2PGDG.rhel8.x86_64
> #
>
> $psql
> psql (15.3)
> Type "help" for help.
>
> postgres=# GRANT ALL PRIVILEGES ON DATABASE cbdevdb TO cbdevdbadmin;
> postgres=# \du
> List of roles
> Role name | Attributes
> | Member of
> --------------+------------------------------------------------------------+-----------
> cbdevdbadmin | | {}
> postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
>
> postgres=#
>
> Am I missing something? Please suggest. Thanks in advance.
No, you aren't. Role attributes are not the same as table privileges.
--
Born in Arizona, moved to Babylonia.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Grant all privileges to user on a database
2023-07-24 13:15 Grant all privileges to user on a database Kaushal Shriyan <kaushalshriyan@gmail.com>
2023-07-24 13:21 ` Re: Grant all privileges to user on a database Ron <ronljohnsonjr@gmail.com>
@ 2023-07-24 13:38 ` Kaushal Shriyan <kaushalshriyan@gmail.com>
2023-07-24 13:56 ` Re: Grant all privileges to user on a database David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Kaushal Shriyan @ 2023-07-24 13:38 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
On Mon, Jul 24, 2023 at 6:51 PM Ron <ronljohnsonjr@gmail.com> wrote:
>
> On 7/24/23 08:15, Kaushal Shriyan wrote:
> > Hi,
> >
> > I am running postgresql15-server 15.3 on Red Hat Enterprise Linux
> > release 8.7 (Ootpa)
> >
> > # rpm -qa | grep -i post
> > postgresql15-server-15.3-2PGDG.rhel8.x86_64
> > postgresql15-libs-15.3-2PGDG.rhel8.x86_64
> > postgresql15-15.3-2PGDG.rhel8.x86_64
> > #
> >
> > $psql
> > psql (15.3)
> > Type "help" for help.
> >
> > postgres=# GRANT ALL PRIVILEGES ON DATABASE cbdevdb TO cbdevdbadmin;
> > postgres=# \du
> > List of roles
> > Role name | Attributes
> > | Member of
> >
--------------+------------------------------------------------------------+-----------
> > cbdevdbadmin |
| {}
> > postgres | Superuser, Create role, Create DB, Replication, Bypass
RLS | {}
> >
> > postgres=#
> >
> > Am I missing something? Please suggest. Thanks in advance.
>
> No, you aren't. Role attributes are not the same as table privileges.
>
>
> --
> Born in Arizona, moved to Babylonia.
>
>
Thanks Ron for the quick response and appreciate it. I am currently facing
the below error while configuring the PostgreSQL 15.3 from Drupal CMS
version 9.5.10 (https://www.drupal.org/project/drupal/) web interface.
Failed to CREATE a test table on your database server with the command
CREATE TABLE {drupal_install_test} (id int NOT NULL PRIMARY KEY). The
server reports the following message: SQLSTATE[42501]: Insufficient
privilege: 7 ERROR: permission denied for schema public LINE 1: CREATE
TABLE "drupal_install_test" (id int NOT NULL PRIMARY ... ^: CREATE TABLE
"drupal_install_test" (id int NOT NULL PRIMARY KEY); Array ( ) .
Are you sure the configured username has the necessary permissions to
create tables in the database?
Please suggest. Thanks in advance.
Best Regards,
Kaushal
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Grant all privileges to user on a database
2023-07-24 13:15 Grant all privileges to user on a database Kaushal Shriyan <kaushalshriyan@gmail.com>
2023-07-24 13:21 ` Re: Grant all privileges to user on a database Ron <ronljohnsonjr@gmail.com>
2023-07-24 13:38 ` Re: Grant all privileges to user on a database Kaushal Shriyan <kaushalshriyan@gmail.com>
@ 2023-07-24 13:56 ` David G. Johnston <david.g.johnston@gmail.com>
2023-07-24 14:09 ` Re: Grant all privileges to user on a database Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: David G. Johnston @ 2023-07-24 13:56 UTC (permalink / raw)
To: Kaushal Shriyan <kaushalshriyan@gmail.com>; +Cc: Ron <ronljohnsonjr@gmail.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
On Monday, July 24, 2023, Kaushal Shriyan <kaushalshriyan@gmail.com> wrote:
>
>
> Are you sure the configured username has the necessary permissions to
> create tables in the database?
>
The error message is misleading, you can’t directly create tables in a
database, you must create them in a schema and the permission to do so is
granted to the role on the schema, not the database.
David J.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Grant all privileges to user on a database
2023-07-24 13:15 Grant all privileges to user on a database Kaushal Shriyan <kaushalshriyan@gmail.com>
2023-07-24 13:21 ` Re: Grant all privileges to user on a database Ron <ronljohnsonjr@gmail.com>
2023-07-24 13:38 ` Re: Grant all privileges to user on a database Kaushal Shriyan <kaushalshriyan@gmail.com>
2023-07-24 13:56 ` Re: Grant all privileges to user on a database David G. Johnston <david.g.johnston@gmail.com>
@ 2023-07-24 14:09 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-07-24 14:51 ` Re: Grant all privileges to user on a database Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2023-07-24 14:09 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Kaushal Shriyan <kaushalshriyan@gmail.com>; Ron <ronljohnsonjr@gmail.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> The error message is misleading, you can’t directly create tables in a
> database, you must create them in a schema and the permission to do so is
> granted to the role on the schema, not the database.
The actual server message is going to be just
ERROR: permission denied for schema public
I'm not sure where the "Are you sure ..." bit came from, but it wasn't
Postgres.
Anyway, backing up two steps, it looks like drupal hasn't yet adapted
to the v15 change that removed default create privileges on the public
schema (for security reasons). You might see if a newer drupal release
is available. If not, the best bet would likely be
GRANT ALL ON SCHEMA public TO public;
If you run into more problems, a more drastic answer might be to
downgrade to whatever drupal considers a supported release of
Postgres.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Grant all privileges to user on a database
2023-07-24 13:15 Grant all privileges to user on a database Kaushal Shriyan <kaushalshriyan@gmail.com>
2023-07-24 13:21 ` Re: Grant all privileges to user on a database Ron <ronljohnsonjr@gmail.com>
2023-07-24 13:38 ` Re: Grant all privileges to user on a database Kaushal Shriyan <kaushalshriyan@gmail.com>
2023-07-24 13:56 ` Re: Grant all privileges to user on a database David G. Johnston <david.g.johnston@gmail.com>
2023-07-24 14:09 ` Re: Grant all privileges to user on a database Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-07-24 14:51 ` Ron <ronljohnsonjr@gmail.com>
2023-07-24 14:59 ` Re: Grant all privileges to user on a database David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Ron @ 2023-07-24 14:51 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
On 7/24/23 09:09, Tom Lane wrote:
> "David G. Johnston" <david.g.johnston@gmail.com> writes:
>> The error message is misleading, you can’t directly create tables in a
>> database, you must create them in a schema and the permission to do so is
>> granted to the role on the schema, not the database.
> The actual server message is going to be just
>
> ERROR: permission denied for schema public
>
> I'm not sure where the "Are you sure ..." bit came from, but it wasn't
> Postgres.
>
> Anyway, backing up two steps, it looks like drupal hasn't yet adapted
> to the v15 change that removed default create privileges on the public
> schema (for security reasons). You might see if a newer drupal release
> is available. If not, the best bet would likely be
>
> GRANT ALL ON SCHEMA public TO public;
I'd have naively expected "GRANT ALL ON SCHEMA public TO public; " to be
taken care of by "GRANT ALL PRIVILEGES ON DATABASE cbdevdb TO cbdevdbadmin;".
--
Born in Arizona, moved to Babylonia.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Grant all privileges to user on a database
2023-07-24 13:15 Grant all privileges to user on a database Kaushal Shriyan <kaushalshriyan@gmail.com>
2023-07-24 13:21 ` Re: Grant all privileges to user on a database Ron <ronljohnsonjr@gmail.com>
2023-07-24 13:38 ` Re: Grant all privileges to user on a database Kaushal Shriyan <kaushalshriyan@gmail.com>
2023-07-24 13:56 ` Re: Grant all privileges to user on a database David G. Johnston <david.g.johnston@gmail.com>
2023-07-24 14:09 ` Re: Grant all privileges to user on a database Tom Lane <tgl@sss.pgh.pa.us>
2023-07-24 14:51 ` Re: Grant all privileges to user on a database Ron <ronljohnsonjr@gmail.com>
@ 2023-07-24 14:59 ` David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: David G. Johnston @ 2023-07-24 14:59 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
On Mon, Jul 24, 2023 at 7:52 AM Ron <ronljohnsonjr@gmail.com> wrote:
> On 7/24/23 09:09, Tom Lane wrote:
>
> > GRANT ALL ON SCHEMA public TO public;
>
> I'd have naively expected "GRANT ALL ON SCHEMA public TO public; " to be
> taken care of by "GRANT ALL PRIVILEGES ON DATABASE cbdevdb TO
> cbdevdbadmin;".
>
>
I'm quite happy that such a command doesn't go ahead and grant read, write,
and execute privileges on every table, function, and view in the database.
The thing that does what you describe is called SUPERUSER.
David J.
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2023-07-24 14:59 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-07-24 13:15 Grant all privileges to user on a database Kaushal Shriyan <kaushalshriyan@gmail.com>
2023-07-24 13:21 ` Ron <ronljohnsonjr@gmail.com>
2023-07-24 13:38 ` Kaushal Shriyan <kaushalshriyan@gmail.com>
2023-07-24 13:56 ` David G. Johnston <david.g.johnston@gmail.com>
2023-07-24 14:09 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-07-24 14:51 ` Ron <ronljohnsonjr@gmail.com>
2023-07-24 14:59 ` David G. Johnston <david.g.johnston@gmail.com>
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