Search Postgresql Archives

Re: grant connect to all databases

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





On Sat, Oct 5, 2024 at 11:26 AM Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
On 10/5/24 09:04, Matt Zagrabelny wrote:
>
>
> On Sat, Oct 5, 2024 at 10:27 AM Adrian Klaver <adrian.klaver@xxxxxxxxxxx
> <mailto:adrian.klaver@xxxxxxxxxxx>> wrote:
>
>     On 10/5/24 07:13, Matt Zagrabelny wrote:
>      > Hi David (and others),
>      >
>      > Thanks for the info about Public.
>      >
>      > I should expound on my original email.
>      >
>      > In our dev and test environments our admins (alice, bob, eve) are
>      > superusers. In production environments we'd like the admins to be
>     read-only.
>
>     What are the REVOKE and GRANT commands you use to achieve that?
>
>
> GRANT alice TO pg_read_all_data;

Does alice have existing GRANTs?

Nope. I create the role (via puppet) and then add the GRANT pg_read_all_data TO (via puppet).
 

I would try:

GRANT pg_read_all_data TO alice;

As example:

psql -d test -U postgres

                      List of role grants
  Role name  |      Member of       |   Options    | Grantor
------------+----------------------+--------------+----------
  aklaver    | app_admin            | INHERIT, SET | postgres
  aklaver    | production           | INHERIT, SET | postgres
  dd_admin   | dd_owner             | ADMIN, SET   | postgres
  dd_user    | dd_admin             | INHERIT, SET | postgres
  pg_monitor | pg_read_all_settings | INHERIT, SET | postgres
  pg_monitor | pg_read_all_stats    | INHERIT, SET | postgres
  pg_monitor | pg_stat_scan_tables  | INHERIT, SET | postgres
  postgres   | dd_owner             | INHERIT, SET | postgres


grant pg_read_all_data to adrian;
GRANT ROLE

test=# \drgS

What is \drgS? I don't believe I have that.
 
                      List of role grants
  Role name  |      Member of       |   Options    | Grantor
------------+----------------------+--------------+----------
  adrian     | pg_read_all_data     | INHERIT, SET | postgres
  aklaver    | app_admin            | INHERIT, SET | postgres
  aklaver    | production           | INHERIT, SET | postgres
  dd_admin   | dd_owner             | ADMIN, SET   | postgres
  dd_user    | dd_admin             | INHERIT, SET | postgres
  pg_monitor | pg_read_all_settings | INHERIT, SET | postgres
  pg_monitor | pg_read_all_stats    | INHERIT, SET | postgres
  pg_monitor | pg_stat_scan_tables  | INHERIT, SET | postgres
  postgres   | dd_owner             | INHERIT, SET | postgres

  \dt csv_test
           List of relations
  Schema |   Name   | Type  |  Owner
--------+----------+-------+----------
  public | csv_test | table | postgres

test=# \q

psql -d test -U adrian

test=> select * from csv_test ;
  id | val
----+------
   1 | test
   2 | dog
   3 | cat
   4 | test
   5 | fish


That looks good.

Here is the output of puppet's create role:

drop role alice;

The next puppet run and I get:

 'CREATE ROLE "alice" ENCRYPTED PASSWORD \'$NEWPGPASSWD\' LOGIN NOCREATEROLE NOCREATEDB NOSUPERUSER  CONNECTION LIMIT -1'
GRANT pg_read_all_data TO alice;

test=# \du
                                             List of roles
      Role name       |                         Attributes                         |     Member of
----------------------+------------------------------------------------------------+--------------------
 alice                |                                                            | {pg_read_all_data}
 postgres             | Superuser, Create role, Create DB, Replication, Bypass RLS | {}


 ...but I still cannot connect:

$ psql -d test -U alice
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  permission denied for database "test"
DETAIL:  User does not have CONNECT privilege.

Thanks for the help!

-m

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux