CODEWITHSUNDEEP
sqlpostgresqlpsqlprivilegessql-grant
Trip
Trip

Reputation: 27114

Why can I still not access any DB function in PSQL?

This is my init.sql file :

DROP DATABASE IF EXISTS my_data;
CREATE DATABASE my_data;

DROP USER IF EXISTS u;
CREATE USER u WITH PASSWORD 'secret';

GRANT ALL ON ALL TABLES IN SCHEMA public TO u;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO u;
GRANT ALL ON ALL FUNCTIONS IN SCHEMA public TO u;

\c my_data;

CREATE TABLE users (
  ID SERIAL PRIMARY KEY NOT NULL,
  email VARCHAR NOT NULL DEFAULT '',
  password VARCHAR NOT NULL DEFAULT '',
  active SMALLINT NOT NULL DEFAULT '1',
  created TIMESTAMP NOT NULL,
  modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  salt VARCHAR NOT NULL DEFAULT ''
);

Then if I :

psql -f init.sql

And..

psql -d my_data -U u

my_data=> select * from users;

ERROR:  permission denied for relation users

Why would this permission be denied if I just granted it?

More info

my_data=> \z users
                       Access privileges
 Schema | Name  | Type  | Access privileges | Column access privileges 
--------+-------+-------+-------------------+--------------------------
 public | users | table |                   | 
(1 row)

Upvotes: 1

Views: 45

Answers (1)

Sami Kuhmonen
Sami Kuhmonen

Reputation: 31153

You only gave permission on the schema, which is separate from the tables. If you want to give permissions on the tables also you can use

GRANT ALL ON ALL TABLES IN SCHEMA public TO u;

Note that if you have sequences or other objects they also need separate permissions.

This has to be set after the tables have been created since permissions are set for existing objects.

If you want to set default permissions for new objects you can use

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO u;

Upvotes: 3

Related Questions