0

Is it possible to restrict user access to a postgresql database by specifying a database, rather than a table?

I understand that the line:

GRANT ALL ON tableName TO joeuser

enables this user to access this table and do anything they want. However I want to allow a user access to the database databaseName that contains tableName (and all tables within databaseName), but not all databases on my postgresql server.

Improve this question

3 Answers 3

Reset to default
2

Not really, no. Granting privileges to a database:

GRANT ALL ON DATABASE databaseName TO joeuser;

does not automatically grant privileges on objects in the database; and granting privileges on every object that's currently in the database won't automatically grant privileges on any future objects that might be created.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thanks @ruakh. I want the user to be able to create tables also; so I suppose I have to allow them full access to the server.
@celenius: Actually, the ability to create tables probably won't be a problem for you; for that, you just need to give joeuser the CREATE privilege on a schema, and then (s)he can create tables on that schema. The problem is just with giving him/her access to tables (and schemata) created by other users.
By the way, you may find postgresql.org/docs/8.4/static/sql-grant.html helpful, whatever approach you take.
Does that mean that I need to create a new schema, other than public? At the moment I'm just using the default setting.
1

It's not clear exactly what you want. It might be that you're trying to find a single SQL statement that handles all your privileges now and forever more. SQL privileges generally don't work that way.

Depending on the version, you can control connection to the database in two ways.

You can change the default privileges for tables, views, sequences, and functions. (Version 9.0+)

ALTER DEFAULT PRIVILEGES is a PostgreSQL extension to SQL.

Improve this answer

2 Comments

I'm not necessarily looking for one SQL statement to do this. What I want is to allow a user to have full permissions on one database on a server, and not be able to edit any of the other databases on that server. I'm not able to control it via pg_hba.conf as I can't control for the ip address.
To get full permissions on tables, views, sequences, and functions, you can create a new database, change the default permissions, disconnect the old database from all users, load all its objects into the new database, rename the old database (drop it later), and rename the new one. You might need to REVOKE CONNECT on the other databases. There are probably several "gotchas" in that, since I'm suggesting the idea without testing it. Do test it yourself, and let us know how that works.
0

Try this:

GRANT SELECT ON DataBase.TableName TO User;
Improve this answer

1 Comment

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.