CODEWITHSUNDEEP
postgresqlpsql
Pavan Katepalli
Pavan Katepalli

Reputation: 2464

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

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!

Upvotes: 22

Views: 48504

Answers (4)

Sammy Iyke Ibeh
Sammy Iyke Ibeh

Reputation: 31

Try sending as a psql command instead.

psql -c "GRANT ALL ON lateraldev TO pavankat";

Upvotes: 2

9_Dave_9
9_Dave_9

Reputation: 714

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.

GRANT ALL PRIVILEGES ON DATABASE my_newly_created_db_name TO my_user_name;

http://www.postgresql.org/message-id/[email protected]

Upvotes: 17

Bruno
Bruno

Reputation: 641

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

Upvotes: 25

OneChillDude
OneChillDude

Reputation: 8006

Try making the user with CREATEDB permission, and then you can create the database with that user. The user will have permission on that database from then on.

CREATE USER myuser CREATEDB;

I know this isn't a perfect solution, but I hope this works out for you

-Brian

Upvotes: 11

Related Questions