5

I have a user that I need to allow SELECT access to a database. I've followed all the instructions I've found scattered all over SO and I can't get anything to work.

I've tried all the suggested solutions to all of the following links:

How do you create a read-only user in PostgreSQL?

ERROR: permission denied for relation tablename on Postgres while trying a SELECT as a readonly user

Permission denied for relation

...and many more references to the PSQL docs, blogs and user groups. Nothing is working.

Here are the commands I recently used as the postgres user to grant privileges:

postgres@dev:~$ psql
psql (9.1.9)
Type "help" for help.

postgres=# grant usage on SCHEMA public to proton_read;
GRANT
postgres=# grant select on all tables in schema public to proton_read;
GRANT
postgres=# alter default privileges in schema public grant select on tables to proton_read;
ALTER DEFAULT PRIVILEGES

Here are the commands used as the read only user:

proton_read@dev:~$ psql proton
psql (9.1.9)
Type "help" for help.

proton=> select * from leads_lead limit 1;
ERROR:  permission denied for relation leads_lead

Here's the kicker, I had this working once. I have a sample database dump that I'm using to teach basic SQL to business co-workers. We are covering simple commands such as SELECT, ORDER BY, LIMIT and JOIN. I had an old dump and was able to grant read only access. I dropped the database and did a pg_restore with a newer version of the database and now I cannot re-grant the SELECT access.

Any help would be greatly appreciated.

Community
  • 1
  • 1
Rico
  • 5,692
  • 8
  • 46
  • 63

1 Answers1

7

In PostgreSQL, every connection is to a particular database, and nearly everything you run has effect only within that one database. (One exception is that creation of users/roles themselves is global to the entire "cluster", i.e. running Postgres server.)

In the psql commands you show, you are connecting the first time without specifying a database (psql), which connects you to a database named after the current system user, which is postgres. This is shown at the beginning of the psql prompt: postgres=# (the # shows that you are connected as a supersuser).

It is therefore only this database that is being changed by your GRANT statements.

Your second example uses the correct command to connect to a specific database, psql proton, and you can see that the prompt reflects this: proton=> (with the > replacing the #, since you are not connected as a superuser).

So all you need to do is run psql proton as your postgres user, and run your GRANT statements as superuser on that specific database. Your prompt will read proton=# to show you are in the right place.

IMSoP
  • 89,526
  • 13
  • 117
  • 169