Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PGsql : Grant every single right to a user on a schema

I can't figure out how to give every single right to a specific user, I want a user to have every single right on a schema:

  • inserts, deletes, updates, selects, ... on existing tables

I have tried doing :

    GRANT ALL PRIVILEGES ON SCHEMA schema to "user";
    GRANT ALL ON SCHEMA schema to "local_518561";
    GRANT ALL PRIVILEGES ON table schema.table to "user";
    GRANT ALL ON table schema.table to "user";

The querys return succesfull, but every time I use the other user I get insuffiecent permissions error.

like image 696
Lucas Kauffman Avatar asked Jul 23 '11 08:07

Lucas Kauffman


People also ask

How do I grant privileges to schema?

The owner of an explicitly created schema automatically receives CREATEIN privilege. An implicitly created schema has CREATEIN privilege automatically granted to PUBLIC. Grants the privilege to drop all objects in the schema. The owner of an explicitly created schema automatically receives DROPIN privilege.

What is grant all on schema?

ALL or ALL PRIVILEGES. Grants one or more privileges. The privileges granted are all those grantable privileges that the authorization ID of the statement has on the specified schemas. Note that granting ALL PRIVILEGES on a schema is not the same as granting the system authority of *ALL.


2 Answers

GRANT ALL PRIVILEGES ON                  SCHEMA schema_name TO role_name;
GRANT ALL PRIVILEGES ON ALL TABLES    IN SCHEMA schema_name TO role_name;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA schema_name TO role_name;
like image 174
Clint Pachl Avatar answered Oct 16 '22 17:10

Clint Pachl


If you are using a version of PostgreSQL < 9, you can use the following store procedures to manage permissions of tables and sequences:

CREATE OR REPLACE FUNCTION grantTablesOfSchema (user VARCHAR,
   permissions VARCHAR, schema VARCHAR) RETURNS VARCHAR AS
$body$
DECLARE
  regActual   RECORD;
  numTables   INTEGER;
BEGIN

   numTables := 0;

   FOR regActual IN
      SELECT tablename FROM pg_tables WHERE schemaname = schema
   LOOP
      numTables := numTables + 1;

      EXECUTE 'GRANT ' || permissions || ' ON ' || schema || '.' || regActual.tablename || ' TO ' || user;
   END LOOP;

   RETURN 'Tables: ' || numTables::VARCHAR;
END;
$body$
LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION grantSequencesOfSchema (user VARCHAR,
   permissions VARCHAR, database VARCHAR, schema VARCHAR) RETURNS VARCHAR AS
$body$
DECLARE
   regActual       RECORD;
   numSequences    INTEGER;
BEGIN

   numSequences := 0;

   FOR regActual IN
      SELECT sequence_catalog, sequence_schema, sequence_name 
      FROM information_schema.sequences 
      WHERE sequence_catalog = database AND sequence_schema = schema
   LOOP
      numSequences := numSequences + 1;

      EXECUTE 'GRANT ' || permissions || ' ON ' || schema || '.' || regActual.sequence_name || ' TO ' || user;
   END LOOP;

   RETURN 'Sequences: ' || numSequences::VARCHAR;
END;
$body$
LANGUAGE 'plpgsql';

And a example of use:

CREATE USER user1 WITH PASSWORD 'user1@user1?user1';
GRANT CONNECT ON DATABASE database1 TO user1;
GRANT USAGE ON SCHEMA schema1 TO user1;

SELECT * FROM grantTablesOfSchema ('user1', 'SELECT, UPDATE, INSERT, DELETE', 'schema1');

SELECT * FROM grantSequencesOfSchema ('user1', 'ALL', 'database1', 'schema1');

If, on the contrary your version of PostgreSQL is >= 9:

GRANT ALL ON ALL SEQUENCES IN SCHEMA schema1 TO user1;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA schema1 TO user1;
like image 28
doctore Avatar answered Oct 16 '22 16:10

doctore