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