CODEWITHSUNDEEP
sqldatabasepostgresqlsql-grant
Dumont Irou
Dumont Irou

Reputation: 33

How can I grant & revoke specific rights from Postgresql users?

I have been tasked to host HTML & PHP files of a website on one virtual machine and to set up a Postgresql database on another virtual machine.

I recently installed Postgresql and have been using the official Postgresql Documentation to learn how to create databases, create user and grant & revoke rights.

After having created a database named mfc_dst, I was ordered to create 4 differents users and this is where I have a problem :

-The first user has to be named admin and must be the only other user than the pre-existing user named postgres to have unlimited rights.

-The second (named cfc) and third user (named sec) must only have the SELECT and UPDATE privileges on all tables of the mfc_dst database.

-And the fourth/last user (named prof) must only be able to view a table named devoir from the database named mfc_dst.

To accomplish this, I used 2 different scripts :

CREATEandGRANT.sql

REVOKE ALL ON ALL TABLES IN SCHEMA public TO cfc;
REVOKE ALL ON ALL TABLES IN SCHEMA public TO sec;
REVOKE ALL ON ALL TABLES IN SCHEMA public TO prof;

GRANT CONNECT ON DATABASE mfc_dst TO admin;
GRANT CONNECT ON DATABASE mfc_dst TO cfc;
GRANT CONNECT ON DATABASE mfc_dst TO sec;
GRANT CONNECT ON DATABASE mfc_dst TO prof;

GRANT SELECT,UPDATE
ON ALL TABLES IN SCHEMA public
TO cfc;

GRANT SELECT,UPDATE
ON ALL TABLES IN SCHEMA public
TO sec;

GRANT SELECT ON devoir TO prof;

and this other one :

REVOKE.sql

REVOKE ALL ON TABLE professeur FROM PUBLIC;
REVOKE ALL ON TABLE reserver FROM PUBLIC;
REVOKE ALL ON TABLE salle FROM PUBLIC;
REVOKE ALL ON TABLE semaine FROM PUBLIC;
REVOKE ALL ON TABLE surveiller FROM PUBLIC;

Thanks to these 2 scripts, I was able to prevent the user named prof from seeing other tables, but the problem I have is that the users named cfc,sec and prof are still all three able to create tables and to drop them.

Is it possible to know how to prevent them from doing this and if possible, in the future, prevent newly created users from having such rights/privileges ?

Thank you in advance

Upvotes: 1

Views: 1427

Answers (1)

rd_nielsen
rd_nielsen

Reputation: 2459

All Postgres users implicitly are also automatically members of the public role, which grants them all permissions on the public schema. You can remove permissions from the public role with

revoke all on database mfc_dst from public;
revoke all on schema public from public;

Additionally, consider defining a new schema for your data tables, so that you can issue grant statements without having to deal further with the public role. If you do this, you can also set the search path to include your custom schema and to exclude the public schema.

Also, you might want to create a group role for the cfc and sec users and assign permissions to that role, rather than to the users individually. This will make future maintenance easier.

Upvotes: 1

Related Questions