can't grant user privileges to postgresql database (for a rails app)

Question

I combed through: http://www.postgresql.org/docs/current/interactive/app-psql.html#APP-PSQL-META-COMMANDSf and https://www.digitalocean.com/community/articles/how-to-use-roles-and-manage-grant-permissions-in-postgresql-on-a-vps--2 but still couldn't get this to work.

I did this to get postgres to work locally: 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

This works:

postgres=# CREATE database lateraldev; CREATE DATABASE postgres=# \l 

Now I try to grant privileges to lateraldev to the user, pavankat:

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:

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.

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:

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!

Answer 1

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