我想在PostgreSQL中创建一个用户，只能从特定的数据库中进行select。在MySQL中，命令是:
GRANT SELECT ON mydb.* TO 'xxx'@'%' IDENTIFIED BY 'yyy';
PostgreSQL中等价的命令或命令系列是什么?
我试着…
postgres=# CREATE ROLE xxx LOGIN PASSWORD 'yyy';
postgres=# GRANT SELECT ON DATABASE mydb TO xxx;
但是，似乎您可以在数据库上授予的权限只有CREATE、CONNECT、TEMPORARY和TEMP。
当前回答
参考本博客:
创建只读用户脚本:
CREATE ROLE Read_Only_User WITH LOGIN PASSWORD 'Test1234'
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL 'infinity';
\connect YourDatabaseName;
为该只读用户分配权限:
GRANT CONNECT ON DATABASE YourDatabaseName TO Read_Only_User;
GRANT USAGE ON SCHEMA public TO Read_Only_User;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO Read_Only_User;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO Read_Only_User;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
分配权限以读取将来创建的所有新表
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO Read_Only_User;
其他回答
默认情况下，新用户将拥有创建表的权限。如果您计划创建只读用户，这可能不是您想要的。
以PostgreSQL 9.0+为例，创建真正的只读用户。
# This will prevent default users from creating tables
REVOKE CREATE ON SCHEMA public FROM public;
# If you want to grant a write user permission to create tables
# note that superusers will always be able to create tables anyway
GRANT CREATE ON SCHEMA public to writeuser;
# Now create the read-only user
CREATE ROLE readonlyuser WITH LOGIN ENCRYPTED PASSWORD 'strongpassword';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonlyuser;
如果您的只读用户没有列出表的权限(即\d没有返回结果)，这可能是因为您没有模式的USAGE权限。USAGE是允许用户实际使用被分配的权限的权限。这有什么意义?我不确定。修复:
# You can either grant USAGE to everyone
GRANT USAGE ON SCHEMA public TO public;
# Or grant it just to your read only user
GRANT USAGE ON SCHEMA public TO readonlyuser;
我为此创建了一个方便的脚本;pg_grant_read_to_db.sh。该脚本将数据库模式中所有表、视图和序列的只读权限授予指定角色，并将其设置为默认值。
参考本博客:
创建只读用户脚本:
CREATE ROLE Read_Only_User WITH LOGIN PASSWORD 'Test1234'
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL 'infinity';
\connect YourDatabaseName;
为该只读用户分配权限:
GRANT CONNECT ON DATABASE YourDatabaseName TO Read_Only_User;
GRANT USAGE ON SCHEMA public TO Read_Only_User;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO Read_Only_User;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO Read_Only_User;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
分配权限以读取将来创建的所有新表
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO Read_Only_User;
摘自回复despesz的链接。
Postgres 9。X似乎有能力完成所请求的事情。参见Grant On Database Objects段落:
http://www.postgresql.org/docs/current/interactive/sql-grant.html
它说:“还有一个选项可以授予一个或多个模式中相同类型的所有对象的特权。该功能目前仅支持表、序列和函数(但请注意，ALL tables被认为包括视图和外部表)。”
本页还讨论了角色和称为“ALL PRIVILEGES”的特权的使用。
同时还介绍了GRANT功能与SQL标准的比较情况。
下面是我发现的添加只读用户的最好方法(使用PostgreSQL 9.0或更新版本):
$ sudo -upostgres psql postgres
postgres=# CREATE ROLE readonly WITH LOGIN ENCRYPTED PASSWORD '<USE_A_NICE_STRONG_PASSWORD_PLEASE';
postgres=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
然后登录到所有相关的机器(主机+读-从机/热备机等)并运行:
$ echo "hostssl <PUT_DBNAME_HERE> <PUT_READONLY_USERNAME_HERE> 0.0.0.0/0 md5" | sudo tee -a /etc/postgresql/9.2/main/pg_hba.conf
$ sudo service postgresql reload
