Linked Questions
15 questions linked to/from Granting access to all tables for a user
36
votes
3
answers
154k
views
Permission denied in Postgres [duplicate]
I added the user myuserto Postgres.
Then I added the database mydatabase in the pgAdmin III GUI and restored from a backup file. So the owner of mydatabase is the superuser postgres.
Then I tried to ...
- 661
1
vote
0
answers
72
views
How to give select privileges to database in postgreSQL [duplicate]
How can we give select access at database level in postgreSQL.
At present I had created new user with select access on all tables in a schema using below syntax.
grant select on all tables in ...
- 967
34
votes
1
answer
24k
views
PostgreSQL roles versus users, grant permissions
I'm currently learning the differences between PostgreSQL and MySQL as I've got a new project and I also intend to migrate my existing software from MySQL to PostgreSQL. I've actually started creating ...
- 655
14
votes
3
answers
12k
views
PostgreSQL 9.3.13, How do I refresh Materialised Views with different users?
[I think the root cause of this problem is me not understanding permissions and privileges...]
So, to set the stage, the set up I have is a DB, call it MyDb.
I have two users, spu1 and u1, spu1 is a ...
- 319
6
votes
2
answers
21k
views
GRANT ALL ON ALL TABLES IN SCHEMA does not allow user to see tables
I have a pretty simple script to create a new database and to create a new user to access this database. This is run by the default postgres user. After granting access to the database and schema to ...
- 63
5
votes
1
answer
5k
views
Can't alter default privileges
I followed the steps below.
I installed PostgreSQL.
I logged in as postgres via (sudo -i -u postgres)
I created new user new_user via shell script createuser --interactive
I granted that user ...
- 353
5
votes
1
answer
8k
views
Create a read-write user and a read-only user
I'm trying to do what I think is a common setup for a PostgreSQL database on a RDS instance. I want to have:
Either on public schema, or on a custom schema I don't especially care
A read-write user
A ...
- 53
6
votes
1
answer
7k
views
How do I GRANT for all tables across all schemas
I want to
GRANT SELECT
ON ALL TABLES
TO foo;
But postgres tells me I have a syntax error at "TO". It expects a GRANT like:
GRANT SELECT
ON ALL TABLES
IN SCHEMA public
TO foo;
...
- 161
2
votes
1
answer
1k
views
What is the equivalent of SQL Server's db_datareader/db_datawriter in PostgreSQL?
I'm porting an application from SQL Server to PostgreSQL. Most of this is simple as the application only really does simple selects, inserts and updates. However I need to create a user to access the ...
- 720
5
votes
1
answer
4k
views
Can't create read-write Postgresql user
I'm trying to create two users in a Postgres database—one with read-write access to all tables in two schemas, and one with read-write-create (i.e., able to make DDL changes) to the same schemas.
I ...
- 169
1
vote
1
answer
204
views
PostgreSQL - automating database grants [closed]
We're managing about 200 instances of postgres on Amazon RDS. The problem that we're constantly experiencing is that our "DBA" accounts can't access stuff. Basic stuff. Tables, views, etc.
Is there ...
- 13
3
votes
1
answer
89
views
Role, whose name doesn't match database name, can't access the database
Role app can access database app. But role app2 can't. I don't see any specific privileges for role app, and it's not an owner according to \l:
app2=> \l
Name | Owner | Encoding | ...
- 299
2
votes
1
answer
44
views
Enforce conditions on queries
Currently I have a simple schema, a sites table and an items table.
Sites own many items, items has a siteId column to store the reference of the site which owns it.
I'd like to be able to enforce a ...
- 165
0
votes
1
answer
55
views
Create, Drop permissions
I would like to grant create, select, update, drop permissions only for user USER_NAME for a particular schema SCHEMA_NAME. It is possible?
- 111
0
votes
0
answers
14
views
Postgres permission for new objects [duplicate]
Postgres version: 11
I've got 2 roles - one used for read and one for write on the same database.
CREATE ROLE reader WITH login;
CREATE ROLE writer WITH login;
CREATE DATABASE thedatabase;
GRANT ...
