我想在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。
当前回答
我阅读了所有可能的解决方案，这些都很好，如果你记得在你授予这些东西之前连接到数据库;)无论如何，感谢所有其他解决方案!!
user@server:~$ sudo su - postgres
创建PSQL用户:
postgres@server:~$ createuser --interactive
Enter name of role to add: readonly
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
启动PSQL命令行，为创建的用户设置密码:
postgres@server:~$ psql
psql (10.6 (Ubuntu 10.6-0ubuntu0.18.04.1), server 9.5.14)
Type "help" for help.
postgres=# alter user readonly with password 'readonly';
ALTER ROLE
连接到目标数据库:
postgres=# \c target_database
psql (10.6 (Ubuntu 10.6-0ubuntu0.18.04.1), server 9.5.14)
You are now connected to database "target_database" as user "postgres".
授予所有需要的特权:
target_database=# GRANT CONNECT ON DATABASE target_database TO readonly;
GRANT
target_database=# GRANT USAGE ON SCHEMA public TO readonly ;
GRANT
target_database=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly ;
GRANT
修改目标db public shema的默认权限:
target_database=# ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;
ALTER DEFAULT PRIVILEGES
其他回答
下面是我发现的添加只读用户的最好方法(使用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
默认情况下，新用户将拥有创建表的权限。如果您计划创建只读用户，这可能不是您想要的。
以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;
从PostgreSQL v14开始，你可以通过授予预定义的pg_read_all_data角色来实现:
GRANT pg_read_all_data TO xxx;
请注意PostgreSQL 9.0(今天在beta测试中)将有一个简单的方法来做到这一点:
test=> GRANT SELECT ON ALL TABLES IN SCHEMA public TO joeuser;
不直接的方法是在数据库的每个表上授权选择:
postgres=# grant select on db_name.table_name to read_only_user;
您可以通过从数据库元数据生成授权语句来实现自动化。
