I am new to PostgreSQL and am having a bit of trouble finding any information on how to grant a user permissions on the database that they own. I have read a few sources that says the owner has all permissions, but it doesn't seem to work for me. I still had to grant permissions in order to run a select statement.

I have also read quite a few things that say that PostgreSQL simply doesn't support granting to multiple tables in a database at once, so I'm hoping that they are just out-of-date.

Here is what I have done so far:

# Create the user
createuser -SDRPE demo

# Create the database
createdb demo -O demo

# Import schema
psql -f myfile.sql demo

# Grant Permissions

Can somebody tell me the best way to go about this? Currently, I am writing all of this inside of a Rake task, so I could technically retrieve all of the tables in the database and iterate through them, but it feels like an awful clunky solution. It seems like I am missing something. Any thoughts/comments/links would be greatly appreciated.

Edit 1: As a quick note, all of these commands are being run as root which is a superuser in the database with the IDENT SAMEUSER authentication specified in pg_hba.conf in case that makes any difference.


Can you try

psql dbname username < filename.sql

So you would use

psql demo demo < myfile.sql

I am not sure where your sql file is going because you are not specifying the db in your script.

| improve this answer | |
  • Updated code to show that it is going into the demo database. Does it matter what user I use to import it? – Topher Fangio Nov 11 '09 at 14:44
  • I think the user is important. Try psql -f myfile.sql -d demo -u demo. This should run the commands as the demo user which should mean the objects created will be owned by the demo user. – JosephL Nov 11 '09 at 19:47
  • @JoesephL - Thanks, I'll give it a try and comment. – Topher Fangio Nov 11 '09 at 21:22
  • @JoesephL - Thanks, it worked! Now the only problem is that it requires me to type the password for every myfile.sql that I import (which is only 3 at the moment, but it's still a lot of user interaction). Any idea how to change that? – Topher Fangio Nov 11 '09 at 21:30
  • 1
    You could use a .pgpass file in your users home dirctory. Not sure how this would work as you are running the script as root but connecting as demo using ident. I would try putting the file in the demo users home directory. See postgresql.org/docs/8.1/interactive/libpq-pgpass.html – JosephL Nov 12 '09 at 1:12

Some examples might be:

pretty much everything: grant all privileges on database demo to demo;

grant some simple selects: grant select on table foo to demo;

more than select: grant select, insert, update, delete on table foo to demo;

More info here: http://www.postgresql.org/docs/8.3/static/sql-grant.html

| improve this answer | |
  • Thanks, I think I tried this, but I'll try again and come back to comment/select an answer. – Topher Fangio Nov 11 '09 at 14:42
  • 1
    I tried grant all privileges on database demo to demo; but I am still getting ERROR: permission denied for relation whatever. I am running 8.3. – Topher Fangio Nov 11 '09 at 15:06
  • try as postgres (instead of your superuser) in case your superuser isn't what you think it is :) – user6373 Nov 11 '09 at 16:25
  • @ironchef - Yeah, that thought crossed my mind; attempted it, still the same problem. – Topher Fangio Nov 11 '09 at 19:26

If all you want to do is grant the user 'demo' the ability to read/write/modify the database 'demo', you are already done, because the owner of the database has all necessary privileges by default.

If you need more specific privileges for more than a single user, you can CREATE USER a new user and GRANT privileges to one or more objects.

For non-local access to the database I strongly recommend adjusting your pg_hba.conf

| improve this answer | |
  • Unfortunately, as my question attempted to state, what I have done is NOT granting permissions on the demo table for the demo user. I can't do a simple select statement on the table until I have executed grant all on table_name to demo. – Topher Fangio Nov 11 '09 at 14:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.