How to give permissions to user groups in Postgresql?

Asked

Viewed 1,404 times

0

inserir a descrição da imagem aqui

*All registered users who are members of the "ACCESS SITE" will have the same permissions.

I created a group of users (Group Roles) in Postgresql called "SITE ACCESS" to put all users (Login Roles) of the sites and systems that will have permissions to search, insert, update and delete information. When creating a user for a particular system or site, I just want to say that it is part of the Group (Group Roles) "SITE ACCESS" and it inherits all the group permissions previously defined without giving permission every time it creates a new user.

How can I set up the Group Roles "ACCESS SITE" giving permission once and later only go allocating new users in the group?

sql database postgresql postgresql-administration

2 answers

1

Create the group access_site:

CREATE ROLE acesso_site NOINHERIT LOGIN PASSWORD '1';

Sets the permissions to it:

GRANT SELECT
  ON public.estoque TO acesso_site;

  GRANT INSERT
  ON public.estoque TO acesso_site;

GRANT USAGE
  ON public.estoque_codigo_seq TO acesso_site;

Create a user within the group acesso_site:

  CREATE ROLE usuario1 LOGIN PASSWORD '1' IN ROLE acesso_site;

I believe that’s all

  • 1

    In CREATE ROLE accesso_site NOINHERIT LOGIN PASSWORD '1'; why I need to put LOGIN PASSWORD '1 this code is not to create the group of users?

0

Try this:

CREATE ROLE acesso_site INHERIT NOLOGIN NOSUPERUSER;
CREATE USER usuario1 WITH LOGIN NOSUPERUSER PASSWORD 'xxx' INHERIT IN ROLE acesso_site;

Then just put the permissions on the objects like:

GRANT SELECT, UPDATE, DELETE, INSERT ON TABLE public.<TABELA> TO acesso_site;
GRANT USAGE ON SCHAME <SCHEMA> TO acesso_site;
GRANT EXECUTE ON FUNCTION <FUNCTION> TO acesso_site;

Browser other questions tagged sql database postgresql postgresql-administration

You are not signed in. Login or sign up in order to post.