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

Question

我以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用户没有足够的权限。如何让我的“数据-工作室”用户读取访问“用户”表？

测试答案#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

是用错误的权限创建的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)

Answer 1

我查看了您提到的页面，并执行了以下步骤：

连接到Cloud实例：

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”，并授予到该架构的连接：

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”用户连接：

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 

关于这个链接有一个评论，表明第一个命令是错误的。我希望这能帮到你。