Postgresql – Proper Way to Grant Permissions in PostgreSQL

database-administrationpermissionspostgresql

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.

Best Answer

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.