Обсуждение: automatical grant - role membership
Hi,
I am trying to avoid neediness of manual settings of privileges for new tables and views for roles in the same membership.Let's say I have one DB schema (myschema) and two DB users (roles: role1, role2). "Role1" and "role2" are in mebership of role "mygroup".
I want my DB to work like this:
"role1" create some table in "myschema" and is the owner of this table. I want "role2" to automatically have all privileges on this table.
I was trying to use ALTER DEFAULT PRIVILEGES, but I still don't have what I need.
#
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT ALL ON TABLES TO mygroup;
#
Please, am I missing something? I am using PostgreSQL 9.1
Thanks a lot.
Petr
On Wed, Nov 13, 2013 at 4:26 PM, Petr Suk <petrxsuk@gmail.com> wrote: > Hi, > I am trying to avoid neediness of manual settings of privileges for new > tables and views for roles in the same membership. > Let's say I have one DB schema (myschema) and two DB users (roles: role1, > role2). "Role1" and "role2" are in mebership of role "mygroup". > I want my DB to work like this: > "role1" create some table in "myschema" and is the owner of this table. I > want "role2" to automatically have all privileges on this table. Making role2 inheriting from role1? Luca
Thanks Luca.
I did some more testing and the result is that ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT ALL ON TABLES mygroup; works fine. The only thing I had to do is to run this SQL at first when logged as 'role1' and then again as 'role2'. Previously I run it as 'postgres' role and that is why it was not working.Thanks again.
2013/11/14 Luca Ferrari <fluca1978@infinito.it>
On Wed, Nov 13, 2013 at 4:26 PM, Petr Suk <petrxsuk@gmail.com> wrote:Making role2 inheriting from role1?
> Hi,
> I am trying to avoid neediness of manual settings of privileges for new
> tables and views for roles in the same membership.
> Let's say I have one DB schema (myschema) and two DB users (roles: role1,
> role2). "Role1" and "role2" are in mebership of role "mygroup".
> I want my DB to work like this:
> "role1" create some table in "myschema" and is the owner of this table. I
> want "role2" to automatically have all privileges on this table.
Luca
How can we find out if a particular sequence exists ? The idea is to check if sequence first and if it does not exist then create it...the goal is to do this when we deploy the application war...
thanks
tg
I tried this from pg_admin, but I get a syntax error (unexpected character)
IF EXISTS (SELECT 0 FROM pg_class
WHERE relkind = 'S'
AND oid::regclass::text = 'public.' || quote_ident('hibernate_sequence'))
THEN
RAISE EXCEPTION 'sequence public.% already exists!', 'hibernate_sequence
ENF IF;
WHERE relkind = 'S'
AND oid::regclass::text = 'public.' || quote_ident('hibernate_sequence'))
THEN
RAISE EXCEPTION 'sequence public.% already exists!', 'hibernate_sequence
ENF IF;
Thanks.
From: Thara Vadakkeveedu <tharagv@yahoo.com>
To: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Sent: Friday, November 15, 2013 2:30 PM
Subject: Re: [ADMIN] checking if sequence exists
To: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Sent: Friday, November 15, 2013 2:30 PM
Subject: Re: [ADMIN] checking if sequence exists
How can we find out if a particular sequence exists ? The idea is to check if sequence first and if it does not exist then create it...the goal is to do this when we deploy the application war...
thanks
tg
On 2013-11-15 14:30, Thara Vadakkeveedu wrote:
In psql if you set ECHO_HIDDEN you can get it to dump out its introspection queries, like the one for \ds, which gives you a list of sequences.How can we find out if a particular sequence exists ? The idea is to check if sequence first and if it does not exist then create it...the goal is to do this when we deploy the application war...thankstg
For instance,
These steps:
\set ECHO_HIDDEN 1
\ds
Yield a query like this:
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('S','s','')
AND n.nspname !~ '^pg_toast'
AND n.nspname = '<schemaname>'
and c.relname = '<sequencename>'
ORDER BY 1,2
;
\d <schema_name>.<table_name> will show you columns, their types and the modifiers. If you look in the modifiers section, you will be able to see existing sequences.
On Fri, Nov 15, 2013 at 2:30 PM, Thara Vadakkeveedu <tharagv@yahoo.com> wrote:
How can we find out if a particular sequence exists ? The idea is to check if sequence first and if it does not exist then create it...the goal is to do this when we deploy the application war...thankstg
The name of the sequence is hibernate_sequence. I already checked in the pg_class table under catalogs..
Thanks,
From: Payal Singh <payal@omniti.com>
To: Thara Vadakkeveedu <tharagv@yahoo.com>
Cc: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Sent: Friday, November 15, 2013 2:38 PM
Subject: Re: [ADMIN] checking if sequence exists
To: Thara Vadakkeveedu <tharagv@yahoo.com>
Cc: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Sent: Friday, November 15, 2013 2:38 PM
Subject: Re: [ADMIN] checking if sequence exists
\d <schema_name>.<table_name> will show you columns, their types and the modifiers. If you look in the modifiers section, you will be able to see existing sequences.
On Fri, Nov 15, 2013 at 2:30 PM, Thara Vadakkeveedu <tharagv@yahoo.com> wrote:
How can we find out if a particular sequence exists ? The idea is to check if sequence first and if it does not exist then create it...the goal is to do this when we deploy the application war...thankstg
Thara Vadakkeveedu <tharagv@yahoo.com> wrote: > I tried this from pg_admin, but I get a syntax error (unexpected > character) > > IF EXISTS (SELECT 0 FROM pg_class > WHERE relkind = 'S' > AND oid::regclass::text = 'public.' || quote_ident('hibernate_sequence')) > THEN > RAISE EXCEPTION 'sequence public.% already exists!', 'hibernate_sequence > ENF IF; First, is this code in a plpgsql context (like a function definition or a DO command)? If not, that IF is not going to work. If it *is*, you seem to have a typo where you meant END IF. Also note that if you force the oid to text for the comparison, it is both more fragile and slower than if you convert the text to regclass for the comparison. explain analyze SELECT 0 FROM pg_class WHERE relkind = 'S' AND oid::regclass::text = 'public.' || quote_ident('hibernate_sequence'); QUERY PLAN ------------------------------------------------------------------------------------------------------- Seq Scan on pg_class (cost=0.00..13.86 rows=1 width=0) (actual time=0.197..0.197 rows=0 loops=1) Filter: ((relkind = 'S'::"char") AND (((oid)::regclass)::text = 'public.hibernate_sequence'::text)) Rows Removed by Filter: 295 Total runtime: 0.221 ms (4 rows) explain analyze SELECT 0 FROM pg_class WHERE relkind = 'S' AND oid = ('public.' || quote_ident('hibernate_sequence'))::regclass; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- Index Scan using pg_class_oid_index on pg_class (cost=0.28..8.29 rows=1 width=0) (actual time=0.031..0.032 rows=1 loops=1) Index Cond: (oid = (('public.hibernate_sequence'::text)::regclass)::oid) Filter: (relkind = 'S'::"char") Total runtime: 0.062 ms (4 rows) Notice that there was a table scan, which was slower, and the record was not found because the cast of the oid to regclass and then text did not include the 'public." part. The other way used an index and matched as you probably intended. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
"First, is this code in a plpgsql contex"?
No, that is my problem.
Does it have to be inside a Create function block or can just wrapping the if with a BEGIN END; suffice ?
Thanks.
From: Kevin Grittner <kgrittn@ymail.com>
To: Thara Vadakkeveedu <tharagv@yahoo.com>; "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Sent: Friday, November 15, 2013 3:06 PM
Subject: Re: [ADMIN] checking if sequence exists
To: Thara Vadakkeveedu <tharagv@yahoo.com>; "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Sent: Friday, November 15, 2013 3:06 PM
Subject: Re: [ADMIN] checking if sequence exists
Thara Vadakkeveedu <tharagv@yahoo.com> wrote:
> I tried this from pg_admin, but I get a syntax error (unexpected
> character)
>
> IF EXISTS (SELECT 0 FROM pg_class
> WHERE relkind = 'S'
> AND oid::regclass::text = 'public.' || quote_ident('hibernate_sequence'))
> THEN
> RAISE EXCEPTION 'sequence public.% already exists!', 'hibernate_sequence
> ENF IF;
First, is this code in a plpgsql context (like a function
definition or a DO command)? If not, that IF is not going to work.
If it *is*, you seem to have a typo where you meant END IF.
Also note that if you force the oid to text for the comparison, it
is both more fragile and slower than if you convert the text to
regclass for the comparison.
explain analyze
SELECT 0 FROM pg_class
WHERE relkind = 'S'
AND oid::regclass::text = 'public.' || quote_ident('hibernate_sequence');
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Seq Scan on pg_class (cost=0.00..13.86 rows=1 width=0) (actual time=0.197..0.197 rows=0 loops=1)
Filter: ((relkind = 'S'::"char") AND (((oid)::regclass)::text = 'public.hibernate_sequence'::text))
Rows Removed by Filter: 295
Total runtime: 0.221 ms
(4 rows)
explain analyze
SELECT 0 FROM pg_class
WHERE relkind = 'S'
AND oid = ('public.' || quote_ident('hibernate_sequence'))::regclass;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Index Scan using pg_class_oid_index on pg_class (cost=0.28..8.29 rows=1 width=0) (actual time=0.031..0.032 rows=1 loops=1)
Index Cond: (oid = (('public.hibernate_sequence'::text)::regclass)::oid)
Filter: (relkind = 'S'::"char")
Total runtime: 0.062 ms
(4 rows)
Notice that there was a table scan, which was slower, and the
record was not found because the cast of the oid to regclass and
then text did not include the 'public." part. The other way used
an index and matched as you probably intended.
--
Kevin Grittner
EDB: http://www.enterprisedb.com/
The Enterprise PostgreSQL Company
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
On 2013-11-15 16:09, Thara Vadakkeveedu wrote:
Control structures like if statements don't exist in straight sql - you need a procedural language like pl/pgsql for that. Wrapping sql statements in begin/end only affects the transactional context of the statements, it does not cause them to be interpreted as pl/pgsql. You can either create a function or you can use a "DO" block, which is sometimes what I use for deploy scripts [http://www.postgresql.org/docs/9.3/static/sql-do.html]."First, is this code in a plpgsql contex"?No, that is my problem.Does it have to be inside a Create function block or can just wrapping the if with a BEGIN END; suffice ?
By itself this sql works:
SELECT 0 FROM pg_class
WHERE relkind = 'S'
AND oid = ('public.' || quote_ident('hibernate_sequence'))::regclass;
WHERE relkind = 'S'
AND oid = ('public.' || quote_ident('hibernate_sequence'))::regclass;
However when I create a function for it and run it I see an error
create function chk_sequence() returns integer as $$
BEGIN
IF EXISTS (SELECT 1 FROM pg_class
WHERE relkind = 'S'
AND oid = ('public.' || quote_ident('hibernate_sequence')))::regclass
BEGIN
IF EXISTS (SELECT 1 FROM pg_class
WHERE relkind = 'S'
AND oid = ('public.' || quote_ident('hibernate_sequence')))::regclass
THEN
return 1;
ELSE
return 0;
END IF;
return 1;
ELSE
return 0;
END IF;
END;
$$ language plpgsql;
$$ language plpgsql;
select chk_sequence();
ERROR: operator does not exist: oid = text
LINE 3: AND oid = ('public.' || quote_ident('hibernate_...
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
QUERY: SELECT EXISTS (SELECT 1 FROM pg_class
WHERE relkind = 'S'
AND oid = ('public.' || quote_ident('hibernate_sequence')))::regclass
ERROR: operator does not exist: oid = text
LINE 3: AND oid = ('public.' || quote_ident('hibernate_...
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
QUERY: SELECT EXISTS (SELECT 1 FROM pg_class
WHERE relkind = 'S'
AND oid = ('public.' || quote_ident('hibernate_sequence')))::regclass
Thanks.
From: Elliot <yields.falsehood@gmail.com>
To: Thara Vadakkeveedu <tharagv@yahoo.com>; Kevin Grittner <kgrittn@ymail.com>; "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Sent: Friday, November 15, 2013 4:13 PM
Subject: Re: [ADMIN] checking if sequence exists
To: Thara Vadakkeveedu <tharagv@yahoo.com>; Kevin Grittner <kgrittn@ymail.com>; "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Sent: Friday, November 15, 2013 4:13 PM
Subject: Re: [ADMIN] checking if sequence exists
On 2013-11-15 16:09, Thara Vadakkeveedu wrote:
Control structures like if statements don't exist in straight sql - you need a procedural language like pl/pgsql for that. Wrapping sql statements in begin/end only affects the transactional context of the statements, it does not cause them to be interpreted as pl/pgsql. You can either create a function or you can use a "DO" block, which is sometimes what I use for deploy scripts [http://www.postgresql.org/docs/9.3/static/sql-do.html"First, is this code in a plpgsql contex"?No, that is my problem.Does it have to be inside a Create function block or can just wrapping the if with a BEGIN END; suffice ?
].
On 2013-11-15 17:56, Thara Vadakkeveedu wrote:
You've got two different queries there. In the first example you're casting the string public.hibernate_sequence to regclass. In the second you've got the parentheses capturing the regclass cast around the entire query.By itself this sql works:SELECT 0 FROM pg_class
WHERE relkind = 'S'
AND oid = ('public.' || quote_ident('hibernate_sequence'))::regclass;However when I create a function for it and run it I see an errorcreate function chk_sequence() returns integer as $$
BEGIN
IF EXISTS (SELECT 1 FROM pg_class
WHERE relkind = 'S'
AND oid = ('public.' || quote_ident('hibernate_sequence')))::regclassTHEN
return 1;
ELSE
return 0;
END IF;END;
$$ language plpgsql;select chk_sequence();
ERROR: operator does not exist: oid = text
LINE 3: AND oid = ('public.' || quote_ident('hibernate_...
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
QUERY: SELECT EXISTS (SELECT 1 FROM pg_class
WHERE relkind = 'S'
AND oid = ('public.' || quote_ident('hibernate_sequence')))::regclassThanks.
However, you probably don't want to use this query anyway as the regclass cast will fail with an exception if the sequence doesn't exist (meaning you'd have to wrap it in an exception catching block instead of an in-else block). This uses the query I sent out in an early response:
create function chk_sequence() returns integer as $$
BEGIN
IF EXISTS (SELECT 1
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('S','s','')
AND n.nspname !~ '^pg_toast'
AND n.nspname = 'public'
and c.relname = 'hibernate_sequence')
THEN
return 1;
ELSE
return 0;
END IF;
END;
$$ language plpgsql;
Thara,
Here are your functions:
bob=> create sequence foo;
CREATE SEQUENCE
bob=> create or replace function public.chk_sequence(namespace name, seq_name name)
bob-> returns int
bob-> language sql
bob-> as $$
bob$> select count(*)::int as sequence_exists
bob$> from pg_class c, pg_namespace n
bob$> where c.relnamespace = n.oid
bob$> and c.relkind in ('S', 's', '')
bob$> and pg_table_is_visible(c.oid)
bob$> and n.nspname = $1
bob$> and c.relname = $2;
bob$> $$;
CREATE FUNCTION
bob=> select chk_sequence('public', 'foo');
chk_sequence
--------------
1
(1 row)
bob=> select chk_sequence('public', 'bar');
chk_sequence
--------------
0
(1 row)
bob=> ----
bob=> -- when you get tired of typing 'public' use this version
bob=> ----
bob=> create or replace function chk_sequence(seq_name name)
bob-> returns int
bob-> language sql
bob-> as $$
bob$> select chk_sequence('public', $1);
bob$> $$;
CREATE FUNCTION
bob=> select chk_sequence('foo');
chk_sequence
--------------
1
(1 row)
bob=> select chk_sequence('bar');
chk_sequence
--------------
0
(1 row)
On Friday, November 15, 2013 5:39 PM, Thara Vadakkeveedu <tharagv@yahoo.com> wrote:
By itself this sql works:
SELECT 0 FROM pg_class
WHERE relkind = 'S'
AND oid = ('public.' || quote_ident('hibernate_sequence'))::regclass;
WHERE relkind = 'S'
AND oid = ('public.' || quote_ident('hibernate_sequence'))::regclass;
However when I create a function for it and run it I see an error
create function chk_sequence() returns integer as $$
BEGIN
IF EXISTS (SELECT 1 FROM pg_class
WHERE relkind = 'S'
AND oid = ('public.' || quote_ident('hibernate_sequence')))::regclass
BEGIN
IF EXISTS (SELECT 1 FROM pg_class
WHERE relkind = 'S'
AND oid = ('public.' || quote_ident('hibernate_sequence')))::regclass
THEN
return 1;
ELSE
return 0;
END IF;
return 1;
ELSE
return 0;
END IF;
END;
$$ language plpgsql;
$$ language plpgsql;
select chk_sequence();
ERROR: operator does not exist: oid = text
LINE 3: AND oid = ('public.' || quote_ident('hibernate_...
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
QUERY: SELECT EXISTS (SELECT 1 FROM pg_class
WHERE relkind = 'S'
AND oid = ('public.' || quote_ident('hibernate_sequence')))::regclass
ERROR: operator does not exist: oid = text
LINE 3: AND oid = ('public.' || quote_ident('hibernate_...
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
QUERY: SELECT EXISTS (SELECT 1 FROM pg_class
WHERE relkind = 'S'
AND oid = ('public.' || quote_ident('hibernate_sequence')))::regclass
Thanks.
From: Elliot <yields.falsehood@gmail.com>
To: Thara Vadakkeveedu <tharagv@yahoo.com>; Kevin Grittner <kgrittn@ymail.com>; "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Sent: Friday, November 15, 2013 4:13 PM
Subject: Re: [ADMIN] checking if sequence exists
To: Thara Vadakkeveedu <tharagv@yahoo.com>; Kevin Grittner <kgrittn@ymail.com>; "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Sent: Friday, November 15, 2013 4:13 PM
Subject: Re: [ADMIN] checking if sequence exists
On 2013-11-15 16:09, Thara Vadakkeveedu wrote:
Control structures like if statements don't exist in straight sql - you need a procedural language like pl/pgsql for that. Wrapping sql statements in begin/end only affects the transactional context of the statements, it does not cause them to be interpreted as pl/pgsql. You can either create a function or you can use a "DO" block, which is sometimes what I use for deploy scripts [http://www.postgresql.org/docs/9.3/static/sql-do.html"First, is this code in a plpgsql contex"?No, that is my problem.Does it have to be inside a Create function block or can just wrapping the if with a BEGIN END; suffice ?
].
Thara Vadakkeveedu <tharagv@yahoo.com> wrote: > By itself this sql works: > SELECT 0 FROM pg_class > WHERE relkind = 'S' > AND oid = ('public.' || quote_ident('hibernate_sequence'))::regclass; > > However when I create a function for it and run it I see an error > > create function chk_sequence() returns integer as $$ > BEGIN > IF EXISTS (SELECT 1 FROM pg_class > WHERE relkind = 'S' > AND oid = ('public.' || quote_ident('hibernate_sequence')))::regclass > THEN > return 1; > ELSE > return 0; > END IF; > END; > $$ language plpgsql; Move the cast to regclass inside one level of parentheses. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company