无法向 postgresql 数据库授予用户权限（对于 rails 应用程序）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20775462/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
can't grant user privileges to postgresql database (for a rails app)
提问by Pavan Katepalli
I combed through: http://www.postgresql.org/docs/current/interactive/app-psql.html#APP-PSQL-META-COMMANDSfand https://www.digitalocean.com/community/articles/how-to-use-roles-and-manage-grant-permissions-in-postgresql-on-a-vps--2but still couldn't get this to work.
我梳理了：http: //www.postgresql.org/docs/current/interactive/app-psql.html#APP-PSQL-META-COMMANDSf和https://www.digitalocean.com/community/articles/how- to-use-roles-and-manage-grant-permissions-in-postgresql-on-a-vps--2但仍然无法让它工作。
I did this to get postgres to work locally: https://askubuntu.com/questions/42013/problem-installing-and-configuring-postgresql.
我这样做是为了让 postgres 在本地工作：https: //askubuntu.com/questions/42013/problem-installing-and-configuring-postgresql。
pavan@myUbuntuLaptop% which psql
/usr/bin/psql
this got me in:
这让我进入：
pavan@myUbuntuLaptop% sudo su - postgres
[sudo] password for pavan:
postgres@myUbuntuLaptop%
So...
所以...
postgres@myUbuntuLaptop% createuser pavankat
Shall the new role be a superuser? (y/n) y
postgres@myUbuntuLaptop%
this gets all the roles
这将获得所有角色
\du
that shows pavankat, so the role was made
这显示了pavankat，所以这个角色被制作了
This works:
这有效：
postgres=# CREATE database lateraldev;
CREATE DATABASE
postgres=# \l
Now I try to grant privileges to lateraldev to the user, pavankat:
现在我尝试向用户 pavankat 授予对lateraldev 的权限：
this doesn't work:
这不起作用：
GRANT RULE ON lateraldev to pavankat
doesn't work:
不起作用：
GRANT ALL ON lateraldev TO pavankat;
doesn't work:
不起作用：
postgres=# GRANT ALL ON lateraldev TO pavankat;
ERROR: relation "lateraldev" does not exist
this looked like it worked but, \du didn't show anything:
这看起来很有效，但是 \du 没有显示任何内容：
postgres=# GRANT ALL ON DATABASE lateraldev TO pavankat;
GRANT
same with this, doesn't do it:
与此相同，不这样做：
postgres=# GRANT ALL ON ALL TABLES IN SCHEMA public TO pavankat;
GRANT
postgres=# GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO pavankat;
GRANT
postgres=# GRANT ALL ON ALL FUNCTIONS IN SCHEMA public TO pavankat;
GRANT
postgres=# \du
I used this post: http://imaginaryrobots.wordpress.com/2010/03/10/grant-all-in-postgres/and copied the shell script into the rails app.
我使用了这篇文章：http: //imaginaryrobots.wordpress.com/2010/03/10/grant-all-in-postgres/并将 shell 脚本复制到 rails 应用程序中。
then ran (to make the file executable):
然后运行（使文件可执行）：
pavan@myUbuntuLaptop% chmod 755 give_permissions_script.sh
and it doesn't work:
它不起作用：
postgres@myUbuntuLaptop$ ./give_permissions_script.sh
GRANT ALL ON DATABASE lateraldev TO pavankat;
could not change directory to "/home/pavan/Dropbox/venturelateral"
could not change directory to "/home/pavan/Dropbox/venturelateral"
tried this out: http://smokeandumami.com/2009/11/11/grant-permissions-on-all-tables-and-sequences-in-postgresql/and it seems to have done something, but doesn't work:
试了一下：http: //smokeandumami.com/2009/11/11/grant-permissions-on-all-tables-and-sequences-in-postgresql/它似乎做了一些事情，但不起作用：
pavan@myUbuntuLaptop% chmod 755 give_permissions_script2.sh
pavan@myUbuntuLaptop% ./give_permissions_script2.sh
GRANT SELECT,UPDATE,DELETE,INSERT ON TABLE public.relname to pavankat;
see:
看：
postgres=# grant all privileges on database lateraldev to pavan;
GRANT
postgres=# \du
This doesn't work either: connect to the database first:
这也不起作用：首先连接到数据库：
postgres=# psql lateraldev
lateraldev=# GRANT ALL ON DATABASE lateraldev TO pavankat;
GRANT
lateraldev=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
pavan | Superuser, Create role, Create DB, Replication | {}
pavankat | Superuser, Create role, Create DB, Replication | {}
postgres | Superuser, Create role, Create DB, Replication | {}
I'm out of ideas. Help me please?
我没有想法了。请帮帮我？
Merry Christmas and Happy Holidays!
圣诞快乐，节日快乐！
回答by Bruno
have you tried:
你有没有尝试过：
--Change the database ownership
alter database lateraldev owner to pavan;
--and do the same for all tables
psql -tc "select 'alter table ' || tablename || ' owner to pavan;' from pg_tables where schemaname not in ('pg_catalog', 'information_schema');" lateraldev | psql -a lateraldev
回答by user1230795
so after some digging around. Found this on the postgres website. Essentially anything other than a table name needs the type to be explicitly called when granting the permission.
所以经过一些挖掘。在 postgres 网站上找到了这个。基本上除了表名之外的任何东西都需要在授予权限时显式调用该类型。
GRANT ALL PRIVILEGES ON DATABASE my_newly_created_db_name TO my_user_name;
回答by OneChillDude
Try making the user with CREATEDBpermission, and then you can create the database with that user. The user will have permission on that database from then on.
尝试使用户具有CREATEDB权限，然后您可以使用该用户创建数据库。从那时起，用户将拥有对该数据库的权限。
CREATE USER myuser CREATEDB;
I know this isn't a perfect solution, but I hope this works out for you
我知道这不是一个完美的解决方案，但我希望这对你有用
-Brian
-布莱恩
回答by Sammy Iyke Ibeh
Try sending as a psql command instead.
尝试作为 psql 命令发送。
psql -c "GRANT ALL ON lateraldev TO pavankat";
