我想在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 USER username SUPERUSER  password 'userpass';
ALTER USER username set default_transaction_read_only = on;

其他回答

请注意PostgreSQL 9.0(今天在beta测试中)将有一个简单的方法来做到这一点:

test=> GRANT SELECT ON ALL TABLES IN SCHEMA public TO joeuser;

如果您的数据库在公共模式中,这很容易(假设您已经创建了readonlyuser)

db=> GRANT SELECT ON ALL TABLES IN SCHEMA public to readonlyuser;
GRANT
db=> GRANT CONNECT ON DATABASE mydatabase to readonlyuser;
GRANT
db=> GRANT SELECT ON ALL SEQUENCES IN SCHEMA public to readonlyuser;
GRANT

如果你的数据库使用customschema,执行上面的命令,但再添加一个命令:

db=> ALTER USER readonlyuser SET search_path=customschema, public;
ALTER ROLE

从PostgreSQL v14开始,你可以通过授予预定义的pg_read_all_data角色来实现:

GRANT pg_read_all_data TO xxx;
CREATE USER username SUPERUSER  password 'userpass';
ALTER USER username set default_transaction_read_only = on;

我阅读了所有可能的解决方案,这些都很好,如果你记得在你授予这些东西之前连接到数据库;)无论如何,感谢所有其他解决方案!!

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