如何在CloudSQL上创建一个只读的PostgreSQL用户？

Question

如何在CloudSQL上创建一个只读的PostgreSQL用户？

5

我以postgres用户身份连接到了我正在使用的数据库，例如app_production。然后，按照在PostgreSQL中创建只读用户中描述的步骤运行了以下命令：

app_production=> GRANT USAGE ON SCHEMA public TO "data-studio";
GRANT
app_production=> GRANT SELECT ON users TO "data-studio";
ERROR:  permission denied for relation users

看起来postgres用户没有足够的权限。我该如何给我的“data-studio”用户读取“users”表的权限？

测试答案 #1

在我新创建的martins_testing表上有效

$ psql -h $HOST -U postgres app_production
app_production=>  create table martins_testing (id int);
CREATE TABLE
app_production=> GRANT SELECT ON martins_testing  TO "data-studio";
GRANT

但是不适用于之前通过运行rake db:create创建的旧用户表。

$ psql -h $HOST -U postgres app_production
app_production=> GRANT SELECT ON users TO "data-studio";
ERROR:  permission denied for relation users

用户表是否使用错误的权限创建？

app_production=> \d users
                               Table "public.users"
     Column     |            Type             |             Modifiers
----------------+-----------------------------+------------------------------------
 id             | uuid                        | not null default gen_random_uuid()
 first_name     | character varying           |
 last_name      | character varying           |
 phone          | character varying           |
 email          | character varying           |
 created_at     | timestamp without time zone | not null
 updated_at     | timestamp without time zone | not null
 birthday       | date                        |
 gender         | integer                     | default 0
 fcm_token      | character varying           |
 device         | integer                     | default 0
 aws_avatar_url | text                        |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "index_users_on_id" btree (id)

列出权限

_production=> \l
                                                  List of databases
       Name        |       Owner       | Encoding |  Collate   |   Ctype    |            Access privileges
-------------------+-------------------+----------+------------+------------+-----------------------------------------
 cloudsqladmin     | cloudsqladmin     | UTF8     | en_US.UTF8 | en_US.UTF8 |
 app_production | cloudsqlsuperuser | UTF8     | en_US.UTF8 | en_US.UTF8 | =Tc/cloudsqlsuperuser                  +
                   |                   |          |            |            | cloudsqlsuperuser=CTc/cloudsqlsuperuser+
                   |                   |          |            |            | "data-studio"=c/cloudsqlsuperuser      +
                   |                   |          |            |            | datastudio=c/cloudsqlsuperuser
 postgres          | cloudsqlsuperuser | UTF8     | en_US.UTF8 | en_US.UTF8 |
 template0         | cloudsqladmin     | UTF8     | en_US.UTF8 | en_US.UTF8 | =c/cloudsqladmin                       +
                   |                   |          |            |            | cloudsqladmin=CTc/cloudsqladmin
 template1         | cloudsqlsuperuser | UTF8     | en_US.UTF8 | en_US.UTF8 | =c/cloudsqlsuperuser                   +
                   |                   |          |            |            | cloudsqlsuperuser=CTc/cloudsqlsuperuser
(5 rows)

- martins

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- gabidavila · Accepted Answer

我看了你提到的页面，并按照以下步骤操作：

连接到Cloud SQL实例：

psql -h $HOST -U postgres -W -d app_development
Password for user postgres:
psql (9.6.10, server 9.6.6)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES128-GCM-SHA256, bits: 128, compression: off)
Type "help" for help.

创建了表格users：

app_development=> create table users (id int);
CREATE TABLE

创建了名为"data-studio"的用户/角色，并授予该模式的连接权限：

app_development=> CREATE USER "data-studio";
CREATE ROLE
app_development=> \password "data-studio"
Enter new password:
Enter it again:
app_development=> GRANT CONNECT ON DATABASE app_development TO "data-studio";
GRANT

最后，为表授予了SELECT权限：

app_development=> GRANT SELECT ON users TO "data-studio";
GRANT

为了测试是否可行，使用"data-studio"用户进行连接：

psql -h 104.154.148.111 -U "data-studio" -W -d app_development                                                                                                           
Password for user data-studio:
psql (9.6.10, server 9.6.6)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES128-GCM-SHA256, bits: 128, compression: off)
Type "help" for help.

app_development=> select * from users;
 id
----
(0 rows)

app_development=> insert into users (id) values (1);
ERROR:  permission denied

这个链接上有一篇评论，指出第一个命令是错误的。希望这可以帮到你。